Multiple value parameter OR select all

[groundclutter]

Here's the problem:

I have a parameter, e.g. {?BookName} that is set up to be a discrete, multiple-value parameter.

The selection criteria is set up so that {db.fieldname} = {?BookName}.

What if I want to "select all" book names? The only way I've been able to get this to work is by using an If-Else formula that either searches for the parameter value or searches for everything 'but' the parameter value. For example...

If {?BookName} <> &quot;&quot; Then
{db.fieldname} = {?BookName}
Else
{db.fieldname} <> {?BookName}

This is &quot;kind of kludgy&quot; and would like to see a better solution as I have several parameters that this would be used for.

Thanks in advance!

BTW, I'm using Crystal 8.5 and the parameters will eventually be passed from a VB6 application.

Thanks again!

 

[kenhamady]

The preferred approach is to give the user the option of selecting a specific value that indicates ALL, like the word ALL. Then use a a selection formula like this:

If {?BookName} = &quot;ALL&quot;
Then True
else {db.fieldname} = {?BookName}

The downside is that this will not get passed to SQL because of the if - then.

You should also put this in parentheses if you have more rules in your selection formula. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[groundclutter]

Thanks Ken! I've tried it both ways and I guess your method is a 'bit more elegant than looking for all values that aren't null. I am going to suggest that the user interface adds the ability to pass some type of standard &quot;ALL&quot; constant that serves as a notifier to the report that we're supposed to select all.

When I look at the query from the Crystal side, I don't see the WHERE clause being built. Is that portion hidden or should I not see the WHERE clause when I do enter parameter criteria?

Thanks again...

 

[kenhamady]

The problem with using an If-Then in the selection is that it doesn't get passed to the SQL. This forces part or all of the missing WHERE clause to be performed by CR at the client. Lousy on performance, but the result is the same.

Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[rhinok]

The other alternative is to allow the &quot;*&quot; wildcard as a parameter value. Then you can specify;

{db.fieldname} like {?BookName}

in your record selection criteria.

Your list of parameters might be:

*
BookTitle01
BookTitle02
BookTitle03

&quot;*&quot; would cause all book titles to be returned, while any one selected parameter or any combination of the selected parameters would cause only those distinct titles to be returned.

This formula will also be process server-side for more efficient report performance.

 

[kenhamady]

That works even better in cases when you don't want to include null values, which is probably the case here. The one advantage to be able to passing the TRUE is it will inlcude all records, including those where the value of the field is null. Of course the disadvantage is that it doesn't get passed to the server. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/