Talk with the Author: Cascading (Dynamic) Pick List

Print Page | Close Window

Cascading (Dynamic) Pick List

Printed From: Crystal Reports Book
Category: General Information
Forum Name: Talk with the Author
Forum Discription: Ask Brian questions about his books and give him your comments. Like the book? Hate the book? Have suggestions? Let me know!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=278
Printed Date: 18 May 2024 at 11:49pm

Topic: Cascading (Dynamic) Pick List

Posted By: mwahid
Subject: Cascading (Dynamic) Pick List
Date Posted: 05 Mar 2007 at 12:25pm

Hi,

I have a client who has a business requirement for a Cascading Pick List like Country->Region->City. At times he would have all three pieces of information and would like to get a report. For example, he would put in USA, East, New York for all the values of the pick list while reunning the report and will get his sales figures for the specific city. I can make this work in Crystal 11.

However, at times he would only want to enter New York in the city section of the parameter list and would like to get a sales report. I am yet to find a way to come up with a selection formula accept a null for first two elements (Country, Region). I get the report only when I fill in all three components of the pick list.

Is there a way to make this happen?

Thanks in advance for any help you can provide.

-------------
MW

Replies:

Posted By: hilfy
Date Posted: 06 Mar 2007 at 9:23am

You'll have to use a couple of formulas for this instead of just the parameters. Set a default value on your parameters - either a blank string or something like "All". Your formulas will then look something like this:

{?parameter} = "All" or {table.field} = {?parameter}

You'll need one formula for Country and one for Region.

In the Select Expert, instead of comparing the field directly to the parameter, you select each of these formulas and "Is True".

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: mwahid
Date Posted: 06 Mar 2007 at 12:06pm

Thanks for the feedback.

I actually had a selection formula like in CR 11

{?CityName - CITY_NAME} = "" or {SALES.CITY_NAME} = {?CityName - CITY_NAME}

But, I can not define a default value of null to a dynamic pick list that selects values from a specific database column. I know how to define a default value for a static pick list. I am hoping that selecting no values from the pick list will make CR understand that a null is selected.

Also, what is the syntax of evaluating above formula to Is True. For example,

Is True ({?CityName - CITY_NAME} = "" or {SALES.CITY_NAME} = {?CityName - CITY_NAME})

does not work for syntax issue

Thanks in advance for your help.

-------------
MW

Posted By: hilfy
Date Posted: 07 Mar 2007 at 5:12am

If you're editing the selection formula itself, what you had should be fine. "Is True" makes no changes to a boolean formula, "Is False" does something like "Not {@formula}".

Your selection formula does essentially what my formula was doing. Now that I understand your problem a little better, try this....Instead of usng the parameter in your selection criteria, you could use a formula like this:

mailto:%7b@CityName - {@CityName }
If IsNull({?CityName - CITY_NAME}) then "" else {?CityName - CITY_NAME}

You would then use the formula in your selection formula instead of the parameter....Like this:

( mailto:%7b@CityName - {@CityName } = "" or {SALES.CITY_NAME} = mailto:%7b@CityName - {@CityName })

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: mwahid
Date Posted: 07 Mar 2007 at 10:19am

Thanks Dell.

I believe we are close to wrapping this up once we overcome the challenge below:

The formula CityName (see below)

If IsNull({?CityName - CITY_NAME}) then "" else {?CityName - CITY_NAME}

works great if string '?CityName - CITY_NAME' refers to a database object's (table or view) column (in that case it would read like {SALES_DETAILS.CITY_NAME}) or command column (selection of a column using SQL - like select distinct CITY_NAME from...). What I have where is actually a Casading Pick List parameter (note the ? before the name). So we may have to come out with a new syntax of IsNull that works with Cascading Parameter or come up with another trick.

Thanks in advance for your help.

-------------
MW

Posted By: mwahid
Date Posted: 17 Mar 2007 at 10:25am

All,

I found out from several trial and error tests that there are limitations of Cascade Pick List. It's architecuture does not allow you to select null values for any of the items. There are compelling reasons why Business Objects have made Cascade LOVs act in this fashion. Nulls have to be part of the LOV. Since that is not realistic, you will end up with actual value entries to make the report work. Alternately, you can have static pick lists that allows you to enter null as long as it is defined. However, you will not get the benefits of a Cascade pick list.

Thanks all (especially Dell) for help.

-------------
MW

Print Page | Close Window