Excel Services in SharePoint 2010

Excel services is yet another important pillar in Microsoft’s business intelligence offering. Business users really like Excel because it is easy to use and they can 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 some Excel sheets with their coworkers. Usually users prefer to send workbooks via email, but sometimes the workbooks are too large for email, sometimes they have backend data connections, and sometimes workbooks send via email cause version confusion hell.

Excel Services solves all of these problems. In short, Excel Services allow you to publish an Excel workbook in a document library. The Excel workbook is then calculated on the server, and is then presented to one or more clients. In the calculation, Excel workbooks can involve external data sources or even custom UDFs (user defined functions) written in .NET. Once you publish the Excel workbook (though you could publish just a worksheet or even just an object like a chart), other persons, applications or services with access to the server can consume it directly through the browser by using the Excel WebApplication component, Excel Web Access Web Part, or ExcelService.asmx Web service. The Excel workbook functionality is also available over REST-based APIs and thus can be exposed as Atom feeds or JSON.

Let’s look at a practical example. Start by setting up the Northwind Traders database in a SQL server. You will find the script to set up the Northwind database in the associated code download of this article. Next I’ll craft an Excel workbook that displays orders information from this database as a pivot table and pivot chart.

Start Excel 2010 and click the Data tab in the ribbon, choose "From other sources" and choose to import data from SQL server. 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 could have also chosen to use 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:

1. Make ShipCountry the Report Filter.
2. Make ShipCity and ShippedDate Row Labels.
3. Show Sum of Values using the Freight column.

Choose to make USA as the filtered selected country. Your pivot table should look like Figure 1.

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. I’ll format the chart a bit and will choose to show a line graph instead of a bar chart (Figure 2).

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 that have freight less than or greater than average. My pivot table now looks like Figure 3.

Finally, select that A1 cell in the pivot table to make the Options tab in the Ribbon visible. With the Options visible, click on Insert Slicer, and 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.

You can see my final Excel sheet in Figure 4.

Now from the Backstage view of Excel 2010, click Share, and choose to publish this workbook to Excel Services. For now, publish this workbook 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. And if you have used Excel Services with SharePoint 2007, you would remember that at this point you would also have to go into Central Administration and add a trusted file location. In SharePoint 2010 this isn’t necessary because by default, all SharePoint sites are available as trusted file locations. You can verify this by going to the Central Administration | Application Management section, click Manage service applications, and choose to manage the Excel Services service. Click on Trusted file locations, and you should see an entry as shown in Figure 5.

As you can guess, this entry makes the entire SharePoint farm available as a trusted file location.

With the Excel workbook published, drop the Excel Web Access Web Part on the homepage of your SharePoint site collection and configure it to show your newly published Excel workbook. You should see your Excel workbook running with full interactivity in the pivot table, pivot chart, the slicer, and showing live data from the Northwind database. You can see this in Figure 6.

Now this is a very compelling example. The end business user can craft up such workbooks talking to real data and publish them for the world to see. It gets more interesting than this. For the current web session, these workbooks can be edited in the browser and can recalculate themselves and present new data. Excel Services also have the ability to parameterize certain sheets. Also, the logic of the workbooks is exposed over a Web service and REST API. Let’s look at that next.

By: Sahil Malik

Sahil Malik is a Microsoft MVP, INETA speaker, a .NET author, consultant, and trainer, and a well-rounded overweight geek. He has a passion for SharePoint, data access, and application architecture.

Sahil loves interacting with fellow geeks in real time. His talks are full of humor and practical nuggets. His talks tend to get very highly charged, fast moving, and highly interactive.

You should check out his blog at http://blah.winsmarts.com

sahilmalik@gmail.com