Microsoft Project 2010
The official blog of the Microsoft Project product team. Learn how to manage your work effectively

Report Pack - Update to the Timesheet Audit Report query

Report Pack - Update to the Timesheet Audit Report query

  • Comments 1

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

    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

Page 1 of 1 (1 items)