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))

ProResTask
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

ProRes
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)

ResCapa
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)) 

ResCapaAvail

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_MOEKSA.pjpub.MSP_WORKFLOW_STATUS WFS

INNER JOIN WSS_Content_MOEKSA.pjpub.MSP_WORKFLOW_STAGES WFStage

ON WFS.STAGE_UID =  WFStage.STAGE_UID

INNER JOIN WSS_Content_MOEKSA.pjpub.MSP_WORKFLOW_PHASES WFP

ON WFStage.PHASE_UID =  WFP.PHASE_UID

LEFT Outer  JOIN WSS_Content_MOEKSA.pjpub.MSP_WORKFLOW_STAGES WFStage1

ON WFStage1.STAGE_UID =  WFS.NEXT_STAGE1

Inner Join WSS_Content_MOEKSA.pjrep.MSP_EpmProject_UserView PUV

ON PUV.ProjectUID = WFS.PROJ_UID

And WFS.stage_status!=0

Order by PUV.ProjectName, WFS.stage_entry_date  

 

post project stage duration

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_MOEKSA.pjpub.MSP_WORKFLOW_STATUS WFS

INNER JOIN WSS_Content_MOEKSA.pjpub.MSP_WORKFLOW_STAGES WFStage

ON WFS.STAGE_UID =  WFStage.STAGE_UID

INNER JOIN WSS_Content_MOEKSA.pjpub.MSP_WORKFLOW_PHASES WFP

ON WFStage.PHASE_UID =  WFP.PHASE_UID

LEFT Outer  JOIN WSS_Content_MOEKSA.pjpub.MSP_WORKFLOW_STAGES WFStage1

ON WFStage1.STAGE_UID =  WFS.NEXT_STAGE1

Inner Join WSS_Content_MOEKSA.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>

post project phase duration

Project Current Workflow Phase and Stage


SELECT

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

FROM

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

ON WFS.STAGE_UID =  WFStage.STAGE_UID

INNER JOIN WSS_Content_MOEKSA.pjpub.MSP_WORKFLOW_PHASES WFP

ON WFStage.PHASE_UID =  WFP.PHASE_UID

Inner Join WSS_Content_MOEKSA.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>

post project phase and stage

Advertisements

One thought on “Project Server Report or Dashboard SQL Queries

  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s