Custom SQL Reporting in MS-Project Server 2013 Custom SQL Reporting in MS-Project Server 2013
Arpit Patel

Arpit Patel

February 13, 2014

All Post
Custom-SQL
Share:

Custom SQL Reporting in MS-Project Server 2013

It is easy to navigate the database schema in MS-Project Server to generate reports.  The SQL can be embedded in an ODC, or can be used within PowerPivot.  If joining Task and Project data, there’s a challenge of rollups.  The first challenge is avoiding double-counting from summary tasks.  The solution is to exclude them on the join, adding this condition:

where TaskIsSummary=0

The next source for double-counting are external tasks; those exposed through cross-linking tasks in separate projects. We can exclude both this way:

where TaskIsSummary=0 and TaskIsExternal = 0

The next problem is if merging task and project tables, project values would roll up incorrectly, however such numeric fields can be pro-rated to the project work, as long as we avoid divide-by-zero errors, here’s how, referencing a custom field called “Budgeted Costs”; note how its value is proportionate to the task work:

, case
 when [MSP_EpmProject_UserView].[Budgeted Costs] = 0 THEN 0
 when MSP_EpmTask_UserView.TaskRegularWork = 0 THEN 0
 when MSP_EpmProject_UserView.ProjectWork = 0 THEN 0
 else
 [MSP_EpmProject_UserView].[Budgeted Costs] * ( MSP_EpmTask_UserView.TaskRegularWork/ MSP_EpmProject_UserView.ProjectWork )
 END as [Budgeted Costs]
 
FROM dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTask_UserView
ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
where TaskIsSummary=0 and TaskIsExternal = 0
ORDER BY MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskIndex, MSP_EpmTask_UserView.TaskName

One step further, we can do the same using task assignment data, here’s what that looks like using the assignment work:

, case
 when [MSP_EpmProject_UserView].[Budgeted Costs] = 0 THEN 0
 when MSP_EpmAssignment_UserView.AssignmentWork = 0 THEN 0
 when MSP_EpmProject_UserView.ProjectWork = 0 THEN 0
 else
 [MSP_EpmProject_UserView].[Budgeted Costs] * ( MSP_EpmAssignment_UserView.AssignmentWork/ MSP_EpmProject_UserView.ProjectWork )
 END as [Budgeted Costs]
 
,[MSP_EpmResource_UserView].[Cost Type]
 
,[MSP_EpmResource_UserView].[Resource Departments]
 ,[MSP_EpmResource_UserView].[RBS]
 ,[MSP_EpmResource_UserView].[Resource Title] FROM dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTask_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID LEFT OUTER JOIN dbo.MSP_EpmAssignment_UserView ON MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID AND MSP_EpmTask_UserView.ProjectUID = MSP_EpmAssignment_UserView.ProjectUID LEFT OUTER JOIN dbo.MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID
where TaskIsSummary=0 and TaskIsExternal = 0
ORDER BY MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskIndex, MSP_EpmTask_UserView.TaskName

Leave a Reply

Your email address will not be published. Required fields are marked *

Want to talk?

Drop us a line. We are here to answer your questions 24*7.