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):
To enforce the EPM security model I recommend you write a PSI extension.