Make sure – before you read any more of this post – you have ADO.Net Data Service Update for .NET Framework 3.5 SP1 installed in your environment. Linkage: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=2343
Now with that out of the way, let me say again, the SharePoint REST API and PowerPivot will change your life.
Slap this into the URL Address bar:
From there you’ll be presented with a list of all the lists and libraries that are compatible with the REST API. Grab the list you’re interested in and tack it on to the end of the above URL like so (case sensitive so be as exact as possible):
You should then be presented with an ATOM feed (looks something like an RSS feed). You may or may not see much, which is fine. The background XML has all the metadata of the list/library you’ll need.
Next, fire up Excel with the PowerPivot add-in installed. Open the PowerPivot window and select “From Data Feeds.”
Paste in the URL above (e.g. http://site.net/sites/sitename/_vti_bin/listdata.svc/%5BList Name]) and then click Next. It’ll chew on that for a second. Then click Finish. The list will now be imported into memory (i.e. PowerPivot does its thing).
Keep in mind here that the REST API only imports 1000 items in at a time. However, for a lot of lists and use cases this is more than sufficient. [See update below.]
Close the PowerPivot window. Go back to Excel and click Insert > Pivot Table/Chart. Then click the “external data source” radio button and then click “Choose Connection”
Find the PowerPivot Data connection you just created above and click Open. Then click OK.
From here you can add all the Values, Slicers, Filters, etc. you want.
Now how will this change your life? The applications of this are endless. One example could be a project list sliced and diced to your users’ liking and displayed via Excel Services web part. Users get the familiar Excel look and feel while you can rest comfortably knowing that they’re not manipulating your list data. You get far more control over the look and feel too as opposed to PerformancePoint. In addition, you get the ability to integrate outside data as well (try integrating TFS or Oracle data in PerformancePoint).
But why not just use SSRS? Good question. Not everyone has access to SSRS. That takes a considerable amount of time, energy, resources, and cash to get that going. But again, with the ability to use slicers and allow your users to see what they want, when they want, that alone makes this a pretty powerful feature in your bag of tricks.
Anyone else tried this? Any other use cases come to mind?
So only 1,000 items will show up in the ATOM view; however, PowerPivot can pull ALL the items down. VERY COOL! I tested it on a list with +32K items and PowerPivot had no problem.