Configuring Excel Services

Developing solutions using Excel Services is pretty straightforward. We’re all familiar with Excel and what it can do, so all we really need to do is save our spreadsheet onto our SharePoint Server and our mission is accomplished. (Maybe it’s not quite as simple as that, but the point I’m making is that we’re not facing a near vertical learning curve here.)

Where we do need to pay a bit more attention is in the configuration of Excel Services. Left unchecked, users could upload server-crushing calculation mayhem. They could easily create a spreadsheet that dragged in the entire contents of the company customer relationship management (CRM) database and performed string matches on description fields before extracting the top ten customers and the value of their sales during the past two weeks. We could, of course, argue that this is a job for the IT pros and it’s probably fair to say that as a day-to-day task it is, but what we’ll find as we start developing solutions using Excel Services is that practically all the problems we’ll encounter come down to configuration issues.

Service Application Settings

Chapter 9 covered the Service Application Framework and how it’s used by SharePoint to provide services that can be shared among web applications. It will probably come as no surprise to learn that Excel Services is implemented using the Service Application Framework.

To configure Excel Services, we use the SharePoint 2010 Central Administration tool. From the Central Administration home page, the Excel Services configuration settings can be reached by clicking Application Management | Manage Service Applications | Excel Services. Of course, if more than one instance of Excel Services is running, you’ll be able to configure that using whatever name you assigned when you set it up.

When you’re configuring and using Excel Services, keep in mind the notion of trust. System administrators determine what is trusted and what isn’t, and users are allowed to create and use workbooks that make use of trusted resources only. So, for example, a system administrator may decide that data from a particular source—such as a data warehouse—is trusted, whereas data from a second source—such as a CRM application—isn’t trusted. Making this distinction doesn’t mean that the CRM system data is any less accurate than the data warehousing data; of course, the opposite is likely true. What it means is that the system administrator has determined that the schema and data volumes within the CRM system are likely to cause issues if they are used freely within Excel. By choosing not to trust this data source, users will be unable to reference it when creating workbooks. So the notion of trust is actually about trusting the resource to play nicely within Excel Services.

The configuration settings for Excel Services involve five sections, as shown next. I’ll quickly run through these, calling out any settings that are relevant to developers.

Figure 1. Configuration Settings

Global Settings

As you might expect, this section covers most of the high-level configuration options such as load balancing and memory utilization. For the most part, these settings are unlikely to cause developers problems and are best left to the IT pros as part of maintaining and configuring the farm overall—with one exception: the external data settings. When a spreadsheet is created that connects to an external data source, you need to consider a few options for authentication. These are covered in more detail later in the section “Using Data Connection Libraries,” but for now you should know that if the authentication type is set to None, connections are made using the unattended service account. Since the unattended service account may be used to connect to many different types of data source, the Secure Store Service is used to map appropriate credentials to the account. This allows non-Windows credentials to be used where appropriate. The configuration of this is covered in the “Demonstration Scenario” section later in the chapter.

Trusted File Locations

The Trusted File Locations section is probably not going to cause you too many problems. In previous versions of SharePoint, this was not configured by default, so no Excel Service workbooks would load properly. Thankfully, with SharePoint 2010, the default setting is to allow all workbooks stored within SharePoint to be accessed using Excel Services.

You can use workbooks that are not stored within SharePoint within Excel Services. Trusted file locations can be created for Universal Naming Convention (UNC) paths or other web sites. A few things worthy of mention include the User-Defined Functions setting and the settings in the External Data section. Although User-Defined Functions have a separate configuration heading, they can be disabled or enabled at the trusted file location level. By default, UDFs are allowed. In the External Data section, external data can be disabled at the trusted file location, and the ability to refresh external data when using the REST API is determined by a setting in this section.

Trusted Data Providers

Trusted Data Providers defines the lists of drivers that can be used when connecting to external data sources. If a driver isn’t listed, it can’t be used to create a connection. Having said that, even if a driver is listed, there’s no guarantee that it can be used.

Trusted Connection Libraries

Data Connection libraries serve a few functions: They allow a system administrator to create a library with a series of preconfigured connections that can be easily used by business users for various tasks. In addition, data connection information is stored in a central location, and any changes that may be required can be made globally without having to update myriad documents throughout an organization. At the Trusted File Location level, you can restrict data connections to trusted data connection libraries only. Unless this option is selected, users are free to create their own data connections using any of the trusted providers and embed those connection details within a workbook.

User-Defined Function Assemblies

In this section, you can configure any assemblies containing user-defined functions. UDF assemblies can either be loaded from a file location or from the Global Assembly Cache (GAC). Note that the assembly must be available on all machines running Excel Services. For ease of administration, an assembly can be disabled, meaning that it remains configured but can’t be used by Excel Services.

Demonstration Scenario

To demonstrate the capabilities of Excel Services, consider the following scenario:

AdventureWorks, one of your clients, has implemented SharePoint 2010 as its corporate intranet platform. To increase awareness of company performance within the organization, you’ve been asked to add an indicator of global sales to the front page of the intranet site. Since AdventureWorks is a global organization, it should be possible to filter the sales figures by geographic region, and the amounts shown should be visible in a range of currencies, selectable by the user.

It probably comes as no surprise to learn that we can implement this solution using Excel Services. We can render an interactive chart using the Excel Web Access web part, and if we base the chart on a pivot table, data will be automatically refreshed. To filter the sales figures by geographic region, we can incorporate a slicer into the design, which will allow users to select from a graphical list of available regions.

Displaying the results in various currencies is a bit more involved, since the data in the underlying database is stored in a single currency only. To achieve this, we’ll create a custom UDF that will retrieve current exchange rates via a web service call. We’ll then make use of that data to recalculate the workbook in the appropriate currency. Because we want to allow the user to select from a list of currencies, we’ll make use of the JavaScript API to pass in a currency code selected from a drop-down list. When the currency code is passed into the workbook via the API, the workbook will be refreshed using the appropriate currency.

Set Up Adventure Works Sample Database

To provide some sample data to work with in our various examples, we need to download and install the SQL Server 2008 sample databases from Samples. Our examples make use of the AdventureWorks OLTP database installed on the local instance of SQL Server 2008.

Create a Sample Site

Now we’ll create a new blank site to use for development. Note that if we were creating a complete Business Intelligence (BI) solution or intended to use the site mainly for hosting dashboards, the Business Intelligence Center site template would be a more appropriate place to start. Since we’re using only a single Excel workbook, we’ll make use of a basic blank site.

1.       In SharePoint Designer, choose File | Sites, and then click the New Blank Web Site button. Type the name of the new site as http://localhost/Chapter12.

2.       We’ll add a new document library to host our Excel Workbook. From the Site Objects pane, select Lists and Libraries.

3.       From the ribbon, select Document Library | Document Library. Name the new library Excel Workbooks:

Figure 2. Create List or Document Library

Create a Workbook for Use with Excel Services

Before we can make use of an Excel workbook in SharePoint 2010, we need to create it using the Excel client application. For our demonstration scenario, we need a simple workbook that contains a pivot table and a pivot chart. The pivot table should be generated from data stored in the organization’s ERP system.

1.       Open Excel 2010. A new blank workbook will be automatically created, and we can make use of this workbook for our demonstration. First, we need to add a data connection so that we can retrieve appropriate sales data. On the Data tab, in the Get External Data section of the ribbon, select From Other Sources | From Data Connection Wizard, as illustrated:

Figure 3. Get External Data

2.       In the Data Connection Wizard dialog, accept the default selection of Microsoft SQL Server by clicking Next to proceed.

3.       In the Server Name text box, enter .\SQLExpress. For the Log On Credentials, select Use Windows Authentication. Click Next.

4.       In the Select Database and Table step, change the selected database to AdventureWorks, and uncheck the Connect To A Specific Table checkbox as shown. Click Finish to complete the wizard.

Figure 4. Data Connection Wizard

5.       After the wizard has completed, the Select Table dialog is displayed. Although the data that we require comes from more than one table, we need to select a table to create a connection in our workbook. Select the SalesOrderHeader table, as shown, and then click OK.

Figure 5. Select Table Dialog Box

6.       The Import Data dialog allows us to select what we want to do with the connected data and includes a few options, such as Table and PivotTable as well as a range selector that we can use to specify where the data should appear in the workbook. When using Excel Services, you should be aware that only PivotTables can be refreshed on the server. Although it is possible to add a table and use the data within the table in Excel Services, the only way to refresh the table data will be to open the spreadsheet in Excel and refresh manually. Bearing this in mind, select PivotTable Report and put the data at the default location of $A$1 in the current worksheet.

Configure a Data Connection

A new PivotTable is inserted on the page at the specified location. Before we start configuring the PivotTable, we need to review our connection settings. Recall that we selected the SalesOrderHeader table as the source of our data; before we can set up our pivot table, we need to change this to use a SQL query instead.

1.       From the Data tab, select the Connections option. In the Workbook Connections dialog, select the ._sqlexpress AdventureWorks connection. Notice that your connection may have a slightly different name, depending on your database server and whether an existing connection with that name already exists.

2.       Click Properties to view the connection details. Change the connection name to AdventureWorksLast30DaysSales.

3.       Click the Definition tab to see details of the connection string, the command type, and the command text as well as a few other options. Change the Command type to SQL and enter the following SQL statement in the Command Text text box:

SQL

Copy

SELECT   H.OrderDate,

         T.Name as Territory,

         T.CountryRegionCode as CountryCode,

         sum(TotalDue) as TotalSales

FROM     [Sales].[SalesOrderHeader] as H

INNER    JOIN [Sales].[SalesTerritory] as T

ON       H.TerritoryID=T.TerritoryID

WHERE    H.OrderDate>'2004-07-01'

GROUP BY H.OrderDate, T.Name, T.CountryRegionCode

4.       When a data connection is used by Excel, a copy of the connection information is stored in the workbook. In the Properties dialog, we’re effectively editing the properties of this cached copy of the data connection. To update our locally saved connection file, click Export Connection File and then, in the file dialog that appears, type the filename as AdventureWorksLast30DaysSales.odc. Click Save to create the new Office Database Connection file.

5.       Click OK to close the Properties dialog, and then click Close to close the Workbook Connections dialog. Notice that the fields listed in the PivotTable Field List have changed to match those in our amended query.

Configure a Pivot Table to Act like an External Datalist

PivotTables are a great help for analyzing a data set interactively. We can easily add in row headers or columns headers or formulas and grouping to the data. Sometimes we don’t need to do any of that clever stuff, though; we might want a simple list of the data as it looks in the database. In Excel client, we could of course achieve such a result by creating an External Data List as opposed to a PivotTable. However, External Data Tables aren’t supported in Excel Services, so we’re stuck trying to reign in the analytical faculties of the PivotTable to produce a more sedate output.

To create a PivotTable that behaves in a similar manner to an External Data List, take the following steps:

1.       From the PivotTable Field List, drag OrderDate, CountryCode, and Territory into the Row Labels section. Drag Sum of TotalSales into the Values section, as illustrated:

Figure 6. PivotTable Field List

2.       From the PivotTable Tools tab, select the Design menu. In the Layout section of the ribbon, select Report Layout | Show In Tabular Form. Again from the Layout section, select Report Layout | Repeat All Item Labels.

3.       The resulting PivotTable is starting to look a bit like a data list. We can now remove the total rows by selecting Subtotals | Do Not Show Subtotals from the Layouts section of the ribbon.

4.       To remove the +/– buttons, open the Options menu from the PivotTable Tools tab. Click the +/– button on the Show section of the ribbon to toggle the buttons off.

Using Named Ranges in Excel Services

You may be wondering why we had to go to the trouble of changing our PivotTable to a flat data list. It’s fair to say that, generally speaking, we wouldn’t normally need to take this step when using data in Excel Services, but this case is a bit different. The TotalSales value retrieved from the database represents the sales value in US dollars (USD). However, our demonstration scenario requires us to be able to present this data using a variety of currencies.So that we can convert this value to a different currency, we need to use a formula, and formulas within PivotTables are limited to include only data from within the PivotTable. In our case, the exchange rate value that will be used by our formula will be stored elsewhere in the workbook, so using a PivotTable formula isn’t an option. We can achieve our desired outcome by flattening our PivotTable and then adding appropriate formulae in adjacent cells.

Let’s move on to add a few named ranges that will be used on our calculation logic:

1.       Navigate to Sheet2 in the Excel workbook. We’ll use this sheet to store the values required by our exchange rate calculation.

2.       In cell A1, type Exchange Rate. In the adjacent cell (B1), type the number 1. We’ll come back to this later when we create a UDF. With the cell B1 selected, in the Name box, enter ExchangeRate, as illustrated:

Figure 7. ExchangeRate

3.       In cell A2, type Currency Code. In the adjacent cell (B2), type USD. With cell B2 selected, in the Name box, type CurrencyCode.

4.       In cell A3, type Chart Title. In the adjacent cell (B3), add the following formula:

="Last 30 Days Sales in " & CurrencyCode

When completed, the first few cells of Sheet2 should look like this:

Figure 8. Exchange Rate

Perform Calculations Using Pivot Table Values

Now that we’ve defined the parameters for our exchange rate calculation, we can add the necessary formulae to Sheet1.

1.       Switch back to Sheet1. In column E, cell E1, add header text SelectedCurrencyValue.

2.       In cell E2, add this formula:

=GETPIVOTDATA("TotalSales",$A$1,"OrderDate",A2,"Territory",C2,"CountryCode", B2)*ExchangeRate

This formula extracts the value of the TotalSales column from the PivotTable, where the OrderDate, Territory, and CountryCode columns match the values contained in cells A2, C2, and B2. In plain English, the formula returns the TotalSales value for the current row.

3.       Since we want to perform this calculation for each row in the table, we need to use this formula in every cell down to the bottom of the table. To do this, type E2:E206 in the Name box, and then press CTRL-D. Alternatively, we can manually select the cells in question and then click Fill | Down from the Editing section of the Home ribbon.

Note:

Using formulae in this manner requires special consideration when the PivotTable referenced will be periodically refreshed. If, during a subsequent refresh, the PivotTable ends up with a different number of rows, the formulae will not automatically be filled down to accommodate the growth of the table. It is important that you ensure that the size of the returned dataset remains constant, and generally this can be done using Transact-SQL (T-SQL) or by calling a stored procedure to produce the required data.

4.       Since we’ll use the data contained in the PivotTable and our calculated column later, we’ll give it a name for ease of reference. Either manually highlight the cells in the range A1:E206 or enter the range in the Names box. Once the range is highlighted, type SourceDataTable. Sheet1 should now look like this:

Figure 9. SourceDataTable

Add a Pivot Chart

Now that we’ve created a data source that can be automatically refreshed by Excel Services, we can move on to create a chart based on the source data. We’ll render the chart on our web page to provide a graphical representation of the sales data.

1.       Select Sheet3. We’ll use this sheet to contain the elements of our workbook that will be rendered on our sample site. Choose Insert | PivotTable | PivotChart.

2.       In the Create PivotTable with PivotChart dialog, type SourceDataTable as the Table/Range:

Figure 10. Create PivotTable with PivotChart

3.       From the PivotTable Field List, drag OrderDate into the Axis Fields section, CountryCode into the Legend Fields section, and SelectedCurrencyValue into the Values section. The field lists should look as shown:

Figure 11. PivotTable Field List

4.       Our chart is automatically generated based on our PivotTable data. However, the default clustered bar chart type doesn’t make it easy to visualize our data set, so let’s change this to something more appropriate. From the Design menu, select the Change Chart Type button. In the Change Chart Type dialog, select the Stacked Area chart type.

5.       To add a title to our chart, select the Chart Title button from the Layout menu. Since we want our chart title to be automatically generated based on the currency code selected, we can add the following formula:

=Sheet2!$B$3

The PivotChart should look like this:

Figure 12. Sum of SelectedCurrencyValue

Publish to Excel Services

The first version of our workbook is now complete and ready to be published to our SharePoint site:

1.       In Excel, click the File menu to enter the backstage area. Select Share from the list of options and then select Publish to Excel Services.

2.       Set the path to http://localhost/Chapter12/Excel Workbooks and the filename to Last30DaysSales.xlsx.

3.      Click to save the file to SharePoint.

 Create a User Interface Using the Excel Web Access Web Part

Now that we have our workbook published to SharePoint, we can move on to make use of it when generating a user interface for our sample application. We’ll customize the homepage of our site to include our sales chart.

1.       Navigate to http://localhost/Chapter12/Default.aspx. From the Site Actions menu, choose Edit Page.

2.       In the Left Web part zone, click Add A Web Part.

3.       Select the Excel Web Access (EWA) web part from the Office Client Applications category. Click Add to include the web part on the page.

4.        To set the properties of the web part, click the Click Here To Open The Tool Pane link.

5.       In the Workbook Display section, type the workbook as /Chapter12/Excel Workbooks/ Last30DaysSales.xlsx.

6.       Since we’re interested only in the chart for now, in the Named Item field type Chart 1. Click Apply to see the results.

We’ve now got our PivotChart on the page ready for use. Let’s tidy up a few of the remaining web part settings to give the page a more integrated look:

1.       Set the Type of Toolbar to None. This will hide the toolbar that appears above the chart.

2.       In the Appearance section, set the Chrome type to None.

3.       Click OK to commit the changes and stop editing the web part.

4.       From the Page ribbon, click the Stop Editing button to switch back to View mode.