CODEWITHSUNDEEP
oracle-databasereporting-servicesparameters
san
san

Reputation:

parameter in sql query :SSRS

I am using oracleclient provider. I was wondering how do I use a parameter in the query.

select * from table A where A.a in ( parameter).

The parameter should be a multivalue parameter.

how do I create a data set?

Upvotes: 2

Views: 17373

Answers (6)

Chandra Raju
Chandra Raju

Reputation: 1

As Victor Grimaldo mentioned… below worked for me very fine. As soon as I use the :parameter in my SQL query in SSRS dataset1.. it asked me to enter the values for these parameters, for which I choose already created SSRS parameters.

SELECT * FROM table a WHERE VALUE IN (**:parametername**)

Thanks Victor.

Upvotes: 0

Victor Grimaldo
Victor Grimaldo

Reputation: 58

The parameters in oracle are defined as ":parametername", so in your query you should use something like:

select * from table A where value in (:parametername)

Add the parameter to the paramaters folders in the report and mark the checkbox "Allow multiple values".

Upvotes: 0

Davos
Davos

Reputation: 5395

Firstly in SSRS with an Oracle OLEDB connection you need to use the colon, not the @ symbol e.g. :parameter not @parameter but then you aren't able to do this as a multi-valued parameter, it only accepts single values. Worse, if you are using an ODBC connection you have to use the question mark by itself e.g. ? not @parameter and then the ordering of parameters becomes important, and they also cannot be multi-valued. The only ways you are left with is using an expression to construct a query (join() function for the param) or calling a stored proc.

The stored proc option is best because the SSRS can handle the parameters for stored procs to both SQL Server and Oracle very cleanly, but if that is not an option you can use this expression:

="select column1, column2, a from table A where A.a in (" + Join(Parameters!parameter.Value,", ") + ")"

Or if the parameter values are strings which need apostrophes around them:

="select column1, column2, a from table A where A.a in ('" + Join(Parameters!parameter.Value,"', '") + "')"

When you right-click on the dataset, you can select "dataset properties" and then use the fx button to edit the query as an expression, rather than using the query designer which won't let you edit it as an expression.

This expression method is limited to a maximum limit of about 1000 values but if you have that many this is the wrong way to do it anyway, you'd rather join to a table.

Upvotes: 1

Daniel Emge
Daniel Emge

Reputation: 1617

You can't have a variable in list in oracle directly. You can however, break apart a comma seperated list into rows that can be used in your subquery. The string txt can be replaced by any number of values seperated by a comma.

   select * from a where a.a in ( 
       SELECT regexp_substr(txt,'[^,]+',1,level)
         FROM (SELECT 'hello,world,hi,there' txt  -- replace with parameter
                 FROM DUAL)
   CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (txt, '[^,]'))+1
   )

The query works by first counting the number of commas that are in the text string. It does this by using a reqular expression to remove all non commas and then counts the length of the remainder.

It then uses an Oracle "trick" to return that number + 1 number of rows from the dual table. It then uses the regexp_substr function to pull out each occurence.

Upvotes: 1

James
James

Reputation: 12796

Simple. Add the parameter to the report and make sure to check it off as multi-valued. Then in the data tab and go in and edit the query click the "..." button to edit the dataset. Under the parameters tab create a mapping parameter so it looks something like this (obviously you will have different names for your parameters):

@ids | =Parameters!ContractorIDS.Value

Then in the query tab use the coorelated sub-query like your example above. I have done this many times with SQL server and there is no reason it should not work with Oracle since SSRS is going to build an ANSI compliant SQL statement which it will pass to Oracle.

where A.myfield in (@ids)

Upvotes: 1

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

I don't think you can use a parameter in such a situation. (Unless oracle and the language you're using supports array-type parameters ? )

Upvotes: 0

Related Questions