Using Parameters with Record Selections

Now that you have completed the task of implementing a parameter field within a report, you learn how a parameter field can also be used to filter the data retrieved by a report. Parameter values that business users enter can be used within record selection formulas to determine what data is retrieved from the database.

In the following exercises, use the same World Sales Report to implement the Countries parameter field (created earlier in the chapter) to filter the report results by including the parameter field within a record selection definition (using the Select Expert dialog). In this case, you enable the business user of the report to select one or more country values to be included in the record selection, thus filtering the report results to include only the desired data. The following steps demonstrate how a single report can be segmented many different ways:

1. Verify that the Countries parameter field is listed below the Parameter Fields group within the Field Explorer.
    
2. Open the Select Expert dialog by clicking the Report menu and choosing Select Expert.
    
3. Create a new record selection definition. Within the Select Expert dialog, click on the tab to create a new record selection definition. This opens the Choose Field dialog. Choose Customer.Country from the Report Fields list and then click OK to return to the Select Expert dialog.
    
4. Define the selection formula. Select Is Equal To from the drop-down list on the left, and then choose the { ?Countries} option from the drop-down list on the right, as shown in Figure 5.10.
    

   Figure 5.10. Parameter fields can be added to record selection formulas quickly via the Select Expert dialog.

   

    

   NOTE

   Parameter Field objects are denoted with the question mark, ?, and enclosed in brackets, {}. This convention is used within various application dialogs, including the formula workshop and record selections, to signify that these objects are parameter fields.

5. Preview the report. To view how this parameter is now used within the generation of the report, run the report by clicking on the Refresh toolbar button (represented by the lightning bolt icon). As shown in Figures 5.11 and 5.12, the report now prompts the business user to select from a list of country values that is used to filter the data retrieved by the report and present only the requested values in the report.
    

   Figure 5.11. Business users can now select one or more countries to be included in the report results.

   

    

   Figure 5.12. Based on the selected parameter field values, the report results display only the desired data.

    

NOTE

After the parameters have been created and implemented into a report, no extra effort is required for parameters to also work within the Crystal Enterprise solution. See Part V, "Web Report DistributionUsing Crystal Enterprise," for more details on Crystal Enterprise.

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.

1. Open the sample report Chap 5 World SalesParm.rpt created earlier in this chapter.
    
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.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.