A question came in from a reader asking about how to create a NOT IN filter. You can see in the first of this series of blog posts about Reporting Services filters, NOT IN is not available in our list of operators for filters. However, it’s still possible to produce a filter that excludes items in the list. The technique is not particularly intuitive, which is why I’ve added this filter type to my series on filters.

Preparing the Report

Let’s start with a simple scenario using the AdventureWorksDW2008R2 database. I set up a query that returns reseller sales by category. In my report, I have two tables: one for Bikes and Components, and the other for everything else. I want to create a filter for the first table using the IN operator, but I’ll have to create an expression for the second table to achieve the NOT IN effect.

To hold the list of values for my IN filter, I create a hidden report parameter with multi-values, without available values, and with a list of default values that includes Bikes and Components, like this:

image image

Of course, I don’t need to manually populate the default values. I could use a query instead, but I think you get the idea.

Using the IN Operator

This step is not really required to set up the NOT IN filter, but I have included it as another example of working with the IN operator to supplement my first post in this series. In my report, I want the first table to include the values that are defined in the parameter, so I add a filter to the tablix like this:

Expression Data Type Operator Value
[Category] Text In =Parameters!FilterList.Value

You can also use [@FilterList] as the value if you want to type in a simple expression into the Value field. If you use the Expression Editor to set up the Value expression, and double-click on FilterList in the Parameters category, be sure to remove the (0) from the end of the expression so that the IN operator is comparing Category to all values in the parameter array, not just the first value.

Creating a NOT IN Filter

For the second table, I also add a filter, but I set it up differently. There is no such thing as a NOT IN operator, so I need to get creative. I need to come up with an expression that evaluates as True or False, and then set that up as my Value in the filter definition. Then I’ll set the Expression of the filter to True and use an = operator. The filter will keep rows where Value is True and exclude rows which cause Value to be False.

Expression Data Type Operator Value
=True Boolean = =Iif(InStr(Join(Parameters!FilterList.Value,”,”),
Fields!Category.Value)=0,True,False)

For Expression, note that I have =True and not just True. This is important because True by itself will be interpreted as a string instead of a Boolean data type.

I use the InStr function in the value to compare the current row’s Category to the FilterList which I convert from an array to a comma-delimited string by using the Join function. The InStr function returns 0 if the category is not found, which is the equivalent of NOT IN. Thus, I have the expression return a True – which keeps the row in the second table.

Checking the Results

Here’s the final report:

image

If you’d like to have a look at how I set this up, you can download the RDL.

Have you found another way to solve NOT IN? Let me know!