Crystal Reports in the Real WorldCustom Filtering

Sometimes a report needs to return all records for a parameterized field where a record selection filter has been created on this parameter. Although it would certainly be possible to create a parameter and select all valid values for the parameter, there certainly must be a better wayand there is. In this example, a filter is added to a report so that if a user enters a specific value or a list of values, only those values are returned. Alternatively, if the user enters an asterisk (*, or other predefined symbol such as All Values), all values are returned. The following steps highlight this capability:

1.	Open the sample report Chap 5.rpt created in the first half of this chapter. Set the Allows Custom Values property to True and Change the Top N sort condition to All in the Group Sorting Expert.
2.	From the Report menu choose Selection Formulas, Record. Remove the following line of text. {Customer.Country} = {?Countries}
3.	Replace the text with the following (as shown in Figure 5.15): (If {?Countries} = "*" Then True Else {Customer.Country} = {?Countries};) Figure 5.15. The updated Record Selection Formula Editor enables the end user to select All Values with one easy selection. Click Save and Close.

When prompted for a new parameter value, remove any existing values, enter the * symbol as a manual custom entry, click the Add (>) button to add the symbol to the list of values, and click OK. You should see something like in Figure 5.16.

Figure 5.16. The report returns all values when * is passed in as a parameter. In this photo, the chart has been edited slightly from the original report but you can clearly see that all countries were returned.

Note

Another way to implement an All Values parameter option for the report consumer is to create a record selection through the Record Selection dialog that uses the is like operator instead of the equals to operator. Using this operator enables you to use the * and ? wildcards in your filter. By having end users enter '*' or providing that as one of the default parameter selection options, the users can specify All Values without needing to add them all independently. One thing to watch for here is that parameters that allow multiple values do not by default allow themselves to be mapped to in the Record Selection dialog with the is like operator. A viable workaround, however, is to map the record selection to the parameter using the equals to operator and then edit the formula record selection manually and replace the = operator with the like operator.