multiple value parameterised

0
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.
seesuresh's picture
Joined: Feb 22 2007 - 10:10pm
Last seen: 12 years 9 months ago

12 Answers:

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

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
csbac's picture
130
Joined: Mar 12 2007 - 7:42pm
Last seen: 12 years 9 months ago
0
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
teodord's picture
4499
Joined: Jun 30 2006 - 9:00am
Last seen: 17 hours 9 min ago
0
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.
anandharaj's picture
Joined: Oct 18 2006 - 5:02pm
Last seen: 6 years 2 months ago
0
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...

Yours,
Sebastian
Post edited by: csbac, at: 2007/04/12 07:12
csbac's picture
130
Joined: Mar 12 2007 - 7:42pm
Last seen: 12 years 9 months ago
0
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?
anandharaj's picture
Joined: Oct 18 2006 - 5:02pm
Last seen: 6 years 2 months ago
0
lucianc's picture
7154
Joined: Jul 17 2006 - 1:10am
Last seen: 3 days 16 hours ago
0
Hi,

Thank you Sebastian, its works fine.
anandharaj's picture
Joined: Oct 18 2006 - 5:02pm
Last seen: 6 years 2 months ago
0
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
Praveen7's picture
Joined: Jul 1 2007 - 10:21pm
Last seen: 12 years 5 months ago
0
lucianc's picture
7154
Joined: Jul 17 2006 - 1:10am
Last seen: 3 days 16 hours ago
0
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...

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
jfabre's picture
2
Joined: Aug 16 2007 - 10:32pm
Last seen: 12 years 4 months ago
0
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
riskop's picture
3
Joined: Feb 4 2007 - 7:15pm
Last seen: 12 years 10 months ago
0
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
lucianc's picture
7154
Joined: Jul 17 2006 - 1:10am
Last seen: 3 days 16 hours ago
Feedback
randomness