nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Parameter with multiple entry

hatra

<p>Hi All I have a new requirement where client wants to enter multiple product code into the parameter text box when runing the report , this values may go to 10s of values. Report run from a sql function and product code is passed to it already as this is a existing parameter. we dont want to modify the function and want to implement the parameter in report designer. Corrently parameter works as we type the value " 12345", and runs for all product with that code.</p>
<p>Now we want to be able to enter " 12345, 23456, 345678, 456789, etc".</p>
<p>any help apprecite it.</p>
<p> </p>

Find more posts tagged with

Comments

JFreeman

<p>You will probably need to override the query text in the beforeOpen of the data set.</p>
<p> </p>
<p>You can parse out the parameter value in beforOpen and the modify the query text using this.queryText.</p>

hatra

<p>Thnaks JF,</p>
<p>could you please give me more detail, how to parse and what to write to modify please. thanks</p>

newbie1

<p>you can use this method here: <a data-ipb='nomediaparse' href='https://code.google.com/a/eclipselabs.org/p/birt-functions-lib/wiki/BindParameters'>https://code.google.com/a/eclipselabs.org/p/birt-functions-lib/wiki/BindParameters</a> </p>
<p>I have used it and it works very well.</p>

hatra

<p>hey thanks newbie1,</p>
<p>I am not sure if this is what I am looking or maybe I miss undrestood, what I want to do it to be able to have multiple values in free text parameter so I can enter more than one value ie: "123456", "6554432", "98765" or more, report runs of sql function and value is already passed into the function, so I dont want to modify the function, is this possible?</p>

newbie1

<p>Oh....for all my search on doing BIRT report, I hadn't run into this. I'll keep an eye out for this ....</p>

JFreeman

<p>You will need to modify the query text in the beforeOpen of the data set with some script to dynamically parse the values of your parameter and add them into the query.</p>
<p>For Example:</p>
<pre class="_prettyXprint _lang-js">
var ordersArray = (params["OrderNumber"].value).split(",");
for(var x=0; x<ordersArray.length; x++){
if(this.queryText.toLowerCase().indexOf("where") != -1){
this.queryText = this.queryText + " or CLASSICMODELS.ORDERS.ORDERNUMBER = " + ordersArray[x];
}else{
this.queryText = this.queryText + " where CLASSICMODELS.ORDERS.ORDERNUMBER = " + ordersArray[x];
}
}
</pre>
<p>Take a look at the attached sample report with this code in place.</p>

hatra

<p>Thanks Jesse,</p>
<p>I have applied it to beforeopen dataset, I removed the parameter from Edit dataset list, I commented this where clause on query ( I still have 8 parameters), I didnot modify or change the function on database. I simply selected all from mytable ( pkg.myquery ( ?,?,?,?,?,?,?,?).</p>
<p>But I get an error sayying "sql statment does not return a ResultSet object.SQL error #1:missing IJN or OUT parameter at index:: 9; java.sql.SQL Exception: Missing IN or OUT parameter at index:: 9.</p>

JFreeman

<p>You should log out what the final query looks like after you have modified it in beforeOpen.</p>
<p>My guess is there is something going wrong when building the query.</p>

hatra

<p>Hi, I have had some testing done and it seems like my function doesnt agree with this logic,</p>
<p>I have 9 params 9?) and the one I am trying to modify to run with "," is number 8 and when I apply the login it thinks that there are 10 "?" and it fails, I need to find a way round it without changing the function</p>

JFreeman

<p>I'm not sure I am understanding your setup.</p>
<p> </p>
<p>Can you attach your report so I can take a look?</p>

hatra

<p>HI Jesse, cause of the security set up I cant attach it here, did you not undrestand the dataset and parametes or the nature of the report?</p>

Matthew L.

<p>I could be way off here but are you calling a stored procedure in the database using 10 input parameters from the report: {call procedure-name(arg1,arg2, ...)} </p>
<p> </p>
<p>If not, then depending on how you are defining the input parameters (List Box, Text Box, etc), this could be accomplished easily with a query modification.</p>
<p> </p>
<p>Similar to Jesse's method, we can dynamically replace a %value% within the query using the data set's beforeOpen statement.</p>
<p>Since this is an array of values separated by a comma, you can use the IN statement for the query.</p>
<p> </p>
<p>The example here: <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/36152-filters-not-adequate-for-large-data-what-then/?p=133966'>http://developer.actuate.com/community/forum/index.php?/topic/36152-filters-not-adequate-for-large-data-what-then/?p=133966</a></p>
<p>has examples for both String inputs and Integer inputs.</p>
<p> </p>
<p>Please note that the query does not have the parameter reference "?" in the SQL as we use a %ReferenceVariable% and replace it with the values from the parameter.</p>
<p> </p>
<p>Here is a code snip from the example:</p>
<pre class="_prettyXprint _lang-sql">
--Query in the Data Set
select CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER,CLASSICMODELS.CUSTOMERS.CUSTOMERNAME,CLASSICMODELS.CUSTOMERS.COUNTRY
from CLASSICMODELS.CUSTOMERS
where CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER IN (%SeeBeforeOpenScript%) --Integer, no single quotes in the IN

</pre>
<pre class="_prettyXprint _lang-js">
//beforeOpen method of the same Data Set
this.queryText = this.queryText.replaceAll('%SeeBeforeOpenScript%',params["IntegerParameter"].value.join("," )); //Integer, no single quotes in the join method
</pre>
<pre class="_prettyXprint _lang-js">
//beforeOpen method of a Data Set that uses String values in the parameter
this.queryText = this.queryText.replaceAll('%SeeBeforeOpenScript%',params["StringParameter"].value.join("','" )); //String, add single quotes to the join method

</pre>

hatra

<p>Thanks for respond Matthew,</p>
<p>I am calling a function from database with 9 parametrers.</p>
<p>select from Table ( my_function (?,?,?,?,?,?,?,?,? ))</p>
<p>Parameer is set to textbox and string , I am not sure how to apply "in " within this statment,</p>
<p>cheers</p>

Matthew L.

<p>It could be the way your function is setup, or how the database driver translates the input.</p>
<p>However in my test using MySQL, I have not run into the same issue that you describe.</p>
<p>Could you provide more details as to how your stored function is defined, or post some detailed notes on how to build an example to replicate the issue?</p>
<p> </p>
<p>In the attached example, I call a function with 9 parameters.</p>
<p>For parameter 8 value, I added an additional comma "," to attempt to throw off the input values.</p>
<p>Please look this example over.</p>
<p>My MySQL simple test function:</p>
<pre class="_prettyXprint _lang-sql">
-- MySQL example
drop function if exists test;
delimiter #
CREATE FUNCTION test(p1 varchar(50), p2 varchar(50), p3 varchar(50), p4 varchar(50),
p5 varchar(50), p6 varchar(50), p7 varchar(50), p8 varchar(50), p9 varchar(50))
RETURNS varchar(100)
begin
return concat(p1,p2,p3,p4,p5,p6,p7,p8,p9);
end#
delimiter ;
</pre>

hatra

<p>Cheers matthew,</p>
<p>function xxxxx</p>
<p>(</p>
<p>post_one IN VARchar2,</p>
<p>all the way same</p>
<p> and</p>
<p>in V_params parat i have somethinf like this</p>
<p>post_eight = NVL (TRIM (post_eight), 'NULL')</p>

JFreeman

<p>I think we need to be clear exactly how your query looks.</p>
<p> </p>
<p>Could you please post the query you have in the BIRT report.</p>
<p>Then also post the query if you were sending it to the data base directly with hard coded values for the parameters.</p>
<p> </p>
<p>This way we can see exactly how your query needs to look vs how it is right now.</p>

SteveRut

<p>Here is how I do multiple parameters. </p>
<p> </p>
<p>The dataset query looks like this:</p>
<p> </p>
<div>SELECT *</div>
<div>FROM</div>
<div>(</div>
<div>SELECT temp FROM temp</div>
<div>)</div>
<div> </div>
<div>Then the beforeOpen has something like:</div>
<div> </div>
<div>query = "Select ..... from.... where ..."</div>
<div> </div>
<div> </div>
<div>
<div>if(params["parametername"].value != null)</div>
<div>{</div>
<div>query = query + " AND (whatever you are querying) IN ('" + params["parametername"].value.join("' , '") + "')";</div>
<div>} </div>
</div>
<p> </p>
<p>this.queryText = this.queryText.replace("SELECT temp FROM temp" , query );<span> </span></p>
<p> </p>
<p>You might need to modify things like the quotes or the query depending on your needs and data types. </p>