parameter returns no records.. 2

[kate007]

when users do not enter a selection in a parameter field, the report does not return any data - is there a way of making it select everything if no criteria is entered?

 

[rhinok]

Use a wildcard as the default value and write your record select statement as follows:

{table.field} like {?Parameter}

This words as long as you don't have any nulls in your parameterized field. If you do, you can get around this by converting nulls to a default value.

 

[Naith]

If your database is fairly big, or you'll be using wildcards on a few fields, you should bear in mind that 'like' operators ignore indexes.

An alternative approach would be to use

If {?Parameter} = 'wildcard_value'
then True
else {?Parameter} = {table.field}

in the record select.

Naith

 

[synapsevampire]

I often prepopulate the Parameter with 'All' or something akin (as was suggested above with *) to demonstrate to users that everything is an option.

In that case you can test for either All or nothing entered with:

(
If trim({?Parameter}) <> '' and
trim({?Parameter}) <> 'All' then
{table.field} = {?Parameter}
else if
{?Parameter} = '' or
{?Parameter} = 'All' then
true
)

This now handles either selecting All or blank.

Note: I intentionally construct the record selection criteria in all the above overkill glory to improve SQL pass through to the database (improve performance).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[cjmartin]

synapsevampire,

Great formula!...

...but how would you handle it with the following scenario: parameter can be an array/multiple values

FYI - I'm working with Crystal 7.0 (no 'join' function available)

Thanks!

 

[Naith]

CJMartin,

That formula accepts multiple values. What it doesn't contend for is partial strings. Like &quot;Na%&quot; for &quot;Naith&quot; and &quot;Nadia&quot;. You'd use the 'like' function for that like Rhinok showed.

All you'd use the join function for is for displaying the multiple values of the parameter, not for affecting the results returned.

Naith

 

[cjmartin]

Naith,

When I use the formula (in the record selection formula editor) with a parameter that &quot;allow multiple values&quot;, I get an error stating...

&quot;This array must be subscripted. For example: Array .&quot;

However, when I deselect the &quot;allow multiple values&quot; option for the parameter, I don't receive an error and the formula works correctly. Any thoughts on what I'm experiencing?

Thanks.

 

[Naith]

Oops. My bad. I've misled you.

Multiple values aren't handled that easily at all - unless you use the 'like' operator.

Like you've suspected, you'd have to quantify multiple value parameters in the selection criteria by subscripting the parameter - which basically means that you would refer to the parameter like {?Parameter}[2] or [3] or whatever.

Trouble is, when you do that, you are really trying to predict how many values will be entered to the parameter at runtime, as whatever number you enter in [n] is essentially saying 'expect this many values'.

If you need your parameter to work with multiples values - the amount of which to remain dynamic - then I would settle for using 'like'.

Sorry for the misinformation earlier. I guess I must not have been thinking straight, poor excuse as it is.

Naith

 

[cjmartin]

Didn't mean to be so harsh on my myself by striking my comments out.

Naith,

To clarify, I was having issues with synapsevampire's formula when using a parameter that accepts multiple values. I just tested yours, and it works for both scenarios. And it will meet my needs b/c I will be placing my wildcard (&quot;ALL&quot at the top of the parameter selection list, so the default will be running for &quot;ALL&quot;.

Thanks!!!

 

[Naith]

Okay, just to be clear, my example doesn't pass to the database - if that's what you were expecting.

Naith

 

[cjmartin]

When you say &quot;doesn't pass to the database&quot;, what exactly do you mean? Are you referring to using indexes?

FYI...We're on Oracle 8.1.6.

Thanks.

 

[Naith]

I mean, if you set your parameter to say {?Get Letters} = 'A', the SQL passed to the database will omit this clause, forcing the database to get all letters.

Crystal then sorts through the dross and displays the information your parameter desired.

When something is passed to the database, Oracle will know to only get letters which equal &quot;A&quot;, which is more efficient and can be quicker. But if your dataset isn't really that big, you probably won't notice that much performance difference, if any at all.

This is all circumstantial anyway, because if you have a multiple value parameter, you aren't going to be getting that passed to the database in the first place.

Naith

 

[cjmartin]

So if the SQL doesn't pass to the database, more of the processing/filtering is done outside of the database...so more stress on the database, more traffic on the network, and more processing done by the Crystal workstation/Info Server. Something like that?

What I don't understand is: How do you know what does and what does not pass to the database? Can you point me in the direction of any documentation I can reference that discusses this? Seagate/Crystal Decisions technical briefs? I'm envious of this knowledge...

 

[Naith]

When you process a report, go to Database/Show SQL query.

If what you got in your record select shows up in the where clause of your sql; it's passing.

Naith

 

[synapsevampire]

If the parm is a multiple value, use:

(
If trim({?Parameter}[1]) <> '' and
trim({?Parameter}[1]) <> 'All' then
{table.field} in {?Parameter}
else if
{?Parameter}[1] = '' or
{?Parameter}[1] = 'All' then
true
)

This assumes that All or blank will be the first choice, so make sure it's at the top of the list.

This is designed to increase the likelihood of pass through SQL.

Sorry for the delay, I've been on vacation.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[cjmartin]

Your formula works great...THANKS!!!