In Chapter 2, you saw how to build a report using the different report objects and tie it into a database. For creating simple reports this is all you need to know. But you will quickly find yourself developing reports that require more effort than listing 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 will go a long way towards making your reports easier to read. Crystal Reports .NET 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 and summarize data. All of these features are covered in this chapter.

Sorting Records

Being able to sort records in either ascending or descending order is a reporting fundamental. Sorting makes it easy for a user to quickly find a particular piece of data buried inside many pages of data.

Reports can be sorted on a single field or on multiple fields. When sorting on multiple fields, you have to specify which field gets sorted first. When there are duplicate values for the first field, the next field is used to resolve which record gets listed first. An example of this type of report is an employee report that sorts by name. The primary sort field is the last name. When there are duplicate last names, the secondary sort field (first name) is used.

Using the Record Sort Expert dialog box makes sorting records easy (shown in Figure 4-1). To use this dialog box, select the menu options Crystal Reports > Report > Record Sort Expert. On the left is the standard tree-view control listing the available fields. You can sort on fields from the report’s data sources or your own custom formulas.

Figure 4-1. The record sort dialog box.

To select a field, either drag and drop it to 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, the next field on the list is used to resolve the conflict. This continues through all the sort fields as long as there are duplicates at each level.

If you wish to change the order that the fields are listed in, use the arrow keys in the top right corner. First, click the field you want to move, then click the appropriate arrow keys.

At the bottom of the dialog box is where you set whether the field is sorted in ascending or descending order. Each field is treated individually. First click on the field name and then click on the sort order.

As an example, let’s look at the report shown in Figure 4-2. The primary sort field is the country. The secondary field is the region and this is followed by the customer name.

Figure 4-2. The multiple field sort report.

The report first lists all the countries that start with the letter “A”. The country Australia has multiple records, so the report performs a secondary sort on the Region field.

If you later determine that you need to change the sorting order, you can modify it by using the same steps mentioned earlier. Just right-click on the report and select Report > Record Sort Expert.