Export (0) Print
Expand All

Timesheet Audit Report

Office 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The timesheet audit report enables a timesheet manager to get the status of timesheet submissions in Microsoft Office Project Server 2007. Timesheet status can be missing, in progress (submitted but not yet approved), approved, or rejected. The report, organized by timesheet period, includes all resources who submit timesheets to the manager for approval. The report query selects all open timesheet periods.

To demonstrate use of the report (Figure 1), the Project Server administrator needs to create open timesheet periods, resources need to submit timesheet data, and the timesheet manager should approve or reject some timesheets.

Figure 1. Timesheet audit report

Timesheet Audit report

The timesheet audit report shows the following new features of Project Server 2007:

  • Timesheet submittal process

  • Timesheet data in the Reporting database (RDB)

  • A report document query to use to access timesheet data

The timesheet audit report uses SQL queries of the RDB. The Retrieve_Timesheet_Manager query provides data for the TimeSheetManager parameter for the Project_Data_Source query.

NoteNote

A dataset in Microsoft SQL Server Reporting Services (SSRS) 2005 is created by a database query. An SSRS dataset includes fields used in the report; it is not the same as a Microsoft ADO.NET DataSet class. For more information, see Working with Data in Reporting Services.

To see the reporting datasets and queries, open the Project 2007 Report Pack solution with Microsoft Visual Studio 2005 (or Business Intelligence Development Studio for SQL Server Reporting Services), expand the Reports folder in Solution Explorer, and then double-click 1.0 Timesheet Audit.rdl. The design view of the report opens to the Layout tab.

To see the fields in the report dataset, click Datasets on the View menu, and then expand all of the nodes in the Datasets pane. To see the queries, click the Data tab in the report design view, and then select one of the datasets. The sample report uses the graphical query designer for both queries.

Query for the Timesheet Manager

On the Data tab, select Retrieve_Timesheet_Manager in the Dataset drop-down list. The query returns the ResourceName and ResourceTimesheetManagerUID fields, where ResourceName is the name of the timesheet manager.

SELECT DISTINCT EpmResource.ResourceTimesheetManagerUID, 
                MSP_EpmResource.ResourceName

FROM            MSP_EpmResource 
                INNER JOIN MSP_EpmResource AS EpmResource 
                   ON MSP_EpmResource.ResourceUID 
                      = EpmResource.ResourceTimesheetManagerUID 
                   AND EpmResource.ResourceUID 
                      <> EpmResource.ResourceTimesheetManagerUID

ORDER BY        MSP_EpmResource.ResourceName

Run the query to see the list of timesheet managers. When you run the report, SSRS populates the drop-down list of timesheet managers using the ResourceName field.

The timesheet manager is a parameter for the Project_Data_Source query. To see the parameter definition, click in the Report Designer pane, and then click Report Parameters in the Report menu. To see the report definition language (RDL) code for the TimeSheetManager parameter, right-click the report name in Solution Explorer, and then click View Code. The value of the TimeSheetManager parameter is the ResourceTimesheetManagerUID.

The following code in the RDL file defines the TimeSheetManager parameter.

  <ReportParameters>
    <ReportParameter Name="TimeSheetManager">
      <DataType>String</DataType>
      <AllowBlank>true</AllowBlank>
      <Prompt>Please select a TimeSheet Manager</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>Retrieve_TimeSheet_Manager</DataSetName>
          <ValueField>ResourceTimesheetManagerUID</ValueField>
          <LabelField>ResourceName</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
  </ReportParameters>

Query for Timesheet Status

On the Data tab, select Project_Data_Source in the Dataset drop-down list. The Project_Data_Source query returns the following fields:

  • ResourceName

  • PeriodName

  • PeriodStatus

  • TimesheetStatus

  • TotalWork

  • StartDate

  • EndDate

  • TimesheetStatusID

  • PeriodStatusID

The report does not use all of the fields. The tables in the report get their data from the query fields. For example, click the Layout tab, open the Properties pane, and then select the PeriodName text box. Right-click the selected text box, and then click Properties. The value is =Fields!PeriodName.Value & " Starting: " & CStr(Fields!StartDate.Value). The text box is the top cell in the MatrixColumn2 column, which repeats for the number of defined timesheet periods.

NoteNote

Sometimes the SSRS query designer gives an error when you try to run the query and provide a valid GUID value for the @TimeSheetManager parameter. Instead, click the Preview tab to preview the report, select a timesheet manager in the drop-down list, and then click View Report.

The background color of the totalWork text box changes according to the timesheet status, using the following formula. The formula is all on one line in the BackgroundColor property of the text box.

=iif(First(Fields!TimesheetStatus.Value)="In Progress", "Red", 
     (iif(First(Fields!TimesheetStatus.Value)="Submitted", "limegreen", 
          iif(First(Fields!TimesheetStatus.Value)="Approved", "limegreen", 
              iif(First(Fields!TimesheetStatus.Value)="Rejected", "yellow", "gainsboro")))))

The Project_Data_Source query uses the following tables in the RDB:

  • MSP_EpmResource

  • MSP_Timesheet

  • MSP_TimesheetActual

  • MSP_TimesheetPeriod

  • MSP_TimesheetResource

  • MSP_TimesheetPeriodStatus

  • MSP_TimesheetStatus

Following is the complete Project_Data_Source query for the timesheet audit report.

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)

The AND statement in the WHERE clause limits the summation to the timesheet period.

The timesheet audit report includes time logged in any open time periods. The report design assumes that you close timesheet periods on a regular basis so that time is not inadvertently put into the wrong period.

NoteNote

It is important to check and close timesheet periods regularly if you are billing for time. If you don't close periods, the timesheet audit report output will continue to grow.

For more information about fields in the RDB tables and views, see the Reporting Database Schema reference (pj12ReportingDB.chm) in the Project 2007 SDK download. For E-R diagrams of the main RDB tables and views, the RDBSchema.zip file in the SDK download contains a Microsoft Office Visio diagram and related information.

Show:
© 2015 Microsoft