How to deploy the Timesheet Compliance Report

  • Article

I recently posted a series of SQL Server Reporting Services (SSRS) reports that are used in our newly updated EPM demo VPC: New SQL Reporting Services Sample Reports for Project Server. Since a few people asked for a step by step guide to use these reports on another Project Server instance, here is the procedure/recommendation for the most popular one the Timesheet Compliance Report.

First you need to have a basic understanding of the PS 2007 database schema as well as T-SQL (MS SQL programming language) to migrate these reports into another environment.

  1. Change the database connection parameters in the SSRS solution
  2. Make the following three T-SQL changes to the dsTimesheet_Compliance Dataset (in red below)
    • Modify the Published database name
    • Pick the appropriate custom field value: from CFVal3 to CVal2 for instance (use the MSP_EpmResource_UserView and MSP_EpmCPResUid0 to figure out which one to use)
    • Remove the filter for team members that are not assigned to teams (so that no one is forgotten): (NOT(tn.[MemberValue] IS NULL))
  3. Create a lookup table called Team and assign it to the Resource custom field called Team Name using the PWA administrative page

In the end the best way to troubleshoot a SSRS report is to run the queries directly in SQL using the query editor. Until you get the results expected in the query window no need to work in SSRS!

As usual with MSDN blog postings the code is supplied “AS-IS”, with no warranties or support and could probably be improved to optimize performance.

 SELECT
     t.RES_UID,
     CASE WHEN (t.TS_STATUS_ENUM = 0) THEN 1 END AS [In Progress],
     CASE WHEN (t.TS_STATUS_ENUM > 0) THEN 1 END AS [Approved],
     SUM(t.TS_GRAND_TOTAL_ACT_VALUE/60/1000) as [Timesheet Actual]
INTO #t
FROM
    ProjectServer2007_Litware_Published.dbo.MSP_TIMESHEETS t INNER JOIN
    dbo.MSP_TimesheetPeriod tp ON
         t.WPRD_UID = tp.PeriodUID
WHERE
     (tp.PeriodUID = @ParmPeriodUID)
GROUP BY
     t.RES_UID,
     t.TS_STATUS_ENUM

SELECT
     r.ResourceName AS [Resource],
     ISNULL(tn.[MemberValue],'<No Team>') AS [Team],
     ISNULL(rm.ResourceName,'<No Manager>') AS [Timesheet Manager],
     CASE WHEN (#t.RES_UID IS NULL) THEN 1 END AS [Not Started],
     #t.[In Progress],
     #t.[Approved],
     #t.[Timesheet Actual]
FROM
    dbo.MSP_EpmResource_UserView r LEFT OUTER JOIN
    dbo.MSP_EpmCPResUid0 c ON
         r.ResourceUID = c.EntityUID LEFT OUTER JOIN
    dbo.MSPLT_Teams_UserView tn ON
        c.CFVal3 = tn.LookupMemberUID LEFT OUTER JOIN
    dbo.MSP_EpmResource_UserView rm ON
         r.ResourceTimesheetManagerUID = rm.ResourceUID LEFT OUTER JOIN
    #t ON
          r.ResourceUID = #t.RES_UID
WHERE
     (r.ResourceIsActive = 1) AND
     (r.ResourceType = 2) AND
     (r.ResourceIsGeneric = 0) 
 --     AND (NOT(tn.[MemberValue] IS NULL)) 

DROP TABLE #t