Filter a list based on a where clausule

[Pemoreels]

Hello,

I am troubled with a report that I am trying to make.
I would be very gratefull if somebody could give me a hint.
I do not consider myself an expert in Cognos analytics.
We use version 11.1

I have a list of dossiers which hold products:
Dossierid    Productdescription
Dossier1              desc1
Dossier1              desc2
Dossier2              desc3
In our datawarehouse  there is a mesurement field called nr_of_products available

My question is actually very simpel:
I want to create a list of all dossiers that have zero products of a specific type.

The only way I can see is to query the list of all dossiers and combine these with a list of dossiers that do have the specific product in an "Except" function
(see attach)
I was wondering if ther is an easier way to do this?

Thanks in advance

[chr.93]

Have you tried to just filter the Query with nbr_of_products=0?

[Pemoreels]

Tried that but it does'nt help.
I think it has something to do with the way the relations are set up in the DWH

Thanks for the suggestion!

[BigChris]

Difficult to answer your question without knowing the structure of your data and the joins used. Essentially you'd need a left outer join between the table with the dossiers and the table with the number of products (or the products table, again it depends on your data).