#ProjectServer data snapshot for reporting #PS2010 #MSProject #SP2010 #Excel #SSRS #SQL

Quite often there is a requirement for some form of data snapshot in Project Server for trend analysis reporting. This post will give a simple explanation of how this can be achieved using the Project Server Reporting database and a SQL job. This example just captures some key project metrics such as Actual Work, Actual Cost, Status Date and a couple of custom field values each month to demonstrate the process.

Below is the first SQL query that will need to be run against the Project Server Reporting database to create the first snapshot:

select        ProjectName
        ,    ProjectUID
        ,    ProjectType
        ,    ProjectActualWork
        ,    ProjectActualCost
        ,    ProjectStatusDate
        ,    [PM TEST]
        ,    [PM TEST 2]
        ,    DateName(month,GETDATE()) + ‘ ‘ + DateName(year,GETDATE())  as SnapshotDate
into        CustomSnapshotProjectData
from        dbo.MSP_EpmProject_UserView

The query above will get the data for the fields listed in the select statement from the MSP_EpmProject_UserView and insert the data into the CustomSnapshotProjectData table. Update the query to get the fields / data that you require to be snapshotted.

To add the next months data the SQL query will look slightly different as the CustomSnapshotProjectData table already exists. This time we need to insert into rather than select into, the query can be seen below:

Insert into CustomSnapshotProjectData (ProjectName, ProjectUID, ProjectType, ProjectActualWork, ProjectActualCost,ProjectStatusDate,[PM TEST], [PM TEST 2],SnapshotDate)
select        ProjectName
        ,    ProjectUID
        ,    ProjectType
        ,    ProjectActualWork
        ,    ProjectActualCost
        ,    ProjectStatusDate
        ,    [PM TEST]
        ,    [PM TEST 2]
        ,    DateName(month,GETDATE()) + ‘ ‘ + DateName(year,GETDATE())  as SnapshotDate
from        dbo.MSP_EpmProject_UserView

This can either be run manually each month at the specified time or a SQL job can be created to execute the SQL query automatically each month.

The contents of the CustomSnapshotProjectData table can be seen below, please note the data in my test system is not ideal for reporting / snapshotting:

This data can be accessed using the usual reporting tools such as Reporting Services and  Excel / Excel Services.

As mentioned this is just a simple example to show how quickly and easily snapshotting can be set up for Project Server 2007 and 2010.

Advertisement