Can we write loops in Report Studio

[phoenixfire]

I have a query with IDs 1,2,3...and each ID has a value x, y. I am trying to filter out only those IDs with value 'x'. If each ID has only a single value, eg.
ID            Value
1              x
2              y
3              x
then its straight forward enough with a filter where value='x'  (answer is 1,3). But sometimes an ID has more than one value. Eg.
ID            Value
1              x
2              y
3              x
4              x
4              y
Then, I need to pull in only (1, 3)..not 4...as it also has a value 'y'.

Can someone help me figure this out? Any ideas are appreciated. Thanks in advance.

[paddhu]

You can use the Except (Minus) sql operation to achieve what you want easily..
For eg.
Select ID from table where Value='x'   --> Query 1
Minus
Select ID from table where Value='y'   --> Query 2

Or you can write this a single custom query.
Hope this helps.

[CognosAdmn]

Hi Paddhu,

I've tried the same approach, with the Except functionality. This would work perfectly if we know that there are only two values, 'x' and 'y'. But what if there are 1000s of Values?

I am not sure if this is the case for Phoenixfire.  Could you please confirm Phoenixfire?

[wyconian]

Hi

This may be a possible solution though it's a bit complex.

Decode the values with numbers e.g. x = 0, anything else = 1, then sum this field for the ID, then filter the report on the sum field = 0 i.e. only Xs.  That should give you all the IDs that have a value of X even when there are mulitples values of X and any number of other values.