How to extract and view Timesheet Audit Data?


In EPM 2007 you have the ability to audit all timesheet transactions using the following flag:


Server Settings >Time and Task Management > Settings and Defaults > Enable Timesheet Auditing


Once audit has been activated, all timesheet changes get recorded in the following table in the Published database: MSP_TIMESHEET_ACTUAL_AUDIT


Since the data is not in the Reporting db, I’ve created a query that uses data from both the Published and Reporting DB to extract and view timesheet audit data:



     DECLARE @DBNAME NVARCHAR(50)


     DECLARE @CMD NVARCHAR(4000)


     SET @DBNAME = REPLACE(DB_NAME(),'_Reporting','')


 


     SET @CMD=


     'SELECT       TSR.ResourceName AS ''Resource'',


                  TS_ACT_AUD_SEQUENCE ''Audit Sequence'',


                  TaskName AS ''Task'',


                  CONVERT(Decimal(9,2), TS_ACT_AUD_DELTA_VALUE/60000) AS ''Delta Value'',


                  TS_ACT_START_DATE ''Start'',


                  TS_ACT_FINISH_DATE ''Finish'',


                  MOD_DATE ''Modified'',


                  TSS.Description AS Status


                 


     FROM '+@DBNAME+'_Published.dbo.MSP_TIMESHEET_ACTUAL_AUDIT AS TSAA


     INNER JOIN MSP_TimesheetLine AS TSL


     ON TSAA.TS_LINE_UID=TSL.TimesheetLineUID


     INNER JOIN MSP_TimesheetTask AS TST


     ON TST.TaskNameUID=TSL.TaskNameUID


     INNER JOIN MSP_Timesheet AS TS


     ON TS.TimesheetUID=TSL.TimesheetUID


     INNER JOIN MSP_TimesheetResource AS TSR


     ON TSR.ResourceNameUID=TS.OwnerResourceNameUID


     INNER JOIN MSP_TimesheetStatus AS TSS


     ON TSS.TimesheetStatusID=TS.TimesheetStatusID


     ORDER BY ResourceName, TSAA.TS_ACT_AUD_SEQUENCE'


 


     EXEC (@CMD)


 


A sample SQL Server Reporting Services report looks like this (RDL attached below):


Timesheet Audit Data


 


To enforce the EPM security model I recommend you write a PSI extension.

Timesheet Audit 2.rdl

Skip to main content