none
Summarizing selected tasks RRS feed

  • Question

  • I have a schedule in MS Project 2010.  I need to produce reports showing summary rollups by custom criteria.  For example, I need to provide a rollup summary showing total duration for only those tasks related to different government regulations.  The regulations require that certain tasks be completed within a given number of days. Another example is a rollup summary showing total duration for only those tasks of interest to each stakeholder group, where the stakeholder group is a custom field.  I don't know where to start.  Should I be using filters, or "group by", or some manipulation of the Summary Task field?  If you can point me in the right direction, I'll experiment from there.  Thank you. 
    Monday, August 18, 2014 7:38 PM

Answers

  • Ingenious1,

    The term "total duration" is subjective. If indeed you want the SUM of durations for all tasks in the criteria group, then the grouping function with calculation for group and task summary lines set to "sum", will easily give you what you need. However, in Project, duration for a group of tasks is defined as the difference in working time between the start of the earliest task in the group and the finish of the latest task in the group. If that is what you really need then you can't get there from here with filtering, grouping or sorting or any combination thereof. You will either need to export the data to Excel and operate on it there or you could use a macro in Project.

    Herewith is a starting point macro. To use it, create an interactive filter to select each group, then run the macro. It will populate the Duration1 field of the first task in the group with the total duration of all tasks, and only those tasks, in the group. With a little refinement the filter can be incorporated into the macro.

    Sub SumUpDurations()
    Dim t As Task
    Dim Ref As Integer
    Dim RefFlg As Boolean
    Dim EStart As Date, LFinish As Date
    SelectTaskColumn
    EStart = "12/31/2049"
    LFinish = "1/1/1984"
    RefFlg = False
    For Each t In ActiveSelection.Tasks
        If Ref = False Then
            Ref = t.ID
            RefFlg = True
        End If
        If t.Start < EStart Then EStart = t.Start
        If t.Finish > LFinish Then LFinish = t.Finish
    Next t
    activeproject.Tasks(Ref).Duration1 = application.DateDifference(EStart, LFinish)
    End Sub

    Hope this helps.

    John



    • Edited by John - Project Tuesday, August 19, 2014 12:19 AM re-thinking the box
    • Marked as answer by Ingenious1 Tuesday, August 19, 2014 4:18 AM
    Monday, August 18, 2014 9:10 PM
  • Put the regulation in a custom field and use the Group function to group on the relevant custom field.

    You will need to create a new Group from teh More Groups... option.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by Ingenious1 Tuesday, August 19, 2014 4:18 AM
    Monday, August 18, 2014 8:07 PM
    Moderator
  • It 's a database. Putting aside doing it with VBA, you have three main tools at your disposal: sorting, grouping and filtering, applied individually or used in some combination. Start by populating the custom fields with some data and then you can sort, group and filter on those fields. Be prepared to not get it exactly right immediately, but any kind of progress is practice and it will open up what's possible.
    • Marked as answer by Ingenious1 Tuesday, August 19, 2014 4:18 AM
    Monday, August 18, 2014 10:49 PM

All replies

  • Put the regulation in a custom field and use the Group function to group on the relevant custom field.

    You will need to create a new Group from teh More Groups... option.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by Ingenious1 Tuesday, August 19, 2014 4:18 AM
    Monday, August 18, 2014 8:07 PM
    Moderator
  • Ingenious1,

    The term "total duration" is subjective. If indeed you want the SUM of durations for all tasks in the criteria group, then the grouping function with calculation for group and task summary lines set to "sum", will easily give you what you need. However, in Project, duration for a group of tasks is defined as the difference in working time between the start of the earliest task in the group and the finish of the latest task in the group. If that is what you really need then you can't get there from here with filtering, grouping or sorting or any combination thereof. You will either need to export the data to Excel and operate on it there or you could use a macro in Project.

    Herewith is a starting point macro. To use it, create an interactive filter to select each group, then run the macro. It will populate the Duration1 field of the first task in the group with the total duration of all tasks, and only those tasks, in the group. With a little refinement the filter can be incorporated into the macro.

    Sub SumUpDurations()
    Dim t As Task
    Dim Ref As Integer
    Dim RefFlg As Boolean
    Dim EStart As Date, LFinish As Date
    SelectTaskColumn
    EStart = "12/31/2049"
    LFinish = "1/1/1984"
    RefFlg = False
    For Each t In ActiveSelection.Tasks
        If Ref = False Then
            Ref = t.ID
            RefFlg = True
        End If
        If t.Start < EStart Then EStart = t.Start
        If t.Finish > LFinish Then LFinish = t.Finish
    Next t
    activeproject.Tasks(Ref).Duration1 = application.DateDifference(EStart, LFinish)
    End Sub

    Hope this helps.

    John



    • Edited by John - Project Tuesday, August 19, 2014 12:19 AM re-thinking the box
    • Marked as answer by Ingenious1 Tuesday, August 19, 2014 4:18 AM
    Monday, August 18, 2014 9:10 PM
  • It 's a database. Putting aside doing it with VBA, you have three main tools at your disposal: sorting, grouping and filtering, applied individually or used in some combination. Start by populating the custom fields with some data and then you can sort, group and filter on those fields. Be prepared to not get it exactly right immediately, but any kind of progress is practice and it will open up what's possible.
    • Marked as answer by Ingenious1 Tuesday, August 19, 2014 4:18 AM
    Monday, August 18, 2014 10:49 PM
  • Thank you, Rod, John and Trevor for taking the time to give me this very helpful advice! 
    Tuesday, August 19, 2014 4:19 AM
  • Ingenious1,

    You're welcome and thanks for the feedback. After I submitted my response and then saw the other responses I wonder if I was "over-thinking" the question, (sometimes I do that), but as long as you feel we were all helpful, that's the whole point of this forum.

    John

    Tuesday, August 19, 2014 3:19 PM