The information in this article applies to:
- Microsoft Excel 97 for Windows
SUMMARY
In Microsoft Excel 97, you can use server page fields to reduce the amount
of data returned from an external data source into a PivotTable. This is
useful when creating PivotTables from very large databases.
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 millions of records, this could take a
very long time to complete.
However, you can use server page fields to "filter" data before it is
returned to the PivotTable in Microsoft Excel -- reducing 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:
- On the Data menu, click PivotTable Report.
- In the Step 1 of 4 dialog box, click "External data source," and then
click Next.
- In the Step 2 of 4 dialog box, click Get Data. Use Microsoft Query
to attach to your external database, select tables, and so on. Once the
data fields have been retrieved, click Next.
- In the Step 3 of 4 dialog box, drag field names into the Column,
Row, Data, and Page areas of the diagram. You MUST add at least one
field name into the Page area; this is the field for which server
page fields will be created.
- In the Page area of the diagram, double-click the field name for
which you want to create server page fields.
- In the PivotTable Field dialog box, click Advanced.
- In the Page Field Options, click "Query external data source." Click OK
twice.
- In the Step 3 of 4 dialog box, click Next.
- In the Step 4 of 4 dialog box, 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:
- Microsoft Excel queries the external data source again and retrieves
the appropriate data for the selected Page field.
- The PivotTable memory cache is cleared and filled with new records.
- 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:
- Double-click the field name in the Page field.
- In the PivotTable Field dialog box, click Advanced.
- In the Page Field Options, click "Retrieve external data for all."
Click OK twice.
REFERENCES
For more information about PivotTables in Microsoft Excel, click the Index
tab in Microsoft Excel Help, type the following text
PivotTables, overview
click Display, click "PivotTables: Analyzing data interactively", and then
click Display.
|