Selecting top or bottom N groups

At times, you might want to show only the "top" or "bottom" groups in a report: the fastest selling product lines, the least productive sales regions, the states that generate the most orders, etc. Because this kind of group selection is so popular, the program includes the Top N/ Sort Group Expert for setting it up easily.

There is one other element to consider when setting up Top N: what to do with all the records from other groups that do not fit the Top N or Bottom N criteria you have set. You need to decide whether to eliminate those records from your report entirely or to lump them all together into a single group. The program enables you to do choose either option.

Note:    A report must contain a summary value in order to be able to perform a top N or bottom N selection. See Summarizing grouped data.

To select the top or bottom N groups

1. Create a report and summarize the data as desired. When you summarize the data, the program breaks the data into groups and summarizes each group.
   • with top N grouping, you are instructing the program to display those groups that have the highest summary values (Top N)
   • with bottom N grouping, you are instructing the program to display those groups that have the lowest summary values (Bottom N).
2. Click the Top N Expert button on the Supplementary toolbar.

   The Top N/Sort Group Expert appears with a tab for your group.

   Note:    If you have multiple groups, the program will display a tab for each of the groups.

3. Choose Top N, Bottom N, or Sort All from the first drop-down list.
4. Choose the summary that you want to base your selection on from the summary drop-down list on the right.

   The summary drop-down list on the right is for those cases in which you have multiple summaries within a single group section. For example, in an orders report, you might sum and average the orders for each customer and then display both the sum and the average in the same group section. In such a case, you would select the sum or the average from this drop-down list.

5. In the where N is text box, enter the number of groups you want to display. For example:
   • to report on the three fastest selling product lines, select the top N option in the Top N Sort Group Expert and set N to be equal to three
   • to report on the five least productive sales regions, select the bottom N option in the Top N Sort Group Expert and set N to be equal to five.
6. Click the include Others, with the name check box and enter a name if you want to group all the other records into a single group.
7. Click OK when finished.

   When the program runs the report, it will include only those groups that you specified.