Passing dynamic, multiple values to a report parameter in BIRT

Hi,

How to pass a list of comma-separated values in a report parameter text box of type String? How will this be used in the query?

We have tried two approaches.
1) Modifying the query in Property binding window.
This approach works when we pass a string of upto 2 comma separated values. This method failed when I tried to pass in more than 2 comma separated values eg., 1,2,3,4,5
The query text looks similar to the one below:
"SELECT * from <table name> where <column Name> in ('" + params["class"].value.replace(",","','") + "')"
where "class" is a report parameter of type String.

2) Modifying the query in beforeOpen method of the dataset.
I have written a script in beforeOpen method which looks something like as follows:
this.queryText="select * from <table_name> where <column name> in ('" + params["class"].value.replace(",","','") + "')"

and in the Edit Data set window I have placed the below query:
select * from <table_name>

This approach did not work as I expected. It executed the query in the Edit data set window and retrieved all the values and did not execute the script in the beforeOpen method. So this approach also failed to work for multiple values in a parameter.

Please let me know if am missing out something or if there is another approach which will work.

Since am new to BIRT I would be thankful if you could explain me a solution in detail.

Thanks in advance,
Nagashree

Find more posts tagged with

Comments

survik

make use of this statement
"SELECT * from <table name> where <column Name> in ('" + params["class"].value.replace(/,/g,"','") + "')"

Hopes now it will solve ur problem

Christoph

I used "SELECT * from <table name> where <column Name> in ('" + params["class"].value.replace(/,/g,"','") + "')" in the Property Binding dialog to resolve a parameter containing a string of comma separated integers. However, when I preview the report I get the following error: 
 
Failed to prepare the following query for the data set type org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet. 
[SQL statement containing correctly resolved parameter values goes here] 
Error preparing SQL statement. 
SQL error #1: Comparisons between BIGINT and CHAR are not supported. 
 
The <column_name> that I'm using with the IN statement is of type BIGINT. The same error occurs if I construct the queryText in the dataset's beforeOpen method. If I hard-code the values into the query it runs without incident. 
 
Any ideas for resolving this would be much appreciated.

neetira

In
"SELECT * from <table name> where <column Name> in ('" + params["class"].value.replace(/,/g,"','") + "')"

SQL error #1: Comparisons between BIGINT and CHAR are not supported.

your <column Name> is BIGINT. if it is integers, i do not think you have to put the ' after the ( and ' before ) for the IN clause. the ' is only for String/char values.

Try it without '

neeti

neetira

Hi nagashree

How are you passing the values? are you passing them through a JSP or you are entering on the parameter page?

neeti

Ashwind

Hi Nagasree

I am facing the same problem when trying to use string (integers separated commas) in the IN clause . Please let me know the solution

sdlemaster

I am having a similiar issue. When using the IN clause and passing three work order numbers like this '5000','5001','5002'. it only returns the first one and never loops through to get the detail for the last two.

kevinshih

I use the following script in the beforeOpen function of the dataset: 
 
var paramCount = params["rpParamName"].value.length 
var whereClause = ""; 
if( paramCount > 0 ){ 
whereClause = " and table_name.FIELD_NAME in ( "; 
} 
for( i=0; i < paramCount; i++ ){ 
if( i == 0 ){ 
whereClause = whereClause + params["rpParamName"].value; 
}else{ 
whereClause = whereClause + " , " + params["rpParamName"].value; 
} 
} 
if( paramCount > 0 ){ 
this.queryText = this.queryText + whereClause + " ) "; 
} 
 
I made rpParamName a report param of type String with Display Type set to dynamic list box that allows multiple values. My JSP form passes in a comma delimited string as rpParamName. The script parses out the string and forms the rest of the query string. Works whether one or more values are passed in from the form. 
 
HTH

Ashwind

click on the report parameter.
go to the properties--> Advance
There will be a property with name "Scalar Parameter Type". Make it as simple, that should do it.

In the script for that data set I used this way

this.queryText = this.queryText + reportContext.getParameterValue("MedID") + ")";

where MedId is the report parameter

querytext=
select col1,col2,col3 from table where table.column in (

drqbass

<blockquote class='ipsBlockquote' data-author="kevinshih">I use the following script in the beforeOpen function of the dataset: 
 
var paramCount = params["rpParamName"].value.length 
var whereClause = ""; 
if( paramCount > 0 ){ 
whereClause = " and table_name.FIELD_NAME in ( "; 
} 
for( i=0; i < paramCount; i++ ){ 
if( i == 0 ){ 
whereClause = whereClause + params["rpParamName"].value; 
}else{ 
whereClause = whereClause + " , " + params["rpParamName"].value; 
} 
} 
if( paramCount > 0 ){ 
this.queryText = this.queryText + whereClause + " ) "; 
} 
 
I made rpParamName a report param of type String with Display Type set to dynamic list box that allows multiple values. My JSP form passes in a comma delimited string as rpParamName. The script parses out the string and forms the rest of the query string. Works whether one or more values are passed in from the form. 
 
HTH</blockquote>
 
hey kevinshih i have a trouble with passing parameters to my birt report i hope you can help me. 
 
I have to send to my params a query column of parameters i mean the parameters are dynamic sent; when de user push the link of the report he going to see a lot of reports with the parameters of the query. How can i do this??? please help me i don't know how to do that.

Pradeep Kumar Rout

Hi All,
We have a requirement that suppose the user gives a certain input parameter like 'abc' then the report will search the entire hierarchy of 'abc' i.e. abc1 abc2-1 ,abc3-11.......abcn. So is it feasible through an input parameter or any dynamic function ? THANK YOU !!