SSRS how to do a NOT IN parameter filter on a dataset

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 .

In Dataset properties

Expression: =True
Operation: “=”
Value: = Iif(InStr(Join(Parameters!FilterList.Value,”,”), Fields!Category.Value)=0,True,False)

Parameters!FilterList.Value is a multi-value parameter. It’s basically a big array of values that uses a dataset for it’s available values.

The values are joined ( join() ) together into one long string separated by commas. It’s important to separate by commas (or some other character that is not present in the data) so that when you search in the string (InStr) in the next step, you are not going to accidentally find false positives that consist of the end of one value and the start of another.

e.g.
If these were your values
newtowns
villenieu
liverpool

If you were looking for townsville for example, if you didn’t have the commas then it would find townsville as part of “newtownsvillenieuliverpool”, where it would not find it in “newtowns,villenieu,liverpool”

So the InStr part is looking within the long string (newtowns,villenieu,liverpool) and seeing if any of the values for each row of Fields!Category.Value have a match. If ( iif() ) there is at least one match then the count will be greater than 0 and the expression will evaluate to FALSE, if it is equal to 0 it will be TRUE.
When the expression is FALSE then the row will be skipped.

This is a very clever trick and is worth walking through to understand how it works.
I’m yet to test SSRS 2012 to see if “not in” has been added.

 

8 thoughts on “SSRS how to do a NOT IN parameter filter on a dataset

  1. Thanks for this idea, it worked perfectly! Well, almost…

    I found that sometimes values will be unintentionally excluded if they a substring of one of the selected values. For example, selecting “townsville” will exclude not only “townsville” from your report, but also “town”, or any other substring. To get around this problem, I added some angle brackets around both the Join() and the values:

    =Iif(
    InStr(
    “,”
    ,””
    ) = 0
    ,True
    ,False
    )

    1. Hey thanks for the reply.

      Do you really mean the other way around, that searching for “Town” would then exclude the row with “Townsville”?

      In other systems I’ve seen the use of the tilde character ~ for that sort of purpose, but thanks for the comment. I was thinking the commas would probably work as delimiters too, but thinking about it more, you do indeed need some extra terminating characters to definitively define the strings you are searching for.

      Thanks!

  2. HTML parsing ate my code… Let’s try again with curly brackets:

    =Iif( InStr( “{” + Join(Parameters!ExcludeFactors.Value,”},{”) + “}”, “{” + Fields!Factor.Value + “}” ) = 0, True, False )

  3. I just came across this issue and looked it up and found this site and Stacia’s but thought surely there has to be an easier way and there is 🙂

    Open your dataset. Open Query Designer. Click on Filter and then New Group. Select None Of and drag the field you want to filter into the group. Click on equals and change to “in this list”. Select the values from the list that you want to exclude. If there’s a value you want to exclude that doesn’t appear most likely because the data doesn’t exist yet, right click the field name and manually enter the values to exclude in the curly brackets { }.

  4. Hi All,

    I’ve come here through Stacia’s site. I’ve got an issue with this workaround I’ve also posted on Stacia’s site (waiting for moderation at this moment), but I give it a shot also here, maybe someone has a clue 🙂

    The issue is:
    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?

    And thanks for sharing the solution!

    1. You really don’t want to be filtering with a massive list like that for the reasons you’ve discovered.
      I would really try to stick to a few hundred at most.

      What are these 320000 rows? What sort of data is it? Can you rather use a group to filter the report?

      If these 320000 rows are in a table in the database, you are better of making the database do the work for you rather than the report. Join the table of 320000 to the rest of your data. There must be something about the data that you can predict and pre-prepare for your users. It would be impossible for them to be choosing 320000 items in a report parameter right?
      Maybe these rows are all “new” products and you have today’s date to work with. Maybe they all fit into some other category you can use to group them.

      It’s a good general rule to do the heavy lifting in the database and not in the report. Try and do all business logic in the database and leave the report expressions for formatting and minor presentation functionality.

Leave a Reply

Your email address will not be published. Required fields are marked *