Resource Capacity SQL Query for a Project Server Report

Here is a good Project Server resource capacity query, which works by time;

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)

Getting the resource availability took a bit of thought, its not available as a direct field in the reporting db. This query can have a narrowed time range as well, and this is commented out in the above query.​

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