NOT IN operator for filter in dataset SQL Server Reporting Services 2016/Report Builder 3?

Question

Does anyone know how to do "NOT IN" as the operator for a filter in a dataset?

Answer 1

'NOT IN' is an operator that will keep any rows where the value of a given field is not in a given list of values. Put another way, it will filter out any rows where the value of a given field is in a given list of values. For example, if I have a table of customers that includes their names, emails, and activation_dates, I could select all customer email addresses by writing:

SELECT

c.email

FROM CUSTOMERS AS c;

If I want to only select emails from customers whose names are either "Jim", "James", or "Josh", I can write:

SELECT

c.email

FROM CUSTOMERS AS c

WHERE c.name IN ('Jim', 'James', 'Josh')

If I want to only select customers whose names are NOT "Jim", "James", or "Josh", I can write:

SELECT

c.email

FROM CUSTOMERS AS C

WHERE c.name NOT IN ('Jim', 'James', 'Josh');

This is effectively the same as writing:

SELECT

c.email

FROM CUSTOMERS AS C

WHERE c.name <> 'Jim'

and c.name <> 'James'

and c.name <> 'Josh';