Passing Optional Parameters in jasper reports

13 / Sep / 2010 by Sachin 5 comments

Hi Guys,

In one of my projects, While working with jasper reports, one of the requirement I faced was of passing optional parameter to the sql query in jasper  report. For eg. I had to write a query to find all new customers which were added between two given dates and if an optional status value of the customer is passed the report should should be further refined to include only those customer which were added and had the same status value as passed, If the status is not provided it should be ignored in SQL query in jasper report. So, in this case the status was an optional parameter.

The solution I found was pretty elegant. On searching over the net, I found that parameters with an ! (exclamation) mark are compiled before the query is executed. That is,

$P!{status}

will be compiled before the query is executed. So, I passed the status from my code in to the jasper report. Now, to take care of the condition that it could be optional, I added a new parameter inside the jasper report, say “status_value”. such that, status_value had a default value expression (DFE) :


($P{status}=="" ? " and customer.status=' "+ $P{status} + " ' " )

Now in the SQL query in jasper I wrote

select * from customer where customer.start_date=$P{startDate} and customer.end_date=$P{endDate} 
$P!{status_value}

what it does is that status_value is compiled before the execution of query. So the condition in the DFE of status_value checks whether we a receive a status from outside or not, If we do the condition will be appended in the query. Other wise it won’t be appended and we are done. :).

With Regards

Sachin Anand
sachin@intelligrape.com

FOUND THIS USEFUL? SHARE IT

comments (5)

  1. Justus

    This is one of the best solutions I figured out. Just create a new parameter and in the sql query and something like
    ‘WHERE customer_id=Coalesce($P!{Account},customer_id)’ in your where clause. That works greatly. No stress. Only one query.

    Reply
  2. Hugo

    Congratulation! This is a realy nice and elegant solution that helped me a lot. But may be it has a mistake.
    $P{status}==””
    This piece of code shouldn’t be like this?
    $P{status}!=””

    Bye

    Reply

Leave a Reply to Justus Cancel reply

Your email address will not be published. Required fields are marked *