JasperReportsFreiBier

This page is about my (User:TBayen) new Plugin to integrate JasperReports in iDempiere. You can decide between the legacy plugin "org.adempiere.report.jasper" (JasperReportsClassic) and this one. My goal was to have the code cleaner organized, modularize it and make the different file access methods more consistent.

Activate the plugin

After including the plugin as a bundle into your iDempiere OSGi container you start the program in the System Client and go into the window for "System Configuration" (table AD_SysConfig). There you create a new key "JASPER_STARTER_CLASS" and set the value "de.bayen.freibier.report.ReportStarter". Now you use the new JasperReports integration.

Overview: How is a Report created?

A report is constructed from the following parts:

• A rule how to access Jasper files and other resource files. In the easiest case this is just a filename. (The rule is the text you enter in the "jasperreport" field of the "Process & Report" window.)
• One or more Jasper file(s) (and resources)
• Context Parameters (given from iDempiere and defined in the Report File)
• an optional Process to prepare your data
• data definition, defined by a SQL Query in every single of your report file(s)
• a Database Connection given by the Plugin to the JasperReports engine
• As a last item you can create different Export Formats (like a Text or Excel file) if you want but the normal case is to show or print the report as a PDF.

All these items work together and create a report.

File Access Rules

The string you give in the field "jasperreport" in the window "Process & Report" is the rule how to access your files and resources. The easiest rule ist to just give a filename. This filename is searched for in your ADEMPIERE_HOME/reports/ directory and in the classpath.

TODO - explain the rules system when the implementation is stable

Parameters

How can I access parameters and other iDempiere context values in my report?

The Plugin gives the following parameters to the JasperReports engine. You can access all these values in your report by creating a "Parameter" entry in your report with the same name as given here. You might be interested in the fact that not all parameters are strings. You can use them as Java Objects if you set the right type in the parameter definition. For example you can access the whole iDempiere Context by using the CONTEXT parameter as a Java Map:

 $P{CONTEXT}.get("#Date")  /* use the date the user set at login */

accessing non-standard classes

In theory you can access Java objects by using them as the class they are. But if you want to use Object of classes that are not part of the Java core classes you will get problems. These classes are defined inside iDempiere but not in iReport. That means you can not compile and test your report in iReport. In such cases you should be able to use report only in iDempiere (I have to admit I did not test this yet). You can try to add the iDempiere base plugin as a jar file to the iReport classpath to test your reports. Another (more portable) possibility is to avoid these class definitions somehow. Perhaps you can use an standard interface defined by the class (like java.util.Map) or you can use Reflection. (One idea I didn't take the time to follow is if there is a standard bean helper class in the iReport classpath like apache BeanUtils. It may be easier to insert such a class in the classpath (that the Plugin uses when calling the JasperReports engine) than doing this with all iReport installations out there.)

defined parameters

process parameters

At first the most important: All given Parameters are copied directly to the parameters list.

If you use range parameters the range end becomes a Parameter with the Suffix "To". So if your iDempiere Process Parameter is "Date" you get two JasperReports Parameters "Date" and "DateTo".

Most people will not need it, but there is a more fine grained access method: There are so-called process parameters and process info parameters. They can be accessed separately in two different hashmaps. That means you can access a parameter directly by using its parameter name and indirect through this hash if you need (there are only rare cases where you need that).

• IDEMPIERE_PARAMETER (HashMap)
• IDEMPIERE_PARAMETER_PI (HashMap)

context parameters

The Plugin creates the following parameters out of the iDempiere context. You can use them directly in your report:

• AD_PInstance_ID - The PInstance Object allows to access information about my process instance by querying the database.
• AD_Client_ID
• AD_Org_ID
• AD_Role_ID
• AD_User_ID
• PRINTER_NAME
• PRINT_FORMAT (org.compiere.print.MPrintFormat object)
• PRINT_INFO (org.compiere.model.PrintInfo object)
• AD_Language the language string like used in iDempiere, for example "en_US" or "de_DE". This reflects the configured language for the client and/or the document.

When doing path substitution you can use some additional terms. These are not accessible from inside the report as parameters.

• AD_Client.Value
• AD_Org.Value
• AD_Role.Value
• AD_User.Value

If you need such values you can access them from the database by inserting them in your query like this

 SELECT ..., AD_Client.Value AS AD_Client_Value FROM ... JOIN AD_Client WHERE (AD_Client.AD_Client_ID=$P{AD_Client_ID})".

optional parameters

These parameters are not set always. It depends on the environment from which the report is started.

• RECORD_ID - If the report is called with the Print Button from the Toolbar then it is meant to print one single record of the shown table. In this case the RECORD_ID variable is set to the
• These parameters are set if you call the report from inside a normal data edit window with a tab. They are not set for example when the report is started from the main menu.
  • TAB_WHERE - The SQL "WHERE" clause (without the word "WHERE" so it can be combined) that was used to show the records in the actual tab
  • TAB_ORDER - The SQL "ORDER BY" clause (without the words "ORDER BY" so it can be combined) that was used to sort the records in the actual tab (or "1" if no order was given)
  • GUI_SORT - If the actual Tab is sorted by the user by clicking on a columns header this parameter contains a order text to include in an "ORDER BY" clause. (this parameter is "1" when the user did not sort)
  • GUI_JOIN - If the user did sort the tab by a column that uses a Lookup this parameter is a JOIN clause to get the value that is used for sorting. The joined table has the name "SORT_*" where "*" is the original name of the table. You can use this joined table if you want to use the name or the value of this table in your report. The parameter starts with "JOIN". If there is no user sort it is "".
  • RECORD_IDs - If you choosed some records from the gui (up to now only available in zk) this is a comma-separated list of the ids. If there is nothing choosed it is "NULL" (the string "NULL", not the value - check the example why this is useful.)

At the moment the GUI can only sort by one single column. So GUI_SORT and GUI_JOIN will have only one single entry. It maybe a good idea to use them so that you are prepared if that changes later. ;-) The GUI_JOIN and GUI_SORT facility does not work in every case but mostly only for table direct lookups. You are free to contribute help if you want to improve that. If you want to be sure about that you do not have to use the GUI_* parameters but sort only by TAB_ORDER which is always save.

You can use these parameters in a way like this in your report's query:

 SELECT ... FROM mytable $P!{GUI_JOIN} WHERE ((mytable_ID IN ($P!{RECORD_IDs})) IS NOT NULL) AND ($P!{TAB_WHERE}) ORDER BY $P!{GUI_SORT}, $P!{TAB_ORDER}, mytable_ID

JasperReports parameters

There are some JasperReports standard parameters that the Plugin sets. You can use them as expected by JasperReports conventions:

• SUBREPORT_DIR - The plugin uses a virtual file system to abstract the access to jasper resources. So there is no file path that the report has to know about. This value is used by iReport generated reports to access subreports and other external resources. To make standard created jasper reports working this value is always set to "".
• REPORT_CONNECTION - This is a read-only connection to the given iDempiere database. (It may be interesting for you that you do not have to (and even can not) set your database credentials in the report.)
• REPORT_LOCALE (java.util.Locale object) - This reflects the configured language for the client and/or the document. JasperReport can be configured to use that for Property Bundles.

Preparation of Data through your own process

You can write a normal iDempiere Process and enter it's classname into the "Report & Process" window as the "process". This process will be called before the report is started. In this Java Process you can prepare data and write it to a kind of temporary table. In iDempiere such tables begin by convention with the "T_" prefix. You can use the AD_PInstance_ID value to mark entries in this temporary table. The JasperReport gets the same AD_PInstance_ID as a parameter and can see which records belong to this process run by accessing the table in it's SQL Query with a appropriate WHERE clause. You can clean this temporary table from time to time (e.g. daily) with the Housekeeping facility.

Export to different formats

The Plugin is able to export to the following formats:

• pdf
• ps (Postscript)
• xml
• csv (comma separated value text file)
• txt (plain text)
• html
• xls (Microsoft Excel)

More filetypes are supported by the jasper library but not yet in the Plugin. This can be extended easily.

Up to now there is an exporting gui only in zk.

direct print, automatic archiving, etc.

In principle all settings that are used for internal reports should work with this plugin too. This means you can configure a special printer name in the pront format, set "direct print" in the process window to not open a print dialog etc. Documents will also be archived automatically if configured so. If some of the normal report's features does not work with this plugin please report this as a bug to me (User:TBayen). :-)

Fonts

There is a standard way to create font extensions for JasperReports. I explain how to create them and how to add them to the iDempiere plugin on the page JasperReport Font Extensions.