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