How to Refresh Excel Online Reporting OData Feeds in Project Online

If you’re using Project Online, you might know about the different tools available (listed below) to you for generating various reports.

• Excel Client – You can use oData feeds in Excel to create and share interactive reports, scorecards, and dashboards using Excel 2013/2016.
• Excel Online – This enables you to view, interact with workbooks using the oData feeds in a browser window.
• Advanced Excel BI – Power Query, Power Pivot, Power View, and Power Map enable you to find and bring together data from a variety of oData feeds and create powerful, interactive views and mashups in Excel.
• Power BI for Office 365 – Available by subscription as an add-on or standalone service in the cloud and you can setup scheduled refresh for workbooks.
• SQL Reporting Services – You can use oData feeds in SQL Integration Services/Custom Tool to create On Premises Project Online database in Azure server and then can develop reports based on the Azure database.

 

Excel Online is a powerful tool to view Project Online excel reports in a browser, but it does not have the capability to refresh the report from the browser itself. To refresh the data, you need to download the excel report, refresh the data in your Excel client and then upload again.

In this article, I am going to show how to configure Project Online to refresh Excel Online reports directly from your browser. Excel Online uses the BI Azure Service to refresh the Project Online OData connections. For this to work, you must first grant permission for the BI Azure Service to access Project Online using the steps listed below.

• Add the following to the end of your Project Online URL: /_layouts/15/appinv.aspx
• For example, if your Project Online URL is http://test.sharepoint.com/sites/pwa,type this URL in the address bar of your browser   http://test.sharepoint.com/sites/pwa/_layouts/15/appinv.aspx
• In the App ID field, copy and paste 00000009-0000-0000-c000-000000000000, and then click Lookup. In the Permission Request XML field, copy and paste the following XML:

<AppPermissionRequests>

<AppPermissionRequest Scope=”http://sharepoint/projectserver/reporting” Right=”Read”>

</AppPermissionRequest>

<AppPermissionRequest Scope=”http://sharepoint/content/tenant” Right=”FullControl”>

</AppPermissionRequest>

</AppPermissionRequests>

• Click Create.
• On the last page, click Trust Itto trust Microsoft.Azure.AnalysisServices. This step will grant the BI Azure service access to retrieve data from the SharePoint Online tenant. When performing the refresh in Excel Online, the BI Azure Service will use the Project Online OData connections located in the Excel workbook. BI Azure will attempt to retrieve data from Project Online using the credentials of the user who is performing the refresh on the workbook.
• The next page that displays is PWA Site Settings. You can navigate back to PWA by clicking Project.
• Just to confirm that your BI Azure Service has access to your SharePoint tenant, navigate to Tenant Admin and look at the app permissions using the steps given below. If you don’t have access to Tenant Admin, reach out to your Office365 Tenant Admin and ask them to use the below steps.
  • Click Admin, then click SharePoint.
  • In the Tenant Admin, click apps.
  • Click App Permissions.
  • Verify that Azure.AnalysisServicesis in the list.

• Now develop a Project Online oData based report using Excel 2013/2016 or you can download a sample Excel 2016 based Portfolio Dashboard report from here, you need to make sure to point all the oData connections to your Project Online URL based on below steps.
  • Open the Excel report in Excel 2016.
  • Select the Data tab and click on Connections.
  • Select oData connection, select Properties and then click on Definition tab.
  • Update the Connection string value to reflect your Project Online URL, there will be two keys in the Connection string value that you need to update i.e. one is in Data Source and another one if Base Url.

 

• Now upload the Excel report to one of the Document Libraries in a Project Online site.
• Open the Excel workbook in browser and it will get displayed in Excel Online.
• Click Data > Refresh All Connections.

• The data in the workbook will refresh and you can see the latest data.
• You can share this report with other Project Online users to view or edit in Excel Online using Share > Share with People option.

 

Looking for a more robust self-service business intelligence solution that does not require any background in development, statistical analysis or data mining?  PPMVUE for Microsoft Project Server and Project Online allows users to build dashboards and reports in minutes, simplifying your project and portfolio management tracking and reporting.

Learn more about PPMVUE by contacting EPMA today.

For more details & kick start your project management journey, sign up for our Microsoft Training Classes or send us an email.

I hope you find this blog post helpful.  For more tips and tricks on Project Management, please visit www.epmainc.com