Excel Web Access and OData Connections: Data Refresh


Warning: preg_match(): Compilation failed: unrecognized character after (?< at offset 10 in /web1/user50603/website/wp-includes/class-wp-block-parser.php on line 418

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 the a financial summary of a project, e.g.  (Profit is a calculated column)

ProfitLoss

Having created the list, I opened up Excel and created a OOData Data Feed connection (below) and generated a graph based on the data within the list.

OData

Graph below

ProfitLossChart

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 DataConnection, the web part loads  the new updated data;

Screenshot below showing new data

newProfit

Screenshot below showing a manual refresh

ManualUpdate

Result after doing a Manual Refresh (notice the profit)

NewChart

If the entire page is refreshed, the webpart reverts back to the original, see image below.

Normal Chart

 

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

The Excel Web Access web part now showed an Warning when the page was loaded, as below

Warning

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:

 

2 thoughts on “Excel Web Access and OData Connections: Data Refresh”

Leave a Reply

Your email address will not be published. Required fields are marked *