Microsoft Office Tutorials and References
In Depth Information
PivotTables play an important role in Excel Services. In addition to providing core interactivity, they are
also the primary way to access external data that resides in databases and OLAP cubes. Excel Services
supports most of the PivotTable sorting operations (as well as expanding and collapsing levels) to
navigate through the data. You cannot, however, pivot fields in the browser, or add or remove fields to or
from the PivotTable.
Excel Services supports PivotTables on data that resides inside the workbook, relational PivotTables
connected to databases, and OLAP PivotTables connected to OLAP cubes. The basic interactivity option
supported for all of these is refreshing the PivotTable so that it reflects the latest values. This could mean
querying the database or cube for updated values, or refreshing against the source data in the workbook.
The latter is relevant when the source data may change (for example, in cells containing volatile
formulas such as RND or Time , cells that are hooked up to user-defined functions, or cells that are also
In addition to refreshing the PivotTable, you can expand and collapse levels, and you can sort and filter
the data, directly in the browser. However, some sorting and filtering options that are available in the
client are not available on the server in the browser. The primary available features are as follows:
Expanding and collapsing levels
Filtering specific values
Number, Name, and Date filters
The browser does not support the Actions feature in OLAP PivotTables, or the Drill-Through feature in
either OLAP or non-OLAP PivotTables.
In the following Try It Out, you use the sample workbook to interact with a PivotTable that is based on
data in the workbook. You learn how to refresh, expand, collapse, sort, and filter PivotTable data while
in the browser. First, you expand all the years in the PivotTable, then you filter to show only the first
quarters for each year, and then you filter to show the top three regions in the first quarter.
In Chapter 12, you work with an OLAP PivotTable connected to SQL Server Analysis Services.
Try It Out Using PivotTables
To use a PivotTable, follow these steps:
Open the original Sales Report.xlsx workbook in Excel client and publish it to the server. Ensure
that you have the Open In Excel Services option selected so that the browser opens with the
workbook displayed. If you already published the workbook, you can simply open it in the
browser directly from the document library on the server.
Switch to the Regional Sales Pivot sheet shown in Figure 10-10.