Although most comparison operators only have a single dropdown box for entering values, there are a couple of exceptions. The is between and is not between comparisons give you two dropdown boxes. This lets you enter a beginning and ending range. The is between comparison is inclusive. The is not between comparison is exclusive. The other exception is when choosing a comparison of is one of or is not one of; the dialog box looks like Figure 6-3. This lets you build a list of items where the field should either be in the list or not in the list. As you select items from the combobox, they are added to the listbox below it. If you type in a value manually, add it by clicking on the Add button. If you add an item by mistake, delete it from the list by clicking the Remove button.
Figure 6-3. Building a list of items.
You are not limited to selecting records based on a single field. The
When selecting multiple fields, Crystal Reports treats each field as being part of a Boolean AND statement. For a record to be selected by the report, it must successfully meet the criteria specified for each field listed in the dialog box. If there were four fields listed, and a record only matched three of the fields, the record wouldn’t be selected.
From what you have seen so far, the Select Expert is a very helpful tool for selecting one or more fields using the basic comparison operators. This is probably adequate for many of the reports you write. But what about the other reports, where you need to build more complex filtering criteria? For those reports, there is the Formula Editor. The Formula Editor is described in complete detail in Chapter 7, but here is a summary of how to use it.
The Formula Editor lets you use Crystal syntax, the built-in programming language, to create sophisticated selection formulas. There is a large library of functions to choose from for building a selection formula. A simple example is when you don’t want to use the Select Expert’s default of requiring a field to match all the criteria selected. You can change the formula so that rather than use the default Boolean AND to join the conditions, it uses Boolean OR. Another example is rather than selecting a range of records based upon customer name, select the records based upon the first character of the customer name. This would let you choose all the customers with names starting with the letter “B”.
When opening the Formula Editor from within the Select Expert, you are only given the option of using Crystal syntax as the programming language. As you will see in Chapter 7, Crystal Reports also gives you the option of using Basic syntax, which is very similar to VB.NET. Unfortunately, the Formula Editor for the Select Expert requires using Crystal syntax.
There are two ways to enter a custom formula. The first way is to click on the Show Formula button. This shows the existing formula built using the fields already selected. You can change this formula directly so that it matches the selection criteria you need. If you can’t remember the different built-in functions well enough to type them in directly, click on the Formula Editor button. This brings up the Formula Editor dialog box (discussed in Chapter 7) and you can use it as a reference tool to build the formula. The second way to enter a formula is to click on the comparison list dropdown box and at the very bottom is an item called formula:. Clicking on this item changes the right side to a multi-line text box that lets you type a formula from scratch.
Crystal Reports raises the error “Failed to open rowset” when a record selection formula performs an empty string comparison on a field with a null value. Here is an example in Crystal syntax.
To correct this you also have to check for null values.
