3

I have a problem with BIRT when I try to pass multiple values from report parameter.

I'm using BIRT 2.6.2 and eclipse.

I'm trying to put multiple values from cascading parameter group last parameter "JDSuser". The parameter is allowed to have multiple values and I'm using list box.

In order to be able to do that I'm writing my sql query with where-in statement where I replace text with javascript. Otherwise BIRT sql can't get multiple values from report parameter.

My sql query is

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 that dataset on beforeOpen state is:

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 tryed many different quote variations, with this one I get no error messages, but if I choose 1 value, I get no data from database, but if I choose at least 2 values, I get the last chosen value data.

If I uncomment one of those additional quote script lines, then I get syntax error like this:

The following items have errors: Table (id = 597): + An exception occurred during processing. Please see the following message for details: Failed to prepare the query execution for the data set: Organization Cannot get the result set metadata. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object. SQL error #1:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rudolfs.sviklis', 'sample' )' at line 25 ; com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rudolfs.sviklis', 'sample' )' at line 25

Also, I should tell you that i'm doing this by looking from working example. Everything is the same, the previous code resulted to the same syntax error, I changed it to this script which does the same. The example is available here: http://developer.actuate.com/community/forum/index.php?/files/file/593-default-value-all-with-multi-select-parsmeter/

If someone could give me at least a clue to what I should do that would be great.

Improve this question

1 Answer 1

Reset to default
3

You should always use the value property of a parameter, i.e.: 

var users = params["JDSuser"].value;

It is not necessary to surround "userquery" with quotes because these quotes are already put in the SQL query arround 'sample'. Furthermore there is a mistake because userquery is not yet defined at line:

var userquery = userquery + users.join("', '");

This might introduce a string such "null" in your query. Therefore remove all references to userquery variable, just use this expression at the end:

this.queryText=this.queryText.replaceAll('sample', users.join("','"));

Notice i removed the blank space in the join expression. Finally once it works finely, you probably need to make your report input more robust by testing if the value is null:

if( params["JDSuser"].value!=null && params["JDSuser"].value[0] != "(All Users)" ){ //Do stuff... }
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for your reply, though this didn't help me. I get no data either I choose 1 value or 2 values. Thought you save me few lines of code and not null value is good too. Any other ideas?
Hmm it should work though. I would recommend to save the content of the final "this.queryText" in a global variable, and display temporarily this variable in a dynamic text of the report. Thus you will see the generated query, and it would probably make it easy to find out what is wrong.
Thanks Dominique, your advice for global variables helped me, i found out, that I had left different names to replace for group parameter, so It didn't change. This works. Now I have to figure out how to fix issue to show All users, because It doesn't work.
I am glad it helped. An easy way to handle "All values" is to add a "OR" clause checking if the value of a dataset parameter is equals to a specific constant value. Otherwise your script should be modified to include the last "AND" clause of the query if and only if the parameter value is not "All values".
Unfortunately I can't get "All values" to work, it only gets it as simple All Users text, though I have computed column which supposedly creates entries for All Users options. I assume that it somehow doesn't give those values to All Users? Do you have any idea?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.