My environment:
– SharePoint on-prem 2013
– Office Online Server
– Excel Office Pro Plus (2016)
– Data source = SharePoint list
Problem Description:
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.
Solution:
- 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.
Have fun!
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
(1 votes, average: 5.00 out of 5)
Loading...