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
Have you tried using an 'IN'
[Product on bill] IN ?Prompt Name Here?
and setting the prompt as multi select.
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.
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....