Chapter 4 - Selecting Records with Parameters
This chapter first shows you how to filter data using the Select Expert and then shows you how to create advance filters using parameters. Parameters are covered in great detail because they are also used within report formulas and for custom formatting. The effective use of parameters within record selection formulas and custom formatting lets you create dynamic reports that are tailored to each specific user.
Become a Crystal Reports expert with the authoritative resource available.
The tuturials and tips in this book will take your skills to the next level.Buy at
Using the Select Expert
To open the Select Expert, select the menu items Report | Select Expert. The other method is clicking on the Select Expert button on the Toolbar.
If this is the first time the Select Expert has been run for the report, it shows the Choose Field dialog box. As you can see in Figure 4-1, it simply lists all the fields available. The fields already used on the report are shown first and they are followed by every available field from the report's data source. A field from either of these groups can be selected.
Figure 4-1. The Choose Field dialog box.
Once you select a field from this dialog box and click the OK button, the dialog box is not shown again. Instead, you are always taken to the Select Expert dialog box shown in Figure 4-2.
Figure 4-2. The Select Expert dialog box.
There are two tabs in the Select Expert dialog box. The first is titled with the field that was just selected in the Choose Field dialog box. The second tab is titled
Table 4-1. Comparison operators for filtering data.
| Criteria | Description |
|---|---|
| Is Any Value | Every record is selected. This is the same as not specifying a record selection formula. |
| Is Equal To | The field must exactly match a specified value. |
| Is Not Equal To | All records where the field does not match the specified value are selected. |
| Is One Of | Lets you build a list of acceptable values. As long as the field's value matches any one of the values in the list then the record is selected. |
| Is Not One Of | Similar to Is One Of except that the record is selected if the field's value doesn't match any of the values in the list. |
| Is Less Than | The value must be less than the value specified. |
| Is Less Than or Equal To | The value must be equal to or less than the value specified. |
| Is Greater Than | The value must be greater than the value specified. |
| Is Greater Than or Equal To | The value must be equal or greater than the value specified. |
| Is Between | You specify a range of values and the field's value must be within this range. It can also be equal to the endpoints of the range. For example, if the start and end points are 100 and 200, then the following values would be acceptable: 100, 101, 199, 200. |
| Is Not Between | The value must be outside the range. For example, if the start and end points are 100 and 200, then the following values would be acceptable: 1, 99 and 201. |
| Starts With | Selects string fields that start with one or more characters. For example, a value of "B" would match the names "Brian" and "Barry". |
| Does Not Start With | Selects string field that do not start with the characters specified. For example, a value of "B" would match the names "Lynn" and "Karen" because they do not start with the letter "B". |
| Is Like | Let's you use wildcards for specifying the matching criteria. Using wildcards is discussed more later in the chapter. |
| Is Not Like | The opposite of Is Like. |
| Formula: | Lets you type in the formula directly. Useful when the formula is too complex for the available criteria. |
If you are setting a filter using a field that is a Boolean data type, you are given two new criteria: Is True and Is False. These criteria do exactly what they imply. They select records where the field has a value of either True or False.
Selecting a DateTime field gives you two additional options listed with the comparison operators. These options let you select dates based on sophisticated date ranges. For example, you can select fields that fall within the last 7 days or select fields that are in the first quarter of the calendar year.1 See Figure 4-3 for a list of the available functions.
Figure 4-3. Available date range functions.
After selecting a comparison method, the right side of the dialog box changes so that you can enter the value to compare the field to. With the majority of the comparisons, only a single dropdown box is shown. For example, comparisons such as Is Equal To or Is Greater Than compare the field to a single value.
To read all my books online, click here for the Crystal Reports ebooks.