Chapter 3 - Sorting and Grouping
Being able to sort records in either ascending or descending order is a fundamental reporting skill. Sorting makes it easy for a user to quickly find a particular piece of data buried within a large report. This chapter has ten tutorials that quickly get you up to speed on every aspect of sorting and grouping data.
Displaying Top N ReportsAn 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 5 sales people that have the best sales for the month. Or you can do the opposite and show the top 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 this numeric value to calculate 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, then 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-j. The Group Sort Expert dialog box.Figure 3-j shows a single tab for the Customer Id field. But if the report has multiple groups with summary fields, then each group gets a separate tab. This gives you the option to create different Top N selections for each group.
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 a Top N, Bottom N, Top Percentage, or Bottom Percentage. This bases the calculation on either 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 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 displayed.
Grouping on Summary ValuesThe Group Sort Expert dialog box actually has a dual purpose. In the previous section I quickly skimmed over the fact that the dropdown box defaults to the value All so that all groups are displayed. But this setting is also useful for creating new ways of sorting your groups.
Normally, a group is sorted based on the Group Name field (as specified in the Change Group Options dialog box). However, the Change Group Options dialog box doesn't give you the ability to sort groups based upon a summary value. For example, you might want to sort your customers based upon the total current year's sales. The Group Sort Expert lets you do this by selecting a sort type of All and then choosing the summary value to sort on. Effectively, you are no longer sorting on a text field and instead sorting on a numeric field (the summary value).
The summary value to sort on is selected using 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.
|If you decide to sort a group on a summary value rather than the group field, you should show the summary value in the group header. Otherwise someone reading the report will see that the values in the group field aren't sorted and think that there is no organization to the report. By making it clear that the summary value is the sort field, then the reader will understand the significance of why groups appear first in the report.|
To read all my books online, click here for the Crystal Reports ebooks.