Extending the Project Backstage… Getting the data
By blurg64
In the first of three posts covering my proof of concept to show the workflow status in the Project backstage, I will be covering off how to access the workflow data itself from within the Project Server.
Now as this was a proof of concept, I decided to go with the simple approach of getting the information directly from the Reporting database instead of calling the Project Server PSI. There were a couple of reasons for this, firstly simplicity, accessing the data in the Reporting database via SQL and VBA is much simpler in my opinion than calling the PSI. The second reason was it appeared that calling the PSI from the client requires custom code to be developed, and my 2010 development environment didn’t have VS installed 🙂
Workflow information in the reporting database
In the 2010 Reporting database four new tables have been added to assist in reporting against the workflows.
MSP_EPMWorkflowPhase – a lookup table consisting of the workflow phases and their associated description
MSP_EPMWorkflowStage – a lookup table consisting of the workflow stages and their associated description
MSP_EpmWorkflowStatusType – a lookup table consisting the workflow status and it’s associated language
MSP_EPMWorkflowStatusInformation – Contains information about each project and it’s associated workflow status over time.
These tables provide the core information about where a project is in a workflow, including the project stage, status and date information of when it entered and left the stage. Unfortunately what they don’t provide is an indication of any associated approvals, which makes sense when you understand that the approvals are not part of Project Server per se, but rather the underlying SharePoint platform.
To extract the current workflow status of the project from the reporting database, I used the following query:
[sourcecode language=”sql” wraplines=”false”]
select epmp.ProjectName,
epmwfs.StageName,
epmwfs.StageDescription,
epmwsi.StageInformation,
epmwfst.StageStateDescription
from MSP_EpmWorkflowStatusInformation epmwsi
inner join MSP_EpmWorkflowStage epmwfs on epmwsi.StageUID = epmwfs.StageUID
inner join MSP_EpmWorkflowStatusType epmwfst on epmwsi.StageStatus = epmwfst.StageStatusID
inner join MSP_EpmProject epmp on epmwsi.ProjectUID = epmp.ProjectUID
where epmwsi.ProjectUID = {Project GUID}
and (StageEntryDate is not null and StageCompletionDate is null)
[/sourcecode]
The query takes the Project GUID as a parameter, and returns the currently in progress workflow stage as determined by the StageEntryDate and StageCompletionDate fields.
For the purposes of the backstage proof of concept, I was only interested in returning the core project workflow information, but the query could easily be extended to return other information such as the Enterprise Project Type and it’s associated description.
Now we have the workflow status information as retrieved from the Project Server Reporting database, in the next post, I will discuss how we extend the Project backstage and wire up the data we retrieved.