none
MS Project 2007 - Help with custom field calculation for grouped by dates view RRS feed

  • Question

  • Hello 

    I am trying to creat a view that counts number of students in courses (while # Students is a custom field that is filled manually and courses are tasks).

    The view should be grouped by Quarters, Months, and weeks (Calculated custom Text fields)

    The weeks groups are displayed by the start day of each week.

    For example:

    The view counts how many students are in courses in a specific week/month/quarter.

    This is the group by definition:

    Those are the fields calculations:

    “WW_Start” field calculation:

    (Year([Start]) & "  W " & Format([Start],"WW") & " " & "   [" & Datevalue(ProjDateSub([Start],CStr(Weekday([Start])-1),"Standard")) & "]")

    “MM” field calculation:

    Year([Start]) & "  M " & Format([Start],"MM")

    Basically it works but it doesn't cover a situation where a course is more then a week.  

    Lets say that the duration of the task is 12 days, the task should appear on 3 different weeks groups.

    But what i did puts the tasks only in the group that shows the first week of the task.

    How can i change my calculations or my view so that in the second, and third week of the task it will also show the task and it's attributes.

    For example:

     - "UV5 ACM" task should also be in W 45 not just W 44.

    Anyone has an idea how to do that?




    Ofir Marco , MCTS P.Z. Projects

    Tuesday, March 4, 2014 12:23 PM

Answers

  • Ofir,

    I'm not a big fan of Visual Reports, it is not as customizable or flexible as VBA, and there have been performance issues with the visual reports feature. So yes, in my view VBA is the more viable solution.

    With a VBA solution, the data is all derived from Project so it is all stored in the Project database.

    You refer to an issue with dates. I'm not sure what that is, but it can be addressed in the VBA code.

    John

    Monday, March 10, 2014 3:22 PM

All replies

  • Sorry, I don't have Project 2007 around for testing so, this is more theoretical (not tested) than I would like.  The issue is that Project is only looking at the Start field in your formulas - it is not looking at the duration of the task nor the finish field.

    You can certainly calculate the week ending dates from the finish field - the challenge is going to be to create the group to see data correctly.  If you calculate week start through week end, you won't have the nice neat groups you currently show - it would create a group for tasks with different start and end dates.

    Given your scenario however - a question:  If a class is only 1 day long how are you counting the 1 student in the class?  Should 1 student in a 1 day class count the same as 1 student in a 5 day task?

    I can't make out your student counts.

    Tuesday, March 4, 2014 6:34 PM
    Moderator
  • Ofir,

    On first glance, I'm wondering what is the end goal. If it is to simply get a student count broken out by time periods, I'm thinking this might be a better done with Excel and not Project. I see dates and a discussion about breaking student count into various periods but I don't really see anything that relates to scheduling, which is what Project does. Maybe there is more to it than what is presented.

    For example, in the graphic the first two items ("SEMVision G6 IPTC, PDC") both have 15 days duration, 5 students, and a "work" content that is exactly the same. So how are those two different? And what exactly is the "work" content?

    As Julie indicates it may be possible to create the type of output you want, but without a little more context it is more difficult for us to offer a good solution.

    Just my thoughts.

    John

    Tuesday, March 4, 2014 10:44 PM
  • Hi

    First, thanks for the comment "nice neat groups" :)

    I think you understood correctly what is my issue.

    Second, about your question:  the class have the same number of students during the course period.

    It will have 10 students even if the duration is 1 day or 10 days.

    if you look at the basic gantt chart you will see a row like this:

    Task name        Duration        Num_Students     Resource names (Instructors)

    Course YYY       10d                10                        Instructor X

    it is an attribute on the task, not the assiment.

    When i group by dates (Weeks, months, quarters) or by another field (can be course type for example) i the students are summed in the task summary level for all tasks.

    Lets say i group by all the tasks (Classes) that are in the 1st quarter, and lets say for the example that there are 3:

    Task 1 -->  5 students

    Task 2 --> 10 students

    Task 3 --> 7 students

    Then, the group by summary should count:

    Quarter 1 - 2014:   22 (students)

    • Task 1 --> 5
    • Task 2 --> 10
    • Task 3 --> 7

    By the way, it doesn't matter if the version is project 2007,2010, 2013, the logic is the same.

    Hope it helps and i'll be glad to have some ideas or evan get examples for solution in a project file by e-mail.


    Ofir Marco , MCTS P.Z. Projects

    Thursday, March 6, 2014 8:09 AM
  • Hi John,

    I'll try to explain.

    There is scheduling, we assign instructors as resources for tasks and give them durations and dates, the goal is to manage the schedule for all resources in project and not to create to lists that needs to be handled (one by project and one by excel).

    So the users want everything to be done by project.

    But they need views and reports that gives them specific data and different cuts for criterias.

    What i asked is not the main goal it is another thing that they need to implement.

    They want to have a utilization that shows how much students they have in courses every week.

    My problem is that if a course starts on one week and ends on another then it counts it's students just on the first week but in reality they also exists in the second week.


    Ofir Marco , MCTS P.Z. Projects

    Thursday, March 6, 2014 8:23 AM
  • Ofir,

    Okay, I can see why you are using Project. If your main goal is to break out each week separately in your grouping view then you will need a formula that examines the time span of each task (i.e. class). This can be readily done using VBA but I don't see a way to do it with a formula in a custom field simply because grouping can only look at a single value in a single field, and you need multiple values in that field.

    I see two approaches. One is to break each task line into individual weeks so if a class is 3 weeks long, it would be entered as three separate task lines. Then you could use your existing formulas and get the grouping format you want. This is the approach to take if everything must be in Project. The second approach is to use VBA to break up each single task line into its weekly parts and then export the data to Excel for the report. The latter requires no change to your existing "schedule" but it does require a macro and pulls Excel into the mix as a reporting tool.

    That's my synopsis. If someone else has a clever way to do what you want, I hope they jump in, I'd be interested to see how they approach it.

    John

    Thursday, March 6, 2014 4:24 PM
  • Thanks John,

    Now i feel better with myself not able to solve it with a custom field formula :-)

    If you don't see a way to do that using a formula then i guess we will look into the option of seperating a class by weeks to different tasks.

    I'll have to ask the project owner if it is OK with her and see if it doesn't affect other views and visual reports that we have created already.

    I'm not sure if i'll succeed doing something with VBA.

    If anyone have a different approach i'll be happy to hear some ideas.

    Thank you all anyway.


    Ofir Marco , MCTS P.Z. Projects

    Sunday, March 9, 2014 7:31 AM
  • Ofir,

    You're welcome and thanks for the feedback. I'm quite sure that if you elect to restructure your tasks by breaking them into weekly elements then it IS going to impact existing views, reports, etc. which then begs the question, does your organization insist on keeping everything in Project and have to re-think the structure, or is the organization willing to use Excel as a reporting tool and leave the Project structure as it stands.

    John

    Sunday, March 9, 2014 4:49 PM
  • John,

    It depends.

    They are willing to use excel as a report tool if the data will be stored in one place (Project - in this case, Project Server 2007).

    I think that if they will have what they need in excel that they can export then it will probably be fine.

    The thing is i'm not sure if it will be different in visual reports for example.  The same dates issue will occur.

    And we also have a problem or a bug in using some templates that we have created in visual reports (We get some errors) and visual reports can't use all fields in all types of reports.

    Do you have another solution using excel with project as a database?

    Ofir


    Ofir Marco , MCTS P.Z. Projects

    Monday, March 10, 2014 8:27 AM
  • Ofir,

    I'm not a big fan of Visual Reports, it is not as customizable or flexible as VBA, and there have been performance issues with the visual reports feature. So yes, in my view VBA is the more viable solution.

    With a VBA solution, the data is all derived from Project so it is all stored in the Project database.

    You refer to an issue with dates. I'm not sure what that is, but it can be addressed in the VBA code.

    John

    Monday, March 10, 2014 3:22 PM
  • I an using visual reports as a solution that is cheaper then requesting a developement.

    I am not against VBA but my knowledge in VBA is not of an expert so i will have to learn it better in order to find a way to do that myself, don't know if i will succeed.

    I agree that visual reports is limited and that i can't use some fields in specific reports because each report uses a different cube, some of the fields can only be added to the pivot table as attributes.  it is very limited.

    I think we will go to a VBA or other tool developement solution, with our developers that understand how to use VBA with the database.

    (I'm working at a company as a contructor from an outsource cunsulting company so it will cost them more money, don't know if they'll agree, i was looking for a way to evoid it).

    The dates issue i was talking about is the same issue that i have within project views.

    Thanks for the responses.


    Ofir Marco , MCTS P.Z. Projects

    Wednesday, March 12, 2014 6:11 AM
  • Ofir,

    You're welcome and thanks for the feedback. Just for reference Project VBA does not run through the Project Server database, it must run through the Project application itself. I believe there are other programming methods for working with the server database (i.e. PSI), but I'm not familiar with their overall capability and whether they can help produce the type of reports you want.

    If you want to learn Project VBA I suggest Rod Gill's book on the subject. You can find out more at: http://www.project-systems.co.nz/project-vba-book/index.html

    John

    Wednesday, March 12, 2014 3:25 PM
  • Thanks.  I think i need VBa for a single project for this issue.

    I am familiar with Rod's site.

    I'm trying to learn VBA as much as i can within my available time.

    I guess i need to think about a logical solution that will seperate task's # students attribute to different weeks, besides knowing what functions and code lines to use.


    Ofir Marco , MCTS P.Z. Projects

    Wednesday, March 12, 2014 3:56 PM
  • Ofir,

    Okay but just being familiar with Rod's site doesn't put the book in your hands :)

    Your last paragraph sounds like you are now receptive to breaking each class into weekly pieces, in which case you won't need VBA.

    John

    Wednesday, March 12, 2014 5:04 PM
  • No, i don't mean to break it into weekly pieces, we checked that option and it's not good.

    What i meant was to break the view or report into weeks in which the # students will not be in only in the first week but it will be copied to all weeks that the class is performed.

    I will need a developement.. 


    Ofir Marco , MCTS P.Z. Projects

    Thursday, March 13, 2014 7:01 AM