– SharePoint on-prem 2013
– Office Online Server
– Excel Office Pro Plus (2016)
– Data source = SharePoint list
One day I wasn’t anymore able to auto refresh my SharePoint embedded charts that pulled data from SP lists.
I had to do some research until I found that it’s a combination of my new Excel version and the Office Online server. Due to changes in the security model, the standard settings didn’t work anymore.
- Enable “From OData Data Feed (Legacy)” in the Excel options on the “Data” tab
- In Excel on the Data tab click on “Get Data – Legacy Wizards – From OData Data Feed (Legacy)
- Connect to your site adding /_vti_bin/listdata.svc to your URL
- on the next page select the list where you want to pull data from
- now on the last wizard page it’s very important, that you use a stored account instead of Windows authentication (create on in “Secure Store Service” – Link)
Now you can go on creating your charts and then embed it in SharePoint with the Excel web part.
If that still doesn’t work, some other things to check:
– check, if the SecureStoreService is set to type “Individual”
– In the members section add the FQDN$ (if on-prem) of the OOS servers
– in OOS configuration make sure -AllowHttpSecureStoreConnection is set to TRUE
– in the c2wtshost.exe.config (under path C:\Program Files\Windows Identity Foundation\v3.5\) make sure, that following line is NOT commented:
<add value=”NT AUTHORITY\Network Service” />
(restart OOS service)
– make sure, all BIServers are registered in OOS
New-OfficeWebAppsExcelBIServer –ServerId YourSQLServer\POWERPIVOT