none
Resource utilization report RRS feed

  • Question

  • Dear Project Server users,

    I want to create a report that measures a team's utilization, using powerpivot and with the information from the SQL PWA database.

    Basically, I want to measure how big a resource's utilization for a period (week, month, quarter, year) is.

    Resource utilization is measured by dividing the time a resource spends on a project (per a period) with the total work time (for the same period).

    I see that there is a total work field in timesheets, but I cannot find it in the database. Is there a way to call this field from a base? Can anyone propose a quick and elegant solution?

     With best regards,
    Petar 
    Wednesday, November 23, 2016 4:03 PM

Answers

  • Hi,

    no it is getting complex. I suggest a SQL training :).

    DECLARE @ParmProjecteriodLevel VarChar(1) = 'M' --'M','D','Y', 'W'
    DECLARE @ParmResourceesourceLevel VarChar(20) = 'Resource' --'Department','Resource'
    DECLARE @From  Date = '20150101'  --'20150101', NULL
    DECLARE @ParmTo DATE = '20170101' --'20170101', NULL
    DECLARE @ParmProject VarChar(255) = 'Test' --'Test', NULL
    DECLare @ParmResource VarChar(255) = NULL --'Barbara Henhapl',NULL
    DECLARE @ParmTaskECF VarChar(255) = NULL --NULL ,'0', '1'
    
    ; WITH _CTE_Capacity AS
    (
    	SELECT   CASE WHEN @ParmProjecteriodLevel = 'W' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + DATEPART(ww,TimeByDay),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'M' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + CAST(MONTH(TimeByDay)AS char(2)),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'Y' THEN CAST(YEAR(TimeByDay) as Char(4)) 
    				   WHEN @ParmProjecteriodLevel = 'D' THEN CAST(CAST(TimeByDay AS date) as varchar(20))
    			  END AS Period
    			, R.ResourceUID
    			, R.ResourceName
    			, SUM(Capacity) AS Capacity
    	FROM dbo.MSP_EpmResource_UserView AS R
    	INNER JOIN dbo.MSP_EpmResourceByDay_UserView AS RbD
    		ON R.ResourceUID = RbD.ResourceUID
    	WHERE 
    		(
    		R.ResourceName = @ParmResource
    		OR
    		@ParmResource IS NULL
    	   )
    	   AND
    	   (
    		RbD.TimeByDay >= @FROM
    		OR
    		@FROM IS NULL
    	   )
    	   AND
    	   (
    		RbD.TimeByDay <= @ParmTo
    		OR
    		@ParmTo IS NULL
    	   )
    	GROUP BY CASE WHEN @ParmProjecteriodLevel = 'W' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + DATEPART(ww,TimeByDay),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'M' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + CAST(MONTH(TimeByDay)AS char(2)),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'Y' THEN CAST(YEAR(TimeByDay) as Char(4)) 
    				   WHEN @ParmProjecteriodLevel = 'D' THEN CAST(CAST(TimeByDay AS date) as varchar(20))
    			  END
    			  , R.ResourceUID
    			  , R.ResourceName
    			  , R.[Resource Departments]
    )
    , _CTE_ActualWork AS
    (
    SELECT        R.ResourceUID
    			, R.ResourceName
    			, R.[Resource Departments]
    			, P.ProjectName
    			, SUM(ABD.AssignmentActualWork) AS ActualWork
    			, CASE WHEN @ParmProjecteriodLevel = 'W' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + DATEPART(ww,TimeByDay),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'M' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + CAST(MONTH(TimeByDay)AS char(2)),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'Y' THEN CAST(YEAR(TimeByDay) as Char(4)) 
    				   WHEN @ParmProjecteriodLevel = 'D' THEN CAST(CAST(TimeByDay AS date) as varchar(20))
    			  END AS Period
    			
    FROM            dbo.MSP_EpmAssignment_UserView AS A 
    INNER JOIN dbo.MSP_EpmAssignmentByDay_UserView AS ABD 
    	ON A.AssignmentUID = ABD.AssignmentUID 
    INNER JOIN  dbo.MSP_EpmTask_UserView AS T 
    	ON A.TaskUID = T.TaskUID 
    INNER JOIN dbo.MSP_EpmResource_UserView AS R 
    	ON A.ResourceUID = R.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON T.ProjectUID = P.ProjectUID 
    WHERE  ABD.AssignmentActualWork > 0
    	   AND
    	   (
    		T.[Billable (Proba)] = @ParmTaskECF
    		OR
    		@ParmTaskECF IS NULL
    	   )
    	  AND 
    	   (
    		R.ResourceName = @ParmResource
    		OR
    		@ParmResource IS NULL
    	   )
    	   AND
    	   (
    		P.ProjectName = @ParmProject
    		OR
    		@ParmProject IS NULL
    	   )
    	   AND
    	   (
    		ABD.TimeByDay >= @FROM
    		OR
    		@FROM IS NULL
    	   )
    	   AND
    	   (
    		ABD.TimeByDay <= @ParmTo
    		OR
    		@ParmTo IS NULL
    	   )
    GROUP BY  R.ResourceUID
    			, R.ResourceName
    			, R.[Resource Departments]
    			, P.ProjectName
    			,  CASE WHEN @ParmProjecteriodLevel = 'W' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + DATEPART(ww,TimeByDay),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'M' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + CAST(MONTH(TimeByDay)AS char(2)),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'Y' THEN CAST(YEAR(TimeByDay) as Char(4)) 
    				   WHEN @ParmProjecteriodLevel = 'D' THEN CAST(CAST(TimeByDay AS date) as varchar(20))
    			  END
    )
    
    
    SELECT Act.Period
    	, CASE WHEN @ParmResourceesourceLevel = 'Resource' THEN Act.ResourceName
    		   WHEN @ParmResourceesourceLevel = 'Department' THEN Act.[Resource Departments]
    	  END AS Resource
    	, SUM(Act.ActualWork) AS ActualWork
    	, Act.ProjectName
    	, SUM(Cap.Capacity) AS Capacity
    	, SUM(Act.ActualWork) / SUM(Cap.Capacity) * 100 AS Utilization
    FROM _CTE_ActualWork AS Act
    	INNER JOIN _CTE_Capacity AS Cap
    ON ACT.Period = Cap.Period
    	AND Act.ResourceUID = Cap.ResourceUID
    WHERE Cap.Capacity > 0
    GROUP BY  Act.Period
    		, Act.ProjectName
    		, CASE WHEN @ParmResourceesourceLevel = 'Resource' THEN Act.ResourceName
    			WHEN @ParmResourceesourceLevel = 'Department' THEN Act.[Resource Departments]
    		  END

    Regards
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    • Marked as answer by PetarB Wednesday, November 30, 2016 8:37 AM
    Saturday, November 26, 2016 8:52 AM
    Moderator

All replies

  • Hi,

    not sure, why you are referring to timesheets? Are you only interested in the past? Following query will return work per assignment with percentage on all assignmenst for a resource. Perhaps at least as starting point?

    --DECLARE @P VarChar(255) = 'Test 1'
    --DECLare @R VarChar(255) = 'Barbara Henhapl'
    --DECLARE @FROM Date = '20150101'
    --DECLARE @TO Date = '20151203'
    
    DECLARE @P VarChar(255) = NULL
    DECLare @R VarChar(255) = NULL
    DECLARE @FROM Date = NULL
    DECLARE @TO Date = NULL
    
    SELECT        R.ResourceName
    			, T.TaskName
    			, P.ProjectName
    			, ABD.TimeByDay
    			, ABD.AssignmentWork
    			, AllWork.ResourceWork
    			, ABD.AssignmentWork / AllWork.ResourceWork AS Utilization
    FROM            dbo.MSP_EpmAssignment_UserView AS A 
    INNER JOIN dbo.MSP_EpmAssignmentByDay_UserView AS ABD 
    	ON A.AssignmentUID = ABD.AssignmentUID 
    INNER JOIN  dbo.MSP_EpmTask_UserView AS T 
    	ON A.TaskUID = T.TaskUID 
    INNER JOIN dbo.MSP_EpmResource_UserView AS R 
    	ON A.ResourceUID = R.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON T.ProjectUID = P.ProjectUID 
    INNER JOIN (SELECT AAll.ResourceUID
    				 , ADBDAll.TimeByDay
    				 , SUM(ADBDAll.AssignmentWork) AS ResourceWork
                FROM  dbo.MSP_EpmAssignmentByDay_UserView AS ADBDAll 
    			INNER JOIN  dbo.MSP_EpmAssignment_UserView AS AAll 
    				ON ADBDAll.AssignmentUID = AAll.AssignmentUID
    			WHERE (
    					ADBDAll.TimeByDay >= @FROM
    					OR
    					@FROM IS NULL
    				   )
    				   AND
    				   (
    					ADBDAll.TimeByDay <= @TO
    					OR
    					@TO IS NULL
    				   )
                GROUP BY AAll.ResourceUID, ADBDAll.TimeByDay
    			) AS AllWork 
    ON		ABD.TimeByDay = AllWork.TimeByDay
    	AND R.ResourceUID = AllWork.ResourceUID
    	AND AllWork.ResourceWork  > 0
    WHERE (
    		R.ResourceName = @R
    		OR
    		@R IS NULL
    	   )
    	   AND
    	   (
    		P.ProjectName = @P
    		OR
    		@P IS NULL
    	   )
    	   AND
    	   (
    		ABD.TimeByDay >= @FROM
    		OR
    		@FROM IS NULL
    	   )
    	   AND
    	   (
    		ABD.TimeByDay <= @TO
    		OR
    		@TO IS NULL
    	   )

    Regards
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    Wednesday, November 23, 2016 5:13 PM
    Moderator
  • Hi Barbara,

    Thank you very very much for your query. This gives me almost all the information I need.

    I don't really understand the AssignmentWork field and the ResourceWork field. Is AssignmentWork actually planned work? I tried to figure out what ResourceWork field measures but I cannot find out. Can you help me and clarify this?

    Basically I want to measure utilization for the past and for the future (through future projects and future planned work). I need to check whether a resource has a minimum of 70% utilization. We measure utilization as time spent on projects (planed work) divided by capacity (8 hours a day or the equivalent for the period).

    I am sorry to bother you so much Barbara, but your queries and answers are a HUGE help to me!

    Withe best regards,
    Petar 

    Thursday, November 24, 2016 2:11 PM
  • Hi

    ResourceWork ist just a total of all AssignmentWork (SUM(ADBDAll.AssignmentWork) AS ResourceWork).

    A resource is assigned to a task, the work for this assignment is called AssignmentWork.. AssignmentWork is the value of Work, you can see e.g. in Task Usage or Resource Usage.

    In your initial post you wrote: "Resource utilization is measured by dividing the time a resource spends on a project (per a period) with the total work time (for the same period)."

    Now you are refering to Capacity, that is someting different. Could you please confirm, that you need Work And Capacity. What level of data? Project and resource? Project, task and resource? Project only? Project and task?

    Regards
    Barbara 


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    Thursday, November 24, 2016 3:14 PM
    Moderator
  • Hi Barbara,

    My apologies, I was not clear enough.

    By total work time I was actually referring to the resource's capacity. Where capacity is measured as 8h (9-17). Of course, when a Resource takes a free day this capacity is decreased. But I think PS takes this in consideration. 

    In conclusion, the formula is a little complicated: Utilization = (Actual hours/capacity)*100
    However, I only need to take in to account actual hours from tasks that have the value in the custom task field Billable (Proba) equal to Yes. When an activity has the value equal to No in the Billable (Proba) field it is considered not billable, and therefore not relevant.  

    I need to measure it for a given period. Now this period could by, day, week, month (usually month). It is measured per a resource and usually per a Resource Department.

    It would be useful to have an option to filter the utilization value per project. I don't need it for tasks.

    With best regards,
    Petar  

    Friday, November 25, 2016 2:46 PM
  • Hi,

    no it is getting complex. I suggest a SQL training :).

    DECLARE @ParmProjecteriodLevel VarChar(1) = 'M' --'M','D','Y', 'W'
    DECLARE @ParmResourceesourceLevel VarChar(20) = 'Resource' --'Department','Resource'
    DECLARE @From  Date = '20150101'  --'20150101', NULL
    DECLARE @ParmTo DATE = '20170101' --'20170101', NULL
    DECLARE @ParmProject VarChar(255) = 'Test' --'Test', NULL
    DECLare @ParmResource VarChar(255) = NULL --'Barbara Henhapl',NULL
    DECLARE @ParmTaskECF VarChar(255) = NULL --NULL ,'0', '1'
    
    ; WITH _CTE_Capacity AS
    (
    	SELECT   CASE WHEN @ParmProjecteriodLevel = 'W' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + DATEPART(ww,TimeByDay),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'M' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + CAST(MONTH(TimeByDay)AS char(2)),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'Y' THEN CAST(YEAR(TimeByDay) as Char(4)) 
    				   WHEN @ParmProjecteriodLevel = 'D' THEN CAST(CAST(TimeByDay AS date) as varchar(20))
    			  END AS Period
    			, R.ResourceUID
    			, R.ResourceName
    			, SUM(Capacity) AS Capacity
    	FROM dbo.MSP_EpmResource_UserView AS R
    	INNER JOIN dbo.MSP_EpmResourceByDay_UserView AS RbD
    		ON R.ResourceUID = RbD.ResourceUID
    	WHERE 
    		(
    		R.ResourceName = @ParmResource
    		OR
    		@ParmResource IS NULL
    	   )
    	   AND
    	   (
    		RbD.TimeByDay >= @FROM
    		OR
    		@FROM IS NULL
    	   )
    	   AND
    	   (
    		RbD.TimeByDay <= @ParmTo
    		OR
    		@ParmTo IS NULL
    	   )
    	GROUP BY CASE WHEN @ParmProjecteriodLevel = 'W' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + DATEPART(ww,TimeByDay),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'M' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + CAST(MONTH(TimeByDay)AS char(2)),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'Y' THEN CAST(YEAR(TimeByDay) as Char(4)) 
    				   WHEN @ParmProjecteriodLevel = 'D' THEN CAST(CAST(TimeByDay AS date) as varchar(20))
    			  END
    			  , R.ResourceUID
    			  , R.ResourceName
    			  , R.[Resource Departments]
    )
    , _CTE_ActualWork AS
    (
    SELECT        R.ResourceUID
    			, R.ResourceName
    			, R.[Resource Departments]
    			, P.ProjectName
    			, SUM(ABD.AssignmentActualWork) AS ActualWork
    			, CASE WHEN @ParmProjecteriodLevel = 'W' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + DATEPART(ww,TimeByDay),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'M' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + CAST(MONTH(TimeByDay)AS char(2)),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'Y' THEN CAST(YEAR(TimeByDay) as Char(4)) 
    				   WHEN @ParmProjecteriodLevel = 'D' THEN CAST(CAST(TimeByDay AS date) as varchar(20))
    			  END AS Period
    			
    FROM            dbo.MSP_EpmAssignment_UserView AS A 
    INNER JOIN dbo.MSP_EpmAssignmentByDay_UserView AS ABD 
    	ON A.AssignmentUID = ABD.AssignmentUID 
    INNER JOIN  dbo.MSP_EpmTask_UserView AS T 
    	ON A.TaskUID = T.TaskUID 
    INNER JOIN dbo.MSP_EpmResource_UserView AS R 
    	ON A.ResourceUID = R.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON T.ProjectUID = P.ProjectUID 
    WHERE  ABD.AssignmentActualWork > 0
    	   AND
    	   (
    		T.[Billable (Proba)] = @ParmTaskECF
    		OR
    		@ParmTaskECF IS NULL
    	   )
    	  AND 
    	   (
    		R.ResourceName = @ParmResource
    		OR
    		@ParmResource IS NULL
    	   )
    	   AND
    	   (
    		P.ProjectName = @ParmProject
    		OR
    		@ParmProject IS NULL
    	   )
    	   AND
    	   (
    		ABD.TimeByDay >= @FROM
    		OR
    		@FROM IS NULL
    	   )
    	   AND
    	   (
    		ABD.TimeByDay <= @ParmTo
    		OR
    		@ParmTo IS NULL
    	   )
    GROUP BY  R.ResourceUID
    			, R.ResourceName
    			, R.[Resource Departments]
    			, P.ProjectName
    			,  CASE WHEN @ParmProjecteriodLevel = 'W' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + DATEPART(ww,TimeByDay),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'M' THEN CAST(YEAR(TimeByDay) as Char(4)) + '-' + CAST(RIGHT(100 + CAST(MONTH(TimeByDay)AS char(2)),2) AS char(2))
    				   WHEN @ParmProjecteriodLevel = 'Y' THEN CAST(YEAR(TimeByDay) as Char(4)) 
    				   WHEN @ParmProjecteriodLevel = 'D' THEN CAST(CAST(TimeByDay AS date) as varchar(20))
    			  END
    )
    
    
    SELECT Act.Period
    	, CASE WHEN @ParmResourceesourceLevel = 'Resource' THEN Act.ResourceName
    		   WHEN @ParmResourceesourceLevel = 'Department' THEN Act.[Resource Departments]
    	  END AS Resource
    	, SUM(Act.ActualWork) AS ActualWork
    	, Act.ProjectName
    	, SUM(Cap.Capacity) AS Capacity
    	, SUM(Act.ActualWork) / SUM(Cap.Capacity) * 100 AS Utilization
    FROM _CTE_ActualWork AS Act
    	INNER JOIN _CTE_Capacity AS Cap
    ON ACT.Period = Cap.Period
    	AND Act.ResourceUID = Cap.ResourceUID
    WHERE Cap.Capacity > 0
    GROUP BY  Act.Period
    		, Act.ProjectName
    		, CASE WHEN @ParmResourceesourceLevel = 'Resource' THEN Act.ResourceName
    			WHEN @ParmResourceesourceLevel = 'Department' THEN Act.[Resource Departments]
    		  END

    Regards
    Barbara


    To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!

    • Marked as answer by PetarB Wednesday, November 30, 2016 8:37 AM
    Saturday, November 26, 2016 8:52 AM
    Moderator
  • Hi Barbara,

    Yes, I agree with you totally. I am in a dire need for SQL training. 

    I am sorry to say that this query does not work for me. When I put it into PowerPivot it returns empty columns. It is Valid however, there are no imported rows. I don't know, maybe I am doing something wrong or I am missing some steps. Anyway I will share it with some IT guys and hopefully they will get it to work.

    I cannot thank you enough for your help and really appreciate the time you put into my questions. 

    With best regards,
    Petar
    Monday, November 28, 2016 11:16 AM
  • Hi Barbara,

    Just wanted to let you know I managed to get it working.
    All I had to do was modify 
    DECLARE @ParmProject VarChar(255) = NULL --'Test', NULL
    DECLARE @ParmTaskECF VarChar(255) = 1 --NULL ,'0', '1'
    I appreciate it very much!

    With best regards,
    Petar 
    Wednesday, November 30, 2016 8:40 AM