Report Pack - Update to the Timesheet Audit Report query
It appears part of the where clause is missing on the published Timesheet Audit report. The result is that the weekly totals are all the same for each resource.
To correct this, replace the SQL with the code below. The addition is in bold below.
SELECT MSP_EpmResource.ResourceName, MSP_TimesheetPeriod.PeriodName,
MSP_TimesheetPeriodStatus.Description AS PeriodStatus,
MSP_TimesheetStatus.Description AS TimesheetStatus,
SUM(MSP_TimesheetActual.ActualWorkBillable)
+ SUM(MSP_TimesheetActual.ActualWorkNonBillable)
+ SUM(MSP_TimesheetActual.ActualOvertimeWorkBillable)
+ SUM(MSP_TimesheetActual.ActualOvertimeWorkNonBillable)
AS TotalWork, MSP_TimesheetPeriod.StartDate, MSP_TimesheetPeriod.EndDate,
MSP_Timesheet.TimesheetStatusID, MSP_TimesheetPeriod.PeriodStatusID
FROM MSP_EpmResource LEFT OUTER JOIN
MSP_TimesheetResource INNER JOIN
MSP_TimesheetActual ON MSP_TimesheetResource.ResourceNameUID =
MSP_TimesheetActual.LastChangedResourceNameUID ON
MSP_EpmResource.ResourceUID = MSP_TimesheetResource.ResourceUID
LEFT OUTER JOIN
MSP_TimesheetPeriod INNER JOIN
MSP_Timesheet ON MSP_TimesheetPeriod.PeriodUID = MSP_Timesheet.PeriodUID INNER JOIN
MSP_TimesheetPeriodStatus ON MSP_TimesheetPeriod.PeriodStatusID =
MSP_TimesheetPeriodStatus.PeriodStatusID INNER JOIN
MSP_TimesheetStatus ON MSP_Timesheet.TimesheetStatusID =
MSP_TimesheetStatus.TimesheetStatusID ON
MSP_TimesheetResource.ResourceNameUID = MSP_Timesheet.OwnerResourceNameUID
WHERE (MSP_EpmResource.ResourceTimesheetManagerUID = @TimeSheetManager)
AND (MSP_TimesheetActual.TimeByDay BETWEEN MSP_TimesheetPeriod.StartDate AND MSP_TimesheetPeriod.EndDate)
GROUP BY MSP_TimesheetPeriod.PeriodName, MSP_TimesheetPeriodStatus.Description,
MSP_TimesheetStatus.Description, MSP_EpmResource.ResourceName,
MSP_TimesheetPeriod.StartDate, MSP_TimesheetPeriod.EndDate,
MSP_Timesheet.TimesheetStatusID, MSP_TimesheetPeriod.PeriodStatusID
HAVING (MSP_TimesheetPeriod.PeriodStatusID = 0) OR
(MSP_TimesheetPeriod.PeriodStatusID IS NULL)
For your information, this report includes time logged in any open time periods. The report was designed assuming you are closing timesheet periods on a regular basis. Closing prevents time from being applied to an incorrect period. If you are billing for your time, this is real important. Anyway, if you aren't closing periods, this report will continue to get grow wider as columns are added for each open period.
We apologize for any inconvenience.
Technorati Tags: Project Management, Project Server 2007, Reporting, Project 2007, SQL Server Reporting Services