Using the IN operator to filter in reporting services

sql-server-2008 reporting-services ssrs-2008
JsonStatham picture JsonStatham · Nov 7, 2013 · Viewed 88k times · Source

Can anyone advise what the syntax should be when using the IN operator within a table filter. I have tried the below but the snytax is wrong:

enter image description here

Answer

Jordan H picture Jordan H · Dec 10, 2013

I had some troubles with this one as well. Microsofts own documentation states a simple comma delimited list of values in the Value box should work. This is confusing because IT DOESN'T. At least it didn't for me and I am using SSRS 2012 with Visual Studio 2010.

It turns out that when you are using the IN operator, SSRS is expecting an array of values. There are a number of ways you can make this work for the filter. The following examples are meant to be typed out in the expression editor.

The first way (also the way explained in the blog linked below) is to simply type your comma delimited list in a string value and then use the split function on it.

=split("2B,2C",",")

Or you can derive your list from a multi-value parameter. The multi-value parameter is already an array so all you have to do is reference it via the parameter expression as seen below.

=Parameters!MultiValueParam.Value

I found out this information via the following blog. http://sqlblogcasts.com/blogs/simons/archive/2007/11/20/RS-Howto--Use-the-IN-operator-in-a-filter-expression.aspx

Related questions

SSRS Query execution failed for dataset

Have just deployed my Project on to my reporting Server. I have multiple datasets which are referencing views which exist on the db on that server. When I try to go into any report part I am getting this message: …

Read More
"An item with the same key has already been added" Error on SSRS When Trying to Set Dataset

When i try to set the Dataset in SSRS IDE, i get the error you see in the snapshot. The query works totally fine in SQL Server Management Studio, i wonder where did i go wrong?! The connection to DB …

Read More
Comments in SSRS Report Expressions

I'm using some complicated expressions in Reporting Services to control the value, format etc of data in a report (see MSDN). Is it possible to insert code comments into these expressions, and if so, what is the syntax? By code …

Read More