Asked by:
Project Server 2013  BI (SQL)  "Head Count" report  how to count resources allocations total grouped to project/product line
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
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

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


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


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


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.TimeByDayFROM 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.TimeByDayOfir Marco , MCTS P.Z. Projects


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.