Sunday, August 22, 2010

Unsupported External List Refresh in Excel Services

Excel Services is a great tool allowing interactive data analysis process by predefined models or pivot tables created in Excel and posted to SharePoint MOSS or 2010.

However, when it comes to external lists it is not possible to refresh the external data source directly from the browser. The visitor needs to do it in Excel which makes the user experience much less attractive for most users.

For example, you can easily export a SharePoint list to Excel as a pivot table. When you refresh the pivot table in the web interface of Excel Services it shows the latest data within the table in Excel even though the list in SharePoint might have been changed since the last synchronization with Excel.

Since Excel Services strips VBA functionality from the Excel model, it is also impossible to run the refresh via a macro button.

One would suspect that this has been designed on purpose to boost the purchase of MS Office latest versions but this limitation is very annoying and doesn't help Microsoft with their attempt to increase the usage of its BI solutions.

As a result, we are planning on developing a solution that will run a background refresh defined in the Scheduler. This will let people get the up-to-date external data based on the time intervals of the auto-refresh process. We haven't decided yet if it would be free though.

7 comments:

  1. Did you ever come up with a solution for this issue?

    ReplyDelete
  2. Unfortunately, there was not too much of an interest in such a tool so we decided to drop this idea.

    ReplyDelete
  3. Can't we reresh it using ECMA scripts?

    ReplyDelete
  4. Check out this:
    http://technet.microsoft.com/en-us/library/gg576960.aspx

    ReplyDelete
  5. Thank you for providing the link to the MS Technet article. However, Power Pivot for Excel is available for 2010 release and the other options require some code modifications.

    ReplyDelete
  6. I have an article that will show you how to use real-time SharePoint list data inside of excel services pivot tables and charts

    https://learn.sharepoint.com/Blog/_layouts/WopiFrame.aspx?sourcedoc=/Blog/Blog%20Post%20Documents/Excel%20Services%20with%20SharePoint%20List%20Data.docx&action=default&Source=https%3A%2F%2Flearn%2Esharepoint%2Ecom%2FBlog%2FBlog%2520Post%2520Documents%2FForms%2FAllItems%2Easpx%3FInitialTabId%3DRibbon%252EDocument%26VisibilityContext%3DWSSTabPersistence&DefaultItemOpen=1

    ReplyDelete
    Replies
    1. Todd, thank you for the tip.

      Is seems that the combination of Excel 2013 and Sharepoint 2013 have a solution to this issue. I haven't tested this though.

      http://blogs.msdn.com/b/analysisservices/archive/2012/12/21/refreshing-powerpivot-data-in-sharepoint-2013.aspx

      Delete

Enter your comment here