XL98: Using Server Page Fields in PivotTables

• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel 98 Macintosh Edition, you can use server page fields to reduce the amount of data that is returned from an external data source to a PivotTable. Doing this is useful when you create PivotTables from very large databases.

MORE INFORMATION

When you create a PivotTable that uses an external data source, Microsoft Excel must process all data records that are returned to it from the external data source. If the database contains millions of records, processing all the records may be very time consuming.

However, you can use server page fields to "filter" data before it is returned to the PivotTable in Microsoft Excel; this reduces the amount of time that is required to create the PivotTable. For example, you can use a server page field to return only records in which a specific field is a specific value. For example, you can return only records in which a field named Country is equal to "USA."

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

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

1. On the Data menu, click PivotTable Report.

2. In step 1 of the wizard, click External Data Source. Then, click Next.

3. In step 2 of the wizard, click Get Data. Use Microsoft Query to attach to the external database, select tables, and so on. After the data fields are retrieved, click Next.

4. In step 3 of the wizard, drag the field names into the Column, Row, Data, or Page areas of the diagram.

   You MUST add at least one field name to the Page area; this is the field for which server page fields are created.

5. In the Page area of the diagram, double-click the name of the field for which you want to create server page fields.

6. In the PivotTable Field dialog box, click Advanced.

7. Under Page Field Options, click the "Query external data source..." option. Click OK. Then, click OK again.

8. In step 3 of the wizard, click Next.

9. In step 4 of the wizard, select a destination for the PivotTable. Then, click Finish.

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 updated with the newly retrieved records.

1. Double-click the field name in the page field.

2. In the PivotTable Field dialog box, click Advanced.

3. Under Page Field Options, click the "Retrieve external data for all..." option. Click OK. Then, click OK again.

REFERENCES

For more information about PivotTables in Microsoft Excel, click the Index button in MS Excel Help, type the following text

   PivotTables, overview