Adding "ALL" Value to Cascading/Dynamic Parameter??? 1

[BeeBear2]

Hi everyone,

I have come up against an issue that I can't work out with dynamic parameters.

I have a parent parameter (Country), and a child parameter (City).
Both parameter lists are created from the database summary table.

My issue is I want to add an "ALL" option to the top of each list and also have the parameter default to that value.
I also want the "ALL" value to display on the child parameter regardless of what I select at parent level.

Hope someone can help

Note - I am on the latest version of Crystal Reports (2016 SP4). The report will eventually be passed through a 3rd party front end software package for dispay.

Cheers.

 

[lbass]

This probably isn't quite what you want, but:

Create a command like this:

SELECT `table`.`country`
FROM `table`
union
select 'All'
from
`table`

Create a second command:
SELECT `table`.`city`
FROM `table`
union
select 'All'
from
`table`

Do not link the commands to each other or to any tables in the report, and ONLY use them for the cascading dynamic parameter--do not use them in the body of the report.

Then create a dynamic parameter with the country field as the first parameter field, and city as the second.

Then use a record selection formula like this:
(
(
{?MyParm - Country}<>'All' and
{table.Country}={?MyParm - Country}
) or
{?MyParm - Country}='All'
) and
(
(
{?MyParm - City}<>'All' and
{table.City}={?MyParm - City}
) or
{?MyParm - City}='All'
)

This will give you the 'All' options, and will provide correct results, BUT the city picklist will include all options regardless of the country you select. If you choose 'all' for cities, only the cities related to your country selection will be returned in the report. Couldn't see a way to get only the related cities to appear in the picklist when not using the 'All' country option.

-LB

 

[BeeBear2]

Thanks LBass - timely as always!

I managed to create the 2 command prompts, but after I saved the Database Expert selection (even without linking the parameters to the tables or even putting the paramter in the Seclect ), my report stopped returning data.
deleting the command prompts didnt even fix it. I had to go back to an earlier saved copy of the report to fix it.

Any idea?

 

[lbass]

Not sure what you mean. Do you mean you created the two commands and then saved before creating the dynamic parameter?

You don't have auto-linking checked, do you?

Would there be more than 1000 results if you selected all countries and all cities? There is a maximum of 1000 that will be returned, but even that should not stop the report from running.

I think you should explain what you did step by step.

-LB

 

[BeeBear2]

Hi Lbass,

Silly me!!! Crystal decided to do the auto joins when I closed the Database Expert - I have set crystal to not do this now.

Its bringing back data now.

Now I just need to make it the right data.