Multi Select List Input Control

[gazza]

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.

[lucianc]

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

[Flokky]

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.

[Flokky]

I'm still stuck. Any input would be great!

 

Thx

[lucianc]

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

[Flokky]

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!

[lucianc]

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

[Flokky]

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! ;)

[rachaputis]

 

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

[kidpollo]

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 is

Code:

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!=1 AND 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?

[kidpollo]

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?

[Simgen]

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 :(

[lucianc]

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

[javydreamercsw]

Any IDE like Eclipse or Netbeans make the jar automatically as part of the build process.

 

It can be made manually but why bother?

[Simgen]

thanks all I resolved with $P! without java.util.Collection

[csrinaldi]

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!!