Поделиться через

Identifying Surrogate Timesheets in the Reporting Database

  • Статья

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'

              , TS.TimesheetUID

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!)

Comments