Recently I've been working with Jasper Reports. I don't often do a lot of report writing so I enjoyed the opportunity to work on something different.
Once I had a query full of data I began to use the Jasper Parameters to filter out items in my query. For example I might have a query that looks like this.
select * from employees where first='$P{first_name}' and last='$P{last_name)'
Really simple stuff here nothing that Jasper didn't do or support. So I of course took this to the next level and said ok, I'm just looking for a last name so lets put in just the last name, leave the first name blank. Well when I did that the whole thing blew up because I had a query that looked like this at runtime
select * from employees where first='' and last='Smith'
So I have to put a parameter in? That's ridiculous! Searching around the meager JasperReports documentation I couldn't find anything helpful either. Checking with co-workers who use this more than I do I found that most of the time they build SQL in a stored procedure for this. I'm not opposed to a stored procedure but when you just want to filter a few things it's really stupid to have to jump through those kind of hoops.
I came up with a elegant solution to this that I could do within iReport and with no extra classes (as jasper suggests using a scriptlet - which isnt really a script its a java class).
The solution was quite simple once I figured it out. I created the regular parameter for input. This would be first_name, I then created a second parameter (not for input) called first_name_value. Inside the first_name_value "Default Value Expression" I entered this java code:
($P{first_name}.isEmpty()==true ? "" : " and first='" + $P{first_name} +"'" )
Now, once I had that inside my SQL code I used the following
select * from employees where last='$P{last_name)' $P!{first_name_value}
What this does is output NOTHING if first_name is blank. If it is filled with a value we add the entire "and first='John'" at runtime into the SQL statement. This of course has the drawback that you have to have at least 1 qualifier in your sql statement. Try something like where last!='' as a default. This then gives you every row and any "and" clauses are appended to the end. Also notice the $P! with the ! in there. That means it will evaluate before the query.
Why not just use a stored procedure? Well a couple of reasons. First, SP's take time. Sure, you might be good at them and can get your SP written in a short amount of time. Go for it. This solution works well because you don't have to bother with an SP at all. A simple query can be done within iReport and these filters applied.
Get fancy. You could get fancy with this and use your expression code to handle replacements as well. For example you could allow a > 95 and change the code to remove the = sign if something like that is input.
Hope this helps in your report development.
