Active TopicsMemberlistSearchHelpRegisterLogin

Talk with the Author
 Crystal Reports Forum : General Information : Talk with the Author
Message Icon Topic: Cascading (Dynamic) Pick List Post Reply Post New Topic
Author Message
mwahid
Newbie
Newbie
Avatar

Joined: 05 Mar 2007
Location: United States
Online Status: Offline
Posts: 8
Quote mwahid Replybullet Topic: Cascading (Dynamic) Pick List
    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
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
mwahid
Newbie
Newbie
Avatar

Joined: 05 Mar 2007
Location: United States
Online Status: Offline
Posts: 8
Quote mwahid Replybullet 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
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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:
{@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:
 
({@CityName} = "" or {SALES.CITY_NAME} = {@CityName})
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
mwahid
Newbie
Newbie
Avatar

Joined: 05 Mar 2007
Location: United States
Online Status: Offline
Posts: 8
Quote mwahid Replybullet 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
IP IP Logged
mwahid
Newbie
Newbie
Avatar

Joined: 05 Mar 2007
Location: United States
Online Status: Offline
Posts: 8
Quote mwahid Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.