Select All on a parameter 1

[drinker]

I have a parameter of {PART.BUYER_USER_ID} = {?Buyer}

How do I let the user have the option of either choosing the buyer ( I have the Allow Multiple Values selected ) or have ALL of the buyers show. What would be the formula or selection to acomplish this? Thank You.

Crystal 8.5

 

[synapsevampire]

Set a default value of "All" and code as:

(
if {?MyParm} <> "All" then
{Table.field} = {?MyParm}
else
if {?MyParm} = "All" then
true
)

Check out my FAQ for more info along these lines:

faq767-3825

-k

 

[mocgp]

Another option is to use the * character in the default (I usually put in a description of "All") then {PART.BUYER_USER_ID} like {?MyParm} in the filter.

 

[synapsevampire]

mocgp: You should avoid adding criteria when it isn't required, at best all you'll do is get similar perfomance, more likely you'll slow the query down.

A reaonsable alternative is:

(
{?MyParm} = "All"
or
{Table.field} = {?MyParm}
)

However with older versions of Crystal you'll find that it does not reliably pass the where clause to the database in some instances.

-k

 

[mocgp]

snyapse: Could you explain what you mean by "adding criteria when it isn't required"? Are you saying that the SQL translation of the "like" function is different (and more risky) than that of "If-then-else"??

 

[synapsevampire]

The like predicate will be passed to the database, so the optimizer must then interpret the like "*" to doing nothing, which is what the TRUE does in the above without passing anything, hence no risk of tripping it up.

-k