Project Server 2016: Reporting on workflow data


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

image

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:

image

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.

image


Comments (6)

  1. Robert Poulin says:

    Hello Brian,

    Over the years, I have developed many SSRS reports that helped delivering to my on-premises customers EPM based solutions that perfectly fit their needs.

    Example of such reports are those comparing timesheet’s to projects’ timephased data, this to support the time declaration process of some projects management cycle. For middle-sized and large organisations, that kind of data integrity check reporting can be quite resource consuming, but data are indexed (should the right tables be used) and one can filter a query to limited spawn of time, list of projets and list of resources.

    With Project Server 2016, I intend to deploy any PWA instance within its own content database. I thus don’t see any need to filter data per the SiteGUI (all data in the BD will then have the same SiteGUID, right?). However, I understand that this requirement might be inherited from Project Online that needs to isolate data between instances.

    For on-premises users, is the RDB the way we knew it from 2007, 2010 and 2013 versions still supported, and should it be improved, at the least backward compatible ?

    Are the reporting TBV functions now the only officially supported direct access to Project Server data, with the the pjrep section being more a staging than a reporting database, since reporting TBV functions can gather data from published data directly, whenever appropriate ?

    Regards and many thanks to you and co-workers to maintain such a usefull blog.

    — Robert Poulin, project management solutions architect.

    1. Hi Robert, the official access points should be through the TVF’s where possible – but as you mention, in your scenario the SiteID and partition of data isn’t the issue – but the TVFs will often get data from the Pub schema so do not have a pjrep basic table or view equivalent. My approach to on-premises queries has always been that we have our supported guidance – but at the end of the day it is your data and there may be some requirements where it makes sense to read what you have to – to get the information you need. Just because it isn’t ‘supported’ doesn’t always mean you shouldn’t do it (but you need to fully understand what you are doing and make that conscious decision). If there are things missing from the ‘supported’ approach then always worth raising this via UserVoice https://microsoftproject.uservoice.com/forums/218133-office-365-project-portfolio-management-ppm. I know this is a bit of a wooly answer – but hope it helps. Now we are in the Content DB then if you need your own tables these should certainly be in your own database – and not adding items to the Content DB.
      Best regards,
      Brian.

      1. Robert Poulin says:

        Hi Brian, thanks for the quick reply.
        I got the point. I will thus need to plan a bit more adaptation work with 2007, 2010, and 2013 SSRS reports when migrating my customers to 2016 as some legacy reporting database/schema tables and views might have been deprecated.
        As you suggest, I will raise the point through UserVoice that this change could has been better communicated to on-premises users.
        Regards,
        Robert.

  2. Adrian says:

    Hi Brian, hi Christoph,

    we created a project custom field (free text), but we can only seem to find it in the project user view, but in none of the TVFs.
    Is it possible to access custom field via TFVs? If so, how?

    Note:
    select OBJECT_SCHEMA_NAME(object_id) as SchemaN, OBJECT_NAME(object_id) as objName, *
    from sys.columns
    –where name = ‘ProjectName’
    where name like ‘%binford%’
    This query showed only columns in the project user view, no results in TVFs at all.

    Note: We have the MSP_TVF_EpmProject, but nothing like a “MSP_TVF_EpmProject_UserView” or similar.
    Should that view exist, but our instance is broken?

    Thank you and kind regards,
    Adrian

  3. Christoph Mülder says:

    Hi Brian,
    yes. The TVF you mention does contain the data but there is also a [pjrep].[MSP_TVF_EpmWorkflowStatusInformation] and this does not show any data in 2016. In my migrated environment I have there data for all the old projects and we built functionality on the corresponding table data. If at least the new TVF would show the data it would “only” be the function call that we would need to replace the table with. But here is something more missing…..
    Regards
    Christoph

  4. Adrian says:

    The Project_UserView is back at least.
    Although I still had to manually update my content databases and manually repair my PWA instances for it.

Skip to main content