Record Grouping and Sorting

When Crystal Reports generates a report that uses fields for grouping and sorting, it has to collect all the data within the client’s computer and process each record. It takes a lot of resources to organize and sort each record as well as perform any necessary summary calculations. This section elaborates on the how-tos of enabling reports to optimize grouping and sorting, the restrictions of doing so, and how to customize the grouping formula during runtime.

Optimizing and modifying a report’s grouping and sorting is very similar to working with the record selection formulas. You want to push down data to the server for processing. You learned in the last section, that for Crystal Reports to push data down to the server, you have to turn the feature on. The same goes for grouping records. Enable the option called “Perform Grouping on Server”. This makes the server do as much of the grouping, summarizing and subtotaling as possible.

You can’t select the option to perform grouping on the server unless the option to use the server for performance is also turned on. If it isn’t selected, the grouping option is disabled.

To turn it on by default, right-click on a report and select Designer | Default Settings. Go to the database tab and click on the “Perform Grouping on Server” option if it isn’t already checked. If you want to set the options for just the current report, right-click on a report and select Report | Report Options. Both options are listed near the bottom of the dialog box. There is also a short-cut just for the grouping option. Right-click on the report and select Database | Perform Grouping On Server. This toggles the grouping option on or off. A check is shown next to the menu option so you can see its current value.

Restrictions on Grouping and Sorting

Having the server perform the grouping has certain restrictions associated with it. These restrictions are as follows:

• The goal of performing grouping and sorting on the server is to reduce the number of records passed back to the client and consequently reduce the amount of processing the client has to do. To make this possible, the report is restricted to printing only the group fields and summary fields. The Details section must be hidden and there can’t be any detail fields in any of the header or footer sections.
• The report derives all its data from a single data source or stored procedure. You can’t have two different data sources linked together.
• Grouping can’t be performed on a formula, and formulas can’t be used in summary fields. If either one of these is true, then all records will be passed back to the client for processing. This probably comes as a surprise since some formulas can be used in a record selection formula and can be passed to the server for processing. It is not the case with grouping.
• Sorting can’t be done using specified order. It is impossible for Crystal Reports to convert the logic required to perform specified order sorting into valid SQL statements. This is always done on the client’s machine.
• Running total fields must be based on summary fields. If a running total is based on a detail field, all the detail fields will be passed to the client to perform the calculation.
• The report cannot use summaries based on Average or Distinct Count.
• The fields that are being grouped must either be the actual database fields or SQL Expressions. SQL Expressions can always be sent to the server because they are built using valid SQL functions. This is discussed in the next section.