Query Task Hierarchy In Project Server 2016/2013

Display Task Hierarchy In Project Server 2016 using SQL

In this post, we will learn how to query Task Hierarchy in Project Server 2016 to get the summary task and all related sub-tasks from Project Database using T-SQL.

You might also like to read Query Enterprise Custom Fields from Project Server Database


Task Hierarchy In Project Server

In the previous article “Display the Task Hierarchy of a subtask within My Assignments view inside Tasks section in Project Server“, we have discussed How to display the Task Hierarchy within “My Assignment” view inside the Tasks section in Project Server 2016 and 2013 as shown below:

Query Task Hierarchy In Project Server

In this post, our main goal is showing the same Task Hierarchy for each task for a specific project using T-SQL as shown below:

Task Hierarchy 2

Query Task Hierarchy In Project Server 2016

Steps

  • Open SQL Server Management Studio.
Connect to sql
  • Open a new query, select your Project Server database.
Query project Database
  • Paste the below query to get Project UID that you would like to show its tasks as a Hierarchy.
-- Project Server 2016
SELECT * FROM pjrep.MSP_EpmProject_UserView
-- Project Server 2013
SELECT * FROM [ProjectWebApp].[dbo].MSP_EpmProject_UserView

Query Task Hierarchy With Summary Task

  • Download Query Task Hierarchy In Project Server from GitHub based on your Project Server version.
  • Open the downloaded file in a new query in SQL Server Management Studio.
  • Make sure that you have selected your project database.
  • Update the ProjectUID value with your own Project UID.
Task Hierarchy 1
  • Run the Query to show Task Hierarchy per each task for a specific project as shown below.
Task Hierarchy 2

Query Task Hierarchy Without Summary Task

As you can see, the above query shows the task hierarchy with the main summary task, so if you would like to exclude the summary task, you should do the following:

Task Hierarchy 4
  • Run the Query to get the desired result.
Task Hierarchy 5

Conclusion

In conclusion, we have learned how to query Task Hierarchy per each task for a specific project in Project Server 2016.

Download

You can download the SQL query for Project Server 2016 / 2013 from GitHub at Query Task Hierarchy In Project Server.

Applies To
  • Project Server 2019.
  • Project Server 2016.
  • Project Server 2013.
You might also like to read
Have a Question?

If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.

Leave a Reply