Displaying Top N Reports
An alternative to standard grouping is to create reports that show the first or last set of records in a certain group. For example, rather than showing all the sales people for the company, you could show the top 5 sales people that have the best sales for the month. Or you can do the opposite and show the 5 sales people with the lowest sales for the month. The first report shows who deserves a bonus and the second report shows who should be talked to about improving their performance. This type of report is called a Top N report.
Generating a Top N report has two requirements. The first is that your report must have at least one group in it. The second requirement is that the group must have a summary field in it (a sub-total, average, etc). The summary field is required because a Top N report uses the summary value to determine the ranking position of each group.
To create a Top N report, select the menu items Report > Group Sort Expert. You can also click on the Group Sort Expert button on the toolbar. If it is grayed out, that means that the report either doesn’t have at least one group section or it doesn’t have a summary field within that group. Correct this and it won’t be grayed out any more.
Figure 3-18. The Group Sort Expert dialog box.
Figure 3-18 shows the Group Sort Expert dialog box. For each group that has a summary field in it, there will be a tab along the top with the group field listed. The tabs give you the option to create different Top N selections for each group. In this figure there is only one tab for Customer ID. So either this is the only group on the report or this is the only group that has a summary field in it.
When the Group Sort Expert dialog box opens, the first property has a setting of All. This is because the group defaults to displaying all values. By clicking on this dropdown box, you can choose from Top N, Bottom N, Top Percentage, or Bottom Percentage. This bases the calculation on either a certain quantity or a percentage of the total records. It lets you select which field to base the comparison on (this must be a summary field), how many groups to show and whether all the non-selected groups should be lumped into a final group called Others (or another name that you specify). If you want to select the number of groups based on the percentage of the total groups, then select that in the dropdown box and N will now represent a percentage.
One item of interest is the Include Ties checkbox. Select this option if you there is a chance that two or more groups will be tied for the same place. If this is checked, then both groups will be displayed. If this box is not checked, then only one of the groups will be displayed and the other won’t be shown. It is not possible to determine ahead of time which group will be the one that gets displayed.
