Multivalue parameters in SSRS

Normally when connecting to a SQL Server datasource you can create a paramter and allow multiple values and then use it in a query in this sort of construct:

Select column from table where column in (@parameter)

This works well and the parameter is passed from SSRS to the SQL Server provider cleanly.

When you are connecting to Oracle using the Oracle OLEDB client , however, you need to use the colon instead of the @ symbol, but unfortunately it doesn’t work for multivalue parameters.


i.e. you can do this

Select column from table where column = :parameter

but you cannot do this

Select column from table where column in (:parameter)


Worse, if you are using a generic ODBC provider then you need to use the question mark, which requires you to remember the voodoo ordering if you have a few of them in the query, and again they can’t be multivalue.

e.g. in your parameter listing, parameter1 will replace the first question mark in the query, parameter2 will replace the second and so on. It looks confusing because they are all question marks and unless you look at the order of your parameters in BIDS or alternatively in the report xml it’s hard to tell which parameter will be used.So you end up with this:

Select column from table where column1 = ? and column2 = ?


If you are using the Oracle client, another option is to call a stored procedure, but this can be messy just as it is with SQL Server stored procs. I’ve seen a system where a previous developer very cleverly created a function in SQL Server to parse a paramter and do some string manipulation to create dynamic SQL using those parameters. It was both SQL Injection friendly and efficient, but very complicated, and certain well known comp sci prophets might object to that technique (but whatevs).

A final option for multivalue parameters is to use an expression in SSRS to construct a query for you, and this works for any target SQL source using ODBC or other providers. You can input an expression, rather than using the query designer by opening the dataset properties and pressing the fx button.

This first example will place apostrophes around each value of the multivalue parameter and add commas between them inline with the query:

=”Select column from table where column in (‘” + Join(Parameters!parameter.Value,”‘, ‘”) + “‘)”

This will do the same without the apostrophes, useful for example if your values are numeric

=”Select column from table where column in(” + Join(Parameters!parameter.Value,”, “) + “)”

9 thoughts on “Multivalue parameters in SSRS

    1. Thanks Jon!
      Keeping notes like this is really a way for me to keep track of snippets and things I pick up along the way and try to learn from other experts in the field. I don’t really promote the site and there’s so many other pro SQL Server people writing blogs that I don’t expect many people to read it. The SQL Server Family is certainly the friendliest and most helpful bunch of IT people I’ve come across. Oracle & Linux forum people could learn a lot from them.

      But it’s nice to have a real person comment for once! I’m getting a bit over the North Face jackets, ugg boots and NFL jersey spamathon.

  1. after you yearn tranquility but enjoyment a long time, allow be able to treasure every! Word specialised trustworthy camaraderie! Not hitting is going to be your head Accessory into expertise. Stressed

  2. for those who anticipate the customer peacefulness and therefore fulfillment once, we have to be able to jewel each morning! Goal centred frank association! Missing out on happens to be go Installation to the experience. Fearful

  3. I tried this solution connecting to Oracle but nothing comes up, the report comes back empty, here is what I used: =”Select ORDER_NUM from ORD_ORDER where ORDER_NUM in (‘” + Join(Parameters!ParamList.Value,”‘,'”) + “‘)”

    I am missing something?

    1. Hi Silver A, are your columns numeric? If so then try the version without the apostrophes.

      Can you verify the values that are being generated in the ParamList? If you can then try and verify the query works with the ParamList values. i.e. try and run the query directly on Oracle. e.g. if you can verify your ParamList is returning the values 1, 2, 3, 4 then try running this on Oracle using the SQL Developer or whatever tool you use.
      Select ORDER_NUM from ORD_ORDER where ORDER_NUM in (1,2,3,4);

    2. Here what I did in ssrs using odbc connected to postgresql: select {col} from {table} where ltrim(to_char({col}, ‘999999’))=any (string_to_array(?,’,’)) and in the dataset parameter function: =join(Parameters!ReportParameter.Value,”,”)

Leave a Reply

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