Tag: REST API

CRM + SharePoint * Excel Services = Epic Awesomeness

What’s the best way to get someone to eat their vegetables? Force feed them 😉

All kidding aside, CRM can be a pretty powerful tool and when people don’t want to use it we have to find creative ways to get them to use CRM. In addition to CRM usage, we have people who are absolutely married to Excel. And these aren’t your typical Excel files. These are files that legends are made of. Crazy formulas, vlookups galore; you name it, we use it. To make matters worse, they e-mail these Excel reports around all day, every day as attachments. So let’s kill two birds with one stone. We stop a big group of people from e-mailing Excel attachments and we get them to use CRM. Win-win for everyone (or at least that’s the hope).

  1. Save Excel file in an easy to find, easy to access place in SharePoint – doing this in SharePoint gives us all the doc mgmt benefits that we’ve come to know and love
  2. Configure your Excel REST API URL – I’ve made it pretty clear that I love SharePoint’s REST APIs in the past and the Excel API is no exception. You can read more about it here: http://msdn.microsoft.com/en-us/library/ee556413(v=office.14).aspx
  3. Create a new Web Resource in CRM – we’re going to iframe our Excel REST API URL call
  4. Choose Web Page (HTML) as the Type and then click “Text Editor”
  5. Click the Source tab
  6. Paste in your iframe code between the <body> tags. Your code should look like this: 
    https://dude.com/sites/site1/_vti_bin/ExcelRest.aspx/Documents/Document.xlsx/Model/Ranges('Scorecard')?$format=html
    • Note the frameborder, height, and width attributes. These are needed to eliminate the nasty border and to make scrolling work correctly. iframe’s aren’t perfect and getting them to work feels “hacky” but the user won’t know the difference and it should perform relatively seamless in all browsers.
  7. Click Publish
  8. Now, navigate to the desired dashboard and add your new Web Resource, click Save, and Publish.

Users should now see the Excel spreadsheet in their dashboard:

If users do not have access to the spreadsheet they should encounter an Error:Access Denied prompt or a blank screen depending on the browser they use.

Extra Credit

In our case, the Excel spreadsheet scrolled FOREVER. I wanted to give users a pleasurable experience but I also didn’t necessarily want them resorting to Excel on the client right away. I added a “Click to View in a separate Window” link in the iframe Web resource. Here’s what my code looked like:

<p><a href="https://dude.crm.dynamics.com/WebResources/new_iframe2">Click to View in separate Window</a></p>https://dude.com/sites/site1/_vti_bin/ExcelRest.aspx/Documents/Document.xlsx/Model/Ranges('Scorecard')?$format=html

All HTML Web Resources are web pages, so I linked directly to the web page. But notice I linked to new_iframe2? I didn’t want users seeing “Click to View” on every page so I made an identical web resource, except I removed the hyperlink from the top, making for a seamless experience for the user. There’s all sorts of other things I could have done on the new_iframe2 page. I could have linked to the Excel Web Access or even directly to Excel itself, but we’ll leave it like that for now.

Ultimately, I’ve gotten the report builders to stop e-mailing this specific report as an attachment, and now the audience of the spreadsheet has to go to CRM to view it rather than getting it e-mailed to them. Awesome.

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:

http://site.net/sites/sitename/_vti_bin/listdata.svc/

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.”

image

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”

SNAGHTML5798800

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.

image

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.