INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

RE: Adding "ALL" Value to Cascading/Dynamic Parameter???

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

RE: Adding "ALL" Value to Cascading/Dynamic Parameter???

(OP)
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?

RE: Adding "ALL" Value to Cascading/Dynamic Parameter???

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

RE: Adding "ALL" Value to Cascading/Dynamic Parameter???

(OP)
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. smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close