How to track EPM Resource field changes in your Reporting Database


Leveraging the EPM 2007 Eventing service as well as the Reporting database here is a way of tracking all changes made to a Resource's fields. A common request I heard from customers is the ability to track when a Resource changes from one node to another node in the RBS.


Steps to do it (code download below!!!):



  • Created a custom table in the Reporting database called: MCS_EpmResource_UserView_Tracking

  • Created a custom stored procedure in the Reporting database called: MCS_EPM_InsertResourceChange that takes the RES_UID as a parameter

DECLARE @ResourceTimesheetManagerUID uniqueidentifier
DECLARE @ResourceModifiedDate datetime

SELECT @ResourceModifiedDate = ResourceModifiedDate,
@ResourceTimesheetManagerUID = ResourceTimesheetManagerUID
FROM MSP_EpmResource
WHERE ResourceUID = @ResourceUID

IF NOT EXISTS(SELECT * FROM dbo.MCS_EpmResource_UserView_Tracking WHERE ResourceUID = @ResourceUID AND ResourceModifiedDate = @ResourceModifiedDate)


Note that I only INSERT a new row in my history table if the Modified Date has changed.



  • Created a custom event handler that gets called every time a Resource is changed (OnResourceChanged)
        public override void OnResourceChanged(PSContextInfo contextInfo, ReportingPostResourceChangedEventArgs e)
{
// Standard processing
base.OnResourceChanged(contextInfo, e);

// Load settings from Microsoft.Office.Project.Server.Eventing.exe.config
LoadSettings();

// Build T-SQL command
string cmdText = string.Format("EXEC MCS_EPM_InsertResourceChange '{0}'",e.ResourceUid.ToString());

// Store Resource field update
ConnectAndExecuteNonQuery(connectionString, cmdText);
}



  • Deploy event handler in GAC and define an entry in the PWA configuration (refer to SDK for more details)

  • and voila!

From now on, every time a Resource's field is changed (for instance it's name, RBS tec ...) and entry will be inserted in the tracking table mentioned above.


Eventually you will want to build a report that leverages that historical data to show you a view before and after the change was made for instance.

ResourceFieldTracking.zip

Skip to main content