Detailed Filter

[Danie]

Hi

I am trying to create a report in Report Studio. However there is a column value I would like to exclude to remove the customers which have that value.

A customer can have multiple codes. Eg. 1,2,3 however some can also have null (in which I used coalasce)
I want to remove any customer who has the code 2.
When I try to filter not in 2 then it removes all the null customers and when I try it with the coalasce it still leaves the customer because they have other values which aren't 2 (eg 1,3)

How can I filter to remove all customers who have the value 2 (even though they might have other values) and leave the once who have no values at all as well.

I tried an outer join to no avail as well

Thanks
Hope it makes sense

[BigChris]

Hi Danie,

Once you've done the coalesce, you should be able to do something like:

[CoalesceField] not contains '2'

C

[Danie]

Thank you. I tried <> '2' but it still returned the customer as he had other values as well. I will try the contains statement and let you know.

[MDXpressor]

It seems like you could do this in two separate base queries. One with the data you want to show on the page (which I assume already exists), and one which determines which has customers a '2' code identifier.

You then join the queries together on customer code, and use the '2' code identifier column in your new query to create a lever by which to filter the old query.