Accept Null Values as Parameters in a Jasper Report
(The original HowTo article 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. If you create a parameter in ADempiere to select a date and a matching parameter in your Jasper Report and leave the date blank, the Jasper Report query will look for matches where the date column is null - probably not what you intended. To fix this, we need a way to communicate the intention that a null value means "I don't care".
You will need to have a report in your database that uses Jasper Reports and that accepts parameters from ADempiere. You should already understand how to create a Report and Process with parameters in ADempiere and link it to a Jasper Report which uses parameters.
Note: <Enter the note text here>
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.
- Select a parameter, say C_BPartner_ID, in the Jasper Report where you want to select one or all possible values.
- Ensure the where clause in your query is c_bpartner_id = $P{C_BPartner_ID} and that you can select c_bpartner_id values from ADempiere.
- 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.
In summary, by adding a second parameter to the Jasper Report that performs a conditional string replacement, we can mimick the behaviour of the ADempiere Report Engine and ignore null values as don't care conditions.
