Microsoft KB Archive/211515

= Using server page fields in PivotTables in Excel =

Article ID: 211515

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q211515





For a Microsoft Excel 97 version of this article, see 157488.



SUMMARY
This article describes how to use server page fields to reduce the amount of data returned from an external data source to a PivotTable. This is useful when creating PivotTables from very large databases.

NOTE: This article does not apply to Online Analytical Processing (OLAP) databases, nor to any third-party ODBC drivers or data source drivers that do not support parameter queries.



MORE INFORMATION
When you create a PivotTable using an external data source, Microsoft Excel has to process all data records returned to it from the external data source. If your database contains a large number of records, this may take a very long time to complete.

You can use server page fields to filter data before it is returned to the PivotTable in Excel to reduce the amount of time required to create the PivotTable. For example, you can use a server page field to return only records where the Country field is equal to "USA."

Using server page fields also helps reduce the amount of memory required to cache information used by the PivotTable.

To use server page fields in a PivotTable, follow these steps:


 * 1) On the Data menu, click PivotTable and PivotChart Report.
 * 2) In step 1 of the wizard, click External data source and then click Next.
 * 3) In step 2 of the wizard, click Get Data. Use Microsoft Query to attach to your external database (this can be any external database and is not limited to databases on a server). Click OK.
 * 4) Follow the steps in the Create New Data Source dialog box. After the data fields have been retrieved, click Next.

The Query Wizard is now open.
 * 1) In the Query Wizard, set up the filters that you want, if any. Click Finish to return to the PivotTable and PivotChart Wizard.
 * 2) In step 2 of the PivotTable and PivotChart Wizard, click Next.
 * 3) In step 3 of the PivotTable and PivotChart Wizard, click Layout. Drag field names into the Column, Row, Data, and Page areas of the diagram.

NOTE: You must add at least one field name to the Page area; this is the field for which server page fields are created.
 * 1) In the Page area of the diagram, double-click the field name for which you want to create server page fields.
 * 2) In the PivotTable Field dialog box, click Advanced.
 * 3) Under Page field options, click Query external data source and then click OK three times.
 * 4) In step 3 of the wizard, select a destination for the PivotTable, and then click Finish.

After your PivotTable has been created, you can click the drop-down list in the page field to retrieve data for the selected item. Each time you do this, Microsoft Excel performs the following steps:
 * 1) Microsoft Excel queries the external data source again and retrieves the appropriate data for the selected page field.
 * 2) The PivotTable memory cache is cleared and filled with new records.
 * 3) The PivotTable is refreshed and redisplayed using the newly retrieved records.

Because of the way in which server page fields work, the (All) item is removed from the drop-down list. This is because only a subset of the data in the external data source is being returned to Microsoft Excel.

If you want to show all records, follow these steps:
 * 1) Double-click the field name in the page field.
 * 2) In the PivotTable Field dialog box, click Advanced.
 * 3) In Page field options, click Retrieve external data for all page field items (faster performance). Click OK twice.

