Many parameter values
I have a problem with BIRT when I try to pass multiple values from a report parameter.
I am using BIRT 2.6.2 and eclipse.
I am trying to put multiple values from the last parameter parameter of the cascading parameter group "JDSuser". The parameter is allowed to have multiple values and I am using a list.
To do this, I write my sql query with a where-in clause where I replace the text with javascript. Otherwise, BIRT sql cannot get multiple values from a report parameter.
My sql query
select jamacomment.createdDate, jamacomment.scopeId,
jamacomment.commentText, jamacomment.documentId,
jamacomment.highlightQuote, jamacomment.organizationId,
jamacomment.userId,
organization.id, organization.name,
userbase.id, userbase.firstName, userbase.lastName,
userbase.organization, userbase.userName,
document.id, document.name, document.description,
user_role.userId, user_role.roleId,
role.id, role.name
from jamacomment jamacomment left join
userbase on userbase.id=jamacomment.userId
left join organization on
organization.id=jamacomment.organizationId
left join document on
document.id=jamacomment.documentId
left join user_role on
user_role.userId=userbase.id
right join role on
role.id=user_role.roleId
where jamacomment.scopeId=11
and role.name in ( 'sample grupa' )
and userbase.userName in ( 'sample' )
and my javascript code for this dataset in the beforeOpen state:
if( params["JDSuser"].value[0] != "(All Users)" ){
this.queryText=this.queryText.replaceAll('sample grupa', params["JDSgroup"]);
var users = params["JDSuser"];
//var userquery = "'";
var userquery = userquery + users.join("', '");
//userquery = userquery + "'";
this.queryText=this.queryText.replaceAll('sample', userquery);
}
I tried many different options for quotes, with this I get no error messages, but if I select one value, I don't get any data from the database, but if I select at least 2 values, I get the last selected data values.
If I uncomment one of these additional script quotes, then I get a syntax error like this:
The following items have errors: Table (id = 597): + An exception occurred during processing. For more information, see the following message: Failed to prepare query execution for dataset: Organization Failed to retrieve result set metadata. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return ResultSet object. SQL Error # 1: You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the correct syntax to use next to 'rudolfs.sviklis',' sample ')' on line 25; com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the correct syntax to use next to 'rudolfs.sviklis ',' sample ')' on line 25
Also, I must tell you that I am doing this by looking at a working example. Everything is the same, the previous code resulted in the same syntax error, I changed it to this script which does the same. An example is available here: http://developer.actuate.com/community/forum/index.php?/files/file/593-default-value-all-with-multi-select-parsmeter/
If someone can even give me a clue as to what I should be doing, that would be great.
source to share
You should always use the value property of the parameter, i.e .:
var users = params["JDSuser"].value;
There is no need to surround "userquery" with quotes, because those quotes are already placed in the SQL query arround "sample". Also, there is a bug as the custom query is not yet defined on the line:
var userquery = userquery + users.join("', '");
This may result in the string "null" in your request. So remove all references to the userquery variable, just use this expression at the end:
this.queryText=this.queryText.replaceAll('sample', users.join("','"));
Note that I've removed the white space in the join expression. Finally, once this is done, you should probably make your report more robust by testing if the value is null:
if( params["JDSuser"].value!=null && params["JDSuser"].value[0] != "(All Users)" ){
//Do stuff...
}
source to share