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.
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
3. The Command looks like this.
SELECT ‘*’ as ‘Country’,
‘All’ as ‘Description’
COUNTRY as ‘Description’
GROUP BY COUNTRY
4. The Selected Tables have 2 sources as shown below
5. The “Links” window is as shown below
6. Ignore the database warning and click ok
7. Inside the report, create a parameter using the field from “Command” table
8. The parameter should look like this.
9. Go over to the “Select Expert” enter the formula as shown below.
11. View report to see data.