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