Update to REST API

Still playing around with the REST API. You can read the official Microsoft documentation HERE.

You can add filters to your URL string to refine what items you see. Try this with a Choice column:

http://%5Byoursite%5D/_vti_bin/listdata.svc/%5BList title]?$filter=[Choice column title]/Value eq ‘[Value you want to filter on]’

Be sure to remove the square brackets and everything in between. Also, be sure to pick a choice column that does not allow multiple selections. I’m still playing around and learning more every time I try something, but I figured I would share this little nugget.

SharePoint’s REST API and PowerPivot will change your life

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):

http://site.net/sites/sitename/_vti_bin/listdata.svc/%5BList Name]

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). Winking smile

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?

UPDATE 4/20/2012

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.

SharePoint Joel beat me to it!: SharePoint on a Mac

While I’ve been busy working on my Mac all week, SharePoint Joel has posted two really good posts that pertain to SharePoint on a Mac; namely ActiveX. You can see his browser support post HERE and his latest post about ActiveX HERE.

Overall there are some subtle, and not so subtle differences when viewing SharePoint on a Mac. From a user perspective the experience is the same, for the most part.

Let’s start with the optimal configuration:

  • Office for Mac 2011
  • Safari (with username and password stored in Keychain) – I outline how to accomplish this HERE

Quite often Admins will tell you that FireFox is the ideal browser on Macs, and while I tend to agree, you’ll get an authentication prompt upon your first open of the site, something most users find insanely annoying. Safari (at least in my environment) does not prompt you when you have successfully saved your password to the keychain. Of course, your mileage may vary.

Issues with known workarounds

  • Rich Text Editor – user must upgrade column type to Enhanced Text in order to customize content; however, user may have ability to drop in html tags (have yet to test this).
  • Open with Explorer, Upload Multiple Documents – Office for Mac comes shipped with “Document Connection.” This little add-in will accomodate Explorer views (to some degree) and multiple uploads. I was a huge fan of this all week.
  • OneNote – There is no native support for OneNote on a Mac; however, you can edit OneNote’s in the Office Web App. Some may find this difficult to use long-term but I thought it was sufficient.

Known Limitations without workarounds

  • Connect to Outlook, Connect to Office, and Sync to SharePoint Workspace– Outlook for Mac does not connect to SharePoint, at all; Office does not connect to SharePoint, with a few slight exceptions; SharePoint Workspace is not offered on Macs.
  • Datasheet View – requires an ActiveX control, which Macs do not support.
  • Drag and Drop Web Parts – Cannot be moved by using drag and drop on Web Part pages. Users must click Edit on the Web Part, select Modify Web Part, and then select the zone from the Layout section of the Web Part properties page. Web Parts can be moved using drag and drop on Pages.
  • Explorer View – not available on Macs.
  • File upload and copy, New Document, Slide Libraries – requires ActiveX control.
  • Editing PowerPivots – now while this may not be a SharePoint thing per se, it’s incredibly frustrating that I can’t edit PowerPivot workbooks. I can’t even edit it in the Office Web App. Throw me a bone here Microsoft. This one bullet point will be a non-issue for a good majority of you out there.

Overall the experience was pretty good. I now have a better understanding of the challenges Mac users have to deal with when viewing SharePoint. Microsoft has tried to make accommodations for Mac users. I have a great appreciation for Document Connection, and personally I think it’s better than SharePoint Workspace since it’s so scaled down.

Ultimately, I found the amount of ActiveX in SharePoint to be overwhelming. I have almost a decade of SharePoint experience.  I know the workarounds and the tips and tricks to get the job done. The average user, not so much.  For me, I found little things like the lack of Windows Explorer, PowerPivot support, and no support for dragging and dropping web parts to be mildly annoying. I’d be interested to hear what the hard-core Mac user’s opinion is and see if they would find these items to be small nuisances or major roadblocks to full-on adoption.  Annoyances/possible roadblocks aside, I still feel from a document and web content mgmt standpoint that there aren’t many tools that equal (or surpass) SharePoint’s power, but we have a long way to go before the Apple faithful jump-in head first. With the Mac users at Trek we’re going to start small and work our way out.

Next steps for me will to do an even more comprehensive write-up specific to Trek and put on a SharePoint for Mac Lunch ‘N Learn. Stay tuned.

Mac Experiment Week: Day #1

So this week I’ll be using a Mac as my primary device at work. Figured I should see how the “other” half lives so I can help them out when they have SharePoint issues.

Initial reactions:
1. I am a hotkey fanatic. More so than I ever thought.
2. Links lists in My Content are an extraordinary idea. Maybe I should take my own advice from time to time.
3. CoRD is one of the best RDP managers I’ve used regardless of OS.

I’ll continue to chronicle my experiences this week here and on Twitter. Any helpful software, hotkeys, advice, etc. you can provide would be much appreciated.