Use a PivotTable report to make external table data available in Excel Services

If you want to work with table data based on an external data connection in Excel Services, you can't use an external data range (also called a query table). You must create a PivotTable report that flattens multidimensional, hierarchical data into a table, or two dimensions. Here is how to do this.

1. Convert a multidimensional PivotTable report …

2. …to a two-dimensional PivotTable report…

3. …so that you can see flattened table data in Excel Services.

What do you want to do?

Learn how Excel Services supports connections to external data sources

Learn about differences between PivotTable reports that are connected to external data and external data ranges

Make a two-dimensional, tabular PivotTable report

Create the PivotTable report and connect it to the external data source

Add, lay out, and arrange the fields in the Row Labels area

Change PivotTable options and field settings

Lay out the PivotTable report as a two-dimensional table

Customize the design of the PivotTable report

Publish the workbook to Excel Services

Learn how Excel Services supports connections to external data sources

PivotTable reports are designed to aggregate a lot of numerical data into sums and totals, and to work with multidimensional data that is organized into a hierarchy. On the other hand, external data ranges are two-dimensional tables structured as rows and columns, that display nonaggregated records of source data.

When you use the Data Connection Wizard or Microsoft Query to connect to external data, you usually create an external data range. The only exception to this is when you create a PivotTable report that is connected to external data. A PivotTable report does not create an external data range. This difference in the way that connections are created is important to understand when you publish a workbook to Excel Services, because Excel Services only supports external data connections based on PivotTable reports and does not support external data ranges.

Top of Page

Learn about differences between PivotTable reports that are connected to external data and external data ranges

When you work with a PivotTable report that is connected to external data, there are differences between the behavior of a PivotTable report and an external data range that you should be aware of, including the following:

PivotTable reports group and subgroup data in a left-to-right order

In general, grouped data is based on a sort of the data by one or more columns. For example, if you want to see all employees grouped by their departments, you can perform a primary sort of the data by departments and a secondary sort by employees. You can also nest groups, such as Product Line, Category, and Product, or Geography, Country/Region, State/Province, and City. By default, PivotTable reports are automatically sorted into groups and subgroups in a left-to-right order to make it easy to see related data, to display heading and summary information, and to expand or collapse related items.

Data grouped by Product Line, and subgrouped within each Product Line by Category

Product Line	Category	Product
Engines	Airplane	Wing-mount
		Tail-mount
		Booster
	Car	269 HP
		454 HP
Exhausts	Passenger	Extreme-Heat
		Standard
	SUV	Small

By putting a column with unique values in the Row Labels area on the left, you can automatically flatten the data.

The same Product Line data flattened by adding the ID-Num column

ID-Num	Product Line	Category	Product
WM-345	Engines	Airplane	Wing-mount
TM-231	Engines	Airplane	Tail-mount
BSTR-567	Engines	Airplane	Booster
6C-734	Engines	Car	269 HP
8C-121	Engines	Car	454 HP
MF-202	Exhausts	Passenger	Extreme-Heat
MF-321	Exhausts	Passenger	Standard
MF-211	Exhausts	SUV	Small

If you don't want to display the column, after adding the column to the PivotTable report, you can hide the column. (Select the column, and then on the Home tab, in the Cells group, click the arrow next to Format, point to Hide & Unhide, and then click Hide Columns.)

Important: If a PivotTable report detects two or more duplicate rows of data from the data source, the PivotTable report displays only one row. If you want all rows, even duplicate rows, displayed in a PivotTable report, you must add a column that contains unique values to the table, query, or view that you import from the data source. If the data source does not have a unique column, you could add one at the data source. For example, you could add a column that has the AutoNum data type to a Microsoft Office Access table, or a column that has the Identity data type to a Microsoft SQL Server table, or a calculated column that displays a unique number for each record to a database query or view.

Editing cell data and adding calculated columns

Before you publish the workbook to Excel Services you can edit the data in an external data range, but PivotTable data is always read-only.

If you double-click a cell in an external data range, you enter edit mode, and you can change the value or even enter a formula. However, note that you are not changing the original value at the data source, and the next time that you refresh the data, the new value might be overwritten, depending on the setting of the external data range property If the number of rows changes upon data refresh.

If you double-click a Row Label cell in a PivotTable report, the cell expands or collapses the items in that Row Label. When you flatten a PivotTable report into a two-dimensional table, the flattening has the effect of making all of the row values in a group to the left of the cell disappear or reappear.

You can add calculated columns anywhere in a Microsoft Office Excel table that is based on an external data range, or you can insert columns and fill down formulas in an external data range. You cannot insert calculated columns inside of a PivotTable report (although you can insert calculated fields). However, you can add a column that fills down data or formulas to the immediate left or right of the PivotTable report, and inside a filled-down formula, you can use cell references within the PivotTable report.

Creating charts

If you want to create a chart based on the data in the PivotTable report, you can create a PivotChart report, which behaves similarly to standard charts, but there are some differences, including the following:

Interaction     With standard charts, you create one chart for each view of the data that you want to see, but the views are not interactive. With PivotChart reports, you can create a single chart and interactively view the data in different ways by changing the report layout or the detail that is displayed. In Excel Services, both a standard chart and PivotChart report can be updated based on user interaction with the workbook, but both charts are displayed as static images.

Chart types     The default chart type for a standard chart is a clustered column chart, which compares values across categories. The default chart type for a PivotChart report is a stacked column chart, which compares the contribution of each value to a total across categories. You can change a PivotChart report to any chart type except xy (scatter), stock, or bubble.

Chart elements     PivotChart reports contain the same elements as standard charts, but they also contain fields and items that can be added to, rotated, or removed, to display different views of your data. Categories, series, and data in standard charts are category fields, series fields, and value fields in PivotChart reports. PivotChart reports can also contain report filters. Each of these fields contains items, which in standard charts are displayed as category labels or series names in legends.

Formatting     Most formatting, including elements, layout, and style, is preserved when you refresh a PivotChart report. However, trendlines, data labels, error bars, and other changes to data series are not preserved. Standard charts do not lose this formatting once applied.

Moving or resizing items     In a PivotChart report, you cannot move or resize the plot area, legend, chart titles, or axis titles, although you can select one of several preset positions for the legend, and you can change the font size of titles. In a standard chart, you can move and resize all of these elements.

After you create a PivotChart report, if you prefer, you can convert it to a standard chart. For more information, see the article Create a PivotTable to anaylze worksheet data, or Create a PivotChart.

Comparing external data range properties and PivotTable options

External data ranges and PivotTable reports have properties that you can use to control the data formatting and layout, and the refresh behavior of the data.

Both external data ranges and PivotTable reports enable you to adjust column width, preserve column sorting, filtering, cell formatting, and the layout of the data if the external data is refreshed.

In an external data range, you also have the option of including row numbers as the first column of data. In a PivotTable report, there is no similar option. See the section, PivotTable reports group and subgroup data in a left-to-right order, for a discussion about adding a unique column at the data source.

When you refresh an external data range, you can control what happens to existing data, if the number of rows in the data range changes, by setting one of the following options:

• Insert cells for new data, delete unused cells (the default)

• Insert entire rows for new data, clear unused cells

• Overwrite existing cells with new data, clear unused cells

When you refresh a PivotTable report, it just brings in new data. However, you may be prompted and asked to decide whether to overwrite existing cells below the current report.

Filtering data

Filtering data in an external data range and PivotTable report are very similar, but there are some differences, including the following:

• External data ranges use the Text Filter, Number Filter, and Date Filter commands, whereas PivotTable reports use the Label Filter, Value Filter, and Date Filter commands.

• The Filter menu of an external data range always removes items that are no longer valid, and the Filter menu never includes new items with an applied filter after a refresh operation, but a PivotTable report has options to control this behavior. For more information, see the section Change PivotTable options and field settings.

• A PivotTable report has a report filter feature (not available to an external data range) that enables users to work with a subset of data in the PivotTable report.

Sorting data

In an external data range, you can perform a multicolumn sort by up to 64 columns, you can choose any column as the primary sort, and you can choose any other columns as secondary sorts. However, the processes of sorting data in a PivotTable report and sorting an external data range are different. Unlike an external data range, in a PivotTable report you can only perform a primary sort on the left-most column. All other columns perform secondary sorts based on the column immediately to the left. In short, you can only perform a primary and secondary sort in a left-to-right order.

Furthermore, when you place a column with unique values as the first column on the left, you can only sort by that column, because, in effect, you are creating a primary group for each row. Depending on the needs of your users, you may want to presort the data in Excel, and after publishing the workbook to Excel Services, turn off sorting by clearing the Sorting check box in the Microsoft Office Excel Web Access Web Part tool pane.

Using hyperlinks

Unlike external data ranges, active hyperlinks are not supported in PivotTable cells. The hyperlink is treated as text, but you cannot follow the hyperlink to a Web page or document, in Excel or Excel Services.

Top of Page

Make a two-dimensional, tabular PivotTable report

There are a number of steps that are required to flatten multidimensional data into a two-dimensional, tabular PivotTable report. For best results, do the following procedures in sequence.

Top of Page

Create the PivotTable report and connect it to the external data source

1. Select a blank cell.

2. On the Insert tab, in the Tables group, click PivotTable, and then click PivotTable.

   

   The Create PivotTable dialog box is displayed.

3. Click Use an external data source.

4. Click Choose Connection.

   The Existing Connections dialog box is displayed.

5. In the Show list at the top of the dialog box, select the category of connections for which you want to choose a connection, or select All Existing Connections (which is the default).

6. Select a connection from the Select a Connection list, and then click Open.

7. Enter a location. Do one of the following:

   • To place the PivotTable report in a new worksheet starting at cell A1, click New Worksheet.

   • To place the PivotTable report in an existing worksheet, select Existing Worksheet, and then type the first cell of the range of cells where you want to place the PivotTable report.

     Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then click Expand Dialog .

8. Click OK.

An empty PivotTable report, with the PivotTable Field List displayed, is added to the location that you entered.

Top of Page

Add, lay out, and arrange the fields in the Row Labels area

Use the PivotTable Field List to add, lay out, and arrange the fields, and to make sure that all fields are added to the Row Labels area.

1. Click the PivotTable report.

2. Do one or more of the following:

   • To add fields to the report, do one or more of the following:

     • Select the check box next to each field name in the field section in the Field List. Each field is placed in a default area of the layout section, but you can rearrange the fields.

       By default, non-numeric fields are added to the Row Labels area, numeric fields are added to the Values area, and time/date fields are added to the Column Labels area.

     • To move a field into the Row Labels area, right-click the field name, and then select Add to Row Labels on the shortcut menu.

       Tip: You can also click and hold a field name, and then drag the field from the field section to an area in the layout section.

   • To rearrange fields, click the field name in one of the areas, and then select one of the following commands:

Move Up	Moves the field up one position in the area.
Move Down	Moves the field down one position in the area.
Move to Beginning	Moves the field to the beginning of the area.
Move to End	Moves the field to the end of the area.
Move to Row Labels	Moves the field to the Row Labels area.

• To remove a field, click the field name in one of the layout areas, and then click Remove Field, or clear the check box next to each field name that you want to remove, in the field section.

  Tip: You can also click and hold a field name in the layout section, and then drag the field name outside of the PivotTable Field List.

  Note: Clearing a check box in the field section removes all instances of the field from the report.

Top of Page

Change PivotTable options and field settings

To ensure that a PivotTable report behaves similarly to an external range, do the following:

Change PivotTable options    

1. Click the PivotTable report.

2. On the Options tab, in the PivotTable group, click Options.

   The PivotTable Options dialog box is displayed.

3. To make a column width automatically adjust after a refresh operation, click the Layout & Format tab, and then under the Display section, select the Autofit column widths on update check box.

4. To keep formatting every time that the data is refreshed, click the Layout & Format tab, and then under the Display section, select the Preserve cell formatting on update check box.

5. To make sure that the Filter menu removes items that are no longer valid, after a refresh operation, click the Data tab, and in the Number of items to retain per field box, select None.

Change field settings    

1. Click the PivotTable report.

2. To include new items in a PivotTable report with an applied filter, for each field, right-click a cell, click Field Settings on the shortcut menu, click the Subtotals & Filters tab, and then under the Filters section, select the Include new items in manual filter check box.

Top of Page

Lay out the PivotTable report as a two-dimensional table

To lay out the PivotTable report as a two-dimensional table, do the following:

1. Click the PivotTable report.

2. On the Design tab, in the Layout group, click Subtotals, and then click Do Not Show Subtotals.

3. On the Design tab, in the Layout group, click Grand Totals, and then click Off for Rows and Columns.

4. On the Design tab, in the Layout group, click Report Layout, and then click Show in Tabular Form.

5. On the Options tab, in the Show/Hide group, click Field Headers.

Top of Page

Customize the design of the PivotTable report

The built-in PivotTable styles and style options are designed for multidimensional data, not a two-dimensional table. When you flatten the report into a two-dimensional table, these styles do not display cell borders, and the Banded Rows and Banded Columns check boxes in the PivotTable Style Options group of the Design tab do not affect the format of cells. However, you can customize a PivotTable style so that each cell has a cell border and the PivotTable report uses conditional formatting to display banded rows or columns.

Customize the PivotTable style    

1. Click the PivotTable report.

2. On the Design tab, in the PivotTable Styles group, select a style that you want, and then do the following:

   1. Right-click a visible style, scroll through the gallery, or to see all of the available styles, click More at the bottom of the scroll bar.

   2. Click Duplicate.

      The Modify Pivot Table Quick Style dialog box is displayed.

   3. Optionally, enter a new name in the Name box.

   4. In the Table element box, select Whole Table, and then click Format.

      The Format Cells dialog box is displayed.

   5. Click the Border tab, and then create a cell border.

   6. Optionally, click the Fill and Font tabs, and make other changes.

   7. Click OK twice.

   8. Click the More button at the bottom of the scroll bar, and then under the Custom category, click the PivotTable style that you just created.

Display banded rows or banded columns by using conditional formatting    

1. Select all of the columns in the PivotTable report.

2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

   

   The Conditional Formatting Rules Manager dialog box is displayed.

3. To add a conditional format, click New Rule.

   The New Formatting Rule dialog box is displayed.

4. Under Select a Rule Type, click Use a formula to determine which cells to format.

   1. Under Edit the Rule Description, in the Format values where this formula is true box, enter a formula.

   2. Do one of the following:

      • To band rows, enter the following formula:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A))

• To band columns, enter the following formula:

=AND(MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

• To band rows and columns, enter the following formula:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A),MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

1. Click Format to display the Format Cells dialog box.

2. Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK.

   You can choose more than one format. The formats that you select are displayed in the Preview box.

Top of Page

Publish the workbook to Excel Services

1. Click the Microsoft Office Button , click the arrow next to Publish, and then click Excel Services under Distribute the document to other people.

2. In the File name box, enter the path to the server, and accept the suggested name for the workbook or type a new name if needed.

   On a computer that is running Windows Vista    

   • In the Address bar, select a network location that is accessible to the intended users, and then click Save.

     On a computer that is running Microsoft Windows XP    

   • In the Save in box, select a network location that is accessible to the intended users, and then click Save.

     For example, type http://server/site/file name

     Note: Excel can publish a workbook to the server in only the Microsoft Office Excel 2007 XML-based file format (.xlsx) or Office Excel 2007 Binary file format (.xlsb).

3. Click OK.

4. To verify that the viewable areas of the workbook are displayed correctly in the browser, select the Open this workbook in my browser after I save check box.

5. Click Save.

For more information, see the article Publish a workbook to Excel Services.

Top of Page