Chapter 3: Sorting and Grouping

When you first create and preview a report, the report shows details sections in natural order. That is, the records appear in whatever order the database sends them to Crystal Reports . This order can vary widely, depending on what database you are using, how you link tables, and the actual order in which records were entered into the database.

You ll probably want to control the order in which information appears on your report. An employee listing, for example, isn t very useful if it isn t in alphabetical order. A sales breakdown is probably more helpful if you see your sales figures in a particular order. You re probably interested in a highest-to- lowest sequence of order quantities if you re about to send gifts to your best customers at the holidays. On the other hand, lowest-to-highest sequence is probably more appropriate if you re about to send your marketing department out to talk to lower-performing customers.

Sorting Your Report

If you simply want to reorder your report s details sections into a particular order, you need to sort the report. Sorting is useful for simple reports that need to present information in a certain order. To sort a report, choose Report Record Sort Expert from the pull-down menus , or click the Record Sort Expert button on the Expert Tools toolbar. This displays the Record Sort Order dialog box.

The left Available Fields box shows all the report fields that you ve placed on the report, followed by a list of all tables and fields you initially added to the report. If you wish to sort on a field you haven t placed on the report, scroll down through the Report Fields list until you find the list of tables and fields you added to your report. Choose one or more fields to control how you want your report sorted. Simply double-click the field you wish to sort on, or select the field and then click the right arrow button. Either method moves the field to the Sort Fields box on the right. You may then choose between the Ascending and Descending radio buttons to choose the sort order.

An ascending sort orders the records alphabetically , A to Z; if the records start with numbers , they will appear before any letters , sorted 0 through 9. In a descending sort, the order is reversed . Records appear in Z to A order, followed by any numbers in 9 to 0 order.

You may sort by as many fields as you like ”you re not limited to one. This is handy, for example, if you want to sort your customers by state, and within state by customer name . Simply add the additional sort fields to the Sort Fields box and choose the Ascending or Descending radio button for each field. The first field in the list will be the primary, or first, sorting field, the second field will be secondary (will be used to sort multiple records that have the same primary field), and so forth. If you wish to change the order of sorting priority, select a field in the Sort Fields list and click the up or down arrows above the list to reorder the sort fields. When you click OK, Crystal Reports will sort by the higher (primary) field first, and then the lower fields.

Tip  

You can sort just as easily on formula fields (covered in Chapter 5) as you can on database fields. This is often required if you want to customize some aspect of the way records are sorted. Just remember that if you re using a SQL database, such as SQL Server or Oracle, sorting on a formula field makes Crystal Reports sort the records instead of having the database server do the work, which may affect the performance of your report (albeit slightly). This is covered in more detail in Chapter 16.