Since Excel Services inception (SharePoint 2007) users have been attempting to consume a SharePoint List in Excel Services. Unfortunately this is not supported. I will explain why and I will also provide a workaround using PowerPivot.
If you are unsure whether or not you are using a SharePoint list as a data source, you can verify this by opening the workbook in Excel > Data > Connections > Properties > Definition tab. If the connection contains Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="":ApplicationName=Excel;Version 220.127.116.11, you are trying to consume a SharePoint List (also see screenshot).
This connection is created by (the below example is using SharePoint 2013) List > Export to Excel:
Below is Microsoft's documentation regarding Unsupported Features in Excel Services.
Unsupported Features in Excel Services
When you have PowerPivot for SharePoint installed you get the option to “Export as Data Feed” from your SharePoint lists.
When using PowerPivot, there are 2 connections. The first one is the Excel connection and the second is the PowerPivot Connection.
Much like the previous screenshot, you can view the Excel connection by opening the workbook in Excel > Data > Connections > Properties > Definition tab
You can view the PowerPivot connection via Excel > PowerPivot tab > Manage
Choose Existing Connections
Click Edit > Advanced > and you will be able to see List.atomsvc in Connection String.
Once the data is in the PowerPivot Add-in then you can create a Pivottable off that set of data.
Once your Pivottable is finished you now can publish this workbook back to SharePoint in the PowerPivot Gallery.
Once the workbook is in the Gallery you can click on Manage data refresh to setup a daily refresh schedule.
To test the connection go to the manage data refresh page and choose "Enable" & "Also refresh as soon as possible".
Important Note 1: I have seen cases where the refresh runs and eventually times out. If you see this, you may need to grant the account running SQL Server Analysis Services (POWERPIVOT) Read permissions to the SharePoint list you are attempting to refresh.
After this runs, you will see fresh data.
Important Note 2: The Scheduled Data Refresh is designed to run once a day during after business hours when server is experiencing the least amount of stress. Therefore you will only see refreshed data once a day (versus real-time like when using Excel Services directly).
Using SharePoint lists as data sources with Excel Services (SharePoint Server 2010)http://technet.microsoft.com/en-us/library/gg576960.aspx
Using a SharePoint list as a data sourcehttp://powerpivotgeek.com/2010/10/28/using-a-sharepoint-list-as-a-data-source/
Using SharePoint List Data in PowerPivot Whitepaperhttp://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SharePointListDataInPP.docx
I have a blog post that shows how to use a managed UDF to accomplish this
If you can write a .UDF, more power to you! Keep in mind that when you write the .UDF it will be for a static range. So if you increase the rows & columns, the .UDF will not pull these in. You should just use PowerPivot.
Does this work with interactive data refreshes? We have reporting services able to interactively refresh data from a SharePoint list, but cannot get the SSAS piece to work. Every time the PowerPivot SSAS service tries to hit mysite/_vti_bin/ListData.svc/MyList, the SharePoint web front end returns a 401 Unauthorized. Scheduled refreshes using the unattended or specifying a user account work fine. Using the current user credentials fails, which wouldn't be so bad if it wasn't the default.
Additionally, within the ULS logs, I can see the SSAS server attempting to hit the site with a blank identity:
10/30/2013 09:42:54.57 w3wp.exe (0x0F58) 0x0DEC SharePoint Foundation Logging Correlation Data xmnv Medium Name=Request (GET:http://mysite/_vti_bin/ListData.svc/MyList)
10/30/2013 09:42:54.57 w3wp.exe (0x0F58) 0x0DEC SharePoint Foundation Authentication Authorization agb9s Medium Non-OAuth request. IsAuthenticated=False, UserIdentityName=, ClaimsCount=0 bec6519c-d58e-90fc-1f52-723562c6276e
I'm not sure if the issue lies at kerberos delegation/impersonation, or back to PowerPivot not liking claims authentication.
You seem to be the only expert on this issue I can find! We even had a local PFE come in and still couldn't resolve the issue. So thanks for any insight you can provide!!
Thanks for the comment. I did see the issue you are describing with interactive refreshes, and it appears that there is indeed an issue with interactive data refreshes using Windows Auth on the data connection in the workbook. Please attempt to change the connection in the workbook to use NONE or a Secure Store ID. Your interactive refresh should work fine at that point.
Can you elaborate as to how your SharePoint data sources show up as external data sources in the managed refresh? I am unable to replicate that in my environment and don't see the ATOMsvc's under this list (technet.microsoft.com/.../jj819452.aspx)
I am facing this issue in SHarePoint 2007 using Excel 2010, can you please help me in this scenerio.Thanks in advance,
Before you get to far on this approach, remember that PowerPivot requires SQL enterprise at about 25K per processor. Not a cost effective solution, in my opinion.
does this work for lookup columns, it doesn't seem to bring them into excel when you export the list as a data feed...