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 |
Want to talk?
Drop us a line. We are here to answer your questions 24*7.