Project online has some great reporting features. One of the features that I really like is the OData service. This OData service is not only available for Project online, but also for on-premise deployments. To access this feed, all you have to do is browse to the following url: “/pwa/_api/ProjectData”.
One of the tools to “explore” this Odata feed is Excel. You can open one of the existing Project Online reports with Excel. Go to “Reports – English – Project Overview Dashboard” and click on the 3 dots, select “Edit“.
The report will open in Excel, and you can now select the ‘DATA‘ tab, and press ‘Refresh All‘. This should load all your data and show you a list of all projects with some additional information. This is pretty cool, but can we also use this within the Excel Web App? Well, let’s try it straight. Go back to the “Reports” folder, and instead of clicking on the 3 dots, just click on the name of the file. Excel Web App should open, and you will see an empty list of projects. Now click on the Data tab and select “Refresh All Connections“.
So, that’s not what we were expecting. It looks like Excel Web App cannot access the Odata feed. The explanation is quite simple:
When Excel workbooks are refreshed in Office 365, the BI Azure Service retrieves updated data from Project Online and recalculates the internal workbook model. If the workbook has data connections pointing to Project Online OData feeds, the BI Azure Service must have permission to the SharePoint Online tenant to retrieve that data.
What we have to do is giving the BI Azure service the required permissions to access our Project Online tenant. Browse to your PWA instance, and add “/_layouts/15/appinv.aspx” to the URL. For example “http://yourtenant.sharepoint.com/sites/pwa/_layouts/15/appinv.aspx“. You will now see the application permission screen. In the App ID field, add 00000009-0000-0000-c000-000000000000 and click Lookup. The title and App domain should tell you we’re working with the Azure AnalyisisServices. Copy and paste the following in the Permission Request XML Field
<AppPermissionRequest Scope = "http://sharepoint/projectserver/reporting" Right="Read">
<AppPermissionRequest Scope = "http://sharepoint/content/tenant" Right="FullControl">
Click create and Trust the App.
To be sure that the App has been granted with the correct permissions, go to “Admin (on top) – SharePoint – Apps – Permissions“. There you should see the App “Microsoft.Azure.AnalysisServices“
That’s it. Now we can open the report using Excel Web App, and click on “Data – Refresh All Connections“. Have fun!
Note: Be sure that you first refresh and save your report using the Excel (desktop) application. This will update the internal model of the workbook so it is supported by Excel Web App.