none
Project Server 2013 - BI (SQL) - "Head Count" report - how to count resources allocations total grouped to project/product line RRS feed

  • Question

  • Hi

    I have created a report for one of the departments on the organization.

    The need is to calculate the workload of each resource divided by the projects or product lines (project level field) that the resources are assigned to. this usage (like percent allocation in resource usage view) is calculated for each month/quarter by dividing AssignmentWork / Capacity.

    on the resource level, it works fine, I am getting the Capacity and Assignment work (from MSP_EpmResourceByDay_UserView and MSP_EpmAssignmentByDay_UserView DB tables), and calculating it. 

    But on the Product line level it also taking the sum of all work assigned to a specific product line for all resources and dividing it with the sum of capacity for all resources.  it gives kind of an average which is needed in the resource level but not in the totals of the pivot table.  

    I need to sum how many manpower / head count are allocated to each product line.

    Any ideas on how to do that in SQL or by the excel calculated fields?   

    This is the output now:

    PL1 Resource1 0.4
    Resource2 0.5
    PL1 Total 0.5
    PL2 Resource3 0.9
    Resource1 1.1
    Resource4 0.9
    PL2 Total 0.9

    While I need it like that (see totals):

    PL1 Resource1 0.4
    Resource2 0.5
    PL1 Total 0.9
    PL2 Resource3 0.9
    Resource1 1.1
    Resource4 0.9
    PL2 Total

    2.9


    Ofir Marco , MCTS P.Z. Projects

    Thursday, May 5, 2016 12:15 PM

All replies

  • My guess is that you need to calculate allocation first and then sum not Sum(work)/Sum(Capacity). For example, assuming every resource has a capacity of 8 hours, in PL1, Resource 1 worked a total of 3.2 totaling an allocation of 0.4 (3.2/8.0) and resource 2 worked 4.0 hours totaling an allocation of 0.5 (4.0/8.0). Which sums to (0.4 + 0.5) = 0.9, hence should be P1 Total. I believe your calculation of PL1 Total is Work(3.2+4)/Capacity(8+8) = 0.45 ~ 0.5 rounded to one decimal?. So instead of calculating (Sum Work)/(Sum Capacity), you'll need to group PL, Resource and day to calculate allocation.

    E.g. For PL2 Resource 3 worked 7.2 Hours, Resource 1 worked 8.8 Hours and Resource 4 worked 7.2 Hours. Currently it is calculating Sum(work)/Sum(Capacity = (7.2 + 8.8 + 7.2)/(8.0 + 8.0 + 8.0) = (23.2/24) = 0.97 which doesn't equal 0.9 and would be rounded to 1.0 but I'm assuming 8 hour capacity without knowing how the values are rounded. So, 1. Calculate allocation for each resource by day E.g. R3 (7.2/8.0) = 0.9, R1 (8.8/8.0) = 1.1 and R4 (7.2/8.0) = 0.9. Then 2. Sum allocation (0.9 + 1.1 + 0.9) = 2.9. My thoughts, hope this helps!
    • Edited by Paul Lor Friday, May 6, 2016 1:58 AM
    Friday, May 6, 2016 1:54 AM
  • Hi,

    I think that the calculation is done on the pivt as you explained.

    but I am not sure what I need to do in the SQL or in the excel pivot so that the report will some all the values and then divide the totals.

    any ideas?

    I divided assignment work by resource capacity

    using excel calculated fields


    Ofir Marco , MCTS P.Z. Projects

    Sunday, May 8, 2016 6:30 AM
  • What's your SQL query?
    Monday, May 9, 2016 4:27 PM
  •  
     
     SELECT
                   (SELECT ResourceName from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [ResourceName],
                   MSP_EpmResourceByDay_UserView.ResourceUID as [ResourceUID],
                   ISNULL(AssignmentTable.AllocatedCapacity,0) as [AllocatedCapacity],
                   MSP_EpmResourceByDay_UserView.Capacity as [ResourceCapacity],
          MSP_EpmResourceByDay_UserView.Capacity/8 as [ResourceCapacity Days],   
         
          AssignmentTable.[Fiscal Quarter]  ,
         AssignmentTable.[Fiscal Month]  ,
        AssignmentTable.[Fiscal Year] ,
        AssignmentTable.Profession,
          AssignmentTable.AssignmentWork,
          AssignmentTable.AssignmentWork/8 as [AssignmentWork Days],
          AssignmentTable.AssignmentCombinedWork,
           AssignmentTable.PeakUnits,
         AssignmentTable.SumPeak,
         AssignmentTable.SUMMaxUnits,
          AssignmentTable.ProjectName,
        AssignmentTable.[Product Line],
        AssignmentTable.MaxUnits,
        
        AssignmentTable.TimeByDay,
        
        AssignmentTable.MaxUnits,
         
          (SELECT [Profession]
        from MSP_EpmResource_UserView
        where (MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID)
        ) as [Profession] ,
      
        (SELECT [Team Name 1]
        from MSP_EpmResource_UserView
        where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [Team Name 1],
      
        (SELECT [Dep]
        from MSP_EpmResource_UserView
        where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [Dep]
        
    FROM
                   dbo.MSP_EpmResourceByDay_UserView
                LEFT OUTER JOIN
                   (
                    SELECT
                       MSP_EpmAssignment_UserView.ResourceUID,
            dbo.MSP_TimeByDay.FiscalQuarter as [Fiscal Quarter],
         dbo.MSP_TimeByDay.FiscalPeriodName as [Fiscal Month],
        dbo.MSP_TimeByDay.FiscalYear as [Fiscal Year],
        MSP_EpmResource_UserView.Profession,
        MSP_EpmAssignmentByDay_UserView.AssignmentWork,
        MSP_EpmAssignmentByDay_UserView.AssignmentCombinedWork,
        
        MSP_EpmProject_UserView.ProjectName,
        MSP_EpmProject_UserView.[Product Line],
        
        MSP_EpmResource_UserView.ResourceMaxUnits / 100 as [MaxUnits],
        SUM(MSP_EpmResource_UserView.ResourceMaxUnits/ 100)  as [SUMMaxUnits],
         
           MSP_EpmAssignment_UserView.AssignmentPeakUnits / 100 as [PeakUnits],
           SUM(MSP_EpmAssignment_UserView.AssignmentPeakUnits/100)  as [SumPeak],
          
           MSP_EpmAssignmentByDay_UserView.TimeByDay,
                       SUM(MSP_EpmAssignmentByDay_UserView.AssignmentCombinedWork / 60)   as [AllocatedCapacity]
                      
                    FROM
                       dbo.MSP_EpmAssignment_UserView
                    INNER JOIN
                       MSP_EpmAssignmentByDay_UserView
                          ON MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID
                             AND MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmAssignmentByDay_UserView.ProjectUID
                             AND MSP_EpmAssignment_UserView.TaskUID = MSP_EpmAssignmentByDay_UserView.TaskUID
         Inner Join MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID
         Inner Join MSP_EpmTask_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
         Inner join  MSP_EpmResource_UserView ON  MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID
         Inner Join dbo.MSP_TimeByDay   ON        MSP_EpmAssignmentByDay_UserView.TimeByDay = dbo.MSP_TimeByDay.TimeByDay
                   WHERE
                       MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN
                                                                 CONVERT(DATETIME, DATEADD(month, - 6, CURRENT_TIMESTAMP), 102)
                                                                 AND CONVERT(DATETIME, DATEADD(month, 12, CURRENT_TIMESTAMP), 102)
                    GROUP BY
                        dbo.MSP_TimeByDay.FiscalQuarter ,
         dbo.MSP_TimeByDay.FiscalPeriodName ,
        dbo.MSP_TimeByDay.FiscalYear ,
        MSP_EpmAssignmentByDay_UserView.TimeByDay,
        MSP_EpmProject_UserView.ProjectName,
        MSP_EpmProject_UserView.[Product Line],
        MSP_EpmResource_UserView.Profession,
           MSP_EpmAssignment_UserView.ResourceUID,
                 
           MSP_EpmAssignment_UserView.AssignmentPeakUnits,
           MSP_EpmResource_UserView.ResourceMaxUnits,
           MSP_EpmAssignmentByDay_UserView.AssignmentCombinedWork,
           MSP_EpmAssignmentByDay_UserView.AssignmentWork
                    ) AS AssignmentTable
                       ON AssignmentTable.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID
                          AND AssignmentTable.TimeByDay = MSP_EpmResourceByDay_UserView.TimeByDay
                WHERE  (AssignmentTable.Profession = 'SWO FIFA SW' ) AND
                  (MSP_EpmResourceByDay_UserView.TimeByDay > CONVERT(DATETIME, DATEADD(month, - 6, CURRENT_TIMESTAMP), 102))
                  AND (MSP_EpmResourceByDay_UserView.TimeByDay < CONVERT(DATETIME, DATEADD(month, 12, CURRENT_TIMESTAMP), 102))
            

    Ofir Marco , MCTS P.Z. Projects

    Tuesday, May 17, 2016 11:01 AM
  • There are some custom fields that are related to our server and you can ignore or change, and might be some fields that are not needed nor used

    Ofir Marco , MCTS P.Z. Projects

    Tuesday, May 17, 2016 11:02 AM
  • Here's a simple TSQL example. Capacity can be null or zero depending on how your system is configured. You'll need to use what makes the most sense for you.

    SELECT DISTINCT
    A.ProjectUID,
    RUV.ResourceName,
    ABD.AssignmentWork,
    RBD.Capacity,
    ABD.AssignmentWork/(CASE WHEN RBD.Capacity IS NULL THEN 8 WHEN RBD.Capacity=0 THEN 1 ELSE RBD.Capacity END) AS Allocation,
    ABD.TimeByDay
    
    FROM dbo.MSP_EpmAssignment A
    LEFT JOIN dbo.MSP_EpmAssignmentByDay ABD ON A.AssignmentUID=ABD.AssignmentUID
    LEFT JOIN dbo.MSP_EpmResource_UserView RUV ON A.ResourceUID=RUV.ResourceUID
    LEFT JOIN dbo.MSP_EpmResourceByDay RBD ON RUV.ResourceUID=RBD.ResourceUID AND RBD.TimeByDay=ABD.TimeByDay
    • Edited by Paul Lor Tuesday, May 17, 2016 8:47 PM
    Tuesday, May 17, 2016 8:38 PM
  • Hi Paul

    I will try it out and let you know

    can you explain why if the capacity is NULL (meaning that it doesn't have any hours) you want to calculate it as '8' and when it is 0 as '1'?   didn't get this logic


    Ofir Marco , MCTS P.Z. Projects

    Wednesday, May 18, 2016 5:59 AM
  • Hi Paul,

    Thank you for the code, it gets me closer to the solution

    your solution  takes in consideration the resource and their  assignment while I also need to know the allocation with the time dimension (Month, Quarter...) and also how much allocation is assigned to each project or product line which is a project level attribute

    I have tried to adjust the code accordingly

    but now it gives me a different result , it summarizes totals but the numbers are calculated as a sum of all the work/capacity for each day while I need it as sum of all work divided by sum of all capacity

    SELECT DISTINCT

    RUV.ResourceName,
    RUV.Profession,
    RUV.[Team Name 1] as [Team],
    ABD.AssignmentWork as [Work],
    RBD.Capacity as [Capacity],
    P.ProjectName,
    P.[Product Line],
    T.TaskUID,

    (ABD.AssignmentWork) / (CASE WHEN (RBD.Capacity) IS NULL THEN 8 WHEN (RBD.Capacity) = 0 THEN 1 ELSE (RBD.Capacity) END) AS Allocation,

    TBD.FiscalYear,
    TBD.FiscalQuarter,
    TBD.FiscalPeriodName,
    ABD.TimeByDay

    FROM dbo.MSP_EpmAssignment A
    LEFT JOIN dbo.MSP_EpmAssignmentByDay ABD ON A.AssignmentUID=ABD.AssignmentUID
    LEFT Join MSP_EpmProject_UserView P ON A.ProjectUID = P.ProjectUID
    LEFT Join MSP_EpmTask_UserView T ON P.ProjectUID = T.ProjectUID
    LEFT JOIN dbo.MSP_EpmResource_UserView RUV ON A.ResourceUID=RUV.ResourceUID
    LEFT JOIN dbo.MSP_EpmResourceByDay RBD ON RUV.ResourceUID=RBD.ResourceUID AND RBD.TimeByDay = ABD.TimeByDay
    LEFT Join dbo.MSP_TimeByDay  TBD ON ABD.TimeByDay = TBD.TimeByDay


    Ofir Marco , MCTS P.Z. Projects

    Wednesday, May 18, 2016 2:51 PM
  • Here is how it look now


    Ofir Marco , MCTS P.Z. Projects

    Wednesday, May 18, 2016 2:53 PM
  • Hi,

    Capacity for ResourceByDay is dependent on how the project server is configured, e.g. resource capacity settings in additional settings and/or resource calendar etc. You'll need to modify capacity to values that makes sense to you. For simplicity let's assume each day all resources can work 8 hours.

    SELECT DISTINCT
    PUV.ProjectName,
    PUV.[Product Line],
    RUV.ResourceName,
    RUV.Profession,
    RUV.[Team Name 1] as [Team],
    YEAR(W.TimeByDay) AS YearTime,
    DATEPART(QUARTER, W.TimeByday) AS QuarterTimeOfTheYear,
    MONTH(W.TimeByDay) AS MonthTimeOfTheYear,
    W.TimeByDay,
    W.AssignmentWork,
    W.AssignmentWork/8 AS Allocation
    
    FROM dbo.MSP_EpmProject_UserView PUV
    
    LEFT JOIN (SELECT DISTINCT A.ProjectUID, RUV.ResourceUID, ABD.TimeByDay, Sum(ABD.AssignmentWork) AS AssignmentWork
    FROM dbo.MSP_EpmAssignment A
    LEFT JOIN dbo.MSP_EpmAssignmentByDay ABD ON A.AssignmentUID=ABD.AssignmentUID
    LEFT JOIN dbo.MSP_EpmResource_UserView RUV ON A.ResourceUID=RUV.ResourceUID
    WHERE ABD.AssignmentWork>0 AND A.TaskIsActive IN(1)
    GROUP BY A.ProjectUID, RUV.ResourceUID, ABD.TimeByDay)
    W ON PUV.ProjectUID=W.ProjectUID
    
    LEFT JOIN dbo.MSP_EpmResource_UserView RUV ON W.ResourceUID=RUV.ResourceUID
    Use the query in a matrix (e.g. SSRS) or pivot table in Excel.


    Wednesday, May 18, 2016 6:26 PM