Working with Reporting Services Filters – Part 4: Creating a NOT IN Filter

January 20, 2011
Tags: ,

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!

12 Responses to “Working with Reporting Services Filters – Part 4: Creating a NOT IN Filter”

  1. Many thanks Stacia. I would never have thought of that!

  2. You’re quite welcome. Thanks for inspiring this post!

  3. Very clever.
    I’ve used your trick here to filter an mdx query using the results of an sql query.
    Unfortunately I am not allowed to add this data into the cube so that filtering would occur naturally with MDX but this is a good work around.

    On the other hand, if the multi-value parameter contains many entries, I think using the InStr function is going to be quite slow, but at least it works. You’d think that 2008 R2 would have ‘not in’ added to it by now.
    Lets hope Denali has it :/

  4. First, thank you for helping me with this. I have it working as long as I’m using one word to filter out. I was wondering if you knew what to do if I want to filter out two words? I get errors with everything I can think of.

  5. Hi Tom,

    Can you be more specific about what you’re trying to do? What have you tried?

  6. Very useful info, I have a link to this.

  7. [...] SSRS 2008 R2 doesn’t have an inbuilt “not in” operator when working with filters. Stacia Misner has a great trick to work around this over here . [...]

  8. Stacia – you are a super star! Brilliant work to come up with a workaround on this one (and shame on Microsoft for not having a “not in” option!).

    I can’t thank you enough for posting this one!

    May all fortune come your way!

    ///Sven

  9. Good stuff Stacia. Thanks for that.

    I have used this to create a filter to exclude records where a text column, [Description] in this case, contains a user entered value ( parameter NotInText1 ).

    The first Iif just handles cases where you want to set the parameter to NULL, ie dont use this filter.

    =Iif(IsNothing(Parameters!NotInText1.Value)=True,True,Iif(InStr(Fields!Description.Value,Parameters!NotInText1.Value)=0,True,False))

  10. You could also create a new data set that selects the inverse value of what populates your filter list (for example in SQL do a “WHERE val NOT IN ( SELECT val FROM …)” and use that to filter on with a normal IN filter

  11. Hi Stacia,

    first of all, thanks for the description, I was searching for days for a solution like this!

    However, I’ve got a problem. My filterlist contains appr. 320.000 rows, resulting in a “nearly inifite” execution time. When I reduce the number of elements to ~500, the query is executed in 45-60 secs. At 8000, it takes 15-20 mins. I didn’t waited the case of 320.000, but I quess it takes minimum a half day.

    Because of our business usage, such a long time is not acceptable. I quess the long execution time is because for every row, it must join 320.000 items into a single string and then search the actual value in this quite long string.

    Is there any way to reduce the execution time in a case like this? Or is it a lost cause?

  12. Really the only way to do something like this efficiently would be to do it in the database engine if you can find a way to make that happen. Reporting Services is not going to be the fastest way to manipulate data, whether sorting, filtering, or whatever is determining which data to display.

Leave a Reply