SharePoint 2010 : Business Intelligence - Excel Services (part 1) - Accessing Excel Services Over REST

Excel services is yet another important pillar in Microsoft's business intelligence offering. Business users really like Excel. This is because Excel for them is easy to use and they are able to add complex formulas to Excel to express their logic. They can do so without involving the IT guy. The problem with this scenario, however, is that it becomes very difficult to share this Excel sheet with their coworkers. Usually they would e-mail the sheet around, but sometimes these sheets are too large to be emailed, sometimes they have backend data connections, and sometimes emailing causes version confusion hell.

Excel services solves all of those problems. In short, Excel services allows you to publish an Excel sheet in a document library, the Excel sheet is then calculated on the server, and is then presented to one or more clients. In the calculation, Excel sheets can involve external datasources or even custom UDF's (user defined functions) written in .NET. Once this Excel sheet has been published, it can be consumed directly through the browser by using the Excel web application component, Excel web access webpart, or Excelservice.asmx web service. The Excel sheet functionality is also available over REST based APIs and thus can be exposed as atom feeds or JSON.

Then, start Excel 2010, and click on the data tab in the ribbon. In the data tab, click "From other sources ", and choose to import data from SQL server. You would note that you have the ability to import data from various other sources as well. When prompted to import data from SQL server, choose to import the data from the "Orders" table in the Northwind database using windows authentication. You have the ability to use either windows authentication, SQL server authentication, or to provide a secure store service ID and get credentials at runtime. Once you have finished importing the data from SQL server, choose to save the .odc file in a SharePoint "Data Connections Library " that you will need to create beforehand.

At this point, Excel will prompt you to import the data as either a table, a pivot table, or a pivot table and pivot chart. Choose to import the data as a pivot table and pivot chart.

Now in the pivot table make the following changes:

• Make ShipCountry the Report Filter

• Make ShipCity

• and ShippedDate as Row Labels

• Show sum of freight values

Choose to make it filtered by selected country. Your pivot table should look like Figure 1.

Figure 1. My PivotTable

Note that the pivot chart has been updating itself and is showing you a graphical view of the data you see in the pivot table. Thus, the pivot chart and pivot table are connected with each other. Format the chart a bit, like choosing to show a line graph instead of a bar chart, and your pivot chart looks like Figure 2.

Figure 2. My PivotChart

Next, add a column next to the pivot table and give it a heading of "Difference from Avg.". Give it a formula of "=B4-AVERAGE(B4:B15)" and choose to repeat this formula on all available cells. Also, apply conditional formatting to this cell, so it graphically shows you all cities have freight less than average and which cities have freight greater than average. The pivot table now looks like Figure 3.

Figure 3. DataBars added within each cell in my pivot table

Finally, select the A1 cell in the pivot table to make the options tab in the ribbon visible. With the options visible, click on insert slicer, and then choose to make shipregion available in the slicer. This will allow you to slice the data at runtime and subsequently affect the pivot table and pivot chart.

The final Excel sheet looks like Figure 4.

Figure 4. The final Excel sheet

From the backstage view of Excel 2010, click save and send\Save to SharePoint\Browse for a location. For now, publish this sheet to a document library called sheets. You will have to pre-create this document library in your site collection. Also, you will need to pre-activate the enterprise features in your site collection. If you have used Excel services with SharePoint 2007, you would remember that you would also have to go into central administration and add a trusted file location. This is no longer necessary in SharePoint 2010. By default, all SharePoint sites are available as trusted file locations. You can verify this by going to central administration, clicking on manage service applications, and choosing to manage the Excel services service. In there, click on trusted file locations and you should see an entry similar to Figure 5.

Figure 5. Trusted file locations within SharePoint for Excel Services

This entry makes the entire SharePoint farm available as a trusted file location.

With the Excel sheet published, drop the Excel web access WebPart on the homepage of your SharePoint site collection and configure it to show your newly published Excel sheet. You should see your Excel sheet running with full interactivity in both the pivot table, pivot chart, the slicer, and showing live data from the Northwind database. This can be seen in Figure 6.

Figure 6. The Excel Sheet running in Excel Services

This is a very compelling example, because the end business user can craft up such sheets talking to real data and publish them for the world to see. However, it gets even more interesting than this. For the current web session, these sheets can be edited in the browser and can recalculate themselves and present new data. In Excel services, you also have the ability of parameterizing certain sheets. Finally, the logic of the sheets is exposed over a web service and REST API.

1. Accessing Excel Services Over REST

Here is the best news. The Excel sheet that you have so far been working with is already being exposed over the rest based API. Don't believe me? Assuming that your filename was "Northwind Orders.xlsx ", Visit the following URL:

http://sp2010/_vti_bin/ExcelRest.aspx/Sheets/Northwind%20Orders.xlsx/model^[].

^[] Note that the URL contains the name of the Excel sheet. If you named your sheet something else, your URL would be different.

As you will note, the URL provides you with all the details embedded in your Excel sheet over an atom feed. Now visit the following URL:

http://sp2010/_vti_bin/ExcelRest.aspx/Sheets/Northwind%20Orders.xlsx/model/Charts('Chart%201')?$format=image.

You would note that your Excel sheet chart is being exposed as a simple image. This is really useful. I'm going to show a practical demonstration of this. Imagine that your task is to craft up a document that shows the graph of the freight cost for USA. Usually, you would copy then paste such a chart from an existing web site and embed it in the document. That is not a perfect approach because sooner or later the chart will become out of date. However, you can use Excel services to create a chart that updates with live data every time the document is opened. To do so, start Word 2010 and under the insert tab click quick parts. Choose to include a new field and in the ensuing dialog box choose to "IncludePicture ", which provides the appropriate URL as shown in Figure 7.

Figure 7. Customizing the QuickPart in Word

This will immediately insert a chart from the web-based URL, and every time the Word document is opened this chart will automatically be refreshed. This can be seen in the Figure 8.

Figure 8. Data coming from Excel services embedded in a Word document

It goes without saying that you can embed this chart in any web-based content, such as a content editor WebPart, blog post, or even a non SharePoint application. What is really compelling is that this functionality of Excel service being exposed as atom-based feeds is also available on sky drive in the cloud as well. Therefore, you can technically host Excel sheets in your sky drive, and have those generate graphs that can be embedded in your blog posts or anywhere else.

Not only can you embed charts, but you can embed truly any content being exposed from an Excel sheet into any other consumer. For example, now try visiting the URL:

http://sp2010/_vti_bin/ExcelRest.aspx/Sheets/Northwind%20Orders.xlsx/model/PivotTables('PivotT
able1')?$format=html.

					  

Note the end of the query string parameter in the previous URL. By default, the content is exposed as atom feeds, but you can choose to request JSON or even HTML.

The previous URLs renders an HTML table, as shown in Figure 9.

Figure 9. Data coming from Excel services available as an HTML table

Now this HTML table can be embedded into any container that can render HTML. This can be your browser, Word or Excel, or something else. For example, to insert this content into Word again choose to insert a quick part, but this time choose to insert using "IncludeText".