Forum Discussion
Refresh Excel Services Reports
Can somebody point me to the proper way to enable refresh of Excel Services reports (using Odata feeds) in Project Online? I need to be able to refresh the basic reports that came with the installation as well as custom ones that we develop through Power Query and Power View.
I found this article, but it is a few years old and is showing a different app (after doing the lookup), so I didn't want to risk breaking anything.
http://www.alexandervanwynsberghe.be/using-project-online-odata-with-excel-web-app/
Hi Adele,
You can refer to this Microsoft Help article:
Granting reporting access in Project Online: https://support.office.com/en-us/article/Grant-reporting-access-in-Project-Online-4F125CF5-A752-4CE6-B9E9-5A2EB6ACE9E2
After you have enabled the site collection feature "Project Web App Permission for Excel Web App Refresh", your reports should refresh just fine.
You should open each report once in Excel to initiate the data model, then save the file back to the document library.
And an additional note on OData feeds in Excel to ensure they refresh properly. When you modify your Excel OData connection ensure that your ODATA connection string follows the following convention:
Data Source=https://myprojecttenant.sharepoint.com/sites/pwa/_api/ProjectData/[en-us]/Projects;Namespaces to Include=*;Integrated Security=SSPI;Persist Security Info=false;Base Url="https:/myprojecttenant.sharepoint.com/sites/pwa/_api/ProjectData/[en-us]/Projects?$select=ProjectId,ProjectName,ProjectOwnerName,ProjectStartDate,ProjectFinishDate,ProjectActualWork,ProjectRemainingWork,ProjectPercentCompleted,ProjectPercentWorkCompleted,ProjectStatus,ProjectDescription&$filter=ProjectName ne 'Timesheet Administrative Work Items'"
The property "Data source" needs to contain only the endpoint to the service without any query or filter parameter.The property "Base Url" contains your complete query including the fields selected and any filter applied.
Kind regards,
Trutz
Hi Adele,
You can refer to this Microsoft Help article:
Granting reporting access in Project Online: https://support.office.com/en-us/article/Grant-reporting-access-in-Project-Online-4F125CF5-A752-4CE6-B9E9-5A2EB6ACE9E2
After you have enabled the site collection feature "Project Web App Permission for Excel Web App Refresh", your reports should refresh just fine.
You should open each report once in Excel to initiate the data model, then save the file back to the document library.
And an additional note on OData feeds in Excel to ensure they refresh properly. When you modify your Excel OData connection ensure that your ODATA connection string follows the following convention:
Data Source=https://myprojecttenant.sharepoint.com/sites/pwa/_api/ProjectData/[en-us]/Projects;Namespaces to Include=*;Integrated Security=SSPI;Persist Security Info=false;Base Url="https:/myprojecttenant.sharepoint.com/sites/pwa/_api/ProjectData/[en-us]/Projects?$select=ProjectId,ProjectName,ProjectOwnerName,ProjectStartDate,ProjectFinishDate,ProjectActualWork,ProjectRemainingWork,ProjectPercentCompleted,ProjectPercentWorkCompleted,ProjectStatus,ProjectDescription&$filter=ProjectName ne 'Timesheet Administrative Work Items'"
The property "Data source" needs to contain only the endpoint to the service without any query or filter parameter.The property "Base Url" contains your complete query including the fields selected and any filter applied.
Kind regards,
Trutz
Thanks a bunch, Trutz!
Should the description you provided above work for reports developed using Power Query? It looks like that is what Adele had asked, but we are having issues getting the browser refresh to work for anything developed in Power Query. The Power Query data connections seem to have a different convention than the one you had described. Is there anything special you need to do to enable browser refresh for Power Query? Is it possible?
That's right, the solution I highlighted works for the basic reports. If you use Power Query, you can't refresh data in Excel Online. There was a "PowerBI for O365", that allowed scheduled refresh, but I think it has been discontinued. So you could use the PowerBI service instead to provide your reports there.
