Why are some of your resource’s timesheet entries doubling in your reports?

Some of you folks who write reports for Microsoft Project Server have experienced this frustrating issue, where for some of your resources, hours they have entered into their timesheet are showing as multiples in your report result set. Why is this, as it seems it’s randomly occurring?

For those who’ve not figured it out yet, I’ll fill you in. In the MSP_TimesheetActual table (Reporting Database, for 2010 users), there’s a field called Adjustment Index. When this field gets incremented (explanation saved for a subsequent post) your result set will include more than one line per single timesheet entry. See sample data below.

TimesheetLineUID	AdjustmentIndex	TimeByDay_DayOfMonth	ActualWorkBillable
B6EEC7F6-BDAA-4EFE-93A5-3E8D5D65B29D	1	26	0
B6EEC7F6-BDAA-4EFE-93A5-3E8D5D65B29D	2	26	2
B6EEC7F6-BDAA-4EFE-93A5-3E8D5D65B29D	3	26	0
B6EEC7F6-BDAA-4EFE-93A5-3E8D5D65B29D	4	26	2

If you’re summing your records, the above data will show a total of 4 hours submitted, rather than 2.

To get around this, simply insert the following code into your SQL. If you’re using a table alias in your script other than “ta” for table MSP_TimesheetActual, adjust the script below as necessary.

— Added to correct duplicates – Display only the top level adjustment
and ta.AdjustmentIndex =

    (Select   max(tma2.AdjustmentIndex)
from     MSP_TimesheetActual tma2
where    ta.TimesheetLineUID = tma2.TimesheetlineUID
and      ta.TimeByDay = tma2.TimeByDay)

Enjoy!

~Todd