Timesheet 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 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
.gif "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
Datasets for the Timesheet Audit Report
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.
.gif "Note") 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.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.
| Note |
|---|
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.
| Note |
|---|
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.