Sorting, Grouping, and Totaling

Sorting, grouping, and totaling are the steps that turn disorganized data into useful information on a report. This section describes the types of sorting, grouping, and totaling that you can do in a report.

8.1 Sorting data

When you first insert a result object into your report, the data within the objects appears in an arbitrary order.

However, you can use the Sorting feature to sort data into a logical format.

8.1.1 Understanding sort options

When you sort, Crystal Reports asks you to define two things: the sort direction (ascending or descending), and the object you want the sort to be based on (sort object).

Sort objects determine the order in which data appears on your report. Almost any object can be used for sorting, including formulas. An object's data type determines how the data from that object is sorted.

Note

You cannot sort on memo or BLOB objects.

Object Type Sort Order

Single-character string objects blanks

punctuation numbers uppercase letter lowercase letters

Multiple character string object two letters

three letters

four letters, and so on For example:

● "123" comes before "124"

● " " (blank) comes before "a"

● "aa" comes before "aaa"

Currency objects numeric order

Object Type Sort Order

Number objects numeric order

Date objects chronological order

DateTime objects chronological order

same-date values sorted by time

Time objects chronological order

Boolean comparison objects False values (0)

True values (1)

Null values null values

non-null values

Note

If sorting and grouping are performed on the data source server, the sort order may vary when Unicode or UTF-8 data is used. The order applied depends on the rules in place for your data source. In certain cases, Unicode data is sorted by its binary value, but it can also be sorted according to a particular locale setting.

Refer to the documentation for your data source for details on how Unicode data objects are sorted.

8.1.2 How to sort your data

In single object sorting, all records used in the report are sorted based on the values found in a single object.

Sorting an inventory report by stock number or sorting a customer list by customer number are examples of single object sorts.

In multiple object sorts, Crystal Reports first sorts the records based on the values in the first object selected, putting them in ascending or descending order as specified. If two or more records have a matching object value in the first sort object, the matching records are sorted by the value in the second sort object.

For example, if you choose to sort first by <Country> and then by <Region>, both in ascending order, the report would appear with countries listed in alphabetic order, and regions within each country listed in alphabetic order.

Any other objects, such as the postal codes within each region, would remain unsorted.

You create single or multiple object sorts using the same procedure.

8.1.2.1 To sort your data

1. On the Data tab, click Sorts.

The Groups and Sorts dialog box appears.

2. Expand the Body area by selecting the Body tag and opening the list menu, and click Add Sort.

3. From the Body list, select the object you want your data sorted by.

4. Click Sort to switch from ascending to descending sort order.

5. Repeat steps 2 to 4 to add additional sort objects.

Note

The data is sorted in the order that the objects are listed. You can change the order of multiple sort objects by moving them up or down in the list.

6. Click OK.

8.1.3 Sort controls

You may want to design your report so that users can modify a sort object or sort direction without refreshing information from the data source. You can do this by using a sort control.

A sort control is useful for several reasons:

● It allows users to sort report data for further analysis without leaving the canvas of the report.

● It eliminates processing demand on the data source.

● It reduces the time spent waiting for data to be sorted.

● It allows users to sort objects in the report with or without a connection or rights to access the data source at the time of viewing.

When planning to include a sort control in your report, it is important to consider the following points:

● Sort controls apply across an entire record set; you cannot sort only one group.

● Sort controls cannot be used in subreports.

● Sort controls cannot be created within a Cross-tab.

There are two ways to insert a sort control on your report: you can insert a sort control as a separate element on the report, or you can bind the sort control to an existing element.

8.1.3.1 To create a sort control

You can bind a sort control to an existing element in the Page Header section of the report canvas. For example, if you are creating a sort control on the <Customer.LastName> sort object, you may want to bind the sort control to the <Customer.LastName> object header. The sort control will then be applied to all data retrieved by that object.

1. In the Page View, open the Page Header section, right-click the object that you want to bind the sort control to (such as the customer field), and then select Bind Sort Control.

The Sort Control pane appears. The Sort Control pane lists the sort objects you have added to the report.

2. From the list, select the sort object to which you want to bind a sort control, and then click OK.

3. Repeat these steps for any additional sort controls you want to add.

Tip

You can also insert a sort control as a separate element on your report through the Insert tab. The sort control will not be bound to an element that already exists on the report, such as a group header.

An interactive sort control is added to the report.

Users can sort the objects you have selected by clicking the sort arrows that appear beside the element. The report promotes the selected object temporarily to the top of the sort order. When a second sort control is used, the first is returned to its original position in the sort order and the second is promoted to the top sort.

Note

Grouping hierarchy is not affected by sort controls.

8.1.3.2 To remove a sort control

A sort control can be removed only through the Bind Sort Control dialog box.

1. Right-click the text object that contains the existing sort control.

2. Click Bind Sort Control.

The Bind Sort Control dialog box appears.

3. Click Not Interactive.

4. Click OK.

The sort control is removed from the report.

8.2 Grouping data

Grouped data is data that is sorted and separated into meaningful groups. For example, a customer list may be grouped by <Postal Code> or <Region>. In a sales report, a group might consist of all orders placed by a particular customer or orders generated by a particular sales representative.