Business Intelligence Reporting in Project Online with Power BI and Excel

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.

Business Intelligence Reporting in Project Online with Power BI and Excel

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 ProjectsAssignmentWork, 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.

Business Intelligence Reporting in Project Online with Power BI and Excel

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

Business Intelligence Reporting in Project Online with Power BI and Excel

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.

Business Intelligence Reporting in Project Online with Power BI and Excel

Once you click on “Connect” you would need to enter your Project Online URL information.Business Intelligence Reporting in Project Online with Power BI and Excel

Select oauth2 and Sign In

Business Intelligence Reporting in Project Online with Power BI and Excel

Click on Sign-In and then Provide your O365 credentials to access your Project Online Data.Business Intelligence Reporting in Project Online with Power BI and Excel

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

Business Intelligence Reporting in Project Online with Power BI and Excel

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

Business Intelligence Reporting in Project Online with Power BI and Excel

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

Business Intelligence Reporting in Project Online with Power BI and Excel

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

Business Intelligence Reporting in Project Online with Power BI and Excel

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.Business Intelligence Reporting in Project Online with Power BI and Excel

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

Business Intelligence Reporting in Project Online with Power BI and Excel

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:

Related Posts

What is Microsoft Project Online What is Microsoft Project Online Microsoft Project Online is a Cloud based Software as a Service (SaaS) offering based on the popular Microsoft Proje...

FOLLOW @ INSTAGRAM