none
Timescaled hours do not roll up more than one level RRS feed

  • Question

  • Hi,

    I'm using (being abused by?) Office Pro 2007 (no project server is installed). In negotiating a Government contract, we are required to list the number of hours each person will work on each task in each year of the project. The Government wants the data for the top-level tasks (e.g., total for Task 1, Task 2, etc., but not Task 1.1, Task 1.2, etc.).

    At first glance, the Resource Usage View with the grouping "Assignments Keeping Outline Structure" is what I need. However, Project does not roll up the time-scaled assignments correctly.

    For instance, I have a resrouce assigned in Year 3 to Task 1.8.1 for 10h. He is assigned to no other subtasks under Task 1 and is only assigned to Task 1.8.1 in Year 3. In the table (left side of the project window) where the project totals are shown, project correctly reports that resource assigned to Task 1.8.1 for 10h, which is rolled up to Task 1.8 to give 10h, which is also rolled up to Task 1 to give 10h. This is correct - his assigned contribution to Task 1 is 10h.

    When I look at the time-scaled work (right side of the project window), I get a different result. As expected, for Year 1 and Year 2, there are no assignments under Task 1 and its subtasks for this resource. In Year 3, I see the resource assigned to Task 1.8.1 for 10h as expected. I see the resource assigned to Task 1.8 for 10h in Year 3 as expected, but for Task 1 I see a blank cell for all three years. So now, project reports that the resource is assigned to a total of 10h for the entire project, and the yearly breakdown is 0h for Y1, 0h for Y2, 0h for Y3. So project is telling me that 0+0+0=10!!! Total assigned hours for the resource add correctly.

    This happens for any resource assigned to a subtask more than 1 level below the summary task. For instance, for another resource Project reports that the total work on Task 1 is 352h, broken down into 45h, 100h, and 21h each year for a total of 166h. However, the total work assigned to the resource is 1,111h broken down into 209h, 548h, and 354h for a total of 1,111h.

    The only thing I can think of is to copy and paste the time-phased data to excel where I can correct Project's mistakes. However, this is very time consuming as I need to go through the entire spreadsheet and figure out which rows I need to replace - which is difficult because I can't copy the indented formatting of the resrouce usage table to excel and I can't get a column for the outline level field in the resource usage view. 

    It appears that Project only rolls hours up 1 outline level.

    Is there a work around for this?

    Monday, September 24, 2012 7:52 PM

Answers

  • Grouping does not seem to work with timescaled data. I can verify the behavior. 

    >> So project is telling me that 0+0+0=10!!!

    No.  I cannot see any zeros in the timephased group rows where Project does not display any values; instead, they are just blank. 

    Sorry, I cannot provide any workaround. I would create a visual work report and modify the pivot to see the timephased summary row values. 



    • Edited by Ismet Kocaman Tuesday, September 25, 2012 11:34 AM
    • Marked as answer by 69L46 Tuesday, September 25, 2012 6:33 PM
    Tuesday, September 25, 2012 11:15 AM
  • Sub FixResourceAssignmentRollup() Dim T1 As Task Dim work1 As Long Dim Res As Resource Dim ExcelWorksheet As Object Dim nLevel1Tasks As Integer Dim nLevel1TaskNum As Integer Dim nResNum As Integer Dim nNumPeriods As Integer nLevel1TasksNum = 0 nResNum = 0 nLevel1Tasks = 0 'count the number of Level-1 Tasks For Each T1 In ActiveProject.Tasks If T1.GetField(pjTaskOutlineLevel) = 1 Then nLevel1Tasks = nLevel1Tasks + 1 End If Next T1 work1 = 0 Set ExcelWorksheet = CreateObject("Excel.Sheet") ExcelWorksheet.Application.Visible = True For Each Res In ActiveProject.Resources ExcelWorksheet.Application.Cells(nResNum * (nLevel1Tasks + 1) + 2, 1) = Res.Name ' Step through tasks For Each T1 In ActiveProject.Tasks ' Step through each subtask work2 = 0 ' Only call recursive function for Level-1 tasks If T1.GetField(pjTaskOutlineLevel) = 1 Then ExcelWorksheet.Application.Cells((nLevel1Tasks + 1) * nResNum + nLevel1TaskNum + 3, 2) = T1.OutlineNumber & " " & T1.Name ' call recursive functino to sum up all resources for each time period For nNumPeriods = 1 To 36 work1 = SumResourceAssignments(Res, T1, ActiveProject.ProjectStart, ActiveProject.ProjectFinish, pjTimescaleMonths, nNumPeriods) If Res.Type = pjResourceTypeWork Then 'MsgBox T1.OutlineNumber & "- " & T1.Name & ": " & Res.Name & ": " & (work1 / 60) & "h" ExcelWorksheet.Application.Cells((nLevel1Tasks + 1) * nResNum + nLevel1TaskNum + 3, nNumPeriods + 3) = work1 / 60 Else 'MsgBox T1.OutlineNumber & "- " & T1.Name & ": " & Res.Name & ": $" & work1 ExcelWorksheet.Application.Cells((nLevel1Tasks + 1) * nResNum + nLevel1TaskNum + 3, nNumPeriods + 3) = work1 End If Next nNumPeriods nLevel1TaskNum = nLevel1TaskNum + 1 End If Next T1 nLevel1TaskNum = 0 nResNum = nResNum + 1 Next Res ExcelWorksheet.SaveAs "C:\Users\ken\Documents\test.xls" 'ExcelWorkbook.Application.Quit Set ExcelWorkbook = Nothing End Sub


    Function SumResourceAssignments(ByVal Res1 As Resource, ByVal T As Task, ByVal SD As String, ByVal ED As String, ByVal ts As Long, ByVal period As Integer) As Double
    
        Dim work As Double
        Dim A As Assignment
        Dim TChild As Task
        
        If T.GetField(pjTaskOutlineLevel) = 1 Then
            work = 0
        End If
    
        ' For each child, call this function
        For Each TChild In T.OutlineChildren
            ' add the summed resource assignments from children to work total
            work = work + SumResourceAssignments(Res1, TChild, SD, ED, ts, period)
        Next TChild
        'add the work for this assignment to the work total
        For Each A In T.Assignments
            'check to see if assignment is for the resource that was passed
            If A.ResourceGuid = Res1.Guid Then
                ' retrieve timescaled work in yearly increments from project start to finish
                Set TSV = A.TimeScaleData(SD, ED, pjAssignmentTimescaledWork, ts)
                ' add the work for the specified period to the work total
                work = work + Val(TSV.Item(period).Value)
            End If
        Next A
        SumResourceAssignments = work
        Exit Function
    End Function

    • Marked as answer by 69L46 Tuesday, September 25, 2012 10:37 PM
    Tuesday, September 25, 2012 10:29 PM

All replies

  • Grouping does not seem to work with timescaled data. I can verify the behavior. 

    >> So project is telling me that 0+0+0=10!!!

    No.  I cannot see any zeros in the timephased group rows where Project does not display any values; instead, they are just blank. 

    Sorry, I cannot provide any workaround. I would create a visual work report and modify the pivot to see the timephased summary row values. 



    • Edited by Ismet Kocaman Tuesday, September 25, 2012 11:34 AM
    • Marked as answer by 69L46 Tuesday, September 25, 2012 6:33 PM
    Tuesday, September 25, 2012 11:15 AM
  • OK - I'm not going crazy :)

    I did manage to create some VBA code to do the summation and expert it to an Excel spreadsheet. It is dissapointing that I had to spend time coding to fix Microsoft's programming mistakes.

    Tuesday, September 25, 2012 6:33 PM
  • Thanks for the feedback on the issue. We would be glad if you could share the code with the community by posting here. I believe there is a reasonable explanation on this behavior or maybe I miss something. 

    Regards.

    Tuesday, September 25, 2012 7:34 PM
  • Sub FixResourceAssignmentRollup() Dim T1 As Task Dim work1 As Long Dim Res As Resource Dim ExcelWorksheet As Object Dim nLevel1Tasks As Integer Dim nLevel1TaskNum As Integer Dim nResNum As Integer Dim nNumPeriods As Integer nLevel1TasksNum = 0 nResNum = 0 nLevel1Tasks = 0 'count the number of Level-1 Tasks For Each T1 In ActiveProject.Tasks If T1.GetField(pjTaskOutlineLevel) = 1 Then nLevel1Tasks = nLevel1Tasks + 1 End If Next T1 work1 = 0 Set ExcelWorksheet = CreateObject("Excel.Sheet") ExcelWorksheet.Application.Visible = True For Each Res In ActiveProject.Resources ExcelWorksheet.Application.Cells(nResNum * (nLevel1Tasks + 1) + 2, 1) = Res.Name ' Step through tasks For Each T1 In ActiveProject.Tasks ' Step through each subtask work2 = 0 ' Only call recursive function for Level-1 tasks If T1.GetField(pjTaskOutlineLevel) = 1 Then ExcelWorksheet.Application.Cells((nLevel1Tasks + 1) * nResNum + nLevel1TaskNum + 3, 2) = T1.OutlineNumber & " " & T1.Name ' call recursive functino to sum up all resources for each time period For nNumPeriods = 1 To 36 work1 = SumResourceAssignments(Res, T1, ActiveProject.ProjectStart, ActiveProject.ProjectFinish, pjTimescaleMonths, nNumPeriods) If Res.Type = pjResourceTypeWork Then 'MsgBox T1.OutlineNumber & "- " & T1.Name & ": " & Res.Name & ": " & (work1 / 60) & "h" ExcelWorksheet.Application.Cells((nLevel1Tasks + 1) * nResNum + nLevel1TaskNum + 3, nNumPeriods + 3) = work1 / 60 Else 'MsgBox T1.OutlineNumber & "- " & T1.Name & ": " & Res.Name & ": $" & work1 ExcelWorksheet.Application.Cells((nLevel1Tasks + 1) * nResNum + nLevel1TaskNum + 3, nNumPeriods + 3) = work1 End If Next nNumPeriods nLevel1TaskNum = nLevel1TaskNum + 1 End If Next T1 nLevel1TaskNum = 0 nResNum = nResNum + 1 Next Res ExcelWorksheet.SaveAs "C:\Users\ken\Documents\test.xls" 'ExcelWorkbook.Application.Quit Set ExcelWorkbook = Nothing End Sub


    Function SumResourceAssignments(ByVal Res1 As Resource, ByVal T As Task, ByVal SD As String, ByVal ED As String, ByVal ts As Long, ByVal period As Integer) As Double
    
        Dim work As Double
        Dim A As Assignment
        Dim TChild As Task
        
        If T.GetField(pjTaskOutlineLevel) = 1 Then
            work = 0
        End If
    
        ' For each child, call this function
        For Each TChild In T.OutlineChildren
            ' add the summed resource assignments from children to work total
            work = work + SumResourceAssignments(Res1, TChild, SD, ED, ts, period)
        Next TChild
        'add the work for this assignment to the work total
        For Each A In T.Assignments
            'check to see if assignment is for the resource that was passed
            If A.ResourceGuid = Res1.Guid Then
                ' retrieve timescaled work in yearly increments from project start to finish
                Set TSV = A.TimeScaleData(SD, ED, pjAssignmentTimescaledWork, ts)
                ' add the work for the specified period to the work total
                work = work + Val(TSV.Item(period).Value)
            End If
        Next A
        SumResourceAssignments = work
        Exit Function
    End Function

    • Marked as answer by 69L46 Tuesday, September 25, 2012 10:37 PM
    Tuesday, September 25, 2012 10:29 PM
  • The subroutine steps through the tasks and resources and calls the SumResourceAssignments function. I hard-coded the number and type of periods for the timescaled work. In my case the project I was working with was 3 years long, hence the 1 to 36 and pjTimescaleMonths. I will probably add a dialog box to select the timescale and then calculate the number of periods, but this shows how it works.

    The SumResourceAssignments function adds work for each subtask (including any work assigned to summary tasks). It calls itself recursively to drill down to the lowest level subtask.

    I'm sure the code is not very efficient as I don't bail from the loops when the resource GUIDs match, but it seems to work for me.


    • Edited by 69L46 Tuesday, September 25, 2012 10:36 PM
    Tuesday, September 25, 2012 10:35 PM
  • Excellent ! Thank you for sharing. -- Regards.
    Wednesday, September 26, 2012 7:00 AM
  • Are you using any service packs or cumulative updates? I just want to compare what you are seeing with our own findings. Thanks.
    Wednesday, September 26, 2012 7:11 PM
  • Thursday, September 27, 2012 12:19 AM
  • Applying the view and grouping that you suggest, I do get top level summarization at the group level (which is the resource being grouped on). I also get rollup from the lowest level (which is the task that the resource is assigned to) onto the very lowest outline level above the task. However there is a lack of rollup data at every level of the outline structure between the lowest level (where I do see data) and the top level (where I do see data). I think you are looking for summarization at the second level from top in the hierarchy shown here (--1 Outline Level) ?

    To me it looks like:

    Name: Resource 1 - work shown as timephased. This is top group level.

    --1 Outline Level - nothing shown.

    ----1.1 Outline level - nothing shown.

    ------1.1.1 Outline level - nothing shown.

    --------1.1.1.1 Outline level - work shown as timephased.

    ------------ Task One Name Here - work shown as timephased.

    Is this what you see in your file?

    As for the numbers not adding up correctly, I see vertical and horizontal integration errors also. I looked at several views and even without group there are errors. Horizontally I see totalling errors that differ depending on the timescale I use. I checked a months worth of work on a task that totals in Excel as 192.58. On the task level in MSP it states 192.53. In PWA it is 192.54. Those were the best cases. When using a 15 minute time scale in MSP' resource usage view, the difference was over 2 hours.

    I also see horizontal errors. Clearly the tasks total in Excel as one thing, and MSP has the total as something else. Again the timescale matters and the errors change with timescale changes.

    Some would say not a big deal. However if you have to answer to the government and have these errors, you can lose your contract.

    I am using the latest August 2012 CU for MS Project Pro 2007 and Project Server 2007




    • Edited by Quized again Thursday, September 27, 2012 10:12 PM more data
    Thursday, September 27, 2012 2:19 PM
  • That lack of rollup of more than one level is what I see as well. So I had to write the VBA code to get the time-phased work assignments for Outline Level 1 (what I needed to report).

    I'll try the CU, but it looks like if you have it installed, then it does not fix the problem since you see it as well. The numbers don't add up because of the incorrect roll up. I have non-summary tasks for Tasks 1.1, 1.2, 1.3, 1.4, 1.5, and 1.6 (this work rolls up to Task 1) but work for summary Task 1.7 (w/subtasks 1.7.1-1.7.11) and Task 1.8 (w/subtasks 1.8.1 and 1.8.2) does not get rolled up to Task 1.

    1. Task 1

         1.1 Task 1.1

         1.2 Task 1.2

         1.3 Task 1.3

                :

                : 

         1.7 Task 1.7

              1.7.1 Task 1.7.1

                        :

         1.8 Task 1.8

              1.8.1 Task 1.8.1

              1.8.2 Task 1.8.2

    The totals in the table (left side of the resource-usage view) do roll up properly, so the value for the total work assigned to a resource for Task 1 includes all the work at each outline level under Task 1, the time-phased data does not include the work assigned to the resource for Tasks 1.7.1-1.7.11 and Tasks 1.8.1 and 1.8.2, hence the discrepancy. If the resource is not assigned to any level 2 tasks, the time-phased roll-up to Task 1 will be blank. If the resource is assigned to both level 2 tasks and level 3 or level 4 tasks, then the time-phased roll-up will not be blank, but be just the sum of the level 2 assignments and not include the lower-level assignments.

    So if you have a 3-month project and the resource is assigned 30h/month to level 2 tasks and assigned 10h/month to level 3 tasks, the level 1 total for a 3-month project (in the work table) will be correct as 30h/mo x 3mo + 10h/mo x 3mo = 120h. However, the timephased totals for the level 1 task will be 30h for month 1, 30h for month 2, and 30h for month 3 for a total of 90h rather than 120h.

    Hopefully, the CU will fix some other wackiness I've seen:

    • saving in MSP 2007 file format causing assignments to be replaced - I have to save as MSP 200-2003 format in order to stop my assignments from having "travel" being replaced by "fabrication"
    • BCWS<>BCWP immediately after baselining a project - a logical impossibility
    • EV measures different in task-usage view and resource-usage view (probably due to the known incorrect calculation of EVMS for summary tasks as noted in a KB article)
    • time-phased cells displaying different values than actually stored in the cells (as confimed by cut-and-paste to Excel or looking at the entry bar)
    • time-phased cells showing values that change depending upon how many cells are visible and that change when moving the cursor left and right in the time-phase EVMS
    Thursday, September 27, 2012 10:49 PM
  • I will test the weirdness you talk about. There was a bug fixed (MS says) in one of the CUs issued after SP3 that is to fix assignments disapearing or changing. Please tell me how to reproduce the copy from MSP to Excel issue, 4th bullet. I am not seeing that. In my original tests I suspected it so I made Excel show 8 decimal places and pasted but only got 2 digits and 6 zeros.

    Also the last bullet you saw values change when you tab over in the cells, or when you click from cell to cell?

    As for the writeup, it looks like you have a task view, not a resource view. Correct? I will retest making sure I have the same outline levels as you.

    Last, if you have the KB number that talks about EVMS values being incorrect I would like to read it.

    Friday, September 28, 2012 6:29 PM
  • Here's the KB talking about incorrect roll-up of EVMS (though it looks like it is a little dated and may not apply to project 2007):

    http://support.microsoft.com/kb/75051

    Here's my thread about cell values changing when moving from cell to cell and it also shows different values in the cell and the entry bar:

    http://social.technet.microsoft.com/Forums/en-US/projectprofessional2010general/thread/db079293-f07f-4716-8cf8-14be836abcac

    The images aren't too clear in that thread - I'll try again- too bad I can't add a Word or pdf attachment - things are clear there.

    Wednesday, October 3, 2012 10:49 PM
  • herehere
    Wednesday, October 3, 2012 10:51 PM
  • Wednesday, October 3, 2012 10:52 PM
  • 96L46,

    You are not the first to report an issue with values in the timescaled side of a Usage view change as the view is scrolled. Take a look at the following thread:http://answers.microsoft.com/en-us/office/forum/office_2010-project/project-2010-actual-cost-acwp-calculation-problem/28d0fb58-64ee-46be-b2e5-899d56f5c7f1.

    The user did send me his file and I was able to reproduce the phenomenon. I have since reported this to a contact at Microsoft and he informed me a couple of days ago that he logged it as a bug. However, that doesn't necessarily mean it will be fixed right away, or even at all. This is what I would call a "fringe" bug, meaning it only occurs under specific circumstances and it is apparently not affecting a lot of users.

    If you wish to report this you can do so at: http://support.microsoft.com/ph/931

    John

    Thursday, October 4, 2012 2:13 AM