It is easy to navigate the database schema in MS-Project Server to generate reports. \u00a0The SQL can be embedded in an ODC, or can be used within PowerPivot. \u00a0If joining Task and Project data, there\u2019s a challenge of rollups. \u00a0The first challenge is avoiding double-counting from summary tasks. \u00a0The solution is to exclude them on the join, adding this condition:<\/p>\n
where TaskIsSummary=0\r\n<\/pre>\nThe next source for double-counting are external tasks; those exposed through cross-linking tasks in separate projects. We can exclude both this way:<\/p>\n
\t\r\nwhere TaskIsSummary=0 and TaskIsExternal = 0\r\n<\/pre>\nThe 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\u2019s how, referencing a custom field called \u201cBudgeted Costs\u201d; note how its value is proportionate to the task work:<\/p>\n
\r\n, case\r\n when [MSP_EpmProject_UserView].[Budgeted Costs] = 0 THEN 0\r\n when MSP_EpmTask_UserView.TaskRegularWork = 0 THEN 0\r\n when MSP_EpmProject_UserView.ProjectWork = 0 THEN 0\r\n else\r\n [MSP_EpmProject_UserView].[Budgeted Costs] * ( MSP_EpmTask_UserView.TaskRegularWork\/ MSP_EpmProject_UserView.ProjectWork )\r\n END as [Budgeted Costs]\r\n \r\nFROM dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTask_UserView\r\nON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID\r\nwhere TaskIsSummary=0 and TaskIsExternal = 0\r\nORDER BY MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskIndex, MSP_EpmTask_UserView.TaskName\r\n<\/pre>\nOne step further, we can do the same using task assignment data, here\u2019s what that looks like using the assignment work:<\/p>\n
\r\n, case\r\n when [MSP_EpmProject_UserView].[Budgeted Costs] = 0 THEN 0\r\n when MSP_EpmAssignment_UserView.AssignmentWork = 0 THEN 0\r\n when MSP_EpmProject_UserView.ProjectWork = 0 THEN 0\r\n else\r\n [MSP_EpmProject_UserView].[Budgeted Costs] * ( MSP_EpmAssignment_UserView.AssignmentWork\/ MSP_EpmProject_UserView.ProjectWork )\r\n END as [Budgeted Costs]\r\n \r\n,[MSP_EpmResource_UserView].[Cost Type]\r\n \r\n,[MSP_EpmResource_UserView].[Resource Departments]\r\n ,[MSP_EpmResource_UserView].[RBS]\r\n ,[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\r\nwhere TaskIsSummary=0 and TaskIsExternal = 0\r\nORDER BY MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskIndex, MSP_EpmTask_UserView.TaskName\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Custom SQL Reporting in MS-Project Server 2013 It is easy to navigate the database schema in MS-Project Server to generate reports. \u00a0The SQL can be embedded in an ODC, or can be used within PowerPivot. \u00a0If joining Task and Project data, there\u2019s a challenge of rollups. \u00a0The first challenge is avoiding double-counting from summary tasks. […]<\/p>\n","protected":false},"author":11,"featured_media":1380,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[73,52],"tags":[],"class_list":["post-1376","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analytics-and-reporting","category-project-server-and-web-access"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1376","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=1376"}],"version-history":[{"count":7,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1376\/revisions"}],"predecessor-version":[{"id":1396,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1376\/revisions\/1396"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/1380"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=1376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=1376"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=1376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}