# 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

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 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
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
Inner Join MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_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 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],

(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