Project Server: Show Task Hierarchy using SQL

2024-01-17

Introduction

In this article, we will show How to get the Task Hierarchy for a Specific Project in Project Server Database using SQL?

Scenario

In Project Server, we have a project schedule with the summary task and sub tasks as shown below：

https://gallery.technet.microsoft.com/site/view/file/178678/1/Task%20Structure.png

Our requirement is showing the task hierarchy for each task in tasks view "[MSP_EpmTask_UserView]" for a specific project as shown below：

https://gallery.technet.microsoft.com/site/view/file/178676/1/Task%20Structure%20output.png

Steps

To get the Task Hierarchy, we will use the Recursive Queries Using Common Table Expressions as shown below:

Open Microsoft SQL Management Studio > Connect to the SQL Server Instance.

https://gallery.technet.microsoft.com/site/view/file/178680/1/connect-to-sql.png

Create a new Query, and make sure that you are using the Project Database (in this article the project database name is "ProjectWebApp").

https://gallery.technet.microsoft.com/site/view/file/178679/1/ProjectWebApp.png

Get the Tasks for a specific project based on its ProjectUID from form "[MSP_EpmTask_UserView]" view as shown below：
- Note: Change the ProjectUID with your ProjectUID.

-- Get the tasks based on the Project ID
SELECT TOP  1000 [TaskName],TaskIsSummary,TaskIndex
FROM [ProjectWebApp].[dbo].[MSP_EpmTask_UserView]
WHERE  ProjectUID = 'D2BB8AF3-FB22-E711-8193-A0481C8F7133' and TaskIndex<>0

https://gallery.technet.microsoft.com/site/view/file/178674/1/Get%20the%20tasks%20based%20on%20the%20ProjectUID.png

As we have mentioned we will use the Recursive Queries Using Common Table Expressions to get the Task Hierarchy as the below query.
- Note: Change the ProjectUID with your ProjectUID.

WITH TasksRecursive(ProjectUID,TaskTitle, TaskUID, TaskParentUID, TaskName, Level)
AS
(
SELECT ProjectUID,TaskName as TaskTitle, TaskUID, TaskParentUID,  CAST(TaskName AS  NVARCHAR(MAX)), 0
FROM   dbo.MSP_EpmTask_UserView WITH(NOLOCK)
WHERE  TaskIndex <> 0
 
UNION ALL
 
SELECT UV.ProjectUID,TasksRecursive.TaskTitle,TasksRecursive.TaskUID,UV.TaskParentUID,CAST(UV.TaskName AS  NVARCHAR(MAX)) + ' -> ' +  CAST(TasksRecursive.TaskName AS  NVARCHAR(MAX)) , TasksRecursive.Level + 1
FROM   TasksRecursive
Inner JOIN   dbo.MSP_EpmTask_UserView UV WITH(NOLOCK)
ON TasksRecursive.ProjectUID = UV.ProjectUID AND TasksRecursive.TaskParentUID = UV.TaskUID
WHERE  TaskIsSummary = 1 AND TasksRecursive.TaskParentUID <> UV.TaskParentUID
)
 
SELECT X.TaskTitle,
(select TaskName from MSP_EpmTask_UserView where TaskIndex = 0 and  ProjectUID = 'D2BB8AF3-FB22-E711-8193-A0481C8F7133') + ' -> ' + X.TaskName as  'Task Hierarchy'
FROM TasksRecursive X
INNER JOIN (SELECT ProjectUID, TaskUID, MAX(Level) as  'Level' FROM  TasksRecursive GROUP  BY ProjectUID, TaskUID) Y
ON X.ProjectUID = Y.ProjectUID AND X.TaskUID = Y.TaskUID AND X.Level = Y.Level
WHERE  X.ProjectUID = 'D2BB8AF3-FB22-E711-8193-A0481C8F7133'

Run the query to get the desired result as shown below：

https://gallery.technet.microsoft.com/site/view/file/178677/1/Get%20the%20tasks%20based%20on%20the%20ProjectUID%20output.png

Applies To

Project Server 2010 Reporting Database.
Project Server 2013 Database.
Project Server 2016 Content Database.
- In Project Server 2016, a single database (SharePoint Content Database) holds the project data and the content.

Conclusion

In this article, we have explained How to show the Task Hierarchy for a Specific Project in Project Server Database using SQL?

References

Query task hierarchy of a subtask using SQL in Project Server.

Share via