Accept Null Values as Parameters in a Jasper Report
Note: The original HowTo was written by by Mario Calderon.
This article explains how to create a parameter in a Jasper Reports which will allow you to select specific records or all records in the underlying data. For example, you may have a report with a date field and you would like the option of selecting a specific date or all dates.
You will need to have a report in your database that uses Jasper Reports and that accepts parameters from ADempiere.
When preparing reports in ADempiere, parameters that are passed to the reports are defined in the Report & Process window. When the report is selected in the ADempiere menu, the parameter window opens and allows you to select the parameters. If you leave a parameter field empty, ADempiere will pass a null value to the report. In ADempiere interprets such empty fields as "don't care" and ignores them.
Note: The built-in Report Engine in ADempiere treats parameters without values as if no filter was selected - essentially, null parameter values passed to queries are ignored. Rather than selecting records with null values in that field, all records are returned.
Jasper Reports work differently. If you pass a Jasper Report a parameter without value, it will be interpreted in the query as column=null, which is undesirable.
Here are the steps to follow so that a null value parameter behaves the same way in a Jasper Report as in ADempiere's report engine.
- Suppose you have the parameter C_BPartner_ID in the Jasper Report and you want to select one or all possible values.
- Suppose the where clause in your query is c_bpartner_id = $P{C_BPartner_ID}
- In your Jasper Report, create a new parameter: C_BPartner_ID_for_query, with the class of the parameter set to String.
- The Default Value Expression for the new parameter will be:
$P{C_BPartner_ID}==null? " true": "c_bpartner_id=".concat($P{C_BPartner_ID}.toString())
- In the Jasper Report query, substitute c_bpartner_id = $P{C_BPartner_ID} with $P!{C_BPartner_ID_for_query}
Note: The $P!{...} is used for proper string subtitution.
When you pass the report a value, the value will be used, becoming the where condition for example c_bpartner_id = 1234567. Otherwise the condition will be true, which will result in no filtering at all, just like in ADempiere's report engine.
