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