Category Archives: Project Server 2013

Issue: Recently faced an issue with a client regarding Project Server 2013 Resource Capacity Planning Report. The client had some old projects uploaded and they wanted to view the resource capacity and allocated hours for last few months. They were only able to see information for one last month only.

Solution: The solution has two steps –

1. Central Administration changes –
   1. In SharePoint Central Administration, click Application Management.
   2. On the Application Management page, in the Service Application section, click Manage Service Applications.
   3. On the Service Applications page, click the Project Application Service that contains the Project Web App instance for which you want to access the Resource Capacity settings.
   4. On the Manage Project Web Apps page, click the drop-down menu for the PWA instance for which you want to access the Resource Capacity settings, and click Manage.
   5. On the Server Settings page, in the Operational Policies section, click Additional Server Settings.
   6. On the Additional Server Settings page, in the Resource Capacity Settings section, for Active capacity view, enter the following:
      1. In the Months behind field, enter the number of months in the past that you want resource data to be calculated from. I changed it to 12.
      2. In the Months ahead field, enter the number of months in the future that you want resource data to be calculated from. Kept the default to 12.
   7. Click Save.
   8. You will have to run the timer job Project Server: Resource Capacity Refresh job for Project Server Service Application for getting the report refreshed with old dates.
2. Open the ResourceCapacityPlanning Report in excel.

Click on Data -> Connections.

On Connections Select Project Server – Resource Capacity and click Properties

On Properties Window click on Definition Tab.

On Definition Window check the Command Text column

The SQL Command text should look like –
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))

Find all instances of CONVERT(DATETIME, DATEADD(month, – 2, CURRENT_TIMESTAMP), 102)) and change the -2 to the months you are interested in going back. There are two instances. I have bolded the text in the SQL above. The client was looking for 3 months back so I changed it to –

CONVERT(DATETIME, DATEADD(month, -3, CURRENT_TIMESTAMP), 102))

Save the file and now your report should pull the appropriate data.