Limiting Data with a Group Selection Formula

When you use the Select Expert or create a record selection formula with the Formula Editor, you affect the way Crystal Reports initially selects data from the database. Record selection occurs during the first pass of the report, before data has been sorted or grouped. Because of this, you can t use record selection to limit your report, say, to groups where the total sales exceeds $100,000 ”the record selection occurs before these totals are calculated (see Chapter 5 for a discussion of report passes ).

You may also want to use an existing report formula in record selection. However, if you use the WhilePrintingRecords function or a summary function in the formula, it will evaluate in the report s second pass and won t show up in the Field Tree box when you create a record selection formula. Again, the record selection occurs during the first pass, and second-pass formulas can t be used.

If you want to limit the report according to group subtotals or summaries, or somehow limit the report using second-pass formulas, you must use a group selection formula instead of a record selection formula. You may actually create a group selection formula inadvertently and not even know it. If you use a subtotal or summary field you create on your report in the Select Expert (perhaps you choose the Sum of Customer.Last Years Sales instead of the Customer.Last Years Sales database field itself), you will be using group selection instead of record selection.

You may also create a group selection formula from the Select Expert by clicking Show Formula and then clicking the Group Selection radio button. If you want to make use of a typed-in formula, you have two choices: Select Report Selection Formulas Group from the pull-down menus or Group Selection in the Selection Formulas category of the Formula Workshop. You can now create a Boolean formula to limit records using group summaries or second-pass formulas.

One word of caution: Group selection occurs after the group tree, subtotals, and grand totals have been calculated. This can lead to apparent inaccuracies on your report. For example, look at the report shown in Figure 8-1.

Figure 8-1: Report using group selection

You ll notice that the group tree shows many more regions than actually appear on the report. And, it doesn t take a math degree to see that the grand totals don t quite add up. Don t forget that selection of a summary or subtotal field in the Select Expert will create a group selection formula instead of a record selection formula. You may see this kind of odd behavior and not fully understand why. Look back at the Select Expert to see if your selection is based on a subtotal or summary field.

This report applies a group selection formula to limit the report to groups where the sum of Last Year s Sales exceeds $250,000. This group selection is applied after the group tree and grand totals have been created. Although there is no way to change the group tree in this situation, you can correct the totaling problem by using running totals instead of grand totals. Look at Chapter 5 for information on running total fields.