Advanced Tutorials

Tutorial 4-6. Selecting Records Using the ‘Or’ Operator

If you recall from the discussion about using the Select Expert dialog box with multiple fields, Crystal Reports builds the selection formula for you automatically. When there is more than one field used, each field’s selection criteria must be met for the record to be selected. For example, if the first criteria is Country=’USA’ and the second criteria is Last Years Sales>25,000, then a record has to meet both conditions for it to be printed. If the Country field is ‘USA’ but Last Year’s Sales were only 10,000, then it won’t be printed.

There will be times when your report needs to display records that meet ANY of the conditions. If only one of the two conditions is met, then the record should still be printed. To do this, you have to use the OR operator to join the conditions.

For example, let’s take the formula from Tutorial 4-1. When you clicked on the Show Formula button, it displayed the formula

{Customer.Country} in [“Canada”, “England”, “USA”] And
{Customer.Last Year’s Sales} >= $25000.00

Notice the And operator at the end of the first line of code. This requires both conditions to be met. To fix it so that only one of the two conditions need to be met, manually change the And to Or and save the formula. Now when you preview the report, there will be a lot more pages. There will be a lot more records that only need to match one of the criteria, rather than both.

The benefit to using the Show Formula button is that you can create very complex formulas for performing record selection. You can have formulas that use a combination of And and Or operators as well as using custom functions you created yourself.

Be aware that once you enter a custom formula, you can’t use the Select Expert prompts for modifying the record selection anymore. Since your custom formula is too complex to be represented by the Select Expert, the prompts are disabled.