Business Intelligence Reporting in Project Online with Power BI and Excel

Introduction to Reporting in Project Online

Microsoft Project (MSP) is one of the most used Project Management tools across Enterprises and is a part of the Microsoft Office offerings. There are multiple MSP offerings, both on the client side as well as on the Server side. On the client side, we have Project Professional and Project Standard. On the Server side, we have Project Server 2013/2016 (On Premises) and Project Online (Cloud Offering). While you have complete access to Project Server Databases in the on-premises version, you only have limited OData feed access in Project Online which limits your options for custom reporting. In this post, I am going to focus on what options you have for Business Intelligence Reporting in Project Online with Power BI and Excel.

Business Intelligence Reporting in Project Online with Power BI and Excel

Option 1: Out of the Box Reporting

Out of the box, you have sample reports that you can use as a reference point for starting with reporting on your projects. To open the sample reports, you need to do the following:

Click Settings > Site Contents >  Reports > Choose Language > Open the language folder, and there you’ll see the sample reports.

PowerBi for Office 365 is discontinued and you will not be able to access it.

You have the following Reports available to get started

• Project Overview Dashboard

  This Report shows data on Projects, AssignmentWork, and AssignmentActualWork by ProjectName. Additionally it shows Count of TaskId by ProjectName 

• Project Overview

  This Report shows Project Name, Percent Complete Count of Assignments and Tasks

• Resource Overview

This is more Resource focused and shows data like Assignment Work and Actual Work and Count of Tasks by Resource.

These reports provide high-level details about all your projects using a combination of the six pre-built OData connections that comes with Project Online. The data can be refreshed, and the latest data is always available.

As you can see from the image above, you can go ahead and create additional reports from the OData connections available like I have in my MSP Online tenant.

Option 2: Analyze your Data in PowerBI

Clicking on this option takes you to the PowerBI online portal where you can connect to Project Online using a Microsoft Project Online Content Pack. The content pack is a set of Pre-Built Reports and Dashboards which gives your a complete end to end view of your current projects.

Once you click on “Connect” you would need to enter your Project Online URL information.

Select oauth2 and Sign In

Click on Sign-In and then Provide your O365 credentials to access your Project Online Data.

Once you have provided your O365 credentials, PowerBI takes some time to import the data into PowerBI.

Once the Import is complete, you can access the Dashboard to view your Project details

You can also use the Quick Insights Option to see more Reports without writing any custom code.

Once the reports are ready, you can view them from the same location.

Option 3: Use Excel 2013/2016 or PowerBI Desktop to create Custom Reports for Project Online

Using Excel can enable you to analyze and explore data views and format them in charts and tables. After creating reports in Excel, you can share interactive reports, scorecards, and dashboards to Power BI, SharePoint, etc. Using tools like Power Query, Power Pivot, Power View, and Power Map you can create powerfully, interactive views and mashups in Excel. You can:

• Increase your reporting capabilities in Excel
• Discover, combine, & refine data (Power Query)
• Create a Data Model and define hierarchies and KPIs (Power Pivot or Power BI Desktop)
• Create interactive views, mashups, and reports (Power View)
• Create three-dimensional, geospatial views (Power Map)

To create a report in Excel, you need to do the following:

1. On the Data tab, in the New Query group, choose From Other Sources > From OData Data Feed.
2. Type the Project Online website address (URL) for your Project Web App instance, followed by: /_api/ProjectData/.For example:https://projectonline.sharepoint.com/sites/pwa, then you would enter https://projectonline.sharepoint.com/sites/pwa/_api/ProjectData/ in the Link or File box.
3. Select Organization Account and Log in using your Microsoft Office 365 credentials.
4. On the Data Connection Wizard dialog box, select the checkbox next to Projects, and then click Next.
5. On the Save Data Connection File and Finish step, click Finish.
6. On the Import Data dialog box, select PivotTable Report and then click OK.

See below for the complete list of OData feeds for Reporting in Project Online

Get specific fields only

https://.SharePoint.com/sites/PWA/_api/ProjectData/Resources?$select=ResourceName,ResourceNTAccount

Get an entity collection by using an association

https://.SharePoint.com/sites/PWA/_api/ProjectData/Projects(guid'263fc8d7-427c-e111-92fc-00155d3ba208')/Assignments

Limiting the data: filter or use select

http://<tenant>.sharepoint.com/sites/pwa/_api/ProjectData/
Projects?$filter=ProjectStartDate gt datetime'2014-01-01T00:00:00'&$orderby=ProjectName&select=ProjectName,
ProjectStartDate,ProjectFinishDate,ProjectCost,EnterpriseProjectTypeName

This method is typically suited for Advanced users who are comfortable with combining data and creating custom reports.

It is strongly advised to you to read the official documentation for more In-Depth Information. Here are the Online References:

• What reporting tools can I use with project data?
• What reporting tools can I use with project data?
• Use Excel 2013 to create a new Project Online report