Multiple ALL statements and Parameters

[slimshades26]

I have the need for the user to choose one of or ALL in the following parameters. My issue is that Crystal is choosing to use one parm over another or not use any at all. I need the user to be able to choose ALL customers in US and not have to fill out what state they are in. Or type one customer number and not complete the state or country by leaving it "ALL". My parameters have been where the user could choose to see customers in TN, TX and CA, so my "ALL" parameters are multiple value type. The others are single choice parameters.
An oddity I've observed is that by moving the statements up or down in the record selection formula seems to cause varying results. But one statement is just as important as another if the user chooses it.
Thanks for looking. ATM

{F5542016.SSDATE} = {?DateYear} and
{F5542016.SSMCU} = {?MCU} and
{F03012.AICO} = {?AICO} and
If {?LOB} = "ALL" then true else {F03012.AIAC29} = {?LOB} and
if {?Cust} = "ALL" then true else totext({F5542016.SSAN8}) = {?Cust} and
if {?State} = "ALL" then true else {F0101.ALADDS} = {?State} and
if {?Country} = "ALL" then true else {F0101.ALCTR} = {?Country}


ss26

 

[slimshades26]

The boss helped me with this one, so I'm answering my own post. Take care.

{F5542016.SSDATE} = {?DateYear} and
{F5542016.SSMCU} = {?MCU} and
{F03012.AICO} = {?AICO} and
({?LOB} = "ALL" or {F03012.AIAC29} = {?LOB}) and
({?State} = "ALL" or {F0101.ALADDS}={?State}) and
({?Country} = "ALL" or {F0101.ALCTR} = {?Country}) and
({?Cust} = "ALL" or totext({F0101.ABAN8}) ={?Cust})


ss26

 

[synapsevampire]

I fear that your boss is mistaken, demote her/him

First, it definitely won't pass all of this to the database as SQL (check Database-Show SQL), secondly the logic appears flawed.

You were on the right track originally, but CR is persnickety about passing the SQL.

Try:

(
{F5542016.SSDATE} = {?DateYear}
)
and
(
{F5542016.SSMCU} = {?MCU}
)
and
(
{F03012.AICO} = {?AICO}
(
and
(
If {?LOB} <> &quot;ALL&quot; then
{F03012.AIAC29} = {?LOB}
else
If {?LOB} = &quot;ALL&quot; then
true
)
and
(
if {?Cust} <> &quot;ALL&quot; then
totext({F5542016.SSAN8}) = {?Cust}
else
if {?Cust} = &quot;ALL&quot; then
true
)

You get the idea...

the parens and seemingly redundant IF's are imnportant to passing the SQL.

After each section is added to the record selection formula, check the Database->Show SQL Query to make sure that it's being passed to the database, this is critical to the performance of the report.

-k