locked
"Task Hierarchy" Field in reporting Database RRS feed

  • Question

  • hi

    how can i find "Task Hierarchy" field in Reporting Database?

    I have a view for reporting task status and i need task hierarchy for each task. how can i add this field to my view?

    Monday, August 29, 2011 12:41 PM

Answers

  • Hi Shadi,

    This should do it... or at least you can use the technique to refine to your exact needs!

    Enjoy
    Wim 

     

    WITH CTE(ProjectUID, TaskUID, TaskParentUID, TaskName, Level)
    AS
     (
    	SELECT	ProjectUID, TaskUID, TaskParentUID, CAST(TaskName AS NVARCHAR(MAX)), 0
    	FROM	Reporting.dbo.MSP_EpmTask_UserView WITH(NOLOCK)
    	WHERE	TaskIsSummary = 0
    	UNION ALL
    	SELECT	UV.ProjectUID, 
    			CTE.TaskUID, 
    			UV.TaskParentUID, 
    			CAST(UV.TaskName AS NVARCHAR(MAX)) + ' - ' + CAST(CTE.TaskName AS NVARCHAR(MAX)), CTE.Level + 1
    	FROM	CTE 
    			INNER JOIN 	Reporting.dbo.MSP_EpmTask_UserView UV WITH(NOLOCK) 
    						ON CTE.ProjectUID = UV.ProjectUID AND CTE.TaskParentUID = UV.TaskUID
    	WHERE	TaskIsSummary = 1 AND
    			CTE.TaskParentUID <> UV.TaskParentUID
    )
    SELECT	X.TaskUID, X.TaskName
    FROM	CTE X
    		INNER JOIN (SELECT ProjectUID, TaskUID, MAX(Level) as 'Level' FROM CTE GROUP BY ProjectUID, TaskUID) Y 
    					ON X.ProjectUID = Y.ProjectUID AND X.TaskUID = Y.TaskUID AND X.Level = Y.Level
    WHERE	X.ProjectUID = '09241C8E-90CA-4DC6-AB9A-34902BD4582A'
    

    Thursday, September 1, 2011 1:35 PM

All replies

  • Hello Shadi,

    You can create a new view from MSP_EPMTask_UserView to get the TaskOutlineNumber for "Position of a task in the outline hierarchy " & TaskOutlineLevel for "Place of a task in the project outline hierarchy".

    Hope that helps.
    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    Monday, August 29, 2011 12:50 PM
  • There's also a Parent Task UID field which may help you:
     
     
     

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Monday, August 29, 2011 1:05 PM
  • Thanks Amit and Andrew

    But I need Full Hierarchy of task... this field is available in TimesheetLine Tables and views. I want to know how could I have this field from task tables...

    Tuesday, August 30, 2011 8:13 AM
  • Hi Shadi,

     

    To understand what you eactly mean: can you provide me with a "picture" for what you want to see?

    It is possible to use a kind of recursive SQL query, in order de "see" the full hierarchy. So If you show me what you want...

     

    Regards
    Wim

    Tuesday, August 30, 2011 10:46 AM
  • Thanks for your reply.

    in some tasks like this:

    I want to add "Task 7" in my report and i want to have a field like this: 6>8>9>7

    Or for task "9",I want to have this: 6>8>9

     

    Tuesday, August 30, 2011 12:50 PM
  • Hi Shadi,

    Can you wait till thursday? I have a planning problem :DDD

     

    Tuesday, August 30, 2011 6:08 PM
  • How about using the WBS Code field?
     
     
     

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    • Proposed as answer by epmXperts Saturday, September 3, 2011 6:17 PM
    Tuesday, August 30, 2011 6:11 PM
  • I think he needs the complete task Name structure... I use CTE's for this kind of queries
    ofcourse WBS would solve it "for free" ;)

     

    PS @ Andrew: Do you have an email adres that I can use to contact you? I looked for it on the UMT site, but couldn't find it...

    Tuesday, August 30, 2011 6:16 PM
  • Hi Shadi,

    This should do it... or at least you can use the technique to refine to your exact needs!

    Enjoy
    Wim 

     

    WITH CTE(ProjectUID, TaskUID, TaskParentUID, TaskName, Level)
    AS
     (
    	SELECT	ProjectUID, TaskUID, TaskParentUID, CAST(TaskName AS NVARCHAR(MAX)), 0
    	FROM	Reporting.dbo.MSP_EpmTask_UserView WITH(NOLOCK)
    	WHERE	TaskIsSummary = 0
    	UNION ALL
    	SELECT	UV.ProjectUID, 
    			CTE.TaskUID, 
    			UV.TaskParentUID, 
    			CAST(UV.TaskName AS NVARCHAR(MAX)) + ' - ' + CAST(CTE.TaskName AS NVARCHAR(MAX)), CTE.Level + 1
    	FROM	CTE 
    			INNER JOIN 	Reporting.dbo.MSP_EpmTask_UserView UV WITH(NOLOCK) 
    						ON CTE.ProjectUID = UV.ProjectUID AND CTE.TaskParentUID = UV.TaskUID
    	WHERE	TaskIsSummary = 1 AND
    			CTE.TaskParentUID <> UV.TaskParentUID
    )
    SELECT	X.TaskUID, X.TaskName
    FROM	CTE X
    		INNER JOIN (SELECT ProjectUID, TaskUID, MAX(Level) as 'Level' FROM CTE GROUP BY ProjectUID, TaskUID) Y 
    					ON X.ProjectUID = Y.ProjectUID AND X.TaskUID = Y.TaskUID AND X.Level = Y.Level
    WHERE	X.ProjectUID = '09241C8E-90CA-4DC6-AB9A-34902BD4582A'
    

    Thursday, September 1, 2011 1:35 PM