Manipulating the Record Selection Formula Directly

When you create record selection criteria with the Select Expert, it actually creates a formula using the Crystal Reports formula language behind the scenes. For most simple record selection criteria, you won t have to worry about manipulating this formula directly. Also, by using the Select Expert directly and not manipulating the actual formula that it creates, you will often maximize performance, particularly when using SQL databases.

However, there are times when the Select Expert itself won t provide enough flexibility for the record selection you need to accomplish. Consider the following scenario ”you have two fields on their own tabs included in the Select Expert: Region Is Equal to CO, and Order Amount Is Greater than 2500.

Since the Select Expert performs a logical AND between the tabs, what do you do if you want to see all orders from Colorado, regardless of the order amount, as well as orders from any other state over $2,500?

In this case, the Select Expert doesn t provide sufficient flexibility to create this type of special record selection. Thus, you must use a record selection formula. The Select Expert creates a record selection formula automatically as you add tabs and selection criteria. You can modify the formula it creates in one of three ways:

• By clicking the Show Formula button on the Select Expert itself

• By choosing Report Selection Formulas Record from the pull-down menus

• By displaying the Formula Workshop (discussed in more detail in Chapter 5) and choosing Record Selection in the Selection Formulas category

  Tip  

  If you know you ll need extra features that the Select Expert doesn t provide, you can skip it entirely and create your record selection formula right in the Formula Editor or Formula Workshop. Choose Report Selection Formulas Record from the pull-down menus or choose Record Selection in the Formula Workshop s Selection Formulas category to create the formula this way.

In the scenario previously mentioned, you need to change the relationship that exists between the two criteria from an And to an Or. This is a simple process that you can apply either right from the Select Expert or by using the Formula Editor. To use the Select Expert, simply click Show Formula.

You can now modify the formula created by the Select Expert to show you all Colorado orders, regardless of amount, and other orders over $2,500. Notice that the Select Expert has placed the And operator between the two parts of the selection formula. Simply position the cursor in the formula and change the And to Or, and then click OK.

Tip  

If you click Show Formula in the Select Expert and then decide you want to use the full-featured Formula Editor, just click the Formula Editor button in the expanded Select Expert dialog box. The formula will be transferred to the Formula Editor, where you can modify or enhance it.

Since you will ultimately be using the Crystal Reports formula language for your record selection, most of the features of the language are available for record selection. In this situation, it may be preferable to edit the selection formula in the Formula Editor so that you can see and use all the built-in functionality. The formula that the Select Expert created will appear when you choose Report Selection Formulas Record from the pull-down menus.

You can modify this formula to your heart s content, provided that the ultimate finished formula is a Boolean formula ”it will ultimately just return true or false (refer to Chapter 5 for more information on Boolean formulas). The formula will be evaluated for each record in the database. If the formula evaluates to true, the record will be included in the report; otherwise , the record will be ignored.

Case Sensitivity with Record Selection

A question that you will probably ask yourself fairly quickly when using record selection is, Is it case sensitive? In other words, if you ask to see records where the country is USA, will a record be returned if the database field contains mixed-case characters , such as Usa ?

Case sensitivity is generally ignored when using SQL databases and PC databases via ODBC, as well as certain PC-style databases using a direct database driver. Although this case insensitivity is the default behavior out of the box, be sure to check the Database Server Is Case-Insensitive option in the File Report Options dialog box to affect the current report, or check the same option on the Database tab of File Options to set the default for all new reports you create in the future.

Even if this option is checked, some databases and ODBC drivers may not support case insensitivity with Crystal Reports. It's best to run a test with your own database to make sure you're retrieving all desired records with your record selection.

Note  

If you modify the formula the Select Expert created, or you create your own formula, running the Select Expert again is fine. However, if the Select Expert is unable to fully interpret the formula you created, you ll see slightly different behavior for one or more tabs. You may see a tab with a field set to Is Formula and part of the selection formula showing in the third list box. You may also see a message indicating that the formula uses a composite expression and prompting you to edit the formula directly.