How to satisfy 2 conditions? 1

[dideb]

Hello All,

I created a Variable called "Action Item". It have a number of If statements as follows:
= If <Last Status> >= "523" And <Next Status> <= "525" Then "A" Else If <Last Status> >= "540" And <Next Status> <= "542" Then "B" Else If <Last Status> >= "530" And <Next Status> <= "542" "C" Else "Other"

The problem is some orders might satisfy 2 conditions (like B and C) and I need to show both. Of course the If condition satisfy only one.

Is there another way to define the variable to get the required outcome?


Thank you

 

[BrianTyler]

I would create a seperate variable for each of the groupings, and give them values of 1000, 100,10,1

A second variable would add the 4 seperate ones together so that 1010 would represent A and C.

Of course this is only feasible for a small number of groups.

Brian

 

[dideb]

Hello Brian,

Thank you for the reply.
I think this won't do because if the 2 conditions are satisfied, 2 rows should appear; one for each Action Item.
What do you think?


Thank you,

Diaa

 

[blom0344]

No, you will not get 2 rows this way. The first condition that satisfied the request will produce the outcome.

This is easily solved by a union query, making sure that you mark each part by a constant like:

SELECT 'A',..... WHERE <Last Status> >= "523"  And <Next Status> <= "525"
UNION
SELECT 'B',..... WHERE <Last Status> >= "540"  And <Next Status> <= "542"
UNION
SELECT 'C',..... WHERE <Last Status> >= "530"  And <Next Status> <= "542"

You can build the union query itself in query panel, but need 3 objects to avoid the union merge (which you would normally want, but not in this case)


Ties Blom
Information analyst

 

[dideb]

Hello Ties,

I have placed your code as an expression in the select statement of the report and named the expression "Subquery".
Unfortuntely, expressions can't return more than one value.

The following error popped up:
Exception: DBD, [Microsoft OLE DB Provider for SQL Server] : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.State: 21000

Any other ideas.


Thank you,

Diaa

 

[blom0344]

Diaa,

You are confused about subquery and union queries. BO allows you to build both with query panel. In this case you need to build a UNION query.

In query panel there is a button that has a tooltip 'combine queries'. Use it to build the union query.

Create 3 universe objects to AVOID the merging of the union data as I pointed out in my earlier reply

Ties Blom
Information analyst