Crystal Reports  

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, and so on. Because this kind of group selection is so popular, the program includes the Group Sort Expert for setting it up easily:

There is one other element to consider when selecting the Top N or Bottom N groups: what will you do with the records from the remaining groups — that is, those groups that do not fit your specified Top N or Bottom N criteria? 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 choose either option.

Note   This procedure shows you how to select top or bottom N groups. Top or bottom percentages work the same way except that you define the percentage value instead of the number of groups.

To select the top or bottom N groups

  1. Right-click in the Crystal Reports ActiveX Designer, point to Report and click Group Sort Expert.
    Note   A report must contain a summary value in order to perform a Top N or Bottom N selection. See Summarizing Grouped Data.

    The Group Sort Expert appears with a tab for your group. If you have multiple groups, the program will display a tab for each of the groups.

  2. Select Top N or Bottom N from the first drop-down list.
  3. 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.

  4. 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.
  5. If you want to group all remaining records together in a single group, select the Include Others, with the name option and enter an appropriate name.
  6. Select Include ties to accommodate groups whose summarized values are equal.

    For example, suppose you had the following groups:

    • Order 1 = 100
    • Order 2 = 90
    • Order 3 = 80
    • Order 4 = 80

    If you set your top N to be three, but you do not select "Include ties," your report will show Order 1, Order 2, and Order 3.

    If, in the same scenario, you do select "Include ties," your report will show Order 1, Order 2, Order 3, and Order 4 even though N is set as three. In this way, the program accommodates the equal values of orders 3 and 4.

  7. Click OK.

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

See Also

Adding Percentages to a Report | Enhancing Report Presentation