1658

Multiple parameter values

Question:

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:

<blockquote> 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 </blockquote>

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: <a href="http://developer.actuate.com/community/forum/index.php?/files/file/593-default-value-all-with-multi-select-parsmeter/" rel="nofollow">http://developer.actuate.com/community/forum/index.php?/files/file/593-default-value-all-with-multi-select-parsmeter/</a>

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

Answer1:

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... }

Recommend

  • jQuery Multiselect delegate event using “on”
  • Fetch new MySQL entries and append to existing div's dynamically
  • Label NSMutableAttributedString programmatically swift 4
  • types mismatch in VBScript
  • Complex sql statement for showing the table names
  • Can't get my SSIS package to run
  • Extract data from html to csv using BeautifulSoup
  • Pass variable to RegExp with word boundary
  • bash, zsh : declare [*] (oh my)
  • Parsing html using agility pack
  • solving two linear equation in program [closed]
  • how do add multi sql dependency to this code?
  • how to assign option string containing to editions value attribute of jqGrid
  • “mvn: not found” in Jenkins slave
  • Python Scrapy Get HTML tag
  • Passing multiple parameters to Crystal Report
  • How do i join the rows into dynamic columns in this case? [duplicate]
  • Cannot log into SQL Server in mssql-server-linux container
  • Recursive function for matrix determinant calculator
  • Exception: System.ArgumentException: Keyword not supported: 'initial catalog' when hard co
  • SQL Server 2008 - Attach mdf without log - Operating system error 5: 5(Access is denied.)
  • SQLException: the result set is closed
  • Entity Framework Connection String Definition
  • Using mgsub function with word boundaries for replacement values
  • create a dynamic SUM column in SQL
  • Dynamically populate a dropdown box with Jquery and Java
  • Rectangular Nesting - Convergence to optimal solution using Simulated Annealing
  • handling sigterm in OSx
  • Grails error: table or view does not exist
  • dbus Variant: How to preserve boolean datatype in Python?
  • Keep same scale in different graphs ggplot2
  • Stacked Bar Chart with percentage composition inside the Bar and total above the Bar in JFreeChart
  • Can you use DataSet and DataTables in a Portable Class Library
  • Unix Network Programming Clarification
  • How to return DataSet (xsd) in WCF
  • json Serialization in asp
  • Acquiring multiple attributes from .xml file in c#
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • How can I remove ASP.NET Designer.cs files?
  • java string with new operator and a literal