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:
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:
Query Task Hierarchy In Project Server 2016
Steps
- Open SQL Server Management Studio.
- Open a new query, select your Project Server 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_UserViewQuery 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.
- Run the Query to show Task Hierarchy per each task for a specific project as shown below.
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:
- Download Query Task Hierarchy In Project Server without the Summary task file based on your Project Serve version.
- Open the downloaded file in a new query in SSMS.
- Make sure that you have selected your project database.
- Update the ProjectUID value with your own Project UID.
- Run the Query to get the desired result.
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
- Query Multiple Values Lookup Table Field Project Server 2019.
- Query Enterprise Custom Fields from Project Server Database.
Have a Question?
If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.