#ProjectOnline #PPM #PowerBI Report Pack #BI #Reporting #PowerQuery #DAX #Office365
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.
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:
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:
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