Specifying a comma separated string as a parameter value in BIDS
Hi, I have a problem that is driving me mad. I am trying to create a report in BIDS that pulls data from oracle database and generates the report of call it sales by month. What I'm attempting to do is to create a parameter in BIDS and force a comma separated value into it and then pass that value into SQL statement for an oracle dataset in other words. BIDS: Parameter @QTR Paramater label QTR1 Paramter value ('01','02','03') Parameter @Year Parameter label Year Parameter value 2010 Oracle side: select * from .... where year=:Year and month in :QTR Whenever I try to run it like that the report generates but it's blank. Replacing value in QTR parameter with a single non quoted value like 01 generates report perfectly fine. Could anyone shed some light as to how properly specify comma delimited strings to be used in :in: statements.
October 19th, 2010 1:45am
Hi matrosov, What most likely is happening in your case is that the statement which Oracle executes is: SELECT * FROM ... WHERE year =:Year AND month in :'01,02,03' Or similar - note the string format. Try splitting the string - e.g. create some sort of a stored proc or a function (no idea how these are called/created in Oracle), which splits your comma-separated string into a number of strings and then you can write: AND month IN (SELECT ...) <- output from function. Boyan Penev --- http://www.bp-msbi.com
October 19th, 2010 2:49am
hello, you can create a hidden multivalue parameter to use for your in clause: have your quarter parameter be a simple selection between QTR1, QTR2, QTR3, QTR4 have the hidden multivalue parameter have options 01, 02, ...etc populate your multivalue parameter's default value with another dataset which depends on what QTR has been selected, something like this: select tt from ( SELECT '01' tt from dual where :QTR = 'qtr1' union select '02' tt from dual where :QTR= 'qtr1' union select '03' tt from dual where :QTR= 'qtr1' union select '04' tt from dual where :QTR= 'qtr2' union select '05' tt from dual where :QTR= 'qtr2' union select '06' tt from dual where :QTR= 'qtr2' union select '07' tt from dual where :QTR= 'qtr3' union select '08' tt from dual where :QTR= 'qtr3' union select '09' tt from dual where :QTR= 'qtr3' ) and then finally in your main dataset you can simply do: select * from .... where year=:Year and month in (:QQ) where :QQ is your hidden, multivalue parameter
October 19th, 2010 3:09am