Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Birt Report Parameter
manjus.kt
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 :<br />
<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>
<br />
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"><p>
Hi,<br />
Last week I ran into a problem and still searching for the clue. <br />
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<br />
<br />
where column_name in (parameter.list_box).<br />
<br />
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.<br />
<br />
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??<br />
<br />
I truly appreciate your help...<br />
<br />
thanks in advance.<br /></p></blockquote>
<br />
<br />
I sugest using a ; seperator and then use the WITH clouse to create your query like this:<br />
<br />
<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>
<br />
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
