When we shipped Project Server 2007 we made sure that timesheet data was readily available to query in the Reporting Database, however one thing that we didn’t do was make it super easy to spot a surrogate timesheet (defined as a timesheet entered on a team member’s behalf by another Project Server user who has the surrogate timesheet permissions) – one reason was that we wanted mainline timesheet reports to not have to care, and be able to aggregate/report on the data without special case code.
If you are faced with the need to identify surrogate timesheets the query below, run against the RDB, will help:
SELECT DISTINCT TS.OwnerResourceNameUID AS N’Team Member’
, TSA.LastChangedResourceNameUID AS N’Surrogate Team Member’
FROM dbo.msp_timesheet AS TS
INNER JOIN dbo.msp_timesheetline AS TSL
ON TS.TimesheetUID = TSL.TimesheetUID
INNER JOIN dbo.msp_timesheetactual AS TSA
ON TSL.TimesheetLineUID = TSA.TimesheetLineUID WHERE TSA.AdjustmentUID = N’00000000-0000-0000-0000-000000000000′ — Not an adjustment
AND TS.OwnerResourceNameUID <> TSA.LastChangedResourceNameUID — Submitter <> TS Owner
We are basically looking at the timesheet lines that haven’t been adjusted – if the person who last saved them isn’t the same as the person who owns the timesheet then we know its a surrogate – the lines were submitted by another user.
Usage Note: The two resource UID can be used to join to other timesheet tables – don’t join on these directly to the MSP_EPMResource_UserView or other tables in the EPM schema as you won’t get a match – instead you should join though the RDB slowly changing resource name table (MSP_TimesheetResource) to get the base ResourceUIDs and use these for the EPM schema.
Hope this helps – if you are struggling to get a question answered from RDB data feel free to post a response to this article and we’ll see what we can do.
Happy New Year for 2009 (Chris – first post!)