Wednesday, June 8, 2016

Reporting from Project Server 2016 - multiple sites and userviews

Just a quickie...
I've been interested in how MS have handled the "multiple PWA sites in a Content DB" thing since I read that this was their new approach.  Most of my reporting is via SSRS so i am reliant (still... in 2016) on DB queries rather than OData feeds (tsk) and this "querying a PWA DB with more than one PWA site in it is unsupported" quote was worrying me.

So it looks like what is happening is this.

When you create the first PWA site in a Content DB it hard-codes the SiteID into the _Userview view design elements.  This means that your first PWA Site is the default.  All the data for subsequent sites are still held in the tables against separate SiteID's but you cannot utilise the OOTB _Userview components (see below)


SELECT        ProjectFields....
FROM            pjrep.MSP_TVF_EpmProject('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmProject 
LEFT OUTER JOIN
                      pjrep.MSP_TVF_EpmInternalProjectHierarchies('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmInternalProjectHierarchies              ON    MSP_EpmInternalProjectHierarchies.ChildProjectUID = MSP_EpmProject.ProjectUID 
LEFT OUTER JOIN
                      pjrep.MSP_TVF_EpmTask('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmTask 
             ON    MSP_EpmTask.ProjectUID = MSP_EpmProject.ProjectUID 
             AND  MSP_EpmTask.TaskIsProjectSummary = 1

now you can see lots of references to pjrep.MSP_TVF....  these are Table Functions that allow the Site ID to be passed and perform a SELECT against the core table.

The above views have the initial SIteID hard coded so (** WARNING:  NOT SUPPORTED APPARENTLY **) if you want to leverage _UserViews you will have to make your own using the appropriate SiteID references for the Table functions.

Or alternatively just use the Custom Field joins from the UserView view design in your own select statement to get just the fields required and avoid the userviews (ideal but hey... we can all be lazy sometimes).

So here's your lesson:  MANAGE YOUR CONTENT DATABASES!  Only allow one PWA site to be stored in the ContentDB otherwise it is just pain and anguish (hot topics for an England fan given its nearly the start of Euro2016)