Home > Paul Mather, Work > #ProjectOnline reporting on task Predecessors and Successors #O365 #MSProject #PPM #PMOT # Excel #PowerBI #OData

#ProjectOnline reporting on task Predecessors and Successors #O365 #MSProject #PPM #PMOT # Excel #PowerBI #OData

Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

A few times I have heard this topic come up so I thought it was worth a quick blog post to give two examples for getting access to this detail. Firstly a quick look at my sample project to see the data and task links:

image

As we can see, all tasks are linked. The predecessor and successor details are not available in the OData reporting API by default: ({PWASiteURL}/_api/ProjectData).

The first option we will explore is using the REST CSOM API ({PWAURL}/_api/ProjectServer). To access this is not a simple read from one endpoint like it would be in the OData reporting API if the data was there. When using the CSOM REST API you have to first get the project then from there you can get the task details and task link details. Below we walkthrough this process and view the results. I am just using the browser to return the data for ease. Let’s have a look at this Project data using: {PWASiteURL}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’) where the GUID is the project GUID for the project seen above. This returns:

SNAGHTML1271759a

Here you can see all of the related endpoints and then the project properties below. I have outlined in red the two related endpoints that are useful to us, the TaskLinks and Tasks.

Lets have a look at the TaskLinks first – we have 4 links in the simple plan displayed above, this matches what we see in the TaskLinks endpoint:

{PWASiteUrl}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/TaskLinks

SNAGHTML127510a3

For each link we can then access two other endpoints /End and /Start and see two properties for the link, Id and DependencyType. Id is the TaskLink Id and DependencyType is the internal dependency type value, the enumerations for the dependency type can be found here: https://msdn.microsoft.com/en-us/library/microsoft.projectserver.client.dependencytype_di_pj14mref.aspx. Looking at the data returned, I have 3 links with a dependency type of 1 (Finish to Start) and 1 link with a dependency type of 3 (Start to Start). Now for one of those TaskLinks, we will look at what the /End and /Start endpoints provide. I will use the TaskLink with a Start to Start dependency type for this. Firstly the /Start endpoint:

{PWASiteUL}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/TaskLinks(‘0d7da2b3-2dcb-e811-9328-1002b5489337’)/Start – where the 2nd GUID is the TaskLink GUID

SNAGHTML1283a2ae

This returns all of the data for the starting task, in this example it is task T2 (I’ve updated the REST call to just return the task name:

SNAGHTML12872358

Task T2 is the task starting the link as seen in the project plan:

image

The /End endpoint, as you can guess will return the same details but for the task ending the link:

{PWASiteUL}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/TaskLinks(‘0d7da2b3-2dcb-e811-9328-1002b5489337’)/End – where the 2nd GUID is the TaskLink GUID – I’ve update the REST call to just return the task name:

SNAGHTML128b4ce6

This returns T3 from the example project:

image

As you can see, using the TaskLinks endpoint once we have the project, we can then navigate to find the task details for the linked tasks.

Now lets look at what the /Tasks endpoint can do for us to find the linked tasks. Accessing the {PWASiteUrl}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/Tasks endpoint will return all of the tasks in the project (based on the project GUID used in the REST call):

SNAGHTML128ffcaa

For each task in the project we can see the task properties but also navigate to another endpoint to view more related data for that one task. For example, we can then navigate and view the /Predecessors and /Successors. I will use task T3 for this walkthrough by passing in the Task GUID for T3. Accessing the predecessors data for task T3:

{PWASiteUrl}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/Tasks(‘b3433ba7-2dcb-e811-9328-1002b5489337’)/Predecessors – where I have passed in the task GUID for T3:

SNAGHTML12964d6d

This returns the TaskLink details for the predecessor task – from that point we can then use the /End and /Start related queries to get the linked task details. The same goes for the /Successors endpoint for the example task T3:

{PWASiteUrl}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/Tasks(‘b3433ba7-2dcb-e811-9328-1002b5489337’)/Successors – where I have passed in the task GUID for T3:

SNAGHTML129abb66

This returns the TaskLink details for the successor task – from that point we can then use the /End and /Start related queries to get the linked task details.

As you can see, trying the get that data for all linked tasks in a report using Power Query wouldn’t be a simple query to one endpoint but it is possible to follow it through to get the data needed.

The next option to look at is creating two task level calculated fields so that you can get the predecessor and successor details in the /Tasks endpoint in the OData reporting API ({PWASiteURL}/_api/ProjectData/Tasks). Whilst this is simplifies the reporting experience there is a performance cost to this – certainly for large projects with many tasks. Also this will use 2 of the recommended maximum 5 task level calculated fields! In PWA Settings > Enterprise Custom Fields and Lookup Tables, create two new Task level text fields that use formulas, one field will be for predecessors and one for successors. In the predecessors field formula use [Predecessors] and in the successors field formula use [Successors]. The predecessors custom field can be seen below:

image

The next time you publish your project/s you will then see the data available in the OData Reporting API:

{PWASiteUrl}/_api/ProjectData/Projects(guid’a28bf087-2acb-e811-afb0-00155d143a0e’)/Tasks?$Select=TaskName,TaskPredecessors,TaskSuccessors

SNAGHTML12a6e5c7

Hope that helps!

Advertisements
Categories: Paul Mather, Work Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: