gazza Posted October 3, 2006 Share Posted October 3, 2006 Hi I have created a report that uses the Multi-Select List Control in JasperIntelligence 1.1.0. What type of object is passed to the report from this control if multiple items are selected and what syntax do I use to access these items. Link to comment Share on other sites More sharing options...
lucianc Posted October 3, 2006 Share Posted October 3, 2006 Multi-select controls produce ordered sets of list item IDs/keys as parameter values. You should therefore declare your report parameter as java.util.Collection or java.util.Set. Having the report parameter declared, the standard JasperReports syntax rules apply. Note that if you want to use the parameter in a SQL "IN" clause, you can't simply use "where column in $P{..}" since passing the collection as a JDBC parameter is not supported in this case. You need to concatenate the values in a comma-separated string and use "where column in (P!{..})" for this. HTH,Lucian Link to comment Share on other sites More sharing options...
Flokky Posted October 12, 2006 Share Posted October 12, 2006 Hi Lucianc, I've read your post very carefully and must say it contains information which I haven't found before. I've tried what you've said and everything is working OK, except executing the query doesn't work. This is my query: select a,b,c from table where aColumn in ($P!{param}) where param is defined in the report as java.util.Collection. The error has to do with using brackets in my sql: java.sql.SQLException: 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 '[OK2, OK3]). Could you eleborate a bit more what you mean with your last sentence? Thank you. Link to comment Share on other sites More sharing options...
Flokky Posted October 16, 2006 Share Posted October 16, 2006 I'm still stuck. Any input would be great! Thx Link to comment Share on other sites More sharing options...
lucianc Posted October 16, 2006 Share Posted October 16, 2006 So you have a java.util.Collection report parameter and you want to use it in an SQL "IN (..)" clause. Since you can't do "IN $P{list}", you'll need to use a parameter to generate a query fragment ($P!{..} syntax). When you have $P!{..} in a query, the String value of the parameter is simply substituted in the query string. The query to be executed by JR should look like "SELECT .. WHERE COLUMN IN (5, 14, 19)" or "SELECT .. WHERE COLUMN IN ('aa', 'bb', 'cc')". Bearing this in mind, you need to have a parameter whose String representation is "5, 14, 19" or "'aa', 'bb', 'cc'". As the toString() methods of collections don't (usually) produce such output, you'll need to create this String yourself and use it in the query. So you could write an utility class like this (the code assumes that your collection values' toString() produces output that can be recognized by the SQL engine): Code:public class SQLUtils{ public static String enumerate(Collection values, boolean quote) { StringBuffer sb = new StringBuffer(); for (Iterator it = values.iterator(); it.hasNext();«») { Object value = it.next(); sb.append(','); if (quote) { sb.append('''); } sb.append(value); if (quote) { sb.append('''); } } return sb.substring(1); }} Then you can create a new parameter, use its default value expression to enumerate the collection values and use this parameter in the query:Code:[code]<parameter name="values" class="java.util.Collection"/><parameter name="enumeratedValues" isForPrompting="false"> <defaultValueExpression>SQLUtils.enumerate($P{values}, false/true)</defaultValueExpression></parameter><queryString>SELECT .. WHERE COL IN ($P!{enumeratedValues})</queryString> HTH,Lucian Link to comment Share on other sites More sharing options...
Flokky Posted October 16, 2006 Share Posted October 16, 2006 Hi Lucian, Thank you for the detailed description. After thinking through your suggested solution, I've got one question. Where can you place the utility class when you are using iReport 1.2.7? I've already tried using the 'Scriptlet Editor' in the 'Edit' menu. But when I look into the XML, the utility class isn't there, and when deploying it to the JasperIntelligence server, the parser doesn't know the method. Thank you for your guidance! Link to comment Share on other sites More sharing options...
lucianc Posted October 16, 2006 Share Posted October 16, 2006 If you plan to use this utility class often, the simplest thing would be to compile it, pack it into a jar and place it on the web application's classpath (i.e. under WEB-INF/lib). This was you'd be able to use it from any report. Regards,Lucian Link to comment Share on other sites More sharing options...
Flokky Posted October 16, 2006 Share Posted October 16, 2006 That did the trick. I thought this code was supposed to be in the JRXML file. I've placed it in a jar and placed it under the WEB-INF/lib and voila, that works great! Thank you again for helping me out! ;) Link to comment Share on other sites More sharing options...
rachaputis Posted November 2, 2006 Share Posted November 2, 2006 Hello, I use iReport 1.2.3. I am trying to define a report input parameter which can display a selection list for the usre, so that the user can select one of the listed items as the input parameter. In the where clause of the SQL query, I write: AND D.DRUG_GCN_SEQ IN (P!{param}) I define the report input parameter by clicking view -> and selecting parameters. - I created a parameter as follows: - Parameter name: param - Parameter class type: java.util.collection - Is for prompting (checked) - Default value expression box(left empty) - Parameter description: select a GCN seq number I think this is the only forum where a selection list for input parameters using iReport is mentioned. I read through the forum many a times and I am confused about the steps I need to take from here. A detailed description of the steps for using iReport 1.2.3 would be greatly appreciated. Looking forward to your response,Thanks a bunch in advance,rachaputis Link to comment Share on other sites More sharing options...
kidpollo Posted April 26, 2007 Share Posted April 26, 2007 I tried this you mentioned in this post, the problem is that the parameter in the query always gets a null value an therefor it does not work: I get no errors just a blank report $P{multi_product_input} is the parameter received with the mullti select my paramenter is $P{case_product}th default value isCode:SQLUtils.enumerate($P{multi_product_input}, true) my query is: Code:[code]SELECT c.id, c.case_number, a.name as account_name, c.name, c.status, ct.case_serial_number_c , c.description, c.resolution, ct.case_diagnostic_c,c.date_entered, c.date_modified, u.user_name FROM cases c JOIN cases_cstm ct ON c.id=ct.id_c JOIN accounts a ON a.id=c.account_id JOIN users u ON u.id=ct.case_attended_by_c WHERE c.deleted!=1AND c.date_entered BETWEEN $P{begin_date} AND $P{end_date}AND ct.case_related_product_c IN ($P!{case_product}) AND c.status LIKE $P{case_status}AND c.priority LIKE $P{case_priority}AND c.account_id LIKE $P{account_id}AND c.assigned_user_id LIKE $P{assigned_user_id}AND ct.case_fee_c LIKE $P{case_fee} any idea why the case_product parameter is getting null even if the multi_product_input is getting the values correctly? Link to comment Share on other sites More sharing options...
kidpollo Posted April 27, 2007 Share Posted April 27, 2007 B) I GOT it!! like in math ! the order of the factors does affect the outcome!!!!! multi_select parameter must be declared first that the string parameter puto on the query is this not a bug? Link to comment Share on other sites More sharing options...
Simgen Posted December 11, 2007 Share Posted December 11, 2007 lucianc wrote:If you plan to use this utility class often, the simplest thing would be to compile it, pack it into a jar and place it on the web application's classpath (i.e. under WEB-INF/lib). This was you'd be able to use it from any report.Regards,Lucian How I compile it? How I pack it into a jar? p.s.: I'm sorry for my ignorance :( Link to comment Share on other sites More sharing options...
lucianc Posted December 12, 2007 Share Posted December 12, 2007 There are plenty Java tutorials that teach how to compile and package code. I don't think this forum should cover that. Also take a look at this post, there are now easier ways to use IN/NOT IN clauses in report queries. Regards,Lucian Link to comment Share on other sites More sharing options...
javydreamercsw Posted December 12, 2007 Share Posted December 12, 2007 Any IDE like Eclipse or Netbeans make the jar automatically as part of the build process. It can be made manually but why bother? Link to comment Share on other sites More sharing options...
Simgen Posted December 12, 2007 Share Posted December 12, 2007 thanks all I resolved with $P! without java.util.Collection Link to comment Share on other sites More sharing options...
csrinaldi Posted January 27, 2014 Share Posted January 27, 2014 Lucianc: Thanks for great article!!! I probe the solution, and go right to the following settings: ... new ArrayList(Arrays.asList(new Integer[] {4,2,6,8})) ... And part of the SQL query is: ... (motivo.id IN ($P!{enumeratedValues}) ... But .... Only work for the default value of values parameter. I use a jasperser and postgres datasource, when change the value of parameter values in the repor page, the enumeratedValues value still having the default value given by defaultValueExpression (4,2,6,8) and not the values selected. Any Idea? Thanks!! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now