quick question on selecting "all" records from within an IF statement 2

[demchak]

I have a few parameter fields in my report that have an "All" option. I would like to have my selection criteria based off of the field but it should not filter anything if "All" is selected but I am having trouble getting the results i want. Here is what i have

parameter field {?Division}
values "All","CB","CM"..etc
DB field {USERINFO.DIVISION}

(
if {?Division} = "All"
Then
{USERINFO.DIVISION} = "*"
else
{USERINFO.DIVISION} = {?Division}
)

This does not work as i get no results in my report.

Thanks for the help

 

[vidru]

Try this:

(
if {?Division} = "All" Then
true
else
{USERINFO.DIVISION} = {?Division}
)

-dave

 

[rhinok]

By using {USERINFO.DIVISION} = "*" you're stating that you only want records where division explicitly equals "*".

Instead, try the following formula, which returns all values if the parameter selected = "All":

(
If
{?Division} <> &quot;All&quot;
Then
{USERINFO.DIVISION} = {?Division}
Else If
{?Division} = &quot;All&quot;
Then
True
)

 

[vidru]

Always forget that Else If part...

-dave

 

[rhinok]

Dave,that formula is simpler syntactically, but it won't pass off the processing to the database.

~Kurt

 

[rhinok]

Lol, we're obviously posting at the same time

/cheers

 

[demchak]

Thanks again for the help guys. I should have been able to get that one on my own, must have been having a bad day.

Thanks

 

[kit2002]

Here is another way to tackle 'All' and it also processes it much faster.

({USERINFO.DIVISION} = ( if {?Division}
<> "All"
then {?Division})
OR
{USERINFO.DIVISION} > ( if {?Division}
= "All"
then "" else 'zzzzzzz'))

 

[synapsevampire]

Check out my FAQ on optimizing SQL for pass through:

faq767-3825

-k