List unsold items per customer

Question

I am working on a Cognos Report to display a list with Customers and Items that were never purchased by those customers, but I can't reverse the association to find the "excluded" Items.

My relevant tables and relationships are:

Customers 1..1 <--> 0..1 Sales 1..1 <--> 1..1 Items

I have customers A, B and C and products X, Y and Z.

A bought X and Y.
B bought Z.
C never bought anything.

The desired output would be:

___________________
| Customer | Item |
|----------|------|
| A        | Z    |
| B        | X    |
| B        | Y    |
| C        | X    |
| C        | Y    |
| C        | Z    |
|__________|______|

Any out-of-the-box ideas on how to build a query for such report?

Thank you!

Answer 1

You current model won't fit your needs. Try to create a custom "data model" for this query in your report. Go to Query explorer tab in Report Studio and add 3 queries: Customer, Item, Sales

1. Join Customer and Item by any field, press "Convert to expression" and set something like 1=1 - emulate cross join.
2. Left join Sales to result of (1) by item_id and customer_id (you have something like this, right?)
3. Filter by "Sales. is null" - no sales for pair Item and customer

Result is your dataset.