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.
- Change the database connection parameters in the SSRS solution
- 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))
- 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