Report Pack - Update to the Timesheet Audit Report query

Artigo
08/09/2007

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

Comments

Anonymous
September 05, 2008
Question please? I don't believe this query is correct? Simply removing '(MSP_TimesheetPeriod.PeriodStatusID = 0)' from the query, causes it to return no rows. I believe this proves that it cannot recognize missing timesheets based on the TimesheetPeriod table? Querying the database with test UIDs shows that the resource is in fact missing timesheets for some of periods defined in the system. Yet, the SQL does not return those rows, only the rows that exist for the resource. I did have to deviate slightly, as we do not use TimesheetManager. Timesheets are self-approving, so the resource is essentially the Timesheet Manager. The modified SQL is below (Example1). Also, hundreds of attempts to show exactly which "period" is missing are to no avail. I cannot come up with a join that will show those. The relationship breaks as soon as you cross into the timesheet tables. If the documentation on the Reporting DB is correct, this makes sense. You cannot traverse the timesheet tables to obtain this data if the resource does not reside in the TimeSheetResource table. This occurs if the resource has never touched a timesheet - something we need to know and report on within the same report. We don’t want to send our Resource Managers to a plethora of different reports for the same type of data, and need to consolidate it onto one report. I have been able to devise SQL that shows a period is missing within a selected period range, but cannot pinpoint the missing one. This may require a great deal of pain: custom functions and views, a CTE. This should be as simple as a “no-match” query, but all the SQL I’ve attempted returns NULL for the TimesheetPeriod.StartDate, as a relationship cannot be drawn where it doesn’t exist (Example2) I’m obviously doing something wrong? Anything you can do to help would be appreciated. We need this data for many reasons: forecasting, planning, estimation accuracy metrics, ROI, and the list goes on. Thanks, Ed Example1: 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 INNER JOIN MSP_EpmResource_UserView ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmResource.ResourceUID 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_UserView.[Resource Dept ID] = 'XYZ') 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 IS NULL) ORDER BY MSP_EpmResource.ResourceName

Example2: SELECT MSP_EpmResource_UserView.[DptID], MSP_EpmResource_UserView.EmpID, MSP_EpmResource_UserView.ResourceName, CONVERT(smalldatetime,(MSP_TimesheetPeriod.StartDate +(1-DATEPART(weekday, MSP_TimesheetPeriod.StartDate)))) As WkOfMth, MSP_TimesheetProject.ProjectName, CASE WHEN MSP_TimesheetTask.TaskName = 'Auto-generated' THEN MSP_TimesheetClass.ClassName ELSE MSP_TimesheetTask.TaskName END As 'TskNme', SUM(MSP_TimesheetActual.ActualWorkBillable) AS TmeShtTtl, CASE WHEN CCS_EPM_Published.dbo.MSP_TIMESHEETS.TS_STATUS_ENUM = 0 THEN 'In Progress' WHEN CCS_EPM_Published.dbo.MSP_TIMESHEETS.TS_STATUS_ENUM = 1 THEN 'Submitted' WHEN CCS_EPM_Published.dbo.MSP_TIMESHEETS.TS_STATUS_ENUM = 2 THEN 'Acceptable' WHEN CCS_EPM_Published.dbo.MSP_TIMESHEETS.TS_STATUS_ENUM = 3 THEN 'Approved' WHEN CCS_EPM_Published.dbo.MSP_TIMESHEETS.TS_STATUS_ENUM = 4 THEN 'Rejected' END As 'Description' --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ FROM MSP_TimeSheetPeriod LEFT JOIN MSP_Timesheet ON MSP_TimeSheetPeriod.PeriodUID = MSP_Timesheet.PeriodUID LEFT JOIN MSP_TimesheetResource ON MSP_TimesheetResource.ResourceNameUID = MSP_Timesheet.OwnerResourceNameUID LEFT JOIN MSP_EpmResource_UserView ON MSP_EpmResource_UserView.ResourceUID = MSP_TimesheetResource.ResourceUID LEFT JOIN MSP_TimesheetLine ON MSP_Timesheet.TimesheetUID = MSP_TimesheetLine.TimesheetUID LEFT JOIN MSP_TimesheetClass ON MSP_TimesheetLine.ClassUID = MSP_TimesheetClass.ClassUID LEFT JOIN CCS_EPM_Published.dbo.MSP_TIMESHEETS ON MSP_Timesheet.TimesheetUID = CCS_EPM_Published.dbo.MSP_TIMESHEETS.TS_UID LEFT JOIN MSP_TimesheetActual ON MSP_TimesheetLine.TimesheetLineUID =MSP_TimesheetActual.TimesheetLineUID LEFT JOIN MSP_TimesheetProject ON MSP_TimesheetLine.ProjectNameUID = MSP_TimesheetProject.ProjectNameUID LEFT JOIN MSP_TimesheetTask ON MSP_TimesheetLine.TaskNameUID = MSP_TimesheetTask.TaskNameUID LEFT JOIN MSP_EpmProject_UserView ON MSP_TimesheetProject.ProjectUID = MSP_EpmProject_UserView.ProjectUID --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ WHERE ((MSP_EpmResource_UserView.[DptID]IN (@Blk))) AND((MSP_EpmResource_UserView.[Cost Type] Is Null OR (MSP_EpmResource_UserView.[Cost Type])<>'Fixed Bid')) AND((MSP_EpmResource_UserView.ResourceEarliestAvailableFrom<=@RptPrdStrt) OR (MSP_EpmResource_UserView.ResourceLatestAvailableTo >=@RptPrdEnd)) AND (MSP_EpmResource_UserView.ResourceIsActive=1) AND (MSP_EpmResource_UserView.ResourceType=2) AND ((MSP_TimesheetPeriod.StartDate+(1-DATEPART(weekday,MSP_TimesheetPeriod.StartDate)) >= CONVERT(smalldatetime, (CAST(@RptPrdStrt As datetime)+(1-DATEPART(weekday, CAST(@RptPrdStrt As datetime)))))) AND((MSP_TimesheetPeriod.EndDate+(1-DATEPART(weekday,MSP_TimesheetPeriod.EndDate))) <= DATEADD(DD, -1, DATEADD(W, 1, @RptPrdEnd)))) AND MSP_EpmResource_UserView.EmpID is Not Null --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ GROUP BY MSP_EpmResource_UserView.[DptID], MSP_EpmResource_UserView.EmpID, MSP_EpmResource_UserView.ResourceName, CONVERT(smalldatetime,(MSP_TimesheetPeriod.StartDate +(1-DATEPART(weekday, MSP_TimesheetPeriod.StartDate)))), MSP_TimesheetProject.ProjectName, MSP_TimesheetTask.TaskName, CCS_EPM_Published.dbo.MSP_TIMESHEETS.TS_STATUS_ENUM, MSP_TimesheetClass.ClassName --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ UNION SELECT MSP_EpmResource_UserView.[DptID], MSP_EpmResource_UserView.EmpID, MSP_EpmResource_UserView.ResourceName, @RptPrdStrt, 'Missing Timesheet', NULL, NULL, NULL --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ FROM MSP_EpmResource_UserView LEFT JOIN MSP_Timesheet ON MSP_EpmResource_UserView.ResourceUID = MSP_Timesheet.OwnerResourceNameUID LEFT JOIN MSP_TimesheetPeriod ON MSP_Timesheet.PeriodUID = MSP_TimesheetPeriod.PeriodUID --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ WHERE MSP_EpmResource_UserView.EmpID is Not Null AND (MSP_Timesheet.PeriodUID Is Null AND MSP_EpmResource_UserView.[DptID]IN (@DptID)) AND((MSP_EpmResource_UserView.ResourceEarliestAvailableFrom<=@RptPrdStrt) OR (MSP_EpmResource_UserView.ResourceLatestAvailableTo >=@RptPrdEnd)) --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ GROUP BY MSP_EpmResource_UserView.[DptID], MSP_EpmResource_UserView.EmpID, MSP_EpmResource_UserView.ResourceName, MSP_TimesheetPeriod.StartDate, MSP_TimesheetPeriod.EndDate --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ORDER BY MSP_EpmResource_UserView.[DptID], MSP_EpmResource_UserView.ResourceName

Partilhar via

Report Pack - Update to the Timesheet Audit Report query

Comments

Recursos adicionais