Report on Project Server baseline save dates? #ProjectServer #PS2003 #PS2007 #PS2010 #MSProject #EPM

August 20, 2011 at 11:16 pm | Posted in Customisation, Functionality | 2 Comments
Tags: , , , , , ,

Quite often clients want to report on the dates when the baselines were saved. This information is not available out of the box in the databases using T-SQL. This post explains how to get this data into the database so that is accessible using T-SQL in the Reporting database.

Firstly we need to create 11 Project level enterprise custom text fields to store the baseline save dates. For the purpose of this post not all custom fields have been created, the custom fields in this example are Baseline0saveddate, Baseline1savedate and Baseline2savedate. Next add a VBA macro into the Enterprise Global that copies the baseline save dates to the enterprise custom fields created above. The following macro is just an example cut down version to copy the baseline save dates to the custom fields. This cut down version also only includes the first 3 baseline save dates.

Private Sub Project_BeforeSave(ByVal pj As Project)
ActiveProject.ProjectSummaryTask.Baseline0saveddate = ActiveProject.BaselineSavedDate(pjBaseline)
ActiveProject.ProjectSummaryTask.Baseline1saveddate = ActiveProject.BaselineSavedDate(pjBaseline1)
ActiveProject.ProjectSummaryTask.Baseline2saveddate = ActiveProject.BaselineSavedDate(pjBaseline2)
End Sub

The macro above will work for Project 2003, 2007 and 2010.

If a baseline value is not set NA will be set in the associated custom field. Once the macro has been added to the enterprise global, save the changes then close Project Professional. Re-launch Project Professional and open a project that has baselines set. Insert the new Project level custom fields into the view, click Save and you will see either the baseline save date appear in the associated custom field or NA if a baseline has not been set for a particular baseline. Once this project is published this information will be available to report on in the Reporting database. For example, the following T-SQL will give you the baseline save dates based on this post example:

use            PWA_Reporting
select        ProjectName
        ,    Baseline0saveddate
        ,    Baseline1saveddate
        ,    Baseline2saveddate
from        dbo.MSP_EpmProject_UserView

The T-SQL above will work for Project Server 2007 and Project Server 2010.

As mentioned above the VBA code is just as an example and would need to be improved for a production environment.

Create a free website or blog at WordPress.com.
Entries and comments feeds.