on 07-30-2015 7:19 PM
Anyone know if there is a way to setup a cascading parameter where both dynamic parameters are optional and both show the full selection list? Specifically, take this example, State and City are the cascading parameters but the report can be run without filtering on either. The problem I have, is that if nothing is selected for the 'State' Parameter, City does not show any values. I want City to be filetred based on the State selection but a non selection of state should equal all and therefore the full list of city should show so the user can skip ahead to 'City' if you did not know what 'State' it was in.
Thanks for any advice on how to do this.
Hi John,
You will need to answer the State prompt to be able to see the list of cities. I had a similar requirement in one of our reports and this is what I did.
1) Create a command object with the following SQL Query:
SELECT State, City FROM table
UNION ALL
Select '*ALL*', City from table
UNION ALL
Select State, '*ALL*' from table
UNION ALL
Select '*ALL*', '*ALL*' from table
2) Create a dynamic cascading prompt using the State and City fields from this command object
3) This populates the 'State' prompt with all the states and a 'wildcard' - '*ALL*'. When you select '*ALL*', the City prompt is populated with All available cities and a wildcard - '*ALL*'.
The wildcard in the City prompt lets you select '*ALL*' and run the report for all Cities of the selected State! Of course you can even run it for a single city if you wish.
The other other thing you would need to do is modify the record selection formula to handle the wildcards and optional prompts. Mine looked like this:
({?State- state} = "*ALL*" OR {locations.state} IN {?State- state})
AND
({?State- city} = "*ALL*" OR {locations.city} IN {?State- city})
Hope this helps.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you're running your report just through Crystal or through an application that is not BusinessObjects or Crystal reports server, that's exactly the way to do it. If you're publishing the report to BO or CRS, you will do something similar in the Business View Manager to create a "List of Values", but there are a few more steps to doing this.
-Dell
Yes, there is a way to do this, but it will take a Command to do it. Are you publishing the report to BusinessObjects or Crystal Reports Server, or are you running it through a different application?
The instructions for how to set this up will depend on the answer to that question.
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 3 | |
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.