Selecting Records for Grouping

The Select Expert is used for selecting records based upon the value of one or more fields in each record. Thus, each record is looked at individually to determine whether it should be used in the report or not. Crystal Reports also lets you filter data based upon group values. In this case, records are not selected based upon their individual values but are selected according to whether the group they belong to is selected.

Selecting records based on grouping data is slightly different than what you’ve seen so far. When selecting records based on data at the grouping level, you have to use summary functions. For example, consider a report that prints the individual product sales and groups the data by customer. In the group footer, it prints the total sales for each customer. To show all the customers that have a minimum purchase level you have to use the Sum() function in the select expert. Otherwise, it will use the individual purchase records to perform the comparison and the results won’t be accurate.

It’s easy to create a filter based on grouping data. When selecting the field to filter on, look in the Choose Field dialog box at the end of the list of the report fields. The grouping fields are listed last and they are identified by their group number and the summary function. Choose one of these as the field to group on.

It’s helpful to realize how filtering on grouping data is different than filtering on database fields. As mentioned in Chapter 1, Crystal Reports uses a two-pass system of processing data. In the first pass the individual records are processed and summarized. This is where the non-grouping selection formulas are used to filter out the raw data. It is in the second pass that all the summary calculations have been performed and their totals are known. This is where the selection formulas based on summary data are used to filter out groups of records.