Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

BeeBear2

Technical User
Sep 15, 2014
39
AU
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.
 
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
 
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?
 
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
 
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. :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top