Crystal Reports Cascading (Dynamic) Pick List

Talk with the Author

Crystal Reports Forum : General Information : Talk with the Author

Topic: Cascading (Dynamic) Pick List

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

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	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 Logged

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

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	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 Logged

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

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

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