Help filtering out customers who haven't bought certain products

[jcrouch]

Hi,

I need help filtering out customers who haven't bought a certain product line. This is being done in a relational crosstab. Here is a sample of what the report looks like:



It shows the market share of each product sold to individual customers, customers are grouped by an internal criteria. What I want to do is not show any customers who did not sell the '123' product. So in this example 'Cust 1' would not be shown because they had no sales of '123'.

Ive tried several approaches in the filters but nothing I try works. I added a Data Item that sums up the sales of '123', then filtered on that being > 0 in the Summary Filters section, setting the scope to Year and Customers.

Thanks,

Jeremy

[Grim]

I don't do a lot of report design, but try this...
http://www-01.ibm.com/support/docview.wss?uid=swg21344613

[navissar]

In your example, there are nulls rather than nills (Or zeros). Try filtering by "is not null" rather than ">0"

[blom0344]

This is not a matter concerning filtering.  The solution lies in building the crosstab against the combination of 2 - joined - queries.  The first query is the one you have now. The second query should only fetch customer(id) associated with sold/bought product 123.
The resulting set from the joined queries ( joined over customer(id) ) will then give you the required dataset.

[barrysaab]

you have to go with outer join,rest do what Blom0344,the ultimate Cognos guru suggested.Thanks

[blom0344]

Why an outer join? A straight join will make sure that customers that do not meet the requirement are removed from the final set, yielding on those that are associated with sold/bought product 123

[jcrouch]

Why an outer join? A straight join will make sure that customers that do not meet the requirement are removed from the final set, yielding on those that are associated with sold/bought product 123

blom0344, thank you very much! Worked perfectly.

And to barrysaab's outer join suggestion, I changed the join to and 'outer join' and it stopped filtering,sorry, excluding the companies that did not purchase 123.

+1 to blom0344 :)

Jeremy