Crystal Reports in the Real World—Custom 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 way—and 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.
1. | Open the sample report Chap 5 World SalesParm.rpt created earlier in this chapter. |
2. | |
3. | Replace the text with the following (as shown in Figure 5.13): If {?Countries} = "*" Then
True
Else
{Customer.Country} = {?Countries};
Figure 5.13. The updated Record Selection Formula enables the end user to select All Values with one easy selection. Click Save and Close. |
4. | When prompted for a new parameter value, remove any existing values, enter the * symbol, click the Add button to add the symbol to the list of values, and click OK. You should see something like in Figure 5.14. Figure 5.14. The report returns all values when * is passed in as a parameter. In this photo the chart has been removed from the report but the group tree clearly shows many countries being 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 endusers 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.