T-SQL - Retrieve Task Hierarchy In Project Server

In this article, I will explain how to get the Task Hierarchy for a specific project in Project Server via T-SQL.
 
Scenario

In Project Server, I have a project schedule with the summary task and subtasks, as shown below.

Based on the requirement, I would like to show the Task Hierarchy for each task in Tasks View "[MSP_EpmTask_UserView]" for a specific project.

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.
  • Create a "New Query" and make sure that you are using the Project Database (in this article, the project database name is "ProjectWebApp").
  • 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.
  1. -- Get the tasks based on the Project ID  
  2. SELECT TOP 1000 [TaskName],TaskIsSummary,TaskIndex  
  3. FROM [ProjectWebApp].[dbo].[MSP_EpmTask_UserView]  
  4. WHERE ProjectUID = 'D2BB8AF3-FB22-E711-8193-A0481C8F7133' and TaskIndex<>0  
  1. WITH TasksRecursive(ProjectUID,TaskTitle, TaskUID, TaskParentUID, TaskName, Level)  
  2. AS  
  3. (  
  4. SELECT ProjectUID,TaskName as TaskTitle, TaskUID, TaskParentUID, CAST(TaskName AS NVARCHAR(MAX)), 0  
  5. FROM dbo.MSP_EpmTask_UserView WITH(NOLOCK)  
  6. WHERE TaskIndex <> 0  
  7. UNION ALL  
  8. SELECT UV.ProjectUID,TasksRecursive.TaskTitle,TasksRecursive.TaskUID,UV.TaskParentUID,CAST(UV.TaskName ASNVARCHAR(MAX)) + ' -> ' + CAST(TasksRecursive.TaskName AS NVARCHAR(MAX)) , TasksRecursive.Level + 1  
  9. FROM TasksRecursive  
  10. Inner JOIN dbo.MSP_EpmTask_UserView UV WITH(NOLOCK)  
  11. ON TasksRecursive.ProjectUID = UV.ProjectUID AND TasksRecursive.TaskParentUID = UV.TaskUID  
  12. WHERE TaskIsSummary = 1 AND TasksRecursive.TaskParentUID <> UV.TaskParentUID  
  13. )  
  14. SELECT X.TaskTitle,  
  15. (select TaskName from MSP_EpmTask_UserView where TaskIndex = 0 and ProjectUID = 'D2BB8AF3-FB22-E711-8193-A0481C8F7133') + ' -> ' + X.TaskName as 'Task Hierarchy'  
  16. FROM TasksRecursive X  
  17. INNER JOIN (SELECT ProjectUID, TaskUID, MAX(Levelas 'Level' FROM TasksRecursive GROUP BY ProjectUID, TaskUID) Y  
  18. ON X.ProjectUID = Y.ProjectUID AND X.TaskUID = Y.TaskUID AND X.Level = Y.Level  
  19. WHERE X.ProjectUID = 'D2BB8AF3-FB22-E711-8193-A0481C8F7133'  
  • Run the query to get the desired result.
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, I have explained how to show the Task Hierarchy for a specific project in Project Server Database using T-SQL.

References See Also