Thursday, July 23, 2009

Dynamic Query Parameters for Jasper Reports

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.

15 comments:

Igor said...

This is not something I'd use for any serious application as it is vulnerable to SQL injection.

Rob said...

It's only vulnerable to SQL injection if you don't properly validate the input upfront. Security minded developers should always do this anyhow. So while your point is technicality valid your presumption for not using it is based on poor programming practices.

Anonymous said...

Thank You
l am very happy with what l read here. Now l am able to do som query
where l was blocked since 2 days!!!!!!!!!!
Thank youuuuuuuuuuuuuuuu!!!!!!!!
Merci bcp!!!!!!!!!!!

John said...

This is helpful, however, has anyone tried to use parameters for the "Order By" Clause? I tried to put a parameter in the Order By

ORDER BY
$P(order_by}

This is passed in via a selction screen. It did not sort. Any thoughts?

Rob said...

try changing it to $P!(order_by}

Remember, you need the ! in order for it to be evaluated when it's part of the query and not part of the selection. This subtle difference should make it sort.

harsh said...

This is really helpful. Thanks

qrueger said...

wow awesome man.. thx...

Anonymous said...

Thank you, I am new to this and I couldn't figure out how to get the parameters to expand in the query. Now I know, use an exclamation mark! I think I'll bite the bullet and pay for the JasperReport manuals as the free documentation is skimpy to say the least!
Lydia

AsadULLAH said...

Well the same can be done using sql DECODE.
Lets say the field name FIRST_NAME and the parameter $P{F_NAME}
then
FIRST_NAME=DECODE($P{F_NAME}, null, FIRST_NAME, $P{F_NAME})
Don't forget the set the default value of $P{F_NAME} as null.
Now whenever you want to search by last name only, simply don't send the value for $P{F_NAME} and let it pick its default value and you are done with.

ReadCabin said...

Jasper Report Cookbook
http://www.readcabin.com/j/a797f6e8

Tharange said...

I found AsadULLAH 's comment useful.
Ireport 4.0.2

Thanks.

Marco said...

That's a very good thing I an user $P!{} syntax, but, for your use you can simply use something like

SELECT FROM
WHERE ( $P{FIRST_NAME} is null or first_name = $P{FIST_NAME} )
AND last_name = $P{LAST_NAME}.


Thank you very much.

rpbarbati said...

Ok, one more fine tuning for parameters...

I typically use the following syntax for my parameters that are optional (meaning they may be NULL).

In the WHERE clause use the following...

DECODE(${parameter_name}, NULL, 1, field_to_compare_against, 1, 0) = 1

This behaves as follows... If the parameter is NULL, then DECODE() returns 1, otherwise if the parameter matches the column you are comparing it against, return 1 otherwise return 0. And now compare the value you returned from the DECODE against 1. This causes a NULL parameter to match every value (1) and a non-null parameter to match the column (1) otherwise it matches nothing (0).

Anonymous said...

Thanks, this is just what I needed.

Kevin Potato said...

In iReport, i want to put the sql query in my parameter expression editor as following.

(!$P{securityGrading}.equals("0")) ? "SecurityGrading- " + "select description from [KRISADMIN].SecurityLevel where Level = $P{securityGrading}" : "SecurityGrading- Default to all"

But the iReport can't execute my sql query. I know it doesn't recognize my sql query since i'm using double quote but how do I get my query works in it?