Dynamic Query Parameters for Jasper Reports

[henryedsl]

Hello.

I am working with iReport 3.7.0

I have a query that looks like this:

select * from employees where first_name='$P{first_name}' and last_name='$P{last_name)'

if i look for a first name and a last name, it works fine, but i'm looking for a last name
so i put in just the last name and leave the first name blank. Well, when i do that, the whole
thing blew up because i have a query that looks like this at runtime:

select * from employees where first_name='' and last_name='de Sousa'

No query returned, and i need all the employees with last_name = 'de Sousa' to be returned.

How can i solve this problem?

Thanks in advance.

Regards.

[typhoon]

Option#1

If you are using Oracle, then you can use LIKE operator, for example:

select * from employees where first_name LIKE '$P{first_name}' and last_name LIKE '$P{last_name)'

In this case if you don't need to filter by first name, then set '%' as default value for $P{first_name} and use this default value.

 

Option#2

If you are using your own UI and passing values to jasper yourself, then you can pass part of SQL query, for example:

select * from employees where $P!{your_where_clause}

where $P!{your_where_clause} will be replaced with dynamical restriction created by your own application/UI (for example, your application will generate string - last_name LIKE '$P{last_name)' - and pass it to jasper).

 

Please also read tutorial about parameters:

http://jasperforge.org/website/ireportwebsite/IR%20Website/ir_using_parameters.html?header=project&target=ireport

Best regards,

ty

[henryedsl]

Hello!

 

Thanks a lot for your answer, it was really helpful for me.

 

Best regards,

 

Henry