Excel Services – Consuming a SharePoint List using the ListData.svc


Here are the steps I performed to Consume a SharePoint List in Excel Services (SharePoint On Premise):

I created a list at this location:

http://sp/_layouts/15/start.aspx#/Lists/TomTestList/AllItems.aspx

 

I then called the listdata.svc from via Excel > Data tab > From Other Sources  > From Odata Feed > http://sp/_vti_bin/listdata.svc

I selected my List.

I then created a simple Pivot Table.

I went to Data > Connections > Properties > Definition tab > Authentication Settings > Use a stored account > (enter a SSS ID you have created).

NOTE: the “Set Credentials” account for this SSS ID needs to have access to the SharePoint List.

 

I changed the List (I added CHEESE).

I then clicked Refresh All Connections. As you can see CHEESE appeared (this wasn’t instantaneous.  It did take a little while, so be patient).

IMPORTANT NOTE:

When you consume a SharePoint List in Excel Services via the ListData.svc, a Data Model is created.

and the Data Model is built on the PowerPivot Server:

If you do not have a PowerPivot Server, the refresh will fail.

Also, if you are using an Office Web Apps 2013 Server, you will need to suppress .xlsx file types from being handled by OWA Server:

New-SPWOPISuppressionSetting -extension xlsx -action view

To reverse this:

Remove-SPWOPISuppressionSetting -extension xlsx -action view

Related Blog:

PowerPivot for SharePoint – Browser Refresh Fails (Data Refresh not supported in Office Web Apps)

http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/01/31/powerpivot-for-sharepoint-browser-refresh-fails-data-refresh-not-supported-in-office-web-apps.aspx  

Comments (7)

  1. Anonymous says:

    Update:
    You can consume a SharePoint Online list in Excel Online and On Premise if you call the ListData

  2. Anonymous says:

    Una vez más, os dejo un nuevo recopilatorio de recursos interesantes sobre SharePoint 2013 y sobre todo

  3. Gabriel says:

    I have the powerpivot addin for excel, and if I refresh from it, the refresh is done succesfully

  4. Someone says:

    Great write-up! Unfortunately it wouldn’t work for list that has calculated fields.

    1. Very true. There are some fields that do not import or are not retrievable depending on the field type or content. Unfortunately this has been the case since the this services inception. Calculated fields are the biggest culprit here.

    2. Scott says:

      It also doesn’t seem to recognize lookup fields that are from another list. I have linked a project list to a document library. I currently export data from the document library into pivot charts. I want to create a dynamic link as described in the article. Unfortunately, I can’t figure out how to do this with the lookup fields.

      1. Some types of columns will not pull properly. We have found that Lookup columns in particular are unique in that sometimes they will work and sometimes they will not. Testing is required to see if your scenario will function as the possibilities for customization are endless and it is really not possible for us to speak to every scenario in this particular situation.

        To your second comment, If you actually export the data rather than create a data connection to it per this article, it is not the same action. This article describes actually creating a data connection to a list like you would create a data connection to a SQL database and then how to subsequently refresh it successfully. This is am important distinction because 2 different technologies are used for these actions. An export will pull the data into a plain Excel sheet, and creating a data connection per this blog will create a PowerPivot model and data connection. While your situation may seem to be the same, it may in-fact be completely different.

        Lastly, there is nothing dynamic about the link here. It is in-fact very static pointing at a specific library to pull all of the data present in the library based on the listdata.svc. You can modify the listdata.svc call if you are familiar with the code behind the web call, but beyond that the base call is a very specific action. Maybe i am misunderstanding your comment here but I want to make sure it is very clear what this blog is explaining and the context.

Skip to main content