Microsoft Office Tutorials and References
In Depth Information
is to create in Excel one range that contains the consolidation of those smaller ranges, and base the
PivotTable on the consolidated range.
In Excel, a PivotTable can be based on a range of data from a different workbook. This feature is not
supported on the server. One workaround for you to consider is to unite these two workbooks into one. If
that is not feasible, you could write a UDF that brings data from the second workbook into a range in the
current workbook, and base the PivotTable on the results of the UDF.
PivotTables with server-based page fields are not supported on the server. In relational PivotTables,
server-based page fields allow you to query the data for the current page field value. When this feature
is on, every change of the filter value will query the database, but each of these queries will be smaller.
To make these PivotTables work on the server, you can disable server-based page fields.
Query tables allow you to display the results of a relational database query in a tabular format. In
addition, query tables allow you to add additional calculated columns (in the middle or to the side of the
table) that grow as new rows are added to the table when the data is refreshed.
To create a query table, select the Data tab on the Excel Ribbon. Click From Other Sources, select From
Data Connection Wizard, and create a new connection. After saving the connection, select the Table
option in the Import Data dialog box to use the connection as a query table.
Excel Services does not support query tables. To create a relational PivotTable that behaves similarly to
the query tables, follow these steps:
Create a relational PivotTable that uses the same connection as the query table.
Add all the relevant fields to the row labels.
In the Design tab of the Excel Ribbon, click Report Layout, and select Show in Tabular Form.
In the Options tab of the Ribbon, click Options, select the Display tab, and uncheck Show
For each field, right-click the field, select Field Settings, and choose None for the subtotal.
By following these steps, you can create a PivotTable that has a very similar look and feel and
functionality to a query table. However, you will not have the ability to create a calculated column that grows and
shrinks with the table. You can approximate this behavior by using Excel formulas that return an empty
value if the value in the respective row is blank. In addition, with the PivotTable, you will lose some of
the sorting and filtering functionality that exists in the query table.
With Excel tables based on SharePoint lists, you can query data from SharePoint directly into your Excel
sheet. The easiest way to create this type of workbook is to open the SharePoint list in the browser, click
Actions, and then select Export To Spreadsheet. In Excel, you can choose to display the data as a
PivotTable or as a table.
Excel Services does not support PivotTables or tables based on SharePoint lists. However, you can write
a UDF that brings the data from SharePoint into your workbook.