how to use multiple value parameter & WHERE ... IN

[shymaza]

 Hi! 

I have an multi-value query input control in jasperserver, pass values to Collection type parameter. I need to pass this values to WHERE ... IN (value1,value2,.. valueN) in report SQL query.

If use 

WHERE XXX IN $P{COLLECTION_TYPE_PARAMETER} 

it shows error: 

Parameter type not supported in query : COLLECTION_TYPE_PARAMETER class java.util.Collection

How can I do IT? 

[mdahlman]

shymaza,

In general the easiest way to use multiple values with an IN clause is to use the $X{} syntax.

The basic idea is this:
SELECT ... WHERE $X{IN, MyColumn, MyCollectionParameter}

There's a sample supplied with JasperReports and iReport: /demo/samples/query. You'll find good examples searching in this forum and the JasperServer forum too.

Regards,
Matt

[k_tripura]

$X{IN, MyColumn, MyCollectionParameter} --- this in not working for me in 5.0.4 version of iReport, I have been struggling to fix it

 

[k_tripura]

$X{IN, MyColumn, MyCollectionParameter}  --- this in not working for me in 5.0.4 version of iReport, I have been struggling to fix it
I am trying with a very basic select query.

Still no luck so far, Any help mdahlman???
 

[k_tripura]

I resolved this issue finally ... The reason why it was throwing error is

My iReport Langauge property is set to "Groovy" instead of "Java"

NOT in tools-> Options-> Language- Java

BUT for Language in Activity Report Properties ->Language ->Java (THIS IS THE CHANGE that resolved my issue)