After learning the basics of creating simple reports, you will quickly find yourself developing reports that are more involved then just listing sequential records one by one. For reports that consist of dozens, if not hundreds, of pages, providing a meaningful format that groups the data into logical units goes a long way towards making reports easier to read. Crystal Reports makes this possible by giving you the ability to sort and group data. Grouping reports also gives you the ability to create drill-down reports. This chapter starts out by showing you the simple task of sorting records and then builds on that knowledge by creating groups and customizing them.

Sorting Records

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. For example, when you use the phone book to look up a phone number, it would take a long time to find a name if they weren’t listed in alphabetical order. The same applies for the reports you create. Figure 3-1 shows a report sorted by Order ID.

Figure 3-1. Sorting by Order ID.

Controlling a report’s sort order is done with the Record Sort Expert dialog box. To open this dialog box, either click on the Record Sort Expert button shown in Figure 3-2 or select the menu items Report > Record Sort Expert.

Figure 3-2. Record Sort Expert button.

The Record Sort Expert dialog box gives you all the options necessary for selecting the fields to sort and choosing their sort order. It is shown in Figure 3-3.

Figure 3-3. The sort order dialog box.

On the left side of the dialog box are the available fields. The fields are categorized by which fields and formulas are currently being used on the report (since that is most likely your first choice) and after that are all the available fields in the database.

Reports can be sorted on a single field or on multiple fields. Sorting on multiple fields is only useful when the first sort field has duplicate values in it. To resolve which duplicate should be listed first, the value in the second field is used to resolve the conflict. An example of this type of report is an employee report that sorts by name. The first sort field is the last name. When there are duplicate last names (e.g. Smith), the second sort field, which is the first name, is used to resolve the sort order. When sorting on multiple fields, you have to specify which field gets first priority.

To select a field for sorting, either drag and drop it onto the Sort Fields window on the right or click on the arrow buttons to move it over. The order in which you add the fields determines which one gets priority in the sort order. The first field listed becomes the primary sort field. The next field is the secondary field, and so on. When there are duplicate values in one of the fields, then the next field on the list is used to resolve the conflict. This continues for all the sort fields whenever there are duplicates. If a field has a duplicate value without another field after it, then the one that gets listed first isn’t always predictable.

If you find that the fields are not listed in the order you want, you can use the arrows in the top right corner. Click on the field and then click the up or down arrow to change the field’s placement in the list.

The bottom of the dialog box is where you establish whether the field is sorted in ascending or descending order. There are two radio buttons that set the sort order and only one can be selected at a time. Since multiple fields can be shown in the Sort Fields list and there is only one place to set the sort order, both radio buttons apply to the currently selected field. To set the sort order of a field, first click on the field you want and then click on the sort order.

Let’s look at the example sort order shown in Figure 3-4. The primary sort field is Country. The secondary field is Region and this is followed by the Customer Name.

Figure 3-4. Sorting records on Country, Region and then Customer Name.

The report first lists all the countries that start with the letter “A”. The country Australia has duplicate values, so the report performs a secondary sort on the Region field. The region New South Wales is duplicated so the final sort order is by Customer Name.

If you later determine that you need to change the sorting order, modify it using the same steps mentioned earlier.