Accept Null Values as Parameters in a Jasper Report

From ADempiere
Revision as of 07:06, 28 June 2013 by Mar cal westf (Talk)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.

(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.

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. ADempiere interprets such empty fields as "don't care" and ignores them by setting the particular SQL where condition to true. Rather than selecting records with null values in that field, all records are returned.

Jasper Reports are different. The query is performed by the Jasper Report itself, not the ADempiere report engine. If you pass a null value as a parameter to a Jasper Report, 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.

  • 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.gif 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.

Links about Jasper Reports