RS Howto: Use the IN operator in a filter expression

Filters are not always a good thing in reports as it generally indicates you are getting more data back from the database than you need. However I often find that I want to use virtually the same data in a report jst formatted differently, i.e. a chart, a table, etc. This is were filters come into their own.

You can filter on many things in a report. Generally these are anything that has a set of data, i.e. a table, a group, a series for a chart.

I recently wanted to filter using the IN operator, but couldn't find out how.

I guessed based on previous experience that a delimted lst of values probably wasn't going to cut it. So I took my delimited list and used the SPLIT  function on it. This returns an array, and it works.

I can only guess that RS loads the array into a dictionary object and does a lookup against it. For that reason it needs an array that it can loop over to load into the dictionary.

So an example of an expression is as follows

="Simon,Sabin,SQL,Server".Split(,)

Go into an filters dialog, select the field (or expression) you want to filter on, select the "In" operator and then enter the expression as above.

Interestingly the Value property of a Multivalue parameter is an array object already so if you ave one of those in your report you should be able to do something like,

=Parameters!MyMultiValueParameter.Value



-
Published 20 November 2007 19:28 by simonsabin

Comments

22 November 2007 13:41 by Repieter

# re: RS Howto: Use the IN operator in a filter expression

Not sure if this is what you mean, but some time ago I used the following query to pass values from a multiselect dropdown box to a query:

SELECT * FROM OrderDetails

WHERE ',' + @SelectedOrderIDs + ',' LIKE '%,' + CONVERT(VARCHAR(50), OrderID) + ',%'

22 November 2007 14:24 by simonsabin

# re: RS Howto: Use the IN operator in a filter expression

Definitely not.

The approach you have used is very bad but is also very common. The reason is that the optimiser can't use an index to filter the order details. This means that your query will likely perform a table scan, rather than reading only the rows that you want.

In your situation, in reporting services you only have to specify an IN clause, i.e.

SELECT * FROM OrderDetails

WHERE OrderID IN (@SelectedOrderIDs)

I explain more here http://sqlblogcasts.com/blogs/simons/archive/2007/11/22/RS-HowTo---Pass-a-multivalue-parameter-to-a-query-using-IN.aspx

22 November 2007 22:51 by Rob Farley

# How multi-value query parameters in SSRS are treated by SQL Server

This is something that has bothered for me for a while. In SQL Server Reporting Services, you write a

22 November 2007 23:40 by SimonS Blog on SQL Server Stuff

# RS How To : Multi value parameters and IN clause

Following my preview two posts about using filter and with IN in the multivalue parameters. Rob Farley