Home Library Learn Downloads Support Community
Sign in | United States - English | |
1 out of 3 rated this helpful - Rate this topic

Timesheet Audit Report

Office 2007

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

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.

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.

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

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

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

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

See Also

Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Here is the correct working hours calculation query .... Enjoy !!!
The following query help you find out the data according to your own choice of week start and end you like. Also there was a bug in query JOIN that was returning wrong No. of working Hours which I have corrected in following query. Enjoy !!!

SELECT
dbo.MSP_TimesheetResource.ResourceName, ResourceUID,
dbo.MSP_TimesheetTask.TaskName,
dbo.MSP_TimesheetProject.ProjectName,
dbo.MSP_TimesheetActual.TimeByDay,
dbo.MSP_TimesheetActual.ActualWorkBillable [Work Hours],
CASE
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '1-Jan-2010' and '7-Jan-2010' then 'Week 1'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '8-Jan-2010' and '14-Jan-2010' then 'Week 2'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '15-Jan-2010' and '21-Jan-2010' then 'Week 3'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '22-Jan-2010' and '28-Jan-2010' then 'Week 4'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '29-Jan-2010' and '4-Feb-2010' then 'Week 5'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '5-Feb-2010' and '11-Feb-2010' then 'Week 6'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '12-Feb-2010' and '18-Feb-2010' then 'Week 7'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '19-Feb-2010' and '25-Feb-2010' then 'Week 8'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '26-Feb-2010' and '4-Mar-2010' then 'Week 9'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '5-Mar-2010' and '11-Mar-2010' then 'Week 10'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '12-Mar-2010' and '18-Mar-2010' then 'Week 11'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '19-Mar-2010' and '25-Mar-2010' then 'Week 12'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '26-Mar-2010' and '1-Apr-2010' then 'Week 13'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '2-Apr-2010' and '8-Apr-2010' then 'Week 14'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '9-Apr-2010' and '15-Apr-2010' then 'Week 15'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '16-Apr-2010' and '22-Apr-2010' then 'Week 16'
when convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) between '23-Apr-2010' and '25-Apr-2010' then 'Week 17'
END as WeekShortName,

INTO EmployeeTimeSheetData.dbo.HourlyTimeSheetData
FROM dbo.MSP_Timesheet
INNER JOIN dbo.MSP_TimesheetResource ON dbo.MSP_Timesheet.OwnerResourceNameUID = dbo.MSP_TimesheetResource.ResourceNameUID
INNER JOIN dbo.MSP_TimesheetLine ON dbo.MSP_TimesheetLine.TimesheetUID = dbo.MSP_Timesheet.TimesheetUID
INNER JOIN dbo.MSP_TimesheetPeriod ON dbo.MSP_Timesheet.PeriodUID = dbo.MSP_TimesheetPeriod.PeriodUID
INNER JOIN dbo.MSP_TimesheetStatus ON dbo.MSP_Timesheet.TimesheetStatusID = dbo.MSP_TimesheetStatus.TimesheetStatusID
INNER JOIN dbo.MSP_TimesheetTask ON dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID
INNER JOIN dbo.MSP_TimesheetProject ON dbo.MSP_TimesheetLine.ProjectNameUID = dbo.MSP_TimesheetProject.ProjectNameUID
INNER JOIN dbo.MSP_TimesheetActual ON -- dbo.MSP_TimesheetResource.ResourceNameUID = dbo.MSP_TimesheetActual.LastChangedResourceNameUID AND
dbo.MSP_TimesheetLine.TimesheetLineUID = dbo.MSP_TimesheetActual.TimesheetLineUID
INNER JOIN dbo.MSP_TimesheetPeriodStatus ON dbo.MSP_TimesheetPeriod.PeriodStatusID = dbo.MSP_TimesheetPeriodStatus.PeriodStatusID
INNER JOIN dbo.MSP_TimesheetClass ON dbo.MSP_TimesheetLine.ClassUID = dbo.MSP_TimesheetClass.ClassUID
where  convert(datetime, dbo.MSP_TimesheetActual.TimeByDay) < '26-Apr-2010'

----------------------------------------------------------------------------------------------------
select * from MSP_TimesheetActual where TimeByDay = '2010-03-05 00:00:00.000'
----------------------------------------------------------------------------------------------------
-- drop table EmployeeTimeSheetData.dbo.HourlyTimeSheetDataNewP
select TaskName, WeekDuration, sum([Work Hours]) [Man Hours], ResourceName, ProjectName from EmployeeTimeSheetData.dbo.HourlyTimeSheetDataNewP
where weekNo > 9
group by TaskName,ProjectName, WeekDuration, ResourceName
having sum([Work Hours]) >0
order by 2, 1, 4
----------------------------------------------------------------------------------------------------
select * from EmployeeTimeSheetData.dbo.HourlyTimeSheetData
----------------------------------------------------------------------------------------------------
select ResourceName, weekDuration,  weekNo, convert(decimal(5,2), sum([work Hours])) as ManHours from EmployeeTimeSheetData.dbo.HourlyTimeSheetDataNewP
where weekNo > 9
group by ResourceName, weekDuration, weekNo
order by 1

History
Fix for correct sorting of the week periods

According to Treb Gatte, who wrote the query, SSRS sorts the week periods from the Project_Data_Source query results by the period name, so if you name the periods 'Wk 2' ... 'Wk 12', they appear in the wrong order in the list. To sort correctly, you need a numeric year and then a period number. If you define the periods to look like, for example, 2007 - WK 01, 2008 - WK 02, then the text sort works correctly. Put a zero in front of 1 - 9 to get the correct sort order.

As an alternative, when you can't change the period name, try the following addition to the Select statement, which turns the year and period into numeric fields. Warning! This solution assumes a period name format where the year is always at the end and always four digits; and the week number starts in position 4 and is always numeric.

cast(substring(periodname,(len(periodname)- 3), 4) as int) as SortYear, cast(substring(PeriodName,4,2) as int) as SortPeriod

Then add SortYear, SortPeriod to the SSRS mechanism that drives sorting.

History