Usually Excel retrieves all the data for a report, including data for pages that aren't currently displayed, when you create or refresh the report. When you set up a page field to retrieve data only when you display a new page, you retrieve smaller amounts of data on an as-needed basis. Each time you display a different page field item, the subset of the data for the new item is then retrieved.
You set this option for each page field individually. If you're trying to accommodate a very large amount of data in a report, you can set up as many page fields this way as you need.
When a page field is set to query page-by-page, the All item summarizing the entire set of data is not available, and the Show Pages command is unavailable. Each time you display a different page, you'll have to wait for the data to be retrieved. If your computer becomes disconnected from the external database, you won't be able to display a different page field item until you can reconnect to the source data. However, this setting does allow you to work with much larger amounts of source data than you could otherwise given your system's limits.
If you query for all the pages at once (the default), this initial retrieval may take longer, but you can then change pages with no delays, because all the data you need is now in memory. You can also display a summary of the entire set of data (the All item) and reorganize the report to use the page fields in other areas. This setting gives you the most versatility when the total amount of data to be retrieved is within your system's limits.
What happens if you move a page field
If you drag a page field that is set to query page-by-page to another position, for example if you try to make it a row field, Excel tries to retrieve the data for all the items, and you may run out of system resources.
When the page-by-page option isn't available
Reports based on source data from OLAP databases automatically query for data on an as-needed basis, and so don't have this option.
The page field must not have any any grouped items; ungroup them to make the option available.
If more than one PivotTable or PivotChart report is based on the same report (you used the Another PivotTable report or PivotChart report option in the wizard), page-by-page retrieval isn't available in any of the reports. For a single PivotChart report and its associated PivotTable report, however, the option is available.
If the ODBC driver for the external database you're querying doesn't support parameter queries, you can't query page-by-page. The ODBC drivers supplied with Microsoft Query all support parameter queries, and third-party driver vendors can tell you whether their drivers have this support.