Pass variables to Dynamic Cascading prompts

[Former Member]

Is there a way to pass a variable to a pick list? For example, in the first pick list, I would like to show top level departments. In the second pick list, I would like to give the option to select all of the divisions or the division unit code under the top level department. If the user selects divisions, I would like to populate the third pick list with the divisions or if division unit code is selected, populate the third pick list with the division unit code.

Is there a way to do this?

[Former Member · Accepted solution]

Hi Marilyn,

The dynamic prompt has an associated List of Values (LOV) contained in a Business View. You want to add an option to the LOV that selects all the elements of a list of values used by the dynamic prompt. Using this technique will add a value of "All" to your List of Values giving you the option of selecting the entire set of values.

Please follow the steps to create

In Crystal Reports:

1. Create a connection to your Database.

2. In the 'Database Expert' dialog box, double-click the 'Add Command' node and then type the following syntax in the 'Add Command to Report' dialog box.

SELECT Country, Region, City // Type the fields that you want the "All" option to be available.

FROM Customer

UNION

SELECT Country, Region, '...All'

FROM Customer

UNION

SELECT Country, '...All', '...All'

FROM Customer

UNION

SELECT '...All', '...All', '...All'

FROM Customer

3. Create your Dynamic Cascading Prompt using the List of Values.

4. In the record selection formula, insert a formula similar to the following:

If {Customer.Country} = '...All' then true

else if {?My Parameterall - Region} = '...All' then

{Customer.Country} = {?My Parameterall - Country}

else if {?My Parameterall - City} = '...All' then

{Customer.Country} = {?My Parameterall - Country} and

{Customer.Region} = {?My Parameterall - Region}

else

{Customer.Country} = {?My Parameterall - Country} and

{Customer.Region} = {?My Parameterall - Region} and

{Customer.City} = {?My Parameterall - City}

After creating this prompts and when we refresh the reports "All" option will be prompted in the pick list.

Please let us know.

Regards,

Naveen.