×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

parameter returns no records..
2

parameter returns no records..

parameter returns no records..

(OP)
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?

RE: parameter returns no records..

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.

RE: parameter returns no records..

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

RE: parameter returns no records..

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

RE: parameter returns no records..

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!

RE: parameter returns no records..

CJMartin,

That formula accepts multiple values. What it doesn't contend for is partial strings. Like "Na%" for "Naith" and "Nadia". 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

RE: parameter returns no records..

Naith,

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

"This array must be subscripted.  For example: Array ."

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

Thanks.

RE: parameter returns no records..

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

RE: parameter returns no records..

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 ("ALL") at the top of the parameter selection list, so the default will be running for "ALL".

Thanks!!!

RE: parameter returns no records..

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

Naith

RE: parameter returns no records..

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

FYI...We're on Oracle 8.1.6.

Thanks.

RE: parameter returns no records..

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 "A", 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

RE: parameter returns no records..

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...

RE: parameter returns no records..

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

RE: parameter returns no records..

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

RE: parameter returns no records..

Your formula works great...THANKS!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close