Project Server 2016: Reporting on workflow data

This post has been republished via RSS; it originally appeared at: Project Support Blog articles.

This blog comes out of a Project Server 2016 support incident where it was identified that the reporting tables (and therefore views and table valued functions) for workflow related data such as Phases and Stages, and the workflow status info were not getting populated during a publish or update of any workflow related data.  Also no Reporting (Workflow Metadata Sync) jobs were seen in the queue,  Initially it seemed like something had been turned off – and I even found the missing line of code that would have triggered the synchronization – so submitted a fix request…  But I got it wrong, all was working perfectly, and as designed – you just need to look in the right place.  The tables that we had been using in 2013 are obsolete and instead any reporting should be carried out through a table valued function (TVF) pjrep.MSP_TVF_EpmProjectWorkflowStatusInformation_UserView.  The Project Server 2016 TVFs need a parameter of the SiteID of the PWA being queried, and as for all direct database reporting in Project Server 2016 this is only supported where a single PWA site resides in the Content DB (other PWA sites can be in other Content DB in the same web application).

So for example in my system where the SiteID is 8F2455DA-2745-40AE-B9E0-F1E6DD573AFB I can use the following queries to get the data.

select * from pjrep.MSP_TVF_EpmProjectWorkflowStatusInformation_UserView('8F2455DA-2745-40AE-B9E0-F1E6DD573AFB')

With results as follows:

For those interested in the technicalities you could always break open the TVF to see where the data is coming from – and you’d find that for the 3rd query this is pulling from pjrep.MSP_EpmProjectWorkflowStatusInformation_UserView and filtering for the SiteID – and the view itself is pulling from the tables in the pjpub schema and just one from the pjrep schema – which is static info (pjrep.MSP_EpmWorkflowStatusType).  This is cool as it means no data is being sync’d between schema – just one source of truth.  The same approach was taken with the Resource Engagement data too – no sync – just views on to the data.  There aren’t any individual TVFs to pull phases and stages – but you could extract them from the data above using something like the following – as selecting from a TVF is exactly like selecting from a table or view:

Select Distinct PhaseName, StageName from pjrep.MSP_TVF_EpmProjectWorkflowStatusInformation_UserView('8F2455DA-2745-40AE-B9E0-F1E6DD573AFB').

.The data is of course available via OData too, and is the only supported option if you have multiple PWA instances in the same Content DB – the end point you need is something like:

http://<servername>/sites/pwa/_api/ProjectData/ProjectWorkflowStageDataSet

And this would return contents like the query above.  As for the TVFs, there isn’t an OData feed specifically for stages and phases – but you do see them in the results anyway so not usually any need to have them individually, although of course you could pull them out.