Grouping on Summary Values

The 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 by the group name and think that there is no organization to the report. By making it clear that the summary value is the sort field, the reader will understand the significance of why certain groups appear first in the report.