Jump to content
Changes to the Jaspersoft community edition download ×

Multi Select List Input Control


gazza

Recommended Posts

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

  • Replies 15
  • Created
  • Last Reply

Top Posters In This Topic

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

  • 2 weeks later...

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

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

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

  • 3 weeks later...

 

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

  • 5 months later...

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?

Link to comment
Share on other sites

  • 7 months later...

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

  • 6 years later...

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...