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,”, “) + “)”

Share →

4 Responses to Multivalue parameters in SSRS

  1. Dave, I just stumbled on your blog today and have really enjoyed it. Your articles are well written, concise, and useful for someone working with the SQL Server BI stack. Thanks!

    • davos says:

      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.

  2. Google says:

    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

  3. Google says:

    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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>