Project Server Report or Dashboard SQL Queries

Resource Capacity Vs Allocation by Month/Year

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],
DATENAME(month, MSP_EpmResourceByDay_UserView.TimeByDay) as [Month],
YEAR(MSP_EpmResourceByDay_UserView.TimeByDay) as [Year],
(SELECT [RBS] from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [RBS]
FROM
dbo.MSP_EpmResourceByDay_UserView
LEFT OUTER JOIN
(
SELECT
MSP_EpmAssignment_UserView.ResourceUID,
SUM(MSP_EpmAssignmentByDay_UserView.AssignmentCombinedWork) as [AllocatedCapacity],
MSP_EpmAssignmentByDay_UserView.TimeByDay
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
WHERE
MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN
CONVERT(DATETIME, DATEADD(month, - 2, CURRENT_TIMESTAMP), 102)
AND CONVERT(DATETIME, DATEADD(month, 6, CURRENT_TIMESTAMP), 102)
GROUP BY
MSP_EpmAssignment_UserView.ResourceUID,
MSP_EpmAssignmentByDay_UserView.TimeByDay
) AS AssignmentTable
ON AssignmentTable.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID
AND AssignmentTable.TimeByDay = MSP_EpmResourceByDay_UserView.TimeByDay
WHERE
(MSP_EpmResourceByDay_UserView.TimeByDay > CONVERT(DATETIME, DATEADD(month, - 2, CURRENT_TIMESTAMP), 102))
AND (MSP_EpmResourceByDay_UserView.TimeByDay < CONVERT(DATETIME, DATEADD(month, 6, CURRENT_TIMESTAMP), 102))

Reference: http://gallery.technet.microsoft.com/projectserver/Server-2010-SQL-Get-5842010f

Project Task’s Resources

SELECT
            dbo.MSP_EpmAssignment_UserView.ProjectUID,
            dbo.MSP_EpmAssignment_UserView.TaskUID,
            dbo.MSP_EpmProject_UserView.ProjectName,
            dbo.MSP_EpmTask_UserView.TaskName,
            dbo.MSP_EpmAssignment_UserView.ResourceUID,
            dbo.MSP_EpmResource_UserView.ResourceName,
            dbo.MSP_EpmResource_UserView.ResourceInitials 

INTO #TempTable  

FROM         dbo.MSP_EpmAssignment_UserView INNER JOIN
dbo.MSP_EpmProject_UserView ON dbo.MSP_EpmAssignment_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID INNER JOIN
dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmAssignment_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID INNER JOIN
dbo.MSP_EpmResource_UserView ON dbo.MSP_EpmAssignment_UserView.ResourceUID = dbo.MSP_EpmResource_UserView.ResourceUID 

SELECT
  ProjectUID,
  TaskUID,
  ProjectName,
  TaskName,
  STUFF((
    SELECT ', ' + ResourceInitials
    FROM #TempTable
    WHERE (TaskUID = Results.TaskUID)
    FOR XML PATH (''))
  ,1,2,'') AS ResourceInitialsCombined,
   STUFF((
    SELECT ', ' + ResourceName
    FROM #TempTable
    WHERE (TaskUID = Results.TaskUID)
    FOR XML PATH (''))
  ,1,2,'') AS ResourceNameCombined
FROM #TempTable Results
GROUP BY TaskUID,ProjectUID,ProjectName,TaskName 

DROP TABLE #TempTable

Reference: Couldn’t remember :S

Resources Capacity Day Wise

SELECT
MSP_EpmResource_UserView.ResourceName,
Year(MSP_EpmResourceByDay_UserView.TimeByDay) AS Year,
MONTH(MSP_EpmResourceByDay_UserView.TimeByDay) AS Month,
Day(MSP_EpmResourceByDay_UserView.TimeByDay) AS Day,
SUM(MSP_EpmResourceByDay_UserView.Capacity) AS Capacity
FROM
MSP_EpmResource_UserView
LEFT OUTER JOIN MSP_EpmResourceByDay_UserView ON
MSP_EpmResource_UserView.ResourceUID =
MSP_EpmResourceByDay_UserView.ResourceUID
/*WHERE
(MSP_EpmResourceByDay_UserView.TimeByDay BETWEEN ’01/01/2010′ AND ’12/31/2015′)*/
GROUP BY
MSP_EpmResource_UserView.ResourceName,
Year(MSP_EpmResourceByDay_UserView.TimeByDay),
MONTH(MSP_EpmResourceByDay_UserView.TimeByDay),
DAY(MSP_EpmResourceByDay_UserView.TimeByDay)

Reference: http://robhardyuk.wordpress.com/2012/10/12/resource-capacity-sql-query-for-a-report/

Resources Capacity and Availability Day Wise

SELECT
               (SELECT ResourceName from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [ResourceName],
               YEAR(MSP_EpmResourceByDay_UserView.TimeByDay) as [Year],
               DATENAME(month, MSP_EpmResourceByDay_UserView.TimeByDay) as [Month],
               Day(MSP_EpmResourceByDay_UserView.TimeByDay) as [Day],
               MSP_EpmResourceByDay_UserView.Capacity as [ResourceCapacity],
               ISNULL(AssignmentTable.AllocatedCapacity,0) as [AllocatedCapacity],
               MSP_EpmResourceByDay_UserView.Capacity-ISNULL(AssignmentTable.AllocatedCapacity,0) as [Availability], 

               (SELECT [RBS] from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID) as [RBS]
            FROM
               dbo.MSP_EpmResourceByDay_UserView
            LEFT OUTER JOIN
               (
                SELECT
                   MSP_EpmAssignment_UserView.ResourceUID,
                   SUM(MSP_EpmAssignmentByDay_UserView.AssignmentCombinedWork) as [AllocatedCapacity],
                   MSP_EpmAssignmentByDay_UserView.TimeByDay
                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
                WHERE
                   MSP_EpmAssignmentByDay_UserView.TimeByDay BETWEEN
                                                             CONVERT(DATETIME, DATEADD(month, - 2, CURRENT_TIMESTAMP), 102)
                                                             AND CONVERT(DATETIME, DATEADD(month, 6, CURRENT_TIMESTAMP), 102)
                GROUP BY
                   MSP_EpmAssignment_UserView.ResourceUID,
                   MSP_EpmAssignmentByDay_UserView.TimeByDay
                ) AS AssignmentTable
                   ON AssignmentTable.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID
                      AND AssignmentTable.TimeByDay = MSP_EpmResourceByDay_UserView.TimeByDay
            WHERE
              (MSP_EpmResourceByDay_UserView.TimeByDay > CONVERT(DATETIME, DATEADD(month, - 2, CURRENT_TIMESTAMP), 102))
              AND (MSP_EpmResourceByDay_UserView.TimeByDay < CONVERT(DATETIME, DATEADD(month, 6, CURRENT_TIMESTAMP), 102))

Workflow Stage Duration


SELECT

PUV.ProjectUID

,PUV.ProjectName

,WFP.PHASE_NAME

,WFStage.STAGE_NAME

,FORMAT(WFS.STAGE_ENTRY_DATE,'dd-MM-yyyy hh:mm:ss tt') AS STAGE_ENTRY_DATE

,FORMAT(WFS.STAGE_COMPLETION_DATE,'dd-MM-yyyy hh:mm:ss tt') AS STAGE_COMPLETION_DATE

,Case

	When DATEDIFF(DAY,WFS.STAGE_ENTRY_DATE,WFS.STAGE_COMPLETION_DATE) = 0 Then ' 0 Then Convert(varchar(50), DATEDIFF(DAY,WFS.STAGE_ENTRY_DATE,WFS.STAGE_COMPLETION_DATE))

END as 'Duration in Days'

FROM

WSS_Content_SP.pjpub.MSP_WORKFLOW_STATUS WFS

INNER JOIN WSS_Content_SP.pjpub.MSP_WORKFLOW_STAGES WFStage

ON WFS.STAGE_UID =  WFStage.STAGE_UID

INNER JOIN WSS_Content_SP.pjpub.MSP_WORKFLOW_PHASES WFP

ON WFStage.PHASE_UID =  WFP.PHASE_UID

LEFT Outer  JOIN WSS_Content_SP.pjpub.MSP_WORKFLOW_STAGES WFStage1

ON WFStage1.STAGE_UID =  WFS.NEXT_STAGE1

Inner Join WSS_Content_SP.pjrep.MSP_EpmProject_UserView PUV

ON PUV.ProjectUID = WFS.PROJ_UID

And WFS.stage_status!=0

Order by PUV.ProjectName, WFS.stage_entry_date

Workflow Phase Duration


--Project Phase Wise

SELECT

PUV.ProjectUID,

PUV.ProjectName,

WFP.PHASE_NAME,

min(WFS.STAGE_ENTRY_DATE) AS PHASE_ENTRY_DATE,

max(WFS.STAGE_COMPLETION_DATE) AS PHASE_COMPLETION_DATE,

Case

	When DATEDIFF(DAY,min(WFS.STAGE_ENTRY_DATE),max(WFS.STAGE_COMPLETION_DATE)) = 0 Then ' 0 Then Convert(varchar(50), DATEDIFF(DAY,min(WFS.STAGE_ENTRY_DATE),max(WFS.STAGE_COMPLETION_DATE)))

END as 'Duration in Days'

FROM

WSS_Content_SP.pjpub.MSP_WORKFLOW_STATUS WFS

INNER JOIN WSS_Content_SP.pjpub.MSP_WORKFLOW_STAGES WFStage

ON WFS.STAGE_UID =  WFStage.STAGE_UID

INNER JOIN WSS_Content_SP.pjpub.MSP_WORKFLOW_PHASES WFP

ON WFStage.PHASE_UID =  WFP.PHASE_UID

LEFT Outer  JOIN WSS_Content_SP.pjpub.MSP_WORKFLOW_STAGES WFStage1

ON WFStage1.STAGE_UID =  WFS.NEXT_STAGE1

Inner Join WSS_Content_SP.pjrep.MSP_EpmProject_UserView puv

ON puv.ProjectUID = WFS.PROJ_UID

And wfs.stage_status!=0

Group by PUV.ProjectUID,puv.projectname,Phase_Name

Order By puv.projectname, min(WFS.STAGE_ENTRY_DATE)<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Project Current Workflow Phase and Stage


SELECT

Top 1 Puv.ProjectName,  WFP.PHASE_NAME, WFStage.STAGE_NAME

FROM

WSS_Content_SP.pjpub.MSP_WORKFLOW_STATUS WFS INNER JOIN WSS_Content_SP.pjpub.MSP_WORKFLOW_STAGES WFStage

ON WFS.STAGE_UID =  WFStage.STAGE_UID

INNER JOIN WSS_Content_SP.pjpub.MSP_WORKFLOW_PHASES WFP

ON WFStage.PHASE_UID =  WFP.PHASE_UID

Inner Join WSS_Content_SP.pjrep.MSP_EpmProject_UserView puv

ON puv.ProjectUID = WFS.PROJ_UID

And WFS.stage_status!=0

where WFS.PROJ_UID='05F9C08A-66A7-E711-80D3-00505692534B'

Order by WFS.stage_entry_date desc<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

2 thoughts on “Project Server Report or Dashboard SQL Queries”

Ben Guess

In the top query, your screenshot shows the Project names in the query data, but the query does not return the project names. Can you update the query to show the project names? The query will then return the allocation and capacity by person by project.

Thanks.

August 22, 2014 at 4:26 pm Reply
William K Collier

Namaste, Are you still monitoring this site?
If so i also use the top SQL for Allocation and Capacity my issue it trying to add in the MSP_EpmAssignment_UserView.AssignmentBookingID so the report can be filtered for proposed and booked as the Resources allocation view on the PWA.
Any ideas would be greatly appreciated!
many thanks
bill

May 28, 2020 at 8:00 pm Reply