Home / Tech Tip / Crystal Tech Tip / Crystal Reports – Selecting all Values in a Dynamic Prompt List

Crystal Reports – Selecting all Values in a Dynamic Prompt List

 

2015-02-06 UPDATE:  A newer version of this techtip has been created – visit the following link: https://www.infosol.com/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:

    1. We will be using the Northwind sample SQL database that comes with SQL Server 2000 and the “Customers” table.
    2. Create a new, blank report connecting to the Northwind SQL database and only add the “Customers” table.

    1. Add the “Customer ID”, “Company Name”, and “Phone” fields to the “Details” section of the report canvas.

    1. Create a group for “Country”.

    1. Go back to the “Database Expert” and select “Add Command”.

    1. We want to create a prompt that will list all of the countries, and an entry that the user can select, that says “ALL” and in turn will select all of the countries.
    2. In order to do this, we can enter in the following SQL syntax in the “Add Command” window (usually a good idea to test this syntax out first in a SQL query tool like “Query Analyzer” or “Toad”).

SELECT ‘*’ as ‘Country’,
‘All’as ‘Description’

UNION ALL

SELECT COUNTRY,
COUNTRY as ‘Description’
FROM CUSTOMERS
GROUP BY COUNTRY

This syntax yields these results which we will use as the values and description for our “Country” prompt.

    1. Copy the above syntax into the “Add Command” window:

    1. On the “Links” tab in the “Database Expert” link the “Country” field from the “Customers” table to the “Country” field in the “Command” table:

    1. Click “OK” in the “Database Expert” window to accept these changes.
    2. You will then receive the message below. Click “OK” – we are receiving this warning because we have one of our data sources as a table and one as a SQL command, so we cannot do any server-side grouping or SQL Expression within this report.

    1. Create a new parameter called “Country”, make it dynamic and use the “Country” field in the “Value” column and the “Description” field in the “Description” column. Also, make sure to select “True” next to “Prompt With Description Only”:

    1. In the “Select Expert”, make sure to link the “Country” field to your new “{?Country}” prompt.

    1. Now, when the user gets a prompt, they will have an option to select “ALL” and all of the countries will appear on the report:

 

About ckruger

Check Also

Let's Speak BO Webinar Now You See Me, Now You Don’t! August 8 2017

Webinar: Now You See Me, Now You Don’t!

Join us and Creative Technology & Training Solutions’ Michael Ward, as he demonstrates and discusses …

5 comments

  1. The dynamic parameter created in 12 doesn’t specify which item you are using for it. Is the dynamic parameter based on Country from the Country table or from the Command? This needs to be identified.

  2. Hi MH,
    This is an old tech tip. We are working on the updated version which will also answer your question here. Stay tuned!!

  3. Yeah, it would be great if this works but it doesn’t.

Leave a Reply

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