Query Task Hierarchy of a subtask using SQL In Project Server

In the previous article, I discussed How to display the Task Hierarchy within My Assignment view inside Tasks section in Project Server 2013,

In this article, I will elaborate How to query this field from ProjectWebApp Database in Project Server 2013?

My scenario

I have a project schedule as shown below:

Tasks Schedule

I need to query the Task Hierarchy for each task in this project via SQL to looks like

Task Hierarchy 2

Steps:

  • Open SQL Server Management Studio.

Connect to sql

  • Create a New Query, ensure that the current database is your project database. In my case, it’s “ProjectWebApp.”

New Query

  • Try to query [MSP_EpmTask_UserView] view using this Query (Change the ProjectUID with your ProjectUID)
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ProjectUID],[TaskName],TaskIsSummary,TaskIndex
FROM [ProjectWebApp].[dbo].[MSP_EpmTask_UserView]
WHERE       ProjectUID = '2D43C978-00F3-E511-80D4-A0481C8F7133'

Task Hierarchy 3

  • Click on a new query and copy the following query (Change the ProjectUID with your ProjectUID)
WITH CTE(ProjectUID, TaskUID, TaskParentUID, TaskName, Level)
AS
(
SELECT ProjectUID, TaskUID, TaskParentUID,  CAST(TaskName AS NVARCHAR(MAX)), 0
FROM   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   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,
(select TaskName from MSP_EpmTask_UserView where TaskIndex = 0 and  ProjectUID = '2D43C978-00F3-E511-80D4-A0481C8F7133') + ' -> ' + X.TaskName as 'Task Hierarchy'
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 = '2D43C978-00F3-E511-80D4-A0481C8F7133'

Task Hierarchy 1

  • Run the Query to get the desired result.

Task Hierarchy 2

If you don’t need to show the Main Summery Task in Task Hierarchy, you should use this query (Change the ProjectUID with your ProjectUID)

WITH CTE(ProjectUID, TaskUID, TaskParentUID, TaskName, Level)
AS
(
SELECT ProjectUID, TaskUID, TaskParentUID,  CAST(TaskName AS NVARCHAR(MAX)), 0
FROM   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   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 as 'Task Hierarchy'
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 = '2D43C978-00F3-E511-80D4-A0481C8F7133'

Task Hierarchy 4

  • Run the Query to get the desired result.

Task Hierarchy 5

Enjoy 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s