THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

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

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!

Published Thursday, January 20, 2011 11:38 AM by smisner
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ramakrishna said:

It's a very nice article. It really helps. Thanks for posting.

January 21, 2011 10:10 AM
 

Ian Yates said:

Great series - very useful information!

Something to add about the filter...

To avoid partial matches  (eg the list might have Cart, Heart, Bart and you're looking for Art, which you shouldn't find) you could extend it slightly to do

=Iif(InStr("," + Join(Parameters!FilterList.Value,",") + ",",

"," + Fields!Category.Value + ",")=0,True,False)

This translates to

=Iif(Instr(",Cart,Heart,Bart,", ",Art,")=0, True, False)

which won't match the *art in each word.

And as per my comment on part 1 of the series, avoiding commas is often necessary, particularly when filtering on company names, etc.

Just thinking some more, the technique described here could have neen used in part 1 by negating the expression, checking for equality to false or swapping the true & false parameters to Iif.  Your part 1 explanation is easier to understand than this though :)

February 10, 2011 9:01 PM
 

smisner said:

Right again, Ian! The nice thing about Reporting Services is that we have many ways to tackle a problem. The frustrating thing for some people is that we have many ways to tackle a problem. Thanks for sharing!

February 10, 2011 9:06 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement