Eclipse Community Forums: BIRT » Make query works with multiple values for one parameter [solved]

Help

Home

Home » Archived » BIRT » Make query works with multiple values for one parameter [solved]

Show: Today's Messages :: Show Polls :: Message Navigator

Make query works with multiple values for one parameter [solved] [message #1255227]

Mon, 24 February 2014 08:09

José Litux

Messages: 52
Registered: March 2013

Member

Hi all

I got a multiple selection ListBox as parameter input.

My problem is that the query must be:

select * from table where parameter_field in (value1,value2,value3......)

I've tried this piece of code in the beforeOpen for the dataset

this.queryText =this.queryText.replace("999999",params["myparameter"].value.join("','" )); + "

taking into account that i've reformatted my query like this:

select * from table where parameter_field in (999999);

but i'm getting this error:

org.eclipse.birt.report.engine.api.EngineException: There are errors evaluating script "this.queryText = this.queryText.replace("999999", params["myparameter"].value.join("','" ));":
Fail to execute script in function __bm_beforeOpen(). Source:
------
" + this.queryText = this.queryText.replace("999999", params["myparameter"].value.join("','" )); + "

-----

any help must be very appreciated

[Updated on: Tue, 25 February 2014 17:22]

Report message to a moderator

Re: Make query works with multiple values for one parameter [message #1255724 is a reply to message #1255227]

Mon, 24 February 2014 19:05

donino donino

Messages: 183
Registered: July 2011

Senior Member

There is a small typo at the end of your script, remove:

+ "

Though this won't be enough, depending on the datatype of "parameter_field" you have to change something:

- If type is Integer, then your script should be (remove quotes in join expression):

this.queryText =this.queryText.replace("999999",params["myparameter"].value.join(","));

- If type is String, then your script is okay but the original query should have quotes wrapping 99999:

select * from table where parameter_field in ('999999');

Let me know if it helps.

Report message to a moderator

Re: Make query works with multiple values for one parameter [message #1256222 is a reply to message #1255724]

Tue, 25 February 2014 08:04

José Litux

Messages: 52
Registered: March 2013

Member

Hi donino

All my params are integer but is Not Working.

beforeOpen

this.queryText =this.queryText.replace(999999,params["myparameter"].value.join(","));

org.eclipse.birt.report.engine.api.EngineException: There are errors evaluating script "this.queryText =this.queryText.replace(999999,params["myparameter"].value.join(","));":
Fail to execute script in function __bm_beforeOpen(). Source:
------
" + this.queryText =this.queryText.replace(999999,params["myparameter"].value.join(",")); + "
-----

there is not a typo. Some characters are added by Birt to the expression, not me.

[Updated on: Tue, 25 February 2014 08:13]

Report message to a moderator

Re: Make query works with multiple values for one parameter [message #1256421 is a reply to message #1256222]

Tue, 25 February 2014 12:12

donino donino

Messages: 183
Registered: July 2011

Senior Member

Hi José,

Although the constant value is an integer in the query, you mustn't remove quotes around 999999 in the "replace" method, as you did in your last post. This is a constant String here, so the statement should be:

this.queryText=this.queryText.replace("999999",params["myparameter"].value.join(","));

And the query:

select * from table where parameter_field in (999999);

This should really work. If it doesn't, the only possible cause is params["myparameter"].value is null or not set as expected. Is this dataset actually used in the body of the report, or is it used to fill selection choices of another parameter?

For investigations, set a valid default value in the parameter definition, and also create a dynamic text in the report with expression:

params["myparameter"].value.join(",")

Let me know if it helps

[Updated on: Tue, 25 February 2014 12:16]

Report message to a moderator

Re: Make query works with multiple values for one parameter [message #1256620 is a reply to message #1256421]

Tue, 25 February 2014 16:14

José Litux

Messages: 52
Registered: March 2013

Member

Hi donino

tested both with and without quotes but to no avail. Default parametter value set to 0.

Dynamic text show correctly the chosen values:

58,57,62,71

org.eclipse.birt.report.engine.api.EngineException: An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: operativos
Cannot set the string value (58) to parameter 1.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:Parameter index out of range (1 > number of parameters, which is 0).
;

[Updated on: Tue, 25 February 2014 16:41]

Report message to a moderator

Re: Make query works with multiple values for one parameter [message #1256673 is a reply to message #1256620]

Tue, 25 February 2014 17:21

José Litux

Messages: 52
Registered: March 2013

Member

Hi donino

solved. Parameter must be deleted from Dataset Properties. Sadly, this must be done one by one because Birt creates an empty parameter for every dataset.

thanks a lot

Report message to a moderator

Re: Make query works with multiple values for one parameter [message #1256677 is a reply to message #1256620]

Tue, 25 February 2014 17:25

donino donino

Messages: 183
Registered: July 2011

Senior Member

The error message suggests there is some query parameter (a question mark '?') in the dataset.

Typically, this error occurs when there is a question mark in the original query, which is dynamically removed by a script such beforeOpen. If possible you should post .rptdesign

EDIT: ok im glad you solved it Smile

[Updated on: Tue, 25 February 2014 17:26]

Report message to a moderator

Re: Make query works with multiple values for one parameter [message #1697135 is a reply to message #1256677]

Mon, 01 June 2015 20:50

RIcky Mercado

Messages: 3
Registered: June 2015

Junior Member

I was looking for a solution to a similar problem in my dataset and this solved it. Thanks!

Report message to a moderator

Re: Make query works with multiple values for one parameter [solved] [message #1765347 is a reply to message #1255227]

Thu, 08 June 2017 15:14

Shweta D

Messages: 4
Registered: June 2017

Junior Member

Hi,

I would like to revisit this topic. I have been working on multiple values in a single parameter for a Birt report and facing some issues with it. In my case instead of a separate DataSet the parameter values are coming from a JSP page in the report url: (as shown below)

?reportDesign=tcDetail.rptdesign&rpAccountID=tc01&rpComponentType=T,P

Have defined a report parameter rpComponentType with the following details:

DataType: String

DisplayType: List Box

Selection list values: Dynamic (Allow Multiple Values has been checked)

Data set: Have selected the same Dataset(defined for the report)

Select value column: COMPONENT_TYPE

Select display text: COMPONENT_TYPE

Default value: none

Have modified the Dataset sql:

select id, descr from tcTable where ext_Id = ? and component_Type in ('999');

And added the below code in beforeOpen method of Dataset:

this.queryText = this.queryText.replace("999", params["rpComponentType"].value.join("','"));

With all these changes in place, my report is displaying the data only if one option is passed from JSP (T or P). If I select both, then the report gives the below error:

org.eclipse.birt.report.engine.api.impl.ParameterValidationException: Required parameter rpComponentType is not set.

If I choose only one option - T or P, it shows corresponding data correctly (one at a time). The SQL uses IN instead of =, so not sure why is it only working with one parameter value.

Also if for any option there is no corresponding data in db, then the sql when executed in database returns blank (as expected it doesn't error). But the report gives the same error as above: org.eclipse.birt.report.engine.api.impl.ParameterValidationException: Required parameter rpComponentType is not set.

My BIRT Version: 4.4.1 and I am using embedded tomcat 8.0.30.

I even tried the following, but again it works with only one parameter value T or P, not with both (getting same error as in above case):

Modified the Dataset sql:
select id, descr from tcTable where ext_Id = ? and component_Type in (?);

And added the below code in beforeOpen method of Dataset:

this.queryText = this.queryText.replace("component_Type in (?)", "component_Type in ('" + params["rpComponentType"].value.join("','") + "')");

Made no updates to report parameter. I noticed in every example on various forums and youtube videos, a secondary dataset is used to populate the parameter values. Is it necessary to populate the report parameter with a secondary dataset? Will it not work with values coming from JSP? Do I need to set up things differently for my scenario?

If I add default values in report parameter, then it shows data for all the options defined as the default value, irrespective of what is chosen on the UI screen (and passed through JSP).

Have been struggling with this entire week and would appreciate any suggestion on how to make this work. I would be happy to provide more details if needed.

Thanks,
Shweta

Report message to a moderator