Using the Reporting Database and Excel – Part 1


I am not sure if this is a programmability post, but there have been many requests to do a post on writing a report in Excel that collects data from the reporting database. Since this will involve SQL and many developers who write code against Project Server will also need to write reports; this may be useful to the developer community. I am going to break this into two posts because it is fairly long and there is a logical break. In the first post, we will create the SQL query to retrieve the data from the reporting database. In the second post, I will describe the process of using the query in Excel to write reports.


Before we begin, it should be known that there is a dedicated reporting database in Project Server 2007. This database is separate from the working and publish database that is heavily used by Project Professional and Project Server. Every time a project is published, or resource is updated, the data is pushed to the reporting database. The reporting database provides a supported interface for gathering project data via SQL queries. This reporting database has been setup to easily create reports.


Someone posted that they wanted a report that showed the assigned work for each resource, grouped by project and then by month. This will allow project managers to better understand the allocation of resources by month. I will try my best writing the query for this, but I shell add the warning that this has not been tested. If you find a mistake, or a better way of writing the query, please post it as a comment.


To begin, we need to write a SQL query against the reporting database to get at the data we need. I wrote the SQL query in stages. The first step was to get the assignment data from the projects. To do that, I wrote the following query:


SELECT


base.ResourceUID as ‘Resource’,


assn.TimeByDay as ‘Day’,


assn.AssignmentWork as ‘Assigned’


FROM


MSP_EPMAssignmentByDay_UserView  AS assn


INNER JOIN MSP_EPMAssignment_UserView AS base


            ON (assn.AssignmentUid  = base.AssignmentUID)


This gives us all the assignments for each resource by name. We will also want the project name that the assignment is coming from. To do this, we will have to do another join on the MSP_EmpProject_UserView:


SELECT


base.ResourceUID as ‘Resource’,


assn.TimeByDay as ‘Day’,


assn.AssignmentWork as ‘Assigned’,


ProjectName


FROM


MSP_EPMAssignmentByDay_UserView  AS assn


      INNER JOIN MSP_EPMAssignment_UserView AS base


            ON (assn.AssignmentUid  = base.AssignmentUID)   


INNER JOIN MSP_EpmProject_UserView


ON base.ProjectUID = MSP_EpmProject_UserView.ProjectUID


Now we have all the assignment data for each resource. For this report we will also want to capture assignments outside of a project. An example of this would be vacation time. To get at this information, we are going to query the Timesheet tables in the reporting database.


SELECT


MSP_TimesheetResource.ResourceUID as ‘Resource’,


ts.TimeByDay as ‘Day’,


ts.ActualWorkBillable as ‘Assigned’


FROM


MSP_TimesheetActual AS ts


      INNER JOIN MSP_TimesheetLine AS tl


            ON (ts.TimesheetLineUID = tl.TimesheetLineUID)


INNER JOIN MSP_TimesheetClass AS tc


ON tl.ClassUID = tc.ClassUID


INNER JOIN MSP_Timesheet AS tsowner


on tl.TimesheetUID = tsowner.TimesheetUID


INNER JOIN MSP_TimesheetResource


ON tsowner.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID


WHERE


tc.[Type] = 2


This returns all the timesheet lines for none project work.


Now we have all the assignments. The next step is to combine the two queries. To do this, I used the union statement. You will notice that in the timesheet query we do not have a project name in the select. We are going to add the project name to the select by adding ‘None Project Time’ as ProjectName to the select statement. Here are the combined queries:


SELECT


base.ResourceUID as ‘Resource’,


assn.TimeByDay as ‘Day’,


assn.AssignmentWork as ‘Assigned’,


ProjectName


FROM


MSP_EPMAssignmentByDay_UserView  AS assn


      INNER JOIN MSP_EPMAssignment_UserView AS base


            ON (assn.AssignmentUid  = base.AssignmentUID)   


INNER JOIN MSP_EpmProject_UserView


ON base.ProjectUID = MSP_EpmProject_UserView.ProjectUID


 


Union ALL


 


SELECT


MSP_TimesheetResource.ResourceUID as ‘Resource’,


ts.TimeByDay as ‘Day’,


ts.ActualWorkBillable as ‘Assigned’,


‘None Project Time’ as ProjectName


FROM


MSP_TimesheetActual AS ts


      INNER JOIN MSP_TimesheetLine AS tl


            ON (ts.TimesheetLineUID = tl.TimesheetLineUID)


INNER JOIN MSP_TimesheetClass AS tc


ON tl.ClassUID = tc.ClassUID


INNER JOIN MSP_Timesheet AS tsowner


on tl.TimesheetUID = tsowner.TimesheetUID


INNER JOIN MSP_TimesheetResource


ON tsowner.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID


WHERE


tc.[Type] = 2


So now we have all the assignment data together. The last step is join with the resource table to get the name of the resources and to group by various fields to roll up the data.


SELECT


ProjectName,


MSP_EpmResource.ResourceName,


CAST(YEAR(Day) as Varchar(4)) + ‘-‘ + CAST(MONTH(Day) as Varchar(2)) as Month,


SUM(Assigned) as Assigned


FROM


MSP_EpmResource


Right Join


(


 


SELECT


base.ResourceUID as ‘Resource’,


assn.TimeByDay as ‘Day’,


assn.AssignmentWork as ‘Assigned’,


ProjectName


FROM


MSP_EPMAssignmentByDay_UserView  AS assn


      INNER JOIN MSP_EPMAssignment_UserView AS base


            ON (assn.AssignmentUid  = base.AssignmentUID)   


INNER JOIN MSP_EpmProject_UserView


ON base.ProjectUID = MSP_EpmProject_UserView.ProjectUID


 


Union ALL


 


SELECT


MSP_TimesheetResource.ResourceUID as ‘Resource’,


ts.TimeByDay as ‘Day’,


ts.ActualWorkBillable as ‘Assigned’,


‘None Project Time’ as ProjectName


FROM


MSP_TimesheetActual AS ts


      INNER JOIN MSP_TimesheetLine AS tl


            ON (ts.TimesheetLineUID = tl.TimesheetLineUID)


INNER JOIN MSP_TimesheetClass AS tc


ON tl.ClassUID = tc.ClassUID


INNER JOIN MSP_Timesheet AS tsowner


on tl.TimesheetUID = tsowner.TimesheetUID


INNER JOIN MSP_TimesheetResource


ON tsowner.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID


WHERE


tc.[Type] = 2


 


) b


on MSP_EpmResource.ResourceUID = Resource


GROUP BY


ProjectName,


Resource,


CAST(YEAR(Day) as Varchar(4)) + ‘-‘ + CAST(MONTH(Day) as Varchar(2)), MSP_EpmResource.ResourceName


Order By


Month


You will notice that I have stripped out the day from the date. This is how we can roll up the assignments for each resource by month.


We now have our completed query! The next step is to get this data into Excel…


Chris Boyd