cancel
Showing results for 
Search instead for 
Did you mean: 

Need help with optional cascading dynamic parameter in CR2013.

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

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 

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

It does thanks and I am familiar with setting up commands objects since the earlier days of  non optional parameters in CR.  I wish the concept of 'all' was somehow built into CR parameters.  Oh well, thanks for your quick input.

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Not publishing. They are running the report through a 3rd party viewer.  Thank you