Home / Tech Tip / Crystal Tech Tip / Crystal Tech Tip – Select ALL values in a dynamic prompt

Crystal Tech Tip – Select ALL values in a dynamic prompt

Question: I would like a user to have a menu option to select ALL values in a dynamic prompt list, instead of having to set up the prompt to allow multiple values and the user having to select the double-arrows, if they want to use all the prompt values to run the report. How would I do this?

Answer: In order to do this, you will need to create a SQL command, that grabs all the values you want for your prompt and joins it to another query, that pulls the wildcard (*) value and the word ALL that a user can select.

Steps:

We will be using the Northwind sample SQL database that comes with SQL Server 2000 and the “Customers” table.

1.  After selecting the data source, add the “Customers” table.

2.  Add a command

1-crystal tech tip - Select ALL values in a dynamic prompt

3. The Command looks like this.

2 -crystal tech tip - Select ALL values in a dynamic prompt

SELECT ‘*’ as ‘Country’,

‘All’ as ‘Description’

UNION ALL

SELECT COUNTRY,

COUNTRY as ‘Description’

FROM CUSTOMERS

GROUP BY COUNTRY

4. The Selected Tables have 2 sources as shown below

4 -crystal tech tip - Select ALL values in a dynamic prompt

5. The “Links” window is as shown below

5-crystal tech tip - Select ALL values in a dynamic prompt

6. Ignore the database warning and click ok

6-crystal tech tip - Select ALL values in a dynamic prompt

7. Inside the report, create a parameter using the field from “Command” table
8. The parameter should look like this.

8-crystal tech tip - Select ALL values in a dynamic prompt

9. Go over to the “Select Expert” enter the formula as shown below.

9-crystal tech tip - Select ALL values in a dynamic prompt

10-crystal tech tip - Select ALL values in a dynamic prompt10. Refresh the report, enter the value for parameter

11. View report to see data.

11-crystal tech tip - Select ALL values in a dynamic prompt

 

About Narmadhaa Surenthran

BI Consultant. BusinessObjects, Crystal Reports and much more...

Check Also

Crystal Reports and Arrays

Question: I need to combine a list of details, or group information, and print it …

3 comments

  1. Very clear…Thank You ! Is there any way to make the “*” the default parameter ?

  2. Very good article! It is working in Crystal Reports and in other application (like Indusoft) that launches Crystal Reports

Leave a Reply

Your email address will not be published. Required fields are marked *