Creating a Project Resource List in a PDP

This question comes up fairly frequently….how do I create a list of project team members on a Project Server PDP?  Well, the answer is, you can’t – well sort of.  You see much of that data is in the ProjectResource table – which is only available in the Publish database.  Since we all know that querying the Publish database is forbidden, we can’t really access that table.

The Reporting database on the other hand contains the assignment data.  Hence, if your resource is actually assigned a task, they will appear there.  If your resource is not assigned a task, they will not appear in the Reporting database.

Got it?

So with that background, assuming that you decide where to query from, here’re a couple of SQL queries to help you get started.

From Published – which picks up all resources on the project team.  Just providing this for reference.  You shouldn’t be using this….

SELECT     MSP_PROJECTS.PROJ_NAME, ProjectResource.RES_NAME, CAST(MSP_PROJECTS.PROJ_UID AS VARCHAR(36)) AS UID 
FROM         ProjectResource INNER JOIN 
                      MSP_PROJECTS ON ProjectResource.PROJ_UID = MSP_PROJECTS.PROJ_UID

And from Reporting – which only picks up resources with assignments.  This is the approved approach.

SELECT     CAST(MSP_EpmAssignment_UserView.ProjectUID AS VARCHAR(36)) AS UID, MSP_EpmProject_UserView.ProjectName, MSP_EpmResource_UserView.ResourceName, 
                      MSP_EpmAssignment_UserView.AssignmentWork 
FROM         MSP_EpmAssignment_UserView LEFT OUTER JOIN 
                      MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID LEFT OUTER JOIN 
                      MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

Note that I modified the ProjectUID field in both instances so it will appear in Excel properly.

From there, it’s just a matter of following the instructions in this post to lay out a filtered Excel or SSRS report that displays the data.

Here’s the report…  Note that I am using the UID as the filter.

image

I promote it into SharePoint, create a PerformancePoint report, validate the filter parameter….and from there, it’s just a matter of inserting it into the PDP and linking it to the Query String Filter.  I didn’t have to resort to the InfoPath query hack as I used the UID in the report itself.

image

Creating a Project Resource List in a PDP