Excel Services was first released as part of Microsoft Office SharePoint Server Enterprise Edition in 2007. The initial release of Excel Services was geared toward extending Excel-based BI solutions on the server and making managing Excel files easier.

Excel Services had its capabilities expanded during the 2010 release wave of Office and SharePoint products.

Generally speaking, you have many ways to build a solution with different BI products in the SharePoint ecosystem. This section is intended to give some guidance on when an Excel Services–based solution might be a better fit than some other products.

You don’t need to do anything special or extra to install Excel Services; it is part of the SharePoint installation. When you install the Microsoft Office SharePoint Enterprise Edition, Excel Services is installed. Similarly, when you run the configuration wizard at the end of the SharePoint installation, Excel Services is configured automatically.

The primary thing to point out for installation is that if you want the ability to create new Excel files or to edit Excel files by using only a browser, you also need to install the Office Web Applications on top of SharePoint. The installation and configuration of the Office Web Applications generally looks and behaves the same as the core SharePoint install—so there isn’t anything new to learn from an install point of view. You should install and configure the Office Web Applications after installing and configuring the Microsoft Office SharePoint Enterprise Edition.

For the sake of simplicity and the purposes of this book, it is easiest to allow the post-setup configuration wizard to run to make sure your server and services are correctly configured.

You administer Excel Services just like any other service application in SharePoint—through an administration landing page where all the settings reside. Excel Services also supports PowerShell, and you can perform advanced administration through scripting. In a default configuration of Excel Services, the service should be secure, ready to use, and should support most workbooks without the administrator needing to do any further configuration.

To get to the administration landing page for Excel Services, on the Start menu, click Microsoft Office SharePoint Server | Central Administration. You’ll see the Central Administration Console, the page from which you can manage all of SharePoint.

On the Central Administration Console, under Application Management, click Manage service applications. You’ll see the Manage Service Applications page, as shown in the following illustration.

You can manage your service applications from the Manage Service Applications page. Each service application generally has both a service application and a service application proxy. The service application is the core engine that loads the files, handles security, manages sessions, and provides the core functionality. The proxy is a component that allows communication with the service application and allows for some advanced configuration options with respect to how the service application relates to the rest of the SharePoint farm. For more information and diagrams to help explain proxies, see http://technet.microsoft.com/en-us/library/cc263199.aspx . Of particular interest on that page are the topics “Services in SharePoint 2010 Products,” at http://go.microsoft.com/fwlink/?LinkID=167092 , and “Cross-farm Services in SharePoint 2010 Products,” at http://go.microsoft.com/fwlink/?LinkID=167095 .

With some service applications, you manage the proxy separately from the service application itself. Excel Services is not one of those. You can perform all Excel Services management from the service application landing page; you don’t need to manage the proxy separately.

Some service applications support running multiple instances of that service application in the farm or support running the proxy on one farm while hosting the service application on a different farm (also known as parent/child farms and inter-farm shared service applications), but Excel Services is designed to have only a single Excel Services application running in the farm. This is because Excel Services supports working with only the default Excel Services proxy of the default proxy group. You can have multiple groups defined per web application, but those scenarios are beyond the scope of this book. Sharing an Excel Services application across farms is also unsupported. For most enterprise deployments, a single Excel Services application running on the SharePoint farm is sufficient.

To get to the Excel Services administration landing page, on the Manage Excel Services Application shown in the following illustration, simply click the name of the Excel Service application you wish to manage.

From this page you can configure and manage the major pieces of Excel Services. Separate pages exist for each set of administrative task if you should need to change any settings to support a custom solution or adjust security settings to control resource usage or broaden capabilities.

Like any other files in SharePoint, Excel files are subject to SharePoint permissions and security. This remains true even after Excel Services is installed.

The most important server-security concept that Excel Services takes advantage of is the notion of trusted file locations. Trusted file locations are simply directories from which Excel Services permits Excel files to be loaded. If an Excel file is not stored in a location in the list of trusted locations, Excel Services does not load it. By default, in Excel Services 2010, the entire SharePoint farm is considered to be a trusted location. That means that Excel Services can load any Excel file from any SharePoint location.

When a workbook is loaded on Excel Services, the server forces that workbook to respect settings that are defined for the trusted location from which it was loaded. Trusted locations have many available settings, so the server administrator can control the allowable operations as well as how many server resources workbooks can use.

For example, you can specify that workbooks loaded from http://portal/teamsiteA cannot be larger than 1 MB and can never be allowed to refresh against any data sources, but workbooks loaded from http://portal/trustedTeamSiteB can load much larger workbooks, up to 20 MB, and can query data sources.

To see the trusted location list, go to the Excel Services Central Administration page and click Trusted Locations.

Select the trusted location you want to view and click the link to that location. As shown in the following illustration, you’ll see a page that shows some of the settings and values that apply to the default trusted location.

One challenge for administrators is to get external data configured for use by Excel Services. Unfortunately, for deployments that aren’t single-box evaluator style deployments, (where all components are installed on the same machine with a default configuration usually as a trial deployment), some amount of configuration is required to make external data connectivity work.

This section doesn’t contain an exhaustive approach to all external data connectivity but does provide some simple guidance and links to detailed steps so that you can complete the high-level configuration needed to get going. Also, you can reuse much of the configuration work described here for other service applications, such as Visio Services, PerformancePoint Services, and even PowerPivot.

The simplest way to get data connectivity working on the server is to use a single account to connect to all the data sources or to save the credentials in the connection string. The downside of this is that there won’t be any per-user security applied to the data. The single account used to get data for Excel Services is known as the “Unattended Account.” Even if the credentials used to connect to the data source are in the connection string, for security purposes, Excel Services requires that you first have an Unattended Account configured. When the connection is made, Excel Services will use either the Unattended Account or the credentials stored in the connection string, depending on what is in the connection string.

The Unattended Account is simply a user account created for the purpose of read-only access to data sources. The account credentials (user name and password) must be stored in the Secure Store Service (SSS). (SSS is another service application, similar to Excel Services, that stores accounts securely.) The article at http://technet.microsoft.com/en-us/library/ff191191.aspx , “Configure Secure Store Service for Excel Services (SharePoint Server 2010),” contains instructions for configuring SSS and the Unattended Account for Excel Services.

Another option is to configure the Secure Store Service explicitly for credential retrieval. Basically, SSS stores credentials in a secure way that makes them available to service applications like Excel Services to use for things such as data refresh. An administrator must configure it and set permissions so the right user groups have access to the credentials. A workbook author must then know the key, or Application ID, to use in the workbook to ensure that the right set of credentials is requested when the user tries to refresh the data in the workbook. So it is better than the simple “one account for everyone to refresh data on the server” approach, does have more setup overhead, but usually isn’t quite as hard to configure as the option we discuss next. For more information about Secure Store Service, see http://technet.microsoft.com/en-us/library/ee806889.aspx .

If you want per-user security, the best option is to configure Kerberos in your environment. Kerberos configuration can be complex—and you might not need it if your users need simple read-only access via a single account. See “Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products,” at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1a794fb5-77d0-475c-8738-ea04d3de1147&displaylang=en, for more information about Kerberos configuration.

Because tight control over Excel files is a goal of many solutions, this section provides an overview of how to publish and secure server views of an Excel workbook.

Excel files can sometimes contain sensitive intellectual property (IP). For example, a workbook may use custom or proprietary formulas and logic to get a result. The result may often need to be shared, and the logic may need to be protected. This kind of overall solution is very difficult to achieve in Excel natively but can be done using Excel Services.

The secret to sharing the workbooks while protecting the IP is to apply the View Only permission for users who need to view the report but shouldn’t be allowed to see any of the logic underneath. View Only permissions means that only a sanctioned application can be used to open files of a certain type. In SharePoint, Excel Services is registered as the file handler for the supported types of Excel files (.xlsx, .xlsb, .xlsm, .odc). Therefore, when View Only permissions are applied, only Excel Services can be used to open the Excel files. This means that users can’t use the Excel client, can’t select Save Target As, cannot download the file, and cannot open those files in any other way. The view provided by Excel Services does not expose any of the IP in the workbook.

The workbook used in the following procedure was created by connecting to the sample Contoso Retail DW database and connecting to the Sales cube.

Use the following procedure to add a second pivot table to the report so that you can compare product sales to the cost of making those sales.

You can use the following procedures to take advantage of some new Excel 2010 BI features.

To make it easier to gain some insights into the data, the following procedure shows how you can add conditional formatting to help make the outliers in the data really pop.

Slicers are a new feature that you can use in conjunction with pivot tables. They make filtering operations to display the correct subset of data much easier than it was in the past. Slicers look like toggle buttons that appear when the data they represent is being displayed in the tables they filter.

Slicers that don’t contain data (because all their values might already be filtered out by some other filter) appear as disabled or empty. So not only is the slicer UI a more intuitive way to filter, it also provides visual clues that say something about the data.

Slicers also give users a great deal of control over the visual display of a report; they can resize and reposition the reports, can adjust the button sizes, and they can control the slicer color theme as well.

Slicers also become very useful in cases where you have different sets of data that need to be filtered by the same set of values. You can connect slicers to more than one pivot table, giving you the ability to “slice” the report.

If you want to make things fit together a bit more tightly in the report, resize columns B and E to make them smaller and spend some time lining up edges of pivot tables, resizing charts, slicers, and so on.

If you don’t like how the pivot table columns resize and adjust the look of your report when you drill down or filter on your pivot table, you can turn the resize behavior off. To do this, right-click your Sales Amount pivot table and click PivotTable Options on the pop-up menu. When the PivotTable Options dialog box opens, clear Autofit Column Widths On Update check box and click OK.

The report, with the preceding minor adjustments to size and position completed, should resemble the following illustration. (Notice that the Sales Amount pivot table has most of its fields collapsed.)

Sparklines are a great new BI feature in Excel 2010 that you can use for showing large amounts of graphical data in a way that can be summarized in a single cell.

To be truly interactive, the sparkline should update when the report is sliced using the slicers. It doesn’t make sense for the date slicer to affect the sparkline because the sparkline is always for the year 2007. But when the products are sliced, the sparkline should change. Because the sparkline is based on a pivot table, you can simply connect the Product SubCategory Name slicer to the pivot table on which the sparkline is based.

To connect the slicer to the pivot table, follow the steps in the earlier procedure titled To connect the slicers to another PivotTable, except perform them for the Product SubCategory slicer against the Sales Total Cost pivot table on which the slicer is based.

Repeat this process for all of your sparklines.

Try clicking the slicer, and notice how the pivot tables—and now the sparklines as well—update with new data as you slice the report.

The file from the preceding section should now be saved in SharePoint. To use Excel Services, simply navigate to the document library where you saved the file and click the file. This renders the file in the browser by using Excel Services, as shown in the following illustration.

Notice that the report’s visual fidelity is maintained even though this version of the file is rendered in your browser. Excel Services also has complete calc fidelity, meaning that a workbook calculated in the Excel client yields the same numbers when calculated on the server.

Also notice that the workbook is interactive. For example, try drilling down on the pivot table: The data refreshes on the server just as it would on the client. Also try using one of the slicers. You’ll see that the entire report is sliced just as it would be sliced in the Excel client.

Excel Services provides a setting to control whether a default click on a document opens the Excel file in the thick client (the Excel client) or Excel Services. To access that setting, from the SharePoint document library in the browser, on the Library tab, click Library Settings. On the next page, under General Settings, click Advanced Settings. In the Opening Documents In The Browser section, you can choose whether the document library follows the default server policy, opens using a web browser, or opens using the Excel client.

You can try editing this or any other Excel file that you can view on Excel Services.

Because this file uses external data refresh, it is editable on Excel Services only if you first check the file out. This is true of all Excel files that perform a data refresh because all editing is collaborative on the server. Any user with permissions to edit the file can join at any time. When editing collaboratively, all users share the same session—and hence all users would share the same set of credentials, which can present a security issue in some environments. To prevent cases where other users might be using your credentials to perform a data refresh, Excel Services requires that you first check the file out to prevent others from joining the edit session.

When you check this file out from the document library and then view it with Excel Services, you can click the Edit In Browser tab. The editing capabilities are basic in Excel Services; you can add formulas (by typing the = sign in a cell and then typing the formula name), add text, format cells, add hyperlinks, add tables, and more. The editing is a subset of the most basic spreadsheet editing functionality and can be useful in many cases where you have a single spreadsheet that many users need to enter data into.

To see collaboration in action, simply open another instance of your browser, go to the SharePoint document library, and chose to edit the file in the browser. In this case, you are now “collaborating” with yourself.

SharePoint provides the ability to combine insights from Excel workbooks to be consumed side by side with other data in SharePoint. The most common way to do this is to configure a Web Part page in SharePoint. This gives a quick and easy way to get Excel based data into a dashboard format that many BI users are familiar with. See Chapter 8, for a more complete discussion and step-by-step guidance.

UDFs are simply managed-code assemblies written to perform a specific task and deployed to the server. These managed assemblies can be called from a workbook just like any other Excel function. So they can take a set of parameters from other cells in the workbook, and they can return a single value or an array of values.

UDFs are particularly useful when you are using custom-coded routines to calculate a particular set of values in a particular way. They are also great mechanisms to use for performing other tasks in the system or in another system. Some UDF examples I have seen include: refreshing data from a SharePoint list; writing some custom data values into a data store that are specific to the user viewing the workbook; retrieving values from a custom data store; performing complex mathematical computations; and parsing data from the web, returning a list of values based on the custom web query.

UDFs aren’t a replacement for full VBA solutions or macros. For example, they don’t provide any type of object model against a workbook on the server.

To learn more about UDFs, see “Excel Services User-Defined Functions” at http://msdn.microsoft.com/en-us/library/ms493934.aspx.

Excel Services provides a SOAP-based web service that allows programmatic access to workbook files loaded on the server. The web service isn’t only for use on the web—you can call it from any application that knows how to call a web service, including custom-built client-side applications and other web-service aware Microsoft applications, such as InfoPath.

The web service is a great way to access values from a workbook when you don’t need to display the Excel UI. It enables you to do things such as set values, recalculate the workbook, refresh the data, and retrieve the entire workbook or just values from certain cells. If the Office Web Applications are installed and Excel Services editing capabilities are enabled, you can also persist values in the workbook.

To learn more about Excel Web Services, see “Excel Web Services” at http://msdn.microsoft.com/en-us/library/ms572330.aspx .

The Excel Services ECMAScript object model (JSOM) is similar to Excel Web Services in many ways: it supports loading workbooks, setting values, recalculating and refreshing workbooks, and the ability to retrieve specific values from the workbook. It also supports a number of things that the SOAP-based web service doesn’t, such as displaying the UI and an event model.

ECMAScript code is designed to be run on a page inside a browser as part of a solution where the user is interacting with the core Excel Services UI. It provides events, so you can programmatically react to things that the user does. For example, when performing some simple data validation, if a user types in a value that is below 100 in cell A1, you might want to show a pop-up window stating, “The value is too low. Enter a value greater than 100.” Events include such activities as “a cell is being edited” or “a value in a cell has changed.” These events are fired by Excel Services, and your solution can take action on them.

Some events allow you to know which cells have been selected. This makes it possible to navigate the Excel Services UI to a different location in the workbook or even to have a second window on the page that shows Excel Services UI based on the selection. Solutions with VBA buttons on the first Excel sheet are fairly common. When users click a button on the first sheet, they might be taken to a different part of the report. Unfortunately, such solutions don’t work on Excel Services, because VBA isn’t supported. But you can re-create such solutions by using the JSOM on a webpage.

To learn more about the Excel Services ECMAScript, see “Excel Services ECMAScript (JavaScript, JScript),” at http://msdn.microsoft.com/en-us/library/ee556354.aspx .

REST stands for Representational State Transfer. It is commonly used to retrieve an XML description of a webpage by using only the URL to the webpage. For Excel Services it means that, given a URL to a workbook file, you can access the entire file or parts of the file from any client that knows how to traverse a URL and request content.

This powerful concept allows users to build useful solutions without actually writing any code. The “code” in this case is just a carefully crafted URL. The URL specifies the path to the workbook. Then the REST query portion of that URL specifies any values that should be set into certain cells in a workbook, as well as what object or values should be retrieved.

REST supports retrieving many types of objects from a workbook (charts, cell values, tables, and so on) and enables you to specify what format they should be in (xml html, or an image).

Using REST, you can easily embed a chart or table of data based on Excel logic into a blog, webpage, or any other application that can traverse a URL. And the file itself isn’t embedded—the file is stored safely in SharePoint. Instead, Excel Services loads the file, recalculates it to get the latest numbers, and returns only the result to be embedded in the webpage. So as the file updates, the blog or page gets the latest numbers or image as the page is reloaded.

REST is also a great way to quickly and easily retrieve data from the workbook in xml format for further processing as part of a more advanced solution.

The same concept works for anything that can traverse a URL and bring back data from it, including applications such as Microsoft Word, PowerPoint, or InfoPath. Have you ever wondered how to get an Excel chart into a PowerPoint deck and make it refreshable—without having to copy the file or embed the full file into PowerPoint? REST is the answer. Leave the file in SharePoint, and insert an image into PowerPoint. Then, in PowerPoint, specify that the image is from a link, provide the REST URL to the chart, and enjoy an image that gets refreshed every time a user opens the PowerPoint file.

To learn more about the Excel Services REST, see “Excel Services REST API,” at http://msdn.microsoft.com/en-us/library/ee556413.aspx .