none
Project Server 2013 - BI SQL reports - roll up task dates into a single row of the summary task level RRS feed

  • 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

    Thursday, February 23, 2017 12:20 PM

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!

    Sunday, February 26, 2017 8:16 AM
    Moderator