Difference between revisions of "Accept Null Values as Parameters in a Jasper Report"

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
 
(3 intermediate revisions by one other user not shown)
Line 1: Line 1:
−
{{Note|The original HowTo was written by by Mario Calderon.}}
+
(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.
+
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 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 [[ManPageW ReportProcess|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.
+
When preparing reports in ADempiere, parameters that are passed to the reports are defined in the [[ManPageW ReportProcess|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. 
  
−
{{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 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.
−
 
+
−
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.
 
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.
+
* Select a parameter, say ''C_BPartner_ID'', in the Jasper Report where you want to select one or all possible values.
  
−
* Suppose the where clause in your query is ''c_bpartner_id = $P{C_BPartner_ID}''
+
* 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''.
 
* In your Jasper Report, create a new parameter: ''C_BPartner_ID_for_query'', with the class of the parameter set to ''String''.
Line 21: Line 19:
 
* The Default Value Expression for the new parameter will be:  
 
* 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())''
+
''$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}''
 
* In the Jasper Report query, substitute ''c_bpartner_id = $P{C_BPartner_ID}'' with ''$P!{C_BPartner_ID_for_query}''
Line 29: Line 27:
 
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.
 
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 ==
 +
*[[ADempiere/JasperReports Integration HowTo]]
 +
*[[HOWTO_Use_Jasper_On_Financial_Reports|HOWTO Use Jasper On Financial Reports]]
 +
*[http://sourceforge.net/forum/message.php?msg_id=5006768 How to use AmtInWords_XX in JasperReports]
 +
*[[HOWTO_Create_Jasper_C_Order_Register|HOWTO Create Jasper C_Order Register]]
 +
*Latest movie showing simple setup for [http://downloads.sourceforge.net/adempiere/D_JasperReports.7z?modtime=1217679230&big_mirror=0 JasperReports]
 +
*Latest [[Workshop:Integration von JasperReports in ADempiere]] in German
 +
*[http://jasperforge.org/projects/jasperreports JasperReports Project Home]
 +
*[http://jasperforge.org/projects/ireport iReport Project Home]
 
[[Category:HowTo]]
 
[[Category:HowTo]]
 
[[Category:Jasper Reports]]
 
[[Category:Jasper Reports]]

Latest revision as of 07:06, 28 June 2013

(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

Retrieved from "http://www.adempiere.com/index.php5?title=Accept_Null_Values_as_Parameters_in_a_Jasper_Report&oldid=444"