Birt Report Parameter

Hi,
Last week I ran into a problem and still searching for the clue.
I'm trying to create a report by passing few report parameters. One of the parameter's type is List Box with Multiple Values allowed. I use these parameters in the data set query to get the result. I use the List Box parameter values in "IN clause", something like this

where column_name in (parameter.list_box).

Now the query runs fine, but only the first value from the list box is considered or passed to IN clause. How do I make all the values from list box available in IN clause. I know we can use as comma separated values where I need to write some script, but is there any better way.

NOTE: when adding the list box parameter to query, I get the warning saying only the first value is considered in the data set, then y do we have list box multiple values allowed??

I truly appreciate your help...

thanks in advance.

Find more posts tagged with

Comments

cypherdj

When i setup a dataset parameter linked to a multiple-values report parameter, in BIRT 3.7, I get the following warning : 
<pre class='_prettyXprint _lang-auto _linenums:0'>If a user sets multiple values for the report parameter, the data set uses only the first value.</pre>
 
So I guess you will still need to use the script functionality to build the in clause.

mcremer

<blockquote class='ipsBlockquote' data-author="'manjus.kt'" data-cid="80822" data-time="1312012976" data-date="30 July 2011 - 01:02 AM">
Hi, 
Last week I ran into a problem and still searching for the clue. 
I'm trying to create a report by passing few report parameters. One of the parameter's type is List Box with Multiple Values allowed. I use these parameters in the data set query to get the result. I use the List Box parameter values in "IN clause", something like this 
 
where column_name in (parameter.list_box). 
 
Now the query runs fine, but only the first value from the list box is considered or passed to IN clause. How do I make all the values from list box available in IN clause. I know we can use as comma separated values where I need to write some script, but is there any better way. 
 
NOTE: when adding the list box parameter to query, I get the warning saying only the first value is considered in the data set, then y do we have list box multiple values allowed?? 
 
I truly appreciate your help... 
 
thanks in advance. </blockquote>
 
 
I sugest using a ; seperator and then use the WITH clouse to create your query like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
WITH PARAMS AS (SELECT ';'||?||';' example
FROM DUAL)
,examples AS (SELECT TRIM(SUBSTR(BEH_GROEP,
INSTR(example, ';', 1, LEVEL ) + 1,
INSTR(example, ';', 1, LEVEL + 1 ) - INSTR(example, ';', 1, LEVEL ) - 1)
) AS example
FROM params
CONNECT BY LEVEL <= LENGTH(example) - LENGTH( REPLACE(example, ';', '' )) -1
)
</pre>
 
more on this method using the With clouse you can find on our company blog: <a class='bbc_url' href='http://enterprisesmartapps.wordpress.com/2011/01/10/re-using-parameters-in-birt-data-set/'>http://enterprisesmartapps.wordpress.com/2011/01/10/re-using-parameters-in-birt-data-set/</a>

manjus.kt

Thnx for the inputs, will try your suggestions..

Hans_vd

Hi,

I thought it might be useful to mention that mcremer's solution is Oracle only.
But it's a very nice piece of SQL :-)

Regards
Hans