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