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