InfoSol We're passionate about inspired business intelligence
Related Articles
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
3. The Command looks like this.
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
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.
10. Refresh the report, enter the value for parameter
11. View report to see data.
Very clear…Thank You ! Is there any way to make the “*” the default parameter ?
Very good article! It is working in Crystal Reports and in other application (like Indusoft) that launches Crystal Reports