none
Accumulate remaining work to reach milestone (MS Project 2010 Standard) RRS feed

  • Question

  • Hi,

    I'm wondering if there is an easy way to calculate the sum of remaining work of all tasks that are (direct or indirect) predecessors of a certain milestone. The idea is to create automatic reports for the remaining work to reach certain gates represented as milestones in the project plan. It will be ok if I had to export data to Excel first, but a MS Project-internal solution would be preferred, of course.

    Thanks for any hint!

    Daniel

    Tuesday, August 25, 2015 4:20 PM

Answers

  • Daniel,

    Not quite sure what you mean by "indirect" predecessors but if it means those tasks further back in the link chain, then what you want might be fairly easy to get or could be rather challenging.

    Let' look at the easy scenario. If all the tasks leading up to a particular milestone are under a single summary, then it is a simple matter of looking at the Remaining Work at summary level. If some of the tasks under the summary are NOT part of the predecessor chain leading up the the milestone then some means of separating those out would be necessary. It could be done by inspection or through the use of perhaps some grouping or filtering.

    If tasks in the link chain leading up the the milestone are sprinkled all over the plan then that gets a little more interesting. Again grouping may be one option but it is also very possible that VBA may be necessary to follow the link chain, flag the tasks and the create the remaining work total.

    To do the above, here is one approach that should do what you want.

    1. First go to: http://masamiki.com/project/macros.htm, and download Jack Dahlgren's Trace macro. It will be needed to provide a full predecessor link path for any given selected task

    2. Set up a custom number field with the following formula:

    IIf([Flag5] And ([% Complete]<100),[Remaining Work]/60,0)

    3. Set calculation for task and group summary rows as rollup to "Sum"

    4. Display the Project summary task

    5. Select your desired milestone and run Jack's macro for a predecessor trace. The total remaining work of all tasks in the link chain leading up to the milestone will appear in the Number1 field at the Project Summary level.

    The screen shots below illustrate the result of the above on a sample file. The first shot is the basic plan. Note that not all tasks feed into the milestone. The second shot is the result after running Jack's macro. Note the total remaining work value for the milestone in the Number1 field of the Project Summary Task.

    Hope this helps.

    John


    Tuesday, August 25, 2015 8:38 PM

All replies

  • Daniel,

    Not quite sure what you mean by "indirect" predecessors but if it means those tasks further back in the link chain, then what you want might be fairly easy to get or could be rather challenging.

    Let' look at the easy scenario. If all the tasks leading up to a particular milestone are under a single summary, then it is a simple matter of looking at the Remaining Work at summary level. If some of the tasks under the summary are NOT part of the predecessor chain leading up the the milestone then some means of separating those out would be necessary. It could be done by inspection or through the use of perhaps some grouping or filtering.

    If tasks in the link chain leading up the the milestone are sprinkled all over the plan then that gets a little more interesting. Again grouping may be one option but it is also very possible that VBA may be necessary to follow the link chain, flag the tasks and the create the remaining work total.

    To do the above, here is one approach that should do what you want.

    1. First go to: http://masamiki.com/project/macros.htm, and download Jack Dahlgren's Trace macro. It will be needed to provide a full predecessor link path for any given selected task

    2. Set up a custom number field with the following formula:

    IIf([Flag5] And ([% Complete]<100),[Remaining Work]/60,0)

    3. Set calculation for task and group summary rows as rollup to "Sum"

    4. Display the Project summary task

    5. Select your desired milestone and run Jack's macro for a predecessor trace. The total remaining work of all tasks in the link chain leading up to the milestone will appear in the Number1 field at the Project Summary level.

    The screen shots below illustrate the result of the above on a sample file. The first shot is the basic plan. Note that not all tasks feed into the milestone. The second shot is the result after running Jack's macro. Note the total remaining work value for the milestone in the Number1 field of the Project Summary Task.

    Hope this helps.

    John


    Tuesday, August 25, 2015 8:38 PM
  • Hi John,

    thanks very much for this solution! Yes, with indirect predecessors I meant any predecessors in the chain of tasks leading to a particular milestone.

    The macro and custom number field as you described worked fine for me :) I would have to run the macro for each milestone individually. If I would like to see the remaining work for all milestones in one view, indeed I could group all tasks or use a custom field to mark for each task to which next milestone (or process gate in my case) they contribute.

    Probably the latter could be automated, as well: For each task follow the successors and add the number of the lowest gate (Gate 1 to Gate n) where completion of the task is required to a custom text field of each task. Then group on that text field and show the sum of remaining work.

    Would be cool if MS Project could introduce an "Accumulated Remaining Work" field for each task as a calculation ;-)

    Thanks again!

    Daniel

    Wednesday, August 26, 2015 11:25 AM
  • Daniel,

    You're welcome and thanks for the feedback.

    Yes, Jack's macro could be tweaked to give you the data you desire without the need to run it multiple times. That's an exercise for the user.

    And yes, there are many things users have said, "it sure would be nice if. . .", and perhaps over time more features will be added to the application. Meanwhile however, I've always liked the fact the Project, and other, Microsoft applications have VBA. It has allowed users to create custom features to make the app do what is wanted/needed.

    John

    Wednesday, August 26, 2015 3:05 PM
  • Absolutely, this programming interface on top of the app is really useful :) and the exercise to modify the Trace macro really easy.

    Daniel


    Daniel Dietterle Program Manager Berlin area, Germany

    Wednesday, August 26, 2015 3:32 PM
  • Daniel,

    Super! It sounds like you are all set.

    A few weeks ago another user posted a question on how to modify Jack's macro to work with dynamic master files (i.e. trace back through subproject links). I gave him some guidance and he later reported that he had a working version which he shared with me. Project VBA can do some really cool stuff.

    John

    Wednesday, August 26, 2015 4:27 PM