SharePoint (and Project Server) Shenanigans

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)

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

http://bit.ly/2j5SRgO

Project 2016 January 2017 update:
http://bit.ly/2ii4STb

SharePoint Server 2016 / Project Server 2016 January 201 update: 
http://bit.ly/2j5Ni1X & http://bit.ly/2ii4AeY

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

http://bit.ly/2j5SRgO

Project Server 2013 January 2017 CU Server Roll up package:
http://bit.ly/2j5ORgo

Project Server 2013 January 2017 update:
http://bit.ly/2ii0YK7

Project 2013 January 2017 update:
http://bit.ly/2j5FFZb

Also worth noting, if you haven’t done so already, install Service Pack 1 http://bit.ly/1uorn2C first if installing the January 2017 CU.

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

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)

Over the holiday period I started to build a Power BI Report Pack for Microsoft’s Office 365 PPM tool Project Online. I have made this publically available to download using the link below:

http://bit.ly/2hLQfCw

This Report Pack currently consists of 5 reports but will be extended in the near future with additional reports and / or changes to existing reports. In this release there are 5 reports, these can be seen below:

Portfolio Report:

Issues Report:

Risks Report:

Project Report:

Resource Demand Report:

These reports only use default intrinsic fields so it should work for all Project Online deployments.

Once downloaded, the report pack data sources will need to be updated to point to your target Project Online PWA instance. To do this you will need the Power BI desktop tool which is a free download here: http://bit.ly/2ivj0HN

Open the downloaded PWMatherProjectOnlinePowerBIReportPack.pbix file in Power BI Desktop and follow the steps below to point the data sources to your Project Online PWA instance:

• Home > Edit Queries > Edit Queries
• The Query Editor will then launch:
•  
• Select the PWAURL query from the Query pane on the left hand side, then click Advanced Editor on the Home tab.
• Update the Source URL from http://bit.ly/2hLX1YJ to your target PWA URL and click Done – ensure the URL is correct.
• Repeat this for IssueData, RiskData and TaskBaselineTimephasedData. These connections are slightly different to the PWAURL query, ensure you just replace the PWA URL in the source and leave the /api/ProjectData part as is then click Done. The remaining queries will not require updating.
• On the Home tab, click Close & Apply > Close & Apply.
• You will then be prompt with the authentication options. Select Organizational account then click Sign In.
• Enter credentials for an account that has access to the OData Reporting API on the target PWA instance
• Then click Connect
• A Privacy levels window may appear, select Organisational then click Save.
• The Apply Query Changes window will appear as the data is refreshed. At this point only the three connections (Issues, Risks and TaskBaselineTimephased) will update. Now click the Refresh button on the Home tab to update all connections / queries. The Refresh window will appear while the data is refreshed.
• Once the refresh window disappears the reports will update to show the data from the target PWA instance. Click the Project Report tab and change the project filter from Project 1 to a project from your PWA instance. Save the file.

This file can either be emailed around to colleagues with details on how to update the credentials to their own or what would be better is to publish the report to your Power BI workspace can create an organisational content pack that others can add to their Power BI workspace. If the Power BI organisational content pack is the chosen option, you might want to create a Dashboard first.

In a follow up blog post to this one, I will touch on creating a dashboard once the report is published to my Power BI workspace then create an organisation content pack.

Enjoy, I hope you like it

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)

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

http://bit.ly/2gPqJyY

Project 2016 December 2016 update:
http://bit.ly/2hSxbmE

SharePoint Server 2016 / Project Server 2016 December 2016 update: 
http://bit.ly/2gPtnoi & http://bit.ly/2hStk8R

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

http://bit.ly/2gPqJyY

Project Server 2013 December 2016 CU Server Roll up package:
http://bit.ly/2gPuAvR

Project Server 2013 December 2016 update:
http://bit.ly/2hSr1Tv

Project 2013 December 2016 update:
http://bit.ly/2gPwk8D

Also worth noting, if you haven’t done so already, install Service Pack 1 http://bit.ly/1uorn2C first if installing the December 2016 CU.

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

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)

Further to my last post on an example method to security trim the Project data in a Power BI report for Microsoft’s Project Online PPM tool, another option is to merge the datasets / tables. If you didn’t see the first post, a link can be found below:

http://bit.ly/2hcHKnT

For this post, I have the same two ODATA / REST queries used, the ODATA one for the Milestones (/_api/ProjectData/Tasks()?$Filter=TaskIsMilestone eq true) which is not security trimmed and the REST one for the Projects (/_api/ProjectServer/Projects) which is security trimmed. With both of these datasets loaded into the Power BI Query Editor, select the Milestone dataset and click Merge from the Home ribbon tab and set the following:

This is merging my two tables based on ProjectId from the Milestone dataset and Id from the Projects dataset using a Left Outer join. You can see from that screen shot that I don’t actually have access to the Agile Product Development project in PWA as it is not returned in the list of projects in the REST dataset. Clicking OK will show a NewColumn:

Click expand and select Name:

That returns nulls for the project names you do not have access to:

These rows should be filtered out, click the Name column heading dropdown and de-select null:

Click OK and the Milestone dataset table will match the list of projects you have access to in PWA – the rows with nulls in the Name column will be removed from the table. I then renamed the Milestones – ODATA dataset to Filtered Milestones:

Click Close & Apply then the load a couple of visualisations on to the report and you can see that now the data is security trimmed as part of the query and not in the report:

In this simple example there is no need for the report level filter to remove the nulls at the report level like there was in the previous post. The same applies as with the previous post, if your users know Power BI  / Power Query they can easily undo all of this and access all the data but this might be an option to explore.

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)

The reporting API for Microsoft’s PPM solution – Project Online doesn’t filter the data based on the access model in PWA. For some organisations this can be problematic. In this blog post I show a simple report example to filter only projects and project milestones that I have access to in PWA.

In this example I have a very simple Power BI Report that is only using ODATA (_api/ProjectData), the Projects resource path to populate the Projects slicer / filter and the Tasks resource path to populate the Milestones for the selected Project:

I have access to a long list of projects (see the scroll bar on the slicer). Using the Audit Tracking project as an example, I can currently see this project in PWA as shown below:

I will change the access model so I get access denied to this project:

I only see these 6 projects projects in an unfiltered Project Center view:

Yet if I refresh my Power BI report I still see the full list as expected (the ODATA reporting API is not data security trimmed, you either access all data or none). One option is to use the REST (_api/ProjectServer) to filter the datasets that use the ODATA API as the source. The REST API is security trimmed to only show the data you have access to based on the access model in PWA. For example, I will get the list of Projects from REST to populate the Projects slicer / filter in Power BI, this only shows the projects I have access to in PWA. I have added a new data source to my simple Power BI report for the REST API:

The join is then set up on Id from the Projects REST dataset to the ProjectId in the Milestones OData dataset:

I now switch my slicer / filter to use the Projects – REST dataset

As you can see the list of projects matches what I see in the Project Center but there is a also a blank option there. The blank value is for rows it can’t join with on the Milestone ODATA dataset, these are the projects I no longer have permission to access in PWA! To resolve this, select the page and add a report level filter like below where Name is not blank:

This report filter will not show any blank values for the Name field on any visualisation in the report:

The blank value is now removed. My data is now security trimmed to match what I have access to in PWA! Obviously if you know what you are doing you can just undo all of this and see all data but for some it might be a workable solution! You can do the same with Resources too.

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)

This post covers a new resource path in the OData endpoint for Microsoft’s PPM tool Project Online to give you the data you need for true resource demand for project assignments and resource engagement assignments. Before resource engagements were introduced, you could have assignments in the project plans or in the resource plans. For reporting, the data was available in the Assignments and the AssignmentTimephasedDataSet resource path feeds, normal project assignments data would be in the AssignmentWork field and resource plan work was in the AssignmentResourcePlanWork field. There was also an AssignmentCombinedWork in the AssignmentTimephasedDataSet feed that displayed the correct resource demand for the resource when you used both resource plans and project plans. The AssignmentCombinedWork field would take into account for the project resource utilisation calculation options. With the introduction of resource engagements there wasn’t a single field that gave you this same resource demand data – until now!

If you regularly view the Project OData service root URL (_api/ProjectData) you might have noticed a new feed, ResourceDemandTimephasedDataSet. This feed has the following properties:

The ResourcePlanUtilizationType type will display either a 0, 1 or 2. This is the property to indicate the project resource utilisation calculation options for the project. The enumeration for these values is below:

• o = Project Plan
• 1 = Resource Engagements
• 2 = Project Plan Until

The ResourcePlanUtilizationDate property will only contain a date when the “Project Plan Until” resource utilisation calculation option is set.

This new ResourceDemandTimephasedDataSet feed will show the true resource demand for the project resource utilisation calculation option when using both project assignments and resource engagements. Some scenarios below show when data would and wouldn’t be included in this new feed:

Scenario 1: Project plan has resource assignments and resource engagements. The “Calculate Resource Utilization from:” option is set to Project Plan. In this scenario only the project assignments will be included in the ResourceDemandTimephasedDataSet feed.

Scenario 2: Project plan has resource assignments and resource engagements. The “Calculate Resource Utilization from:” option is set to Resource Engagements. In this scenario only the resource engagement assignments will be included in the ResourceDemandTimephasedDataSet feed.

Scenario 3: Project plan has resource assignments and resource engagements. The project assignments start from the 20th November to the 10th December 2016. The Resource engagements start from the 25th November to the 20th December 2016. The “Calculate Resource Utilization from:” option is set to Project Plan until with a date set to 1st December 2016. In this scenario only the project assignments will be included from the 20th November 2016 up to and including the 1st December 2016 and only resource engagement assignments from the 2nd December to 20th December 2016  will be included in the ResourceDemandTimephasedDataSet feed.

Example data can be seen below:

That makes things far easier for reporting resource demand, without this new feed you had to use the AssignmentTimephasedDataSet feed and the EngagementsTimephasedDataSet feed and join the datasets.

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)

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

http://bit.ly/2fCbbev

Project 2016 November 2016 update:
http://bit.ly/2eJY8tK

SharePoint Server 2016 / Project Server 2016 November 2016 update:
http://bit.ly/2fCavG2 & http://bit.ly/2eJZpkt & http://bit.ly/2fCftm0

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

http://bit.ly/2fCbbev

Project Server 2013 November 2016 CU Server Roll up package:
http://bit.ly/2eK2oJI

Project Server 2013 November 2016 update:
http://bit.ly/2fCdoXq

Project 2013 November 2016 update:
http://bit.ly/2eK7FBl

Also worth noting, if you haven’t done so already, install Service Pack 1 http://bit.ly/1uorn2C first if installing the November 2016 CU.

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

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)

This blog post will demonstrate a simple example of how Microsoft’s PPM tool, Project Online, can include / sync metadata from Azure AD user attributes to resource custom fields in Project Online. This example script will update the resource custom field “Job Title” on my test tenant with the data from the Azure AD user “Title” attribute.

This script example can be downloaded here: http://bit.ly/2exqPXw

The script does require some additional modules / DLLs to work. Firstly you will need the Azure AD module installed, this can be downloaded here: http://bit.ly/2fV43Ou. You will also need the SharePoint Online and Project Online CSOM DLLs. The DLL’s used are from the NuGet package here: http://bit.ly/2eMprBu. There are later version available, check here: http://bit.ly/1SO8x4X. Download those as required. These modules can be seen here on line 3,5 and 14 in the example script, the location will need to be updated to reference the correct location for your SP / PS Online CSOM DLLs.

The user setting up the script will need to update the correct location to the DLLs required, the AD username, AD password, CSV file location, PWA instance URL, username, password and the custom field internal name. The PWA account specified will need edit access to all of the resources in PWA and the AD account will need access to read all the users in Azure AD. Ensure the variables have been updated correctly, placeholder values seen below:

In this example all users in the Azure AD will be exported, clearly if you have a large organisation it would be efficient the filter for only those users that exist in Project Online. The code matches users and resources based on the AD display name and PWA resource name when updating the resources in PWA.

To get the correct custom field internal ID I use the REST API, <PWA Site URL> + /_api/ProjectServer/CustomFields. Find the resource level custom field and copy the InternalName as seen below:

Once the PowerShell script has been updated, save it and it can then be tested on a test / non-production environment. Once executed in PowerShell, each resource found in the CSV file will be outputted as seen in the example below. In this example there are many AD users in the CSV export that do not exist in the PWA instance.

Checking in the Resource Center after the script has run you can see for the resources that exist in the CSV file created, the Job Title has been updated with the values found in Azure AD / the export CSV file:

The CSV file generated can be seen here:

This PowerShell script could be run on a schedule from a server on-prem or even in Azure / webjob. The script would just need access to save / access a CSV file and the DLLs / modules required. Fully test this script on a test / non-production PWA instance before running on a Production PWA instance.

The script is provided “As is” with no warranties etc.

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)

This blog post will demonstrate a simple example of how Microsoft’s PPM tool, Project Online, can include data from an external system. This is a very simple example using a demo CSV mock up of data that could be from an external system. The CSV part could easily be replaced by a SQL query or a REST API query to the source system etc.

Example CSV data used can be seen below:

As you can see, my imagination for example project names has not improved! The code uses the Project Name as the key between the two data sets – the CSV file and PWA so the names will need to match.

This script example can be downloaded here: http://bit.ly/2edq0Ii

The user setting up the script will need to update the correct location to the DLLs required, the PWA instance URL, username, password, the custom field internal name and the location of the CSV file. The account specified will need edit access to all of the projects in PWA. Ensure the variables have been updated correctly, placeholder values seen below:

The DLL’s used are from the NuGet package here: http://bit.ly/2eMprBu. There are later version available, check here: http://bit.ly/1SO8x4X. Download those as required.

The get the correct custom field internal ID I use the REST API, <PWA Site URL> + /_api/ProjectServer/CustomFields. Find the project level custom field and copy the InternalName as seen below:

Once the PowerShell script has been updated, save it and it can then be tested on a test / non-production environment. Once executed in PowerShell, each project found in the CSV file will be outputted as seen in the example below. In this example two projects exist in the CSV data but not in my example Project Online PWA instance (remember for this example, the names need to match in the CSV file and PWA):

Checking in the Project Center after the script has run you can see for the projects that exist in the CSV file, the project budget includes the values found in the CSV file:

This PowerShell script could be run on a schedule from a server on-prem or even in Azure. The script would just need access to the data to import (CSV file etc.) and the DLLs. Fully test this script on a test / non-production PWA instance before running on a Production PWA instance.

The script is provided "As is" with no warranties etc.