The Maximum Impact Solutions Blog

How Do I Add an All Option to a Parameter in Crystal Reports?

This week I was asked "How does one add an All option when populating dynamic list of values for parameters in Crystal Reports?"

To create an All option in a dynamic list of values for a parameter, to allow the user to return all the required customers based on their country of residence.

Create the data source using the Union SQL statement:

1. In the Database Explorer dialog box, double click on the Add Command option of the database
2. In the Command dialog box, enter the SQL union statement to the command:

Select Country from Customers

Union

Select "All"  from  Customers

Note: To rename the Command, click on the name twice and overtype the Command name

Create the Parameter, using the Command statement data source:

1. In the Field Explorer, click on the Parameter Fields category
2. Press the New Button
3. In the Create New Parameter dialog box, Enter the name of the parameter in the Name text box
4. Select the Dynamic radio button option, for the List of Values
5. In the Choose a Data Source area, click in the first Value box
6. Select the Country field, from the Commad statement
7. Click in the Parameters box, on the same row
8. Add the required options for the parameter:
9. 
10. Press the OK button

Create a select criteria for the report, using the parameter:

1. In the Reports menu select the Select Expert option
2. In the Choose Field dialog box, select the Customer.Country field
3. Press the OK button
4. In the Select Expert dialog box, press the Show Formula button, and at the bottom enter the following formula:

If {?Select Country} = "All"  then True else {Customer.Country} = {?Select Country}:

 

 

 

Create a formula that displays the values of the parameters selected by the user:

"Selected Countries: "& Join({?Select Country}, ", ")

Luc

Julia Emelogu

Luc

Ivan

Julia

Smyrna Mills

Michelle

Jenny

Jenny

Julia

Ben

MaryFay