Displaying Top N Reports
An alternative to the 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 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.
Generating a Top N report has a couple of 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 needs a numeric value to calculate how to rank the groups.
To create a Top N report, right-click on the report and select Report | Top N/Sort Group Expert. If it is grayed out, then that means that you either don’t have a group section or you don’t have a summary field within the group. Correct this and it won’t be grayed out any more.
Figure 4-7. The Top N Expert dialog box.
When the Top N Expert dialog box opens it defaults to a setting of All, for displaying all values. By clicking on this dropdown box, you can choose from a Top N or Bottom N selection basing it on a certain quantity or as 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 remaining 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 the dialog box will stay the same with the exception that the number now represents a percentage.
The Top N Expert dialog box shows a separate tab for every group on your report that uses a summary field. This lets you create different Top N selections for each group.
This dialog box actually has a dual purpose. I quickly skimmed over the fact that the dropdown box defaults to All because it isn’t relevant to printing a Top N report. However, it is useful for creating new ways of sorting your groups. Normally, the group sorting is set via the Change Group dialog box. In this circumstance you can override that by sorting on the value of a group’s summary field. Select the summary fields with the right-most dropdown box. Each summary field you select gets added to the listbox below it. You can change whether the field is sorted in ascending or descending order by clicking on it in the listbox and then selecting the sort order. By using summary fields to sort your groups, you get a lot more flexibility with how the groups get displayed.
