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

Answer 1

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!