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.
Thanks for resolution.