Filtering on Variables

[Former Member]

I have 3 variables.

variable 1

if query 1.id <> query2.id then 9999 else 0000

variable 2

if query 1.customer<> query2.customer then 9999 else 0000

variable 3

if query 1.name<>query2.name then 9999 else 0000

I want to filter report

variable 1=9999 or variable2=9999 or variable3=9999

For some reason I can only seem to filter by one of them and only at a global filter.

[Former Member]

Is there a reason you used 9999 and 0000? Perhaps you can set up something like this:

variable 1

if query 1.id = query2.id then 1 else 0

variable 2

if query 1.customer = query2.customer then 1 else 0

variable 3

if query 1.name = query2.name then 1 else 0

New variable called "flag"

=sign(variable 1 + variable 2 + variable 3)

If any one of the values is 1, or any number of the variables are 1, then the results of the "flag" variable will also be 1. Then you can filter on just that single value without having to do an OR.

[Former Member]

When you link objects <ID>,<Customer>, and <name> they result in an equivalent of unionall.

The 3 variables wont have effect.

If you are trying to display only the columns which has all three column then try,

create a variable

 Flag= If (Not Isull(<ID>) and Not Isull(<customer>) and Not Isull(<name>) ) Then "Show" Else "Dont Show"

Filter on the above object for "Show"

[ashish_gupta3]

If you are using Oracle, you might be able to use 'DECODE' - if you are using Sybase or SQL Server use 'IIF'.