Optimizing Grouping and Sorting

The GROUP BY and ORDER BY keywords specify how to group and sort the records. Just like performing record selections, this is a time intensive process that is best done on the database server. This section elaborates on the how-to of enabling reports to optimize grouping and sorting and the restrictions of doing so.

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 for all reports, select the menu items File > Options. 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, select the menu options File > Report Options.

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 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.

If you want to see a summary of the settings that affect whether grouping and sorting is performed on the server and where record selection is performed, you can see this in the Performance Information dialog box. Open the Performance Information dialog box by selecting the menu items Report > Performance Information. Then click on the Processing node. It is shown in Figure 11-1.

Figure 11-1. Viewing processing information with the Performance Information dialog box.