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.
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.
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]
ProjectServer2007_Litware_Published.dbo.MSP_TIMESHEETS t INNER JOIN
dbo.MSP_TimesheetPeriod tp ON
t.WPRD_UID = tp.PeriodUID
(tp.PeriodUID = @ParmPeriodUID)
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],
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
r.ResourceUID = #t.RES_UID
(r.ResourceIsActive = 1) AND
(r.ResourceType = 2) AND
(r.ResourceIsGeneric = 0)
-- AND (NOT(tn.[MemberValue] IS NULL))
DROP TABLE #t
I posted this set of sample SQL Server Reporting Services (SSRS) reports almost a year ago: New SQL Reporting