Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
"Task Hierarchy" Field in reporting Database RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Shadi,

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

     

    Tuesday, August 30, 2011 6:08 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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