I found a really useful function today, allowing you to supply a List as a parameter, and use in your SQL Query  with IN or NOT IN.

Supply the parameter as a List:

<parameter name="myList" isForPrompting="true" class="java.util.List"/>

Then use the the function

$X{IN, <column>, <param>}

For example

<queryString><![CDATA[SELECT *
FROM mytable WHERE $X{IN, mycolumn,myList}]]>
</queryString>

Very useful!

I have been mainly writing Jasper reports this week. There seems to be a dearth of documentation (unless you pay for ‘The Definitive Guide’), but a combination of iReport and trial and error seems to be working.

I wanted to reuse the same report with some optional parameters – add an additional condition to the where clause if a certain paramteter was supplied. I realise that I can pass an entire SQL statement into the report, or even some objects containing the data, but I like the consistency of having valid SQL in the report and letting Jasper substitute the parameters appropriately.

My solution was to use the ‘case’ statement (I’m using PostgreSQL):

WHERE manager = (case when $P{manager} != '' then $P{manager} else manager end)

I guess there would be performance implications on a big dataset, but it seems to work.