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.
