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)
Passing multiple values on a single parameter of type NVCHAR2
Anish_TS
<p>BERT version: 2.5.2</p><p> </p><p>I am trying to understand how one would pass multiple values on a single parameter.</p><p> </p><p>An example:</p><p>I am looking for movies in a movies table</p><p> </p><p>The movies are arranged into genres:</p><p>action,</p><p>comedy,</p><p>childrens,</p><p>horror</p><p> </p><p>I want to create a parameter which would retrieve:</p><p>action,comedy,childrens but not horror</p><p> </p><p>AND another parameter that should show just horror.</p><p> </p><p>Thanks</p><p> </p><p> </p>
Find more posts tagged with
Comments
Anish_TS
<p>When I do try and use multiple values I get the following error:</p><p> </p><p><span>+ </span>An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: Movies
Cannot set the string value ("Comedy","Action") to parameter 1.
Cannot set preparedStatement parameter string value.
SQL error #1: Invalid column index</p>
micajblock
<p>check this out</p><p> </p><p><a data-ipb='nomediaparse' href='
http://www.birt-exchange.org/devshare/_/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause'>http://www.birt-exchange.org/devshare/_/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause</a></p>
;
Anish_TS
<blockquote class="ipsBlockquote" data-author="mblock" data-cid="120506" data-time="1379593073"><div><p>check this out</p><p> </p><p><a data-ipb='nomediaparse' href='
http://www.birt-exchange.org/devshare/_/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause'>http://www.birt-exchange.org/devshare/_/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause</a></p></div></blockquote><p>Just
tried it out and the list comes out blank in the parameters</p>
micajblock
<p>I just downloaded and tested and I saw the list. What version of BIRT are you using?</p>
Anish_TS
<blockquote class="ipsBlockquote" data-author="mblock" data-cid="120513" data-time="1379602562"><div><p>I just downloaded and tested and I saw the list. What version of BIRT are you using?</p></div></blockquote><p>[color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]BERT version: 2.5.2[/color]</p><p> </p><p>[color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]Instead of using on OpenScript I am trying it on beforeFactory.[/color]</p><p> </p><p>[color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]I've modified it as follows:[/color]</p><pre class="_prettyXprint _linenums:0">if(params["Movies"].value=="All"){this.queryText = this.queryText.replace("All", "''Horror','Comedy'");}</pre>
micajblock
<blockquote class="ipsBlockquote" data-author="Anish_TS" data-cid="120515" data-time="1379602992"><div><p> </p><p>[color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]Instead of using on OpenScript I am trying it on beforeFactory.[/color]</p><p> </p></div></blockquote><p>Why? The beforeFactory does not have a 'this.queryText'. You need to put the script in the beforeOpen of the dataset. BTW, what does your query look like?</p>
Anish_TS
<blockquote class="ipsBlockquote" data-author="mblock" data-cid="120516" data-time="1379603271"><div><p>Why? The beforeFactory does not have a 'this.queryText'. You need to put the script in the beforeOpen of the dataset. BTW, what does your query look like?</p></div></blockquote><div>I was told to use beforeFactory because of the way I have multiple columns and I wish to merge them I can use beforeFactory so that it could generate the parameters and THEN change them.</div><div> </div><div> </div><div> </div><div> </div><div>SELECT row_number() over(order by Total_NUMBER DESC) AS rn,</div><div> MovieID,</div><div> MoviedDesc,</div><div> Total_NUMBER,</div><div> movieType</div><div>FROM(</div><div> </div><div><div>)</div><div> <span> </span>WHERE MovieID IS NOT NULL</div><div> <span> </span>AND movieType IN ?</div><div> GROUP BY MovieID,</div><div> MovieDesc,</div><div> movieType</div><div> ORDER BY TOTAL_NUMBER DESC<span style="font-size:14px;"> </span></div><div><span style="font-size:14px;">)</span></div></div>
micajblock
<p>Multi-select parameters in BIRT return an array. You cannot use an array in a SQL query. Even if you cannot run my report look at the script.</p><p> </p><p>The query should not have a '?', but something like movieType in ('All')</p>
Anish_TS
<div><blockquote class="ipsBlockquote" data-author="mblock" data-cid="120519" data-time="1379604948"><div><p>Multi-select parameters in BIRT return an array. You cannot use an array in a SQL query. Even if you cannot run my report look at the script.</p><p> </p><p>The query should not have a '?', but something like movieType in ('All')</p></div><div> </div></blockquote></div><p> </p><p> </p><p>Apologies for my explanation I am not trying to do a multi select and instead am trying to limit the users choices so that they can ONLY see </p><p>- 'All' which represents action,comedy,horror</p><p>- 'Childrens'</p><p> </p><p>Hope that clears things up</p>
micajblock
<p>Same trchnique but have the paramter a hard coded list with two items. The first have a display text of 'All' with a value of (note the quotes)</p><p> </p><p>[color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]action','comedy','horror[/color]</p><p> </p><p>[color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]...and the second both display and value of children.[/color]</p>
