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 worst 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 this numeric value to calculate how to rank the groups.

To create a Top N report, select the menu options Crystal Reports > Report > Group Sort Expert. If it is grayed out, 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-8. The Group Sort Expert dialog box.

When the Group Sort 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 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 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, 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 Group Sort 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.

Normally, you would use the Change Group dialog box (discussed in the previous section) to set the sort order, the group can only be sorted by the base value of a field in your data source. The Group Sort Expert dialog box shown in Figure 4-8 actually has a dual purpose. If you leave it at the default setting of All, you can have the group sort on its summary values, selecting a summary field in the right-most dropdown box to add it to the listbox below. You can have the group sorted on one or more summary values. By using summary fields to sort your groups, you get a lot more flexibility with how the groups get displayed.