20 May | 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:
-
In the Database Explorer dialog box, double click on the Add Command option of the database
-
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:
-
In the Field Explorer, click on the Parameter Fields category
-
Press the New Button
-
In the Create New Parameter dialog box, Enter the name of the parameter in the Name text box
-
Select the Dynamic radio button option, for the List of Values
-
In the Choose a Data Source area, click in the first Value box
-
Select the Country field, from the Commad statement
-
Click in the Parameters box, on the same row
-
Add the required options for the parameter:
-
-
Press the OK button
Create a select criteria for the report, using the parameter:
-
In the Reports menu select the Select Expert option
-
In the Choose Field dialog box, select the Customer.Country field
-
Press the OK button
-
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:
Create a new formula field Selected Countries:
"Selected Countries: "& Join({?Select Country}, ", ")
Add the Select Countries formula field to the report header.
If you have any questions, or need any assistance, please do not hesitate to Contact Us |
|
|
1 | Luc | 22 May |
| You can also without union any table go to selection formula and
type
({?select country}= {customer.country} or {?select country}="*")
it works fine. |
2 | Julia Emelogu | 25 May |
| Hi Luc,
Thank you for your comment. Whilst it is possible to use your proposed method the client wanted a parameter list, where the user could only select a single, multiple or All list values options, from a specific list of values generated from an existing table field. The parameter would have to be set up to have discrete values, thus the user would not be able to type in "*" when the report was run. |
3 | Luc | 27 May |
| you're right. My proposal cover only single and all value, not multiple |
4 | Ivan | 19 April |
| If i have a field in this report form (txtCountry), how could i write the formula to have a report only filtered by this txtCountry.text? Thank you. |
5 | Julia | 20 April |
Hi Ivan,
Using the Select Expert, the formula would still be:
If {?Select Country} = "All" then True else {Tablename.txtCountry} = {?Select Country}, but you might change the parameter option to Multiple = False, so that one can only have one value
Hope this helps
Julia |
6 | Smyrna Mills | 24 May |
| Hello there, just became alert to your blog through Google, and found that it is really informative. I am gonna watch out for brussels. I will appreciate if you continue this in future. Numerous people will be benefited from your writing. Cheers! |
7 | Michelle | 26 November |
| Hi Luc, Thanks for this. I'm having trouble setting this up. When attempting to run the report using blank parameters I get an error 'The value is not valid'. I'm sure I'm missing something simple... |
8 | Jenny | 23 December |
| Hello, I'm having trouble setting up this parameter and really need help.
My report's datasource is not straight from tables but from a Command :join from 4 tables, 1 of these table is country table with include countryID and country name, the countryID is FK for the main table Order table. I used CountryID in where clause of the Command as
Select ....
join .....
where
Order.CountryID = {?P_CountryID)
My question is where should I put in the parameter for "All" in this select statement ? Thank you |
9 | Jenny | 23 December |
| I forgot to add the code for Record Selecttion:
(If {?P_CountryID}<>-1 Then
{Command.Country_ID} IN {?P_CountryID}
|
10 | Julia | 30 December |
Hi Jenny,
You can either add a union to your command, with the All as a label, alternatively you can change your record selection, so that in your false bit, you just add True
Hope this helps
Julia |
11 | Ben | 03 December |
| if {?parameter} = "All" then {table_field} like '*'
else
{table_field} like {?parameter}
This works fine if the table field is of string type.
But I would also like to know the code to select All when the type is integer. Please assist!!! |
12 | MaryFay | 22 May |
| how would i change the parameter results in the header from showing a list of selected employee numbers only to a message that says 'All records selected" |
|
