Asked by:
Project Server 2013 - BI SQL reports - roll up task dates into a single row of the summary task level

Question
-
Hello all SQL experts,
I am trying to create a report that takes dates out of 2 tasks in each summary task of a project and reflect them on the summary level rolled up in a way that i can subtract them to the duration between the first task's finish date and the second task's start date.
(the tasks might have a gap between them, second one os not a successor of the first one).
I have tried a few ways to get the output of 2 new columns (PO date, Execution date) in a flat row that will show the summary task name and other summary level fields with the rolled up dates and then get the diff.
Below is my default query. and a part of the output that shows one summary task results.
SELECT MSP_EpmProject_UserView.ProjectName as [File], MSP_EpmTask_UserView.TaskStartDate as [TaskStartDate], MSP_EpmTask_UserView.TaskFinishDate as [TaskFinishDate], Summary.TaskName as [FCR Project Name], Summary.TaskCost, Case When MSP_EpmTask_UserView.TaskName = 'PO' then CONVERT(VARCHAR(10),MSP_EpmTask_UserView.TaskFinishDate,101) END as [PO Date], Case When MSP_EpmTask_UserView.TaskName = 'Execution' then CONVERT(VARCHAR(10),MSP_EpmTask_UserView.TaskStartDate,101) END as [Execution Date], Case When Summary.TaskCost <= 2.5 then 'Projects below 2.5K responsiveness' When (Summary.TaskCost > 2.5) AND (Summary.TaskCost <= 5) then 'Projects between 2.5K and 5K responsiveness' When Summary.TaskCost > 5 then 'Projects above 5K responsiveness' Else '' END as [Project Responsiveness], Summary.TaskCount, MSP_EpmTask_UserView.TaskParentUID, TimeByDay.[TimeQuarter] , TimeByDay.TimeYear, TimeByDay.[TimeMonthOfTheYear], TimeByDay.TimeByDay FROM dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTask_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID INNER JOIN dbo.MSP_EpmTask_UserView as Summary ON dbo.MSP_EpmTask_UserView.TaskParentUID = Summary.TaskUID INNER JOIN MSP_EpmTaskByDay ON MSP_EpmTaskByDay.TaskUID = MSP_EpmTask_UserView.TaskUID Left Join [dbo].[MSP_TimeByDay] as [TimeByDay] ON MSP_EpmTaskByDay.TimeByDay = TimeByDay.TimeByDay
Any idea how to get the highlighted dates to appear in all rows or to get them in a single row?
In Excel Pivot table or in SQL i cannot use them to calculate the diff.
Ofir Marco , MCTS P.Z. Projects
All replies
-
Hi,
I am not sure what you want to achieve with "Left Join [dbo].[MSP_TimeByDay] as [TimeByDay] ON MSP_EpmTaskByDay.TimeByDay = TimeByDay.TimeByDay" so I ignored that part for now.
To get all information in a single row, you need to ensure to get one row per summary task only, joining relevant data from sub tasks.
Here a starting point:
SELECT P.ProjectName as [File], Summary.TaskStartDate as [TaskStartDate], Summary.TaskFinishDate as [TaskFinishDate], Summary.TaskName as [FCR Project Name], Summary.TaskCost, Case When NOT PO.TaskUID IS NULL then CONVERT(VARCHAR(10),PO.TaskFinishDate,101) END as [PO Date], Case When NOT Summary.TaskUID IS NULL then CONVERT(VARCHAR(10),Execution.TaskStartDate,101) END as [Execution Date], Case When Summary.TaskCost <= 2.5 then 'Projects below 2.5K responsiveness' When (Summary.TaskCost > 2.5) AND (Summary.TaskCost <= 5) then 'Projects between 2.5K and 5K responsiveness' When Summary.TaskCost > 5 then 'Projects above 5K responsiveness' Else '' END as [Project Responsiveness], COUNT(PO.TaskCount) + Count(Execution.TaskUID) AS TaskCount, Summary.TaskUID --, --TimeByDay.[TimeQuarter] , --TimeByDay.TimeYear, --TimeByDay.[TimeMonthOfTheYear], --TimeByDay.TimeByDay FROM dbo.MSP_EpmProject_UserView AS P INNER JOIN dbo.MSP_EpmTask_UserView as Summary ON P.ProjectUID = Summary.ProjectUID AND Summary.TaskIsSummary = 1 AND Summary.TaskIsProjectSummary = 0 LEFT OUTER JOIN dbo.MSP_EpmTask_UserView PO ON PO.TaskParentUID = Summary.TaskUID AND PO.TaskName = 'PO' LEFT OUTER JOIN dbo.MSP_EpmTask_UserView Execution ON Execution.TaskParentUID = Summary.TaskUID AND Execution.TaskName = 'Execution' --INNER JOIN MSP_EpmTaskByDay -- ON MSP_EpmTaskByDay.TaskUID = Summary.TaskUID --Left Join [dbo].[MSP_TimeByDay] as [TimeByDay] ON MSP_EpmTaskByDay.TimeByDay = TimeByDay.TimeByDay GROUP BY P.ProjectName , Summary.TaskStartDate , Summary.TaskFinishDate , Summary.TaskName , Summary.TaskCost , Case When NOT PO.TaskUID IS NULL then CONVERT(VARCHAR(10),PO.TaskFinishDate,101) END , Case When NOT Summary.TaskUID IS NULL then CONVERT(VARCHAR(10),Execution.TaskStartDate,101) END , Case When Summary.TaskCost <= 2.5 then 'Projects below 2.5K responsiveness' When (Summary.TaskCost > 2.5) AND (Summary.TaskCost <= 5) then 'Projects between 2.5K and 5K responsiveness' When Summary.TaskCost > 5 then 'Projects above 5K responsiveness' Else '' END , Summary.TaskUID
To get information in all rows (again without TimeByDay):
SELECT P.ProjectName as [File], AllTasks.TaskStartDate as [TaskStartDate], AllTasks.TaskFinishDate as [TaskFinishDate], Summary.TaskName as [FCR Project Name], Summary.TaskCost, Case When NOT PO.TaskUID IS NULL then CONVERT(VARCHAR(10),PO.TaskFinishDate,101) END as [PO Date], Case When NOT Summary.TaskUID IS NULL then CONVERT(VARCHAR(10),Execution.TaskStartDate,101) END as [Execution Date], Case When Summary.TaskCost <= 2.5 then 'Projects below 2.5K responsiveness' When (Summary.TaskCost > 2.5) AND (Summary.TaskCost <= 5) then 'Projects between 2.5K and 5K responsiveness' When Summary.TaskCost > 5 then 'Projects above 5K responsiveness' Else '' END as [Project Responsiveness], Summary.TaskCount, AllTasks.TaskParentUID --, --TimeByDay.[TimeQuarter] , --TimeByDay.TimeYear, --TimeByDay.[TimeMonthOfTheYear], --TimeByDay.TimeByDay FROM dbo.MSP_EpmProject_UserView AS P INNER JOIN dbo.MSP_EpmTask_UserView AS AllTasks ON P.ProjectUID = AllTasks.ProjectUID INNER JOIN dbo.MSP_EpmTask_UserView as Summary ON AllTasks.TaskParentUID = Summary.TaskUID LEFT OUTER JOIN dbo.MSP_EpmTask_UserView PO ON PO.TaskParentUID = AllTasks.TaskParentUID AND PO.TaskName = 'PO' LEFT OUTER JOIN dbo.MSP_EpmTask_UserView Execution ON Execution.TaskParentUID = AllTasks.TaskParentUID AND Execution.TaskName = 'Execution' --INNER JOIN MSP_EpmTaskByDay -- ON MSP_EpmTaskByDay.TaskUID = AllTasks.TaskUID --Left Join [dbo].[MSP_TimeByDay] as [TimeByDay] -- ON MSP_EpmTaskByDay.TimeByDay = TimeByDay.TimeByDay WHERE AllTasks.TaskIsProjectSummary = 0 AND AllTasks.TaskIsSummary = 0
Does that help?
Barbara
To increase the value of this forum, please mark the replies that helped to solve your issue as answer. If you find answers to questions from other forum participants to be helpful, please mark them as helpful. Your participation will help others to find an appropriate solution faster. Thanks for your support!