Kashif Nizam Qureshi

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

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

,Case

When DATEDIFF(DAY,WFS.STAGE_ENTRY_DATE,WFS.STAGE_COMPLETION_DATE) = 0 Then '<1'

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

END as 'Duration in Days'

FROM

  PUB.MSP_WORKFLOW_STATUS WFS

  INNER JOIN pub.MSP_WORKFLOW_STAGES WFStage

  ON WFS.STAGE_UID =  WFStage.STAGE_UID

  INNER JOIN pub.MSP_WORKFLOW_PHASES WFP

  ON WFStage.PHASE_UID =  WFP.PHASE_UID

  LEFT Outer  JOIN pub.MSP_WORKFLOW_STAGES WFStage1

  ON WFStage1.STAGE_UID =  WFS.NEXT_STAGE1

  Inner Join MSP_EpmProject_UserView PUV

  ON PUV.ProjectUID = WFS.PROJ_UID

  And WFS.stage_status!=0

  Order by PUV.ProjectUID, WFS.stage_entry_date             

ResCapaAvail

Workflow Phase Duration


SELECT

PUV.ProjectUID,

PUV.ProjectName,

WFP.PHASE_NAME

,PUV.Department

,PUV.Entity

,PUV.ProjectOwnerName

,min(WFS.STAGE_ENTRY_DATE) AS STAGE_ENTRY_DATE

,max(WFS.STAGE_COMPLETION_DATE) AS STAGE_COMPLETION_DATE

,Case

When DATEDIFF(DAY,min(WFS.STAGE_ENTRY_DATE),max(WFS.STAGE_COMPLETION_DATE)) = 0 Then '<1'

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

END as 'Duration in Days'

  FROM

  pub.MSP_WORKFLOW_STATUS WFS

  INNER JOIN pub.MSP_WORKFLOW_STAGES WFStage

  ON WFS.STAGE_UID =  WFStage.STAGE_UID

  INNER JOIN pub.MSP_WORKFLOW_PHASES WFP

  ON WFStage.PHASE_UID =  WFP.PHASE_UID

  LEFT Outer  JOIN pub.MSP_WORKFLOW_STAGES WFStage1

  ON WFStage1.STAGE_UID =  WFS.NEXT_STAGE1

  

  Inner Join MSP_EpmProject_UserView puv

  ON puv.ProjectUID = WFS.PROJ_UID

  And wfs.stage_status!=0

  Group by PUV.ProjectUID,puv.projectname,Phase_Name

  ,PUV.Department

,PUV.Entity

,PUV.ProjectOwnerName

Order By puv.projectname, min(WFS.STAGE_ENTRY_DATE)

One thought on “Project Server Report or Dashboard SQL Queries

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s