I have a simple query "SELECT * FROM PRODUCT WHERE ID IN (?)". I want to pass multi value parameter to this query so the query will be executed as "SELECT * FROM PRODUCT WHERE ID IN (1,2,3)". I created the above query in the query designer and for set the Parameter to "multi value". On previewing when I enter multiple values for the parameter it is showing me the error: "cannot add multi value query parameter '?' for dataset 'test' because it is not supported by the data extension" Kindly let mw know how to use an Integer multi value parameter in an ORACLE query. Thnak you so much for your help.

Hi Sachin, Pls. refer this link and let me know if it helps your cause. http://stackoverflow.com/questions/547235/parameter-in-sql-query-ssrs Thanks.-Nitin Pawar

Hi Nitin, I have seen this link before and tried the solution. It works fine for MS SQL queries but not for ORACLE. Infact oracle does not even treat @Parameter as a parameter (Parameter has to be passed as ?). Can you or someone kindly help me with passing multi value parameter to an oracle query. Also I am using ORACLE by OLE DB connection from SSRS. Is that the problem? Sachin Agarwal

what type of Oracle data connection are you using? if you are using the standard Oracle connection you precede your parameter with a ':' and can easily use a multi value parameter like so: SELECT * FROM PRODUCT WHERE ID IN (:id_parameter) Make sure you go into the parameter options and set your parameter to Multi Value and the correct datatype. if you are using ODBC where you specify parameters with '?' it will not support multi value parameters directly but there are work arounds like turning your query into an expression where you can use SSRS array functions.

Hi Nehemiah, I am using Microsoft OLE DB Provider for Oracle to connect to oracle database (its a read only account). I did try the ':' option to pass parameters but it gave me the following error ORA-01008: not all variables bound hence I had to fall back to '?' to use any parameter. ? only lets you use a single value parameter. You mention there are work arounds to make ? take multi value parameters, can you please elaborate and if possible provide an example. Thanks a lot for your help. Sachin Agarwal

you may want to consider switching to the oracle dataclient connection because this can get pretty messy but basically you build concatenate a string expression for your query so you change your dataset type text to expression. To do multivalue parameters this way you split out your parameter using the join function: ="SELECT * FROM PRODUCT WHERE ID IN (" + Join(Parameters!id_parameter.Value,", ") + ")"

HMM.. thanks a lot for your help. I entered the above expression in the dataset Query type as expression and it seems to be accepting multiple parameters. I will try some more features of it. But I agree in the long term oracel dataclient connection is better. Just a quick left over question, why would ':' not work over OLE DB connection and give error (ORA-01008: not all variables bound). Sachin Agarwal

Its simply an entirely different data source type that specifies query parameters in a different way.

Hi Nehemiah, If possible can you please also write an expression to use Date Parameter in the above query (using expression) Sachin Agarwal

i didnt test this but this is how you can do it ="select * from dual where sysdate > to_date('" & format(Parameters!date.Value, "dd-MMM-yyyy") & "')"