This blog post was originally written in May 2016 – The post was copied from my old blog (Which is no longer active) site to here
Have you ever tried to use a SharePoint list to populate a Power Pivot Graph in excel; and then render the graph in SharePoint using Excel Web Apps? – Sounds pretty cool eh!?
Not Quite.
I created a SharePoint list, for e.g., Profit Loss, which details a financial summary of a project, e.g. (Profit is a calculated column)
Having created the list, I opened up Excel and created a OData Data Feed connection (below) and generated a graph based on the data within the list.
Graph below
Seems relative.
However, if you make a change to the SharePoint list, the excel web access web part in SharePoint does not dynamically update to show an updated chart to reflect the change made on the list. You must manually open the excel sheet, refresh data connections and then save the excel sheet again to update the excel web access web part.
If you manually refresh the Excel web part by clicking Data, Refresh Selected Data Connection, the web part loads the new updated data;
Screenshot below showing new data
Screenshot below showing a manual refresh
Result after doing a Manual Refresh (notice the profit)
If the entire page is refreshed, the webpart reverts back to the original, see image below.
I noticed that if you have the excel sheet open in real time and make a change to the SharePoint list – the Excel will in fact update – but it does not save the sheet. (You can check the modified date on the library).
Having done further tests – I also noticed the Pivot Chart had a ‘Refresh data when opening the file’ – I checked this and saved the excel file to my SharePoint document library.
The Excel Web Access web part now showed a Warning when the page was loaded, as below
The user must click Yes to load the worksheet – which as a result refreshes the latest data. Not, really a solution though.
For On-Premise users – we can resolve the warning message by adding the Excel file as a trusted location in the Excel Services Application; however, the Excel Service Service Application is not available in SharePoint on-line
I guess the only viable solution is to for a user to manually open the Excel sheet(s) and do a refresh all and then save the worksheet back into SharePoint; thus, updating all the Power Pivot Charts.
I will let you know if there are any updates on when or if the Excel Service Application is available so we could add worksheets into the trusted locations.
There is a good blog article here which could be of help should you run into a similar issue:
Leave a Reply