multiple value parameterised

[seesuresh]

Hi,

 

I have created parameterised reports in iReport Designer (of parameter Class type "java.lang.String") it works in IR. and deployed this in JS and created a Input control of Type Muilt-select Query to pass muiltiple values to the report. when executed it throws error "nested exception is com.jaspersoft.jasperserver.api.JSException: Unknown parameter type java.lang.Stringfor multiple value input"

 

Any help or information on this would be greatful.

 

Regards,

Suresh.

[csbac]

Hello,

I understand your problem because I have just been trying to do the same ...

There are two problems there.

 

* JasperServer passes Multi-Selection values to the Jasper report in a class that implements Collection. Thus, if you define the report parameter as java.util.Collection, you can access it from the report and print it etc.

As a default value in the parameter definition, you can use, e.g., Collections.singletonList("entry") or Arrays.asList(new String[] {"entry1", "entry2" }).

This works for me w/o any problem.

 

* If you want to use this parameter in an SQL query, like in

SELECT * FROM TABLE WHERE idSth in ($P!{param}), you need to look at the post

http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=&func=view&catid=8&id=19639

 

The only viable way is to convert the multi-selection list (Collection) in a string list, and set this as

a parameter.

Take care of the "!" in $P!{PARAM}, though. Otherwise, the string is passed as JDBC parameter, and that one only places the single string into the "in (?)", not as a list of items/strings.

 

I will have to find out how to best do this conversion; toString() does not work, as it writes

[1,2,3] in case of an Integer collection ... maybe a replaceAll call afterwards?

 

Yours,

Sebastian

Post edited by: csbac, at: 2007/03/16 12:44

[teodord]

Hi,

 

I just want to raise an issue with this.

Many have wondered why the support for multi-value input controls is not as transparent as possible.

I think this is because of the complexity of using such parameters in queries.

 

You have already shown that a multi-value input control could be expanded into a "WHERE IN" clause inside the SQL query. Yes, this can be even automated in JasperReports so that we could detect arrays or collections passed into the SQL query using a $P{} token.

 

An SQL query like the following:

 

SELECT * FROM MyTable WHERE myCol IN $P{multiValParam}

 

could easily be transformed into:

 

SELECT * FROM MyTable WHERE myCol IN (?, ?)

 

when two values are passed for the multiValParam.

 

It could even work with a single value passed in:

 

SELECT * FROM MyTable WHERE myCol IN (?)

 

But what if no value is supplied for the multiValParam?

Should we execute this?

 

SELECT * FROM MyTable WHERE myCol IN (null)

 

I'm pretty sure this is not desired, as the end user would actually expect the whole filter on myCol to be dropped. But there is no way for us to know how the multi-value parameter is used in the query, can we.

 

So my point is this:

Using multi-value parameters imply dynamic queries.

And the logic involved when dynamically building queries is the responsibility of the person designing the report. Conditional expressions need to be used in order to adapt the SQL query to the actual parameter values that are passed in at runtime. This kind of logic could reside in the <defaultValueExpression> of some dummy report parameters in order to have everything at JRXML level only.

 

Feedback is welcomed.

Teodor

[anandharaj]

I also having the same issue. I have created multi-select query (check box), but i cant pass it to a parameter which is java.lang.String. If i chang to java.util.Collection also doesn't work. what am i suppose to do?

 

I want to use it in WHERE IN clause.

[csbac]

Hi!

How far do you get?

As I explained below, the parametere needs to be defined as a Collection. Then, you should at least be able to print it in some report field (just use $P{param} as field value).

Also, $P{param}.getClass() should give you the name of the class (some apache collection implementation).

 

For converting the string list into the format needed for WHERE ... IN ($P!{param}), have a look at

http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=&func=view&catid=8&id=23204#23204

 

Yours,

Sebastian

Post edited by: csbac, at: 2007/04/12 07:12

[anandharaj]

Thank you very much, i'll try to do it, if still have problem, then i shall get more information on this from you.

 

meanwhile, can you post an example on jrxml file which have this kind of scenario?

[lucianc]

Also take a look at this.

 

Regards,

Lucian

[anandharaj]

Hi,

 

Thank you Sebastian, its works fine.

[Praveen7]

Hi,

I am new user here, working on multiple values to pass.

 

I declare a PARAM parameter of type java.util.Collection.

 

I put Default value expression as

 

Arrays.asList(new String[] {"AGN", "HKG","TAO" })

 

and update Sql queiry as

 

and AB.OFFICE IN ($P!{PARAM})

 

I use iReport 1.3.3 also use 2.0.0 recently

 

it prompt for argument while i put argument

 

it complies fine. but when i try to Execute it , it shows error such as

 

SQL Problems:ORA-00936: missing expression

 

help plz.

 

Thank you in advance

[lucianc]

Read this and this.

 

HTH,

Lucian

[jfabre]

Hi,

 

I'm newbie in JasperReports, but I followed your indications and.. it works!.

 

First I defined the input parameter (PARAM1) that comes from a JasperServer multi-selection check-box. I defined it as you mentioned (java.util.Collection datatype).

 

Then I defined a second parameter (PARAM2), and on its default value Expression I used:

 

$P{PARAM1}.toString().replace('[','(').replace(']',')')

 

Finally, I can use this second param in the SQL expression as you mentioned.

 

...AND FIELD_NAME IN ( $P!{PARAM2} )

 

I don't know if in an SQL expression you can use a variable instead of a parameter, but using a "dummy" parameter, it works.

 

Greetings

 

csbac wrote:

Hello, I understand your problem because I have just been trying to do the same ... There are two problems there. * JasperServer passes Multi-Selection values to the Jasper report in a class that implements Collection. Thus, if you define the report parameter as java.util.Collection, you can access it from the report and print it etc. As a default value in the parameter definition, you can use, e.g., Collections.singletonList("entry") or Arrays.asList(new String[] {"entry1", "entry2" }). This works for me w/o any problem. * If you want to use this parameter in an SQL query, like in SELECT * FROM TABLE WHERE idSth in ($P!{param}), you need to look at the post http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=&func=view&catid=8&id=19639 The only viable way is to convert the multi-selection list (Collection) in a string list, and set this as a parameter. Take care of the "!" in $P!{PARAM}, though. Otherwise, the string is passed as JDBC parameter, and that one only places the single string into the "in (?)", not as a list of items/strings. I will have to find out how to best do this conversion; toString() does not work, as it writes [1,2,3] in case of an Integer collection ... maybe a replaceAll call afterwards? Yours, Sebastian<br><br>Post edited by: csbac, at: 2007/03/16 12:44

[riskop]

Hello,

 

We created a QueryExecuter for executing multiple SQL queries. This executer can utilize for example the following query string (mysql):

 

"

create temporary table temp as

select age,count(*) as groupSize from user group by age;

select groupSize, max(age) from temp group by groupSize

"

 

For that we modified the createStatement() method of JRJdbcQueryExecuter. But we couldn't test the following portion of the code (lines 178-181, version 1719):

 

if (queryParameter.isMulti())

{

paramIdx += setStatementMultiParameters(paramIdx, queryParameter.getName());

}

 

Could you point us an example which utilizes this branch of code (multiParameters howto?) ?

 

Thanks, Peter

[lucianc]

riskop wrote:

For that we modified the createStatement() method of JRJdbcQueryExecuter. But we couldn't test the following portion of the code (lines 178-181, version 1719): if (queryParameter.isMulti()) { paramIdx += setStatementMultiParameters(paramIdx, queryParameter.getName()); } Could you point us an example which utilizes this branch of code (multiParameters howto?) ?

 

Check the "query" sample included (under demo/samples) in the JasperReports project distribution. Query clauses such as $X{NOTIN, City, ExcludedCities} generate such "multi" parameters.

 

HTH,

Lucian