The 80/20 rule often applies to a business dataset. Perhaps 80% of the revenue comes from 20% of the customers. You may find that beyond the top 10 or 20 customers, the report contains many tiny customers who are just buying a few dollars of repair parts. Your VP of Sales probably has the attention span to look at a one-page report of the top 20 customers, but may ignore a 10-page report with 500 customers on it.
Luckily, pivot tables make it easy to filter the report to the top 5, bottom 10, or any conceivable combination of top or bottom records.
Figure 5.29 shows a report of copier sales by store. There are 128 stores in the report.
Figure 5.29. This summary report will span four printed pages and contains too much detail for a high-level manager.
Top 10 reports are controlled on the Advanced Options dialog box. Double-click on the Store field to display the PivotTable Field dialog box. Then click the Advanced button to display the PivotTable Field Advanced Options dialog box. The Top 10 AutoShow settings appear on the right side of this dialog box.
Initially, the AutoShow settings are disabled. After you choose the option button to turn on the AutoShow feature, the three controls are enabled. The Show drop-down allows you to choose Top or Bottom. The spin button allows you to select a number between 1 and 255. The Using Field control will be used to limit the Store field to the top 15 records using the Revenue field. Figure 5.30 shows the completed dialog box.
Figure 5.30. The Top 10 AutoShow settings allow you to limit a report to just the top or bottom records.
As you can see in Figure 5.31, the resulting report is now limited to the top 15 stores. The Store field is now shown in blue to indicate that the Store list has been filtered using the AutoShow feature. Note that using AutoShow does not automatically sort the report. You have to use the AutoSort feature in order to sort the report. Also, note that the grand total at the bottom of the report reflects the total for only the 15 customers shown.
Figure 5.31. Using the AutoShow option to produce a report of just the largest stores.
As you change the page field, the list of top stores will automatically change.
Remember that if you manually filtered the Store field and then moved it to the page field, the report would continue to be filtered for the matching stores. This technique does not work with fields filtered using AutoShow. Move the filtered Store field to the page area, and all the stores become selected again.