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.
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
MSP_TimesheetActual
ON MSP_TimesheetResource.ResourceNameUID = MSP_TimesheetActual.LastChangedResourceNameUID
ON MSP_EpmResource.ResourceUID = MSP_TimesheetResource.ResourceUID
MSP_TimesheetPeriod
MSP_Timesheet
ON MSP_TimesheetPeriod.PeriodUID = MSP_Timesheet.PeriodUID
MSP_TimesheetPeriodStatus
ON MSP_TimesheetPeriod.PeriodStatusID = MSP_TimesheetPeriodStatus.PeriodStatusID
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,
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
ON MSP_TimeSheetPeriod.PeriodUID = MSP_Timesheet.PeriodUID
ON MSP_EpmResource_UserView.ResourceUID = MSP_TimesheetResource.ResourceUID
MSP_TimesheetLine
ON MSP_Timesheet.TimesheetUID = MSP_TimesheetLine.TimesheetUID
MSP_TimesheetClass
ON MSP_TimesheetLine.ClassUID = MSP_TimesheetClass.ClassUID
CCS_EPM_Published.dbo.MSP_TIMESHEETS
ON MSP_Timesheet.TimesheetUID = CCS_EPM_Published.dbo.MSP_TIMESHEETS.TS_UID
ON MSP_TimesheetLine.TimesheetLineUID =MSP_TimesheetActual.TimesheetLineUID
MSP_TimesheetProject
ON MSP_TimesheetLine.ProjectNameUID = MSP_TimesheetProject.ProjectNameUID
MSP_TimesheetTask
ON MSP_TimesheetLine.TaskNameUID = MSP_TimesheetTask.TaskNameUID
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],
+(1-DATEPART(weekday, MSP_TimesheetPeriod.StartDate)))),
MSP_TimesheetTask.TaskName,
CCS_EPM_Published.dbo.MSP_TIMESHEETS.TS_STATUS_ENUM,
MSP_TimesheetClass.ClassName
UNION
@RptPrdStrt,
'Missing Timesheet',
NULL,
NULL
FROM MSP_EpmResource_UserView
ON MSP_EpmResource_UserView.ResourceUID = MSP_Timesheet.OwnerResourceNameUID
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))
MSP_TimesheetPeriod.EndDate
ORDER BY MSP_EpmResource_UserView.[DptID],
MSP_EpmResource_UserView.ResourceName