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

October 13, 2018 at 9:23 am | Posted in Administration, Configuration, Customisation, Fixes, Functionality, Information, Reporting | 3 Comments
Tags: , , , ,

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

#ProjectOnline Supporting Projects and Programs Part 3 #PPM #MSProject #Office365 #PMOT #PMO #SharePoint #PowerBI

October 1, 2018 at 6:27 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | Leave a comment
Tags: , , , , , , ,

In part 3 of this mini series of blog posts we will look at a basic report example to support projects and programs making use of the configuration changes in part 1 and 2. For those of you that missed part 1, see the post here: https://pwmather.wordpress.com/2018/09/19/projectonline-supporting-projects-and-programs-part-1-ppm-msproject-office365-pmot-pmo/ and part 2 here: https://pwmather.wordpress.com/2018/09/21/projectonline-supporting-projects-and-programs-part-2-ppm-msproject-office365-pmot-pmo-sharepoint/

Now that we have done some very simple configuration changes in PWA and the Project Sites and then populated some example test data in the PWA instance we can look at example reports. We won’t cover creating these reports from start to end as this isn’t the purpose of the post, it is purely to highlight how to make use of the configuration changes to give to the program level reporting. These reports are also not engaging or showing casing Power BI, so you will want to create much better looking reports as these are just used to show examples of the data!

Firstly, lets look at the Project Center so you get an idea of the Project data I have in this test instance:

image

Notice I have two projects tagged and 1_Program projects but one in each program. These are the projects that will provide the data in the first page of my Program report:

image

The slicer is using the Program custom field:

image

To limit the data on this page, I have added page filter using the Project Plan Type field and filtered to “1_Program” projects:

image

So this page shows data for the project tagged with “1_Program” in the Project Plan Type field and in this case, the project tagged with “IT Transformation” which in my data set is the “IT Change Program” project. I don’t have much data on this page but this is just to show the data is for the program level project.

The next two pages show similar details for the program, one shows the details and the other shows some charts (just to add some colour!) but they both work the same way in filtering data that is only relevant at the program level:

image

image

On these pages there are no page level filters set, the tasks, risks and issues visualisations all have a filter applied to only display tasks, risks or issues that are requiring attention at the program level. On the tasks visuals we are using the task level “Escalation Level” field and filtering to only include tasks tagged with “1_Program”:

image

On the risks and issues visuals, we do the the same but use the “Category” field and filter to only include risks or issues tagged with “1_Program”:

image

This provides quick access to data relevant to the program. As we can see, these are very simple examples but the concept can be applied to larger datasets with more fields and data but the first page / report example will only work providing you one have 1 project plan per “program” value tagged with “1_Program” in the “Project Plan Type” Project level field.

That’s it for this short series – I hope that you found it useful!

#ProjectOnline Supporting Projects and Programs Part 2 #PPM #MSProject #Office365 #PMOT #PMO #SharePoint

September 21, 2018 at 9:00 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , ,

In part 2 of this mini series of blog posts we will look at the configuration on the Project Sites to support projects and programs. For those of you that missed part 1, see the post here: https://pwmather.wordpress.com/2018/09/19/projectonline-supporting-projects-and-programs-part-1-ppm-msproject-office365-pmot-pmo/ 

As the Project Site are SharePoint sites, this also has many configuration options but this needs to be considered careful based on your reporting requirements. Whilst all of the data in SharePoint is accessible for reporting not all data on the Issues and Risks lists is available in the Project Online OData Reporting API. Only the data from default list columns Microsoft include on the Issues and Risks are included in the Project Online OData Reporting API. Other data from custom columns on the lists is accessible but only via the SharePoint list REST APIs but this can be tricky to report on for a cross project report. Here is an example for accessing this data in Power BI reports: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/ As we want to keep this as simple as possible, we will ensure the data we need in synchronised to the Project Online OData API. The Category column on the Issues and Risks lists is the ideal default column to use for our requirements. By default this contains the following values:

(1) Category1
(2) Category2
(3) Category3

We will update these values for the Category columns to match the lookup table values we created for the Project Plan Type and Escalation Level PWA custom fields:

1_Program
2_Project

image

This is done on each list, for example access the Risks list, click the List tab then List Settings. Scroll down the page to the columns and click the Category column and update the values. Repeat for the Issues list then repeat for the other project sites. You need to be careful updating some of the default Issues and Risks columns as you can break the synchronisation processes to the Project Online reporting schema which the OData Reporting API uses. If you do break this sync, you will see queue errors in the PWA Manage Queue page. Changing just the choice values as I have will be fine and not cause sync issues but fully test changes to ensure the data syncs as expected with no queue errors. As the Issues and Risks use a list content type, these change need to be made in the site template so new project sites get new values and manually or via code in the existing project sites but that is beyond the scope of this post but here is a post that might help get you started: https://pwmather.wordpress.com/2016/07/08/access-projectonline-project-sites-using-powershell-and-sharepoint-csom-office365/ or https://pwmather.wordpress.com/2016/05/04/projectonline-projectserver-project-site-provisioning-using-office365-pnp-remote-provisioning-sharepoint-powershell/ When updating existing project site lists, you will need to consider existing data on those lists as they might be using values you are wanting to remove.

Now our project sites have the correct Category values for Issues and Risks, we can tagged the items as needed as seen below on an example project:

Issues:

image

Risks:

image

You could also update the Risks and Issues view to and views that filter to just Program or Project or group by Category etc. Now the project sites are updated, when Issues and Risks are created these can be tagged with the correct category to make these visible in Program level reports.

In the final part of this blog post series we will look at using this data in example Power BI reports.

#ProjectOnline Supporting Projects and Programs Part 1 #PPM #MSProject #Office365 #PMOT #PMO

September 19, 2018 at 7:57 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 2 Comments
Tags: , , , , , , ,

Microsoft’s Office 365 PPM tool, known as Project Online is a very flexible tool in that it is fully configurable to support your organisations PPM requirements. An intro to some of the configuration options can be found in my getting started guide I wrote a few years back: https://pwmather.wordpress.com/2014/07/22/getting-started-with-projectonline-round-up-ps2013-office365-project-ppm-sharepointonline-pm-sp2013/ 

In this mini series of blog posts we will look at an option for supporting a simple project hierarchy of projects and programmes – known as programs across the pond. Due to the flexibility Project Online offers, there are several ways this can be done – there is no right or wrong way. The right way is the way that works for your organisation. In this example we will use custom fields to support projects and programmes, these will be at the project level, task level and also the issues and risks lists. But you could do this with Enterprise Project Types (EPTs) with different project site templates and custom fields but for the purpose of this blog post we will just use the fields and all projects are under that same EPT. In this series of posts we will look at the minimum required PWA configuration, the SharePoint configuration and then finish off with some simple example reports making use of the configuration changes we implement.

Firstly we will look at the PWA custom fields then the Project Site columns. In PWA navigate to PWA Settings > Enterprise Custom Fields and Lookup Tables. I created a new lookup table to hold the following values to determine the level, I called this Project Plan Type:

image

I created another lookup table called Program to list the programs used in the organisation:

image

As you can see, I just created two test / example program values just for the purpose of this blog post. Next I created two project level custom fields, one call Program linking to the Program lookup table and one called Project Plan Type linking to the Project Plan Type lookup table:

image

These are used to tag the projects with the correct project type and associate the projects to the correct program.

I also created a task level field called Escalation Level and linked that to the Project Plan Type lookup table:

image

This task level field is used to escalate / highlight tasks or milestones from the project plans up to the program level if needed.

These are the only fields I need to add to support my simple project / program scenario.

Next up I will configure a Project Center view to support my projects and programs, in PWA Settings navigate to Manage Views and create the new view/s as required. In this example, I copied the default Summary view, called it Programs. I then edited this new Programs view to include the two new project level fields – Program and Project Plan Type. I then added a grouping to group by Program then by Project Plan Type and sort by Project Plan Type:

image

Which results in – these are just test projects for the purpose of this blog post:

image

This view enables us to easily see the project and program data as well as aggregate the data to the summary grouping rows where applicable.

I then updated the Task Summary Project view to include the new Escalation Level field so that this new field can be used in PWA. It could also be added to an Enterprise Global view so that it was available by default in a Project Desktop client view/s. The updated view can be seen here:

image

Next, ensure the two new Project level fields are present on a Project Detail Page (PDP) so that users can set the values as needed.

image

We are now able to capture the schedule data required to support this simple scenario for projects and programs. The details for each project are managed as normal in the “2_Project” type projects, any tasks or milestones that need escalating to the program would be tagged correctly and viewed in reports. Program level activities are managed in the “1_Program” type project, all of the program level summary details such a Status Summary as seen on the PDP image above are added to the program project. In the next post we will look at how we can support this on the Issues and Risks lists on the Project Sites.

#ProjectOnline data flows diagram #Office365 #PPM #MSProject

August 23, 2018 at 9:51 pm | Posted in Administration, Configuration, Functionality, Information | Comments Off on #ProjectOnline data flows diagram #Office365 #PPM #MSProject
Tags: , , ,

I have put together a very simple diagram to describe some core data flows in Project Online / Project Server for actions like opening projects, save a project on a PDP etc. This high level diagram can be seen below:

ProjectOnlineDataFlows

Click the image to see a larger version.

There are probably some scenarios where this image isn’t 100% accurate but it gives you a good starting point for most core scenarios / data interactions with Project Online and Project Desktop.

I hope that you find it useful – happy to extend it / update it with more Project Online data entities if people find it useful!

Reporting on #ProjectOnline Resource Cost Rate Tables #Office365 #PPM #PowerBI #Excel #PowerQuery #MSProject

August 10, 2018 at 4:18 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , ,

The resource cost rate table details are not available in the Project Online / Project Server OData Reporting API (_api/ProjectData) but they are accessible using OData but from the CSOM REST API (_api/ProjectServer). In this blog post, I will walkthrough getting this data into an example Power BI report. It wont look pretty, that’s not the idea of this post!

To get this data you need to use the _api/ProjectServer API as seen below in the example for cost rate table A:

{PWAURL}/_api/ProjectServer/EnterpriseResources(‘{RESGUID}’)/CostRateTables(‘A’)/CostRates

Which gives the detail:

SNAGHTML5adc642

To get all of the resources different cost rate A details, you would need to dynamically pass in the RESGUID. In the steps below we look at doing this in Power Query so this would work for either Power BI or Excel but for the purpose of the blog post, I’m using Power BI.

In Power BI, create a new OData connection using the Get Data > OData option. Use the following URL:

{PWAURL}/_api/ProjectServer/EnterpriseResources(‘{RESGUID}’)/CostRateTables(‘A’)/CostRates

Update with the correct PWA URL and a valid resource GUID from that PWA instance. Edit the data so it loads the Power Query Editor:

image

I renamed this to fn_getResCostRateA as this will become a function. Open the advanced editor:

SNAGHTML581e6c0

The code needs to be updated to:

SNAGHTML5817fb9

Click done and you will see the following:

image

No need to do anything with the parameter or buttons. Now we need to add another data source in for the resource metadata. Add a new new OData data source in from the Power Query Editor window and use the following URL:

{PWAURL}/_api/ProjectServer/EnterpriseResources?$Select=Id,Name&$Filter=ResourceType ne 3

Update with the correct PWA URL. This will get the list of resource GUIDs to pass into the function and also the resource name to be used in the report. I renamed the connection to Resource Details – Cost Rate Table A:

image

Once you have edited the query as required a new custom column needs to be added to invoke the function created earlier. Click the Add Column tab then click Custom Column. Give the column a name such as GetCostRateADetails then enter the following: fn_getResCostRateA([Id]) as seen below:

image

When clicking OK, this might take a while depending on how many resources you have as this will invoke the function for each project and call the REST API, passing in the Id for that row and bring back the cost rate A table records. Once completed you will see the tables as below in the new custom column:

image

Now the column needs to be expanded, click the double arrow in the custom column heading and expand the cost rate fields:

image

Click OK and the data will refresh / load then display the data for the cost rate fields:

image

Notice for those resources with multiple cost rate table entries there are multiple rows per resource. These are just resources from the Microsoft Project Online demo content with updated cost rate entries.

That’s it, now load into Power BI and create the report – a basic table example below:

image

For other cost rate tables, repeat the process but replace the A for the other cost rate tables such as:

{PWAURL}/_api/ProjectServer/EnterpriseResources(‘{RESGUID}’)/CostRateTables(‘B’)/CostRates

This dynamic function process is the same process I’ve used and detailed before in previous blog posts for Power Query such as this one: https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-2/

#ProjectServer and #SharePoint 2013 / 2016 June 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject

June 14, 2018 at 7:33 pm | Posted in Administration, Configuration, CU, Fixes, Functionality, Information | Comments Off on #ProjectServer and #SharePoint 2013 / 2016 June 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject
Tags: , , , , , , ,

The Office 2016 June 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4299875

Project 2016 June 2018 update:
https://support.microsoft.com/en-us/kb/4022164

SharePoint Server 2016 / Project Server 2016 June 2018 update: 
https://support.microsoft.com/en-us/kb/4022173 & https://support.microsoft.com/en-us/kb/4022178

The Office 2013 June 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4299875

Project Server 2013 June 2018 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/4022185

Project Server 2013 June 2018 update:
https://support.microsoft.com/en-us/kb/4022192

Project 2013 June 2018 update:
https://support.microsoft.com/en-us/kb/4022171

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the June 2018 CU for 2013.

As always, fully test these updates on a replica test environment before deploying to production.

#ProjectServer and #SharePoint 2013 / 2016 May 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject

May 11, 2018 at 10:06 am | Posted in Administration, Configuration, CU, Fixes, Functionality, Information, Installation | Comments Off on #ProjectServer and #SharePoint 2013 / 2016 May 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject
Tags: , , , , , , ,

The Office 2016 May 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4133083

Project 2016 May 2018 update:
https://support.microsoft.com/en-us/kb/4018373

SharePoint Server 2016 / Project Server 2016 May 2018 update: 
https://support.microsoft.com/en-us/kb/4018386 & https://support.microsoft.com/en-us/kb/4018381

The Office 2013 May 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4133083

Project Server 2013 May 2018 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/4018395

Project Server 2013 May 2018 update:
https://support.microsoft.com/en-us/kb/4022130

Project 2013 May 2018 update:
https://support.microsoft.com/en-us/kb/4018379

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the May 2018 CU for 2013.

As always, fully test these updates on a replica test environment before deploying to production.

#ProjectServer and #SharePoint 2013 / 2016 April 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject

April 11, 2018 at 6:26 am | Posted in Administration, Configuration, CU, Customisation, Fixes, Functionality, Information, Installation | Comments Off on #ProjectServer and #SharePoint 2013 / 2016 April 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject
Tags: , , , , , , ,

The Office 2016 April 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4098622

Project 2016 April 2018 update:
https://support.microsoft.com/en-us/kb/4018320

SharePoint Server 2016 / Project Server 2016 April 2018 update: 
https://support.microsoft.com/en-us/kb/4018340 & https://support.microsoft.com/en-us/kb/4018336

The Office 2013 April 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4098622

Project Server 2013 April 2018 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/4018346

Project Server 2013 April 2018 update:
https://support.microsoft.com/en-us/kb/4018352

Project 2013 April 2018 update:
https://support.microsoft.com/en-us/kb/4018335

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the April 2018 CU for 2013.

As always, fully test these updates on a replica test environment before deploying to production.

#ProjectServer and #SharePoint 2013 / 2016 March 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject

March 16, 2018 at 6:22 pm | Posted in Administration, CU, Fixes, Functionality, Information, Installation | Comments Off on #ProjectServer and #SharePoint 2013 / 2016 March 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject
Tags: , , , , , , ,

The Office 2016 March 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4090988

Project 2016 March 2018 update:
https://support.microsoft.com/en-us/kb/4011734

SharePoint Server 2016 / Project Server 2016 March 2018 update: 
https://support.microsoft.com/en-us/kb/4018293 & https://support.microsoft.com/en-us/kb/4011687

The Office 2013 March 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4090988

Project Server 2013 March 2018 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/4018301

Project Server 2013 March 2018 update:
https://support.microsoft.com/en-us/kb/4018305

Project 2013 March 2018 update:
https://support.microsoft.com/en-us/kb/4018292

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the March 2018 CU for 2013.

As always, fully test these updates on a replica test environment before deploying to production.

Next Page »

Blog at WordPress.com.
Entries and comments feeds.