Getting Project ‘Workflow Stage’ from Project Server 2010 Reporting DB

Below is a script which obtains the projects workflow stage from the Reporting Database. This isn’t in the MSP_EPMProject_UserView as you would expect;

SELECT
MSP_EpmProject_UserView.ProjectName AS [Project Name],
ProjectOwnerName AS [Owner],
MSP_EpmWorkflowStage.StageName AS [Workflow Stage Name]

FROM
MSP_EpmWorkflowStage

INNER JOIN
MSP_EpmWorkflowStatusInformation ON MSP_EpmWorkflowStage.StageUID = MSP_EpmWorkflowStatusInformation.StageUID

INNER JOIN
MSP_EpmProject_UserView ON MSP_EpmWorkflowStatusInformation.ProjectUID = MSP_EpmProject_UserView.ProjectUID

WHERE
(MSP_EpmWorkflowStatusInformation.StageEntryDate IS NOT NULL)
AND
(MSP_EpmWorkflowStatusInformation.StageStatus <> 0)
AND
(MSP_EpmWorkflowStatusInformation.StageStatus <> 4)
AND [Project Status] = ‘Active’

ORDER BY MSP_EpmProject_UserView.ProjectName

If using workflow, this is a dynamic way of reporting on the stage of the project, rather than using a custom field.. You can also create a project center view to group on this information.

One comment

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