Retrieve PivotTable data one report filter item at a time

1. Make sure the PivotTable and PivotChart Wizard command is on the Quick Access Toolbar. If you don't see it, follow these steps:

   Where is the Quick Access Toolbar?

   1. Click the arrow next to the toolbar and then click More Commands.

   2. Under Choose commands from, select All Commands.

   3. In the list, select PivotTable and PivotChart Wizard, click Add, and then click OK.

2. Now click the PivotTable and PivotChart Wizard icon on the Quick Access Toolbar.

3. On the Step 1 page of the wizard, select External data source, and then click Next.

4. On the Step 2 page of the wizard, click Get Data.

5. Connect to a data source.

   For more information about how to connect to an ODBC data source, see Use Microsoft Query to retrieve external data.

6. On the Step 3 page of the wizard, click Layout.

7. In the Layout dialog box, drag one or more fields to the Report Filter area.

8. For each report filter field, do the following:

   1. Double-click the field.

      The PivotTable Field Advanced Options dialog box appears.

   2. To retrieve only up-to-date data from the data source every time that you select a new item in a report filter, click Query external data source as you select each report filter field item (requires less memory).

      To prevent a report filter field from being moved to the Row Labels, Column Labels, or Values areas, select the Disable pivoting of this field (recommended) check box.

      To return to the default operation, retrieving all the data from the data source every time that you select a new item in a report filter, click Retrieve external data for all report filter field items (faster performance).

You can configure a report filter to retrieve selected data from the data source of a PivotTable by using the PivotTable Field Advanced Options dialog box in the PivotTable and PivotChart Wizard. By default, Microsoft Office Excel retrieves all the source data for a PivotTable, which includes data that is filtered out (and thus not displayed in the report), when you create or refresh the report. When you configure a report filter to retrieve data only for the item that you specify in the filter, you can retrieve smaller amounts of data on an as-needed basis. Each time that you display a different item in the filter, only the subset of the data for the new item is retrieved. You can configure as many report filters as you want and then apply them as needed.

How performance is affected

When a report filter is configured to retrieve data for one or more (but not all) items, the (All) item summarizing the complete set of data is unavailable (dimmed), and the Show Report Filter Pages command (on the Options tab, in the PivotTable group, when you click the arrow next to Options) is also unavailable. Each time that you select a different item, you retrieve less data, but you still have to wait for the data to be retrieved. If your computer becomes disconnected from the external data source, you will not be able to select a different item until you can reconnect to the data source. However, this option does let you work with much larger amounts of data than you could otherwise.

If you retrieve all the data for all the items, the initial data retrieval operation may take longer, but you can then select new items in the report filter without a delay because all the data that you need is loaded into the memory on your computer. You can also open a summary of the complete set of data by selecting the (All) item and reorganize the report to use the report filters in other areas. The (All) option gives you the most versatility when the total amount of data to be retrieved is within your computer's available memory limits.

Moving report filters

If you drag a report filter that is configured to retrieve all data for all items to another position, for example if you try to make it a row label, Excel tries to retrieve the data for all the items, and you might run out of computer resources. However, you can prevent a user from doing this. For more information, see the section Retrieve report filter data for each item or for all items.

Retrieving data for items when a report filter isn't available

This ability to use this feature is not available under the following circumstances:

• Reports based on source data from OLAP databases automatically query for data on an as-needed basis, and so do not use this feature.

• The PivotTable is sharing the data cache with one or more other PivotTables.

• The ODBC driver for the external database you're querying does not support parameter queries. The ODBC drivers that are supplied with Microsoft Office all support parameter queries. You can contact third-party driver vendors to determine whether their drivers have this support.