Skip to main content

Project Online and Excel Web App: Cloud data improves reporting

Mike McLean is a Senior Program Manager on the Microsoft Project team.

Along with the general availability of Project Online in Office 365, new scenarios have been enabled for Project Online reporting.

We introduced the Project Online OData feed to help you access your data using Excel 2013. During Public Preview, you may have noticed that any attempt to refresh the Excel workbooks in Office 365 generated errors. Now you can refresh Excel workbooks in Excel Web App that consume Project Online OData feeds.

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.

These instructions document how to grant the BI Azure Service access to your SharePoint Online tenant.

  1. Log on to Project Online.
  2. Add the following to the end of your Project Web App URL: /_layouts/15/appinv.aspx

    For example, if your Project Web App URL is http://contoso.sharepoint.com/sites/pwa, type this URL in the address bar of your browser: http://contoso.sharepoint.com/sites/pwa/_layouts/15/appinv.aspx

  3. In the App ID field, copy and paste 00000009-0000-0000-c000-000000000000, and then click Lookup. (Note: In the string of digits in Step 3, there are 7 zeroes before the 9, and the last set of zeros is 12 zeroes in a row.)

  4. 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>

     

  5. Click Create.

  6. On the last page, click Trust It to trust Microsoft.Azure.AnalysisServices. (Note: This step will grant the BI Azure service access to retrieve data from the SharePoint Online tenant. When performing the refresh in Excel Web App, 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 Web App.

If you’d like to verify the BI Azure Service has been granted permission to your SharePoint tenant, navigate to Tenant Admin and take a look at the app permissions.

  1. Click Admin, then click SharePoint.

  2. In the Tenant Admin, click apps.

  3. Click App Permissions.

  4. Verify that Microsoft.Azure.AnalysisServices is in the list.

Now you can open your Excel workbooks and refresh them in Excel Web App.

  1. Select the workbook in a SharePoint Document Library. It will display in Excel Web App.

  2. Click Data > Refresh All Connections.

  3. The data in the workbook will refresh.

There are three sample OData reports available for Project Online: Project Overview, Resource Overview, and Project Overview Dashboard. For these to refresh in Excel Web App, you must first perform one refresh in Excel 2013 client and save the report. This will update the internal model of the workbook so it is supported by Excel Web App.

The refresh described in this blog post is in Preview mode as we’re gathering feedback on use cases for Project Online customers. Feel free to try it out and refresh your Project Online workbooks.

For more information, you can check out our official online help; see Grant reporting access in Project Online!

–Mike McLean, Senior Program Manager

 

You may also like these articles

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…

Image for: Surface Pro X and Surface Arc Mouse inside on desk with Excel and search on screen.

Connect to your own data with more new data types in Excel

The evolution of Excel Excel is the ultimate decision-making tool. We’re amazed every day by the ways in which you, our customers, use Excel to make better decisions, leveraging the flexibility of the 2D grid and formulas to capture, analyze and collaborate on data. Up to this point, Excel has only had a couple base…