Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
A man who does not think for yourself, does not think at all
 
Oscar Wilde
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

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:

  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. Crystal Reports Dynamic LOV All Option
  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}:

 

crystal Reports Select Expert for All Parameter option 

 

 

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
I forgot to add the code for Record Selecttion: (If {?P_CountryID}<>-1 Then {Command.Country_ID} IN {?P_CountryID}
9

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
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"
13

DARODI

06
February
Write a sample parameter to me. Thanks for sending me a sample

MAKE A COMMENT

Name *

Email Address *

Comment *