Filtering on 2 values (and - not or)

[Fielola]

Hello,

I want to filter my report on 2 values. I tryed different kinds of filters/filter expressions, but I don't get the result I want.

For example:

Bill nr       Product on bill         
1              A                           
1              B
2              A
3              B
3              C

I want a list of bill nrs where both products A and B are mentioned.

When I make my filter (with in, contains, ...) I always get all bill nrs, because the filter is on A or B. But I need a filter that will search on A and B.

I also tried making 2 queries: query 1 gives me all the bills with product A. Query 2 all bills with product B. Then I made a join, but I get no results...

Who can help me?

Thank you!

Sofie

[kc9400]

Have you tried using an 'IN'

[Product on bill] IN ?Prompt Name Here?

and setting the prompt as multi select.

[Fielola]

Hi kc9400,

yes, I tried working with 'in'. But I still get all the bills where there is or product A or product B, not only the bills where I have both products.

[kc9400]

Hmmm well in SQL I'd do something like this:

SELECT DISTINCT Number, letter
FROM Testing
WHERE EXISTS (SELECT 1
FROM Testing t WHERE Letter = 'A'
AND Testing.Number = t.Number) AND EXISTS (SELECT 1 FROM
Testing t WHERE Letter = 'B' AND Testing.Number = t.Number) AND Letter IN ('A', 'B')

Trying to think how to translate that to Cognos....