Home » Archived » BIRT » Make query works with multiple values for one parameter [solved]
| Make query works with multiple values for one parameter [solved] [message #1255227] |
Mon, 24 February 2014 03:09  |
Eclipse User |
|
|
|
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 12:22] by Moderator |
|
| | |
| Re: Make query works with multiple values for one parameter [message #1256421 is a reply to message #1256222] |
Tue, 25 February 2014 07:12  |
| Eclipse User |
|
|
|
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 07:16] by Moderator |
|
| | | | |
| Re: Make query works with multiple values for one parameter [solved] [message #1765347 is a reply to message #1255227] |
Thu, 08 June 2017 11:14 |
| Eclipse User |
|
|
|
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
|
|
| |
Goto Forum:
Current Time: Mon Jul 07 23:15:54 EDT 2025
Powered by FUDForum. Page generated in 0.04241 seconds |
Home