Parameters in Record Selection

[LeslieMcLean]

I have a parameter ProductLine in my report on all sales. The user can select three values "M" for mail order, "E" for event and "A" for all. I have a database field called OrderType which has multiple values starting with M (which are all mail order types), E (which are all event types) and then other values starting with various letters. When my report runs, I want to use the parameter to select the records with the correct order type without having to have three seperate select statements. I have a series of parameters that I would like to do this with and if i do them as all separate select statements seperated by "OR" I'll have 12 statements which becomes confusing to edit. I'm assuming this is done with variables but I can't figure it out.

For example:

The customer can choose ProductLine (described above), InTheCatalog (3 values=yes, no, all), SelfSupply (yes, no, all). I need a relatively simple select that evaluates this and pulls out the right info. Basically, when the user picks "all" for any of these lines, I don't want to limit the select on another field.

I'm assuming it's something like this...

then Order.Ordertype startswith ?ProductLine and
Order.Catalog=?InTheCatalog and
Order.Supplier=?SelfSupply

This works for the limiting choices but not for the "ALL" choice where I want the record regardless of the value in the actual field.

Thanks.

 

[briangriffin]

Try this:

if {?ProductLine} = "All" then true else Order.Ordertype startswith {?ProductLine}
and
if {?InTheCatalog} = "All" then true else Order.Catalog = {?InTheCatalog}
and
if {?SelfSupply} = "All" then true else Order.Supplier = {?SelfSupply}

 

[hilfy]

In selection criteria, it is better not to use an If statement. Using If means that Crystal is less likely to be able to push the criteria down to the database for processing which means that it will pull ALL of the data into memory and filter it there. Here's a better way to do it;

({?ProductLine} = 'All' or Order.Ordertype startswith {?ProductLine}) and
({?InTheCatalog} = 'All' or Order.Catalog=?InTheCatalog) and
({?SelfSupply} = 'All' or Order.Supplier=?SelfSupply)

Note where I've put the parentheses - this won't work without them surrounding the "or" statements!

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com