Work Versus Effort Audit Report

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 work versus effort audit report compares timesheet entries for a task with update information for the same task in Microsoft Office Project Server 2007. The report helps a project manager find discrepancies between actual work and planned work within timesheet periods.

To work correctly, the report (Figure 1) assumes that resources enter timesheet data at the task level. The report also requires that a Project Server administrator define timesheet periods.

Figure 1. Work versus effort audit report

The work versus effort audit report shows the following new features of Project Server 2007:

• Timesheets and timesheet periods

• Timesheet data in the Reporting database (RDB)

• A report document query to use to access timesheet and task data

Datasets for the Work versus Effort Audit Report

The work versus effort audit report uses SQL queries of the RDB. The PickTimeSheetPeriod query provides data for the TimeSheetPeriod parameter for the Project_Data_Source query.

Note
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.01 Work Versus Effort Audit Report.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 Periods

Select PickTimeSheetPeriodin the Dataset drop-down list on the Data tab. The query returns the timesheet Description, PeriodName, and PeriodUID fields, where Description is assembled from the timesheet period name, start date, end date, and status.

SELECT   'Period: ' 
            + RTRIM(CAST(MSP_TimesheetPeriod.PeriodName AS char)) 
            + '  (' + RTRIM(CONVERT(char, MSP_TimesheetPeriod.StartDate, 1)) 
            + ' - ' + RTRIM(CONVERT(char, MSP_TimesheetPeriod.EndDate, 1)) 
            + ')  Status: ' 
            + MSP_TimesheetPeriodStatus.Description 
            AS Description, 
         MSP_TimesheetPeriod.PeriodName, 
         MSP_TimesheetPeriod.PeriodUID
         
FROM     MSP_TimesheetPeriod 
            INNER JOIN MSP_TimesheetPeriodStatus 
               ON MSP_TimesheetPeriod.PeriodStatusID 
                  = MSP_TimesheetPeriodStatus.PeriodStatusID
              
ORDER BY MSP_TimesheetPeriod.PeriodName

Run the query in the Query Designer pane to see the resulting timesheet descriptions. You can revise the ORDER BY statement to organize the list for your timesheet period names or dates. For example, the result of the previous query is not in sequential order by date, for the sample timesheet period names.

If you change the last statement of the query to ORDER BY MSP_TimesheetPeriod.StartDate, then the drop-down list for timesheet periods shows the descriptions in the following table.

When you run the report, SSRS populates the drop-down list of timesheet managers using the Description 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 on the Report menu. To see the report definition language (RDL) code for the TimeSheetPeriod parameter, right-click the report name in Solution Explorer, and then click View Code. The value of the TimeSheetPeriod parameter is the PeriodUID.

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

  <ReportParameters>
    <ReportParameter Name="TimeSheetPeriod">
      <DataType>String</DataType>
      <AllowBlank>true</AllowBlank>
      <Prompt>Please select a TimeSheet Period</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>PickTimeSheetPeriod</DataSetName>
          <ValueField>PeriodUID</ValueField>
          <LabelField>Description</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
  </ReportParameters>

Query for Actual and Planned Work

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

• PeriodName

• StartDate

• EndDate

• ResourceName

• ProjectName

• TaskName

• TimeByDay

• ActualWork

• PlannedWork

• AssignmentPercentWorkCompleted

• ProjectUID

• TaskUID

The text boxes and tables in the report get data from the query fields and sometimes manipulate that data. 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 the following (all on one line).

="Period: " 
  & Fields!PeriodName.Value 
  & "  " 
  & FormatDateTime(Fields!StartDate.Value,1) 
  & " - " 
  & FormatDateTime(Fields!EndDate.Value,1)

In the sample report, if you pick the Wk 1 2007 period in the drop-down list, the report subheading shows the following: Period: Wk 1 2007 Sunday, December 31, 2006 - Saturday, January 06, 2007

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 work versus effort 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)

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 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.

See Also

Concepts

Report Pack Setup

Other Resources

Working with Data in Reporting Services