Revision as of 10:38, 5 March 2009

Overview

• DO NOTE that today's version of ADempiere is already integrated to JasperReports, as can be seen in this movie here.. Thus this tutorial is historical on how it evolved.

• Integration with ADempiere was started by Trifon helped by Alejandro, Johannes and Heng Sin. If you want to integrate to older ADempiere versions, take a look here.
• JasperReports is a powerful open source Java reporting tool that has the ability to deliver rich content onto the screen, to the printer or into PDF, HTML, XLS, CSV and XML files. JasperReports will allow you to generate wonderful reports, including reports with subreports into them.

Process Creation

Simple standalone process creation

In the sample we will create a process called Standalone accessible directly from the main menu. You are logged in as System Administrator role, then go to Report & Process:

Select New Record...

Fill the fields and Save.

Note: if you click the Report check box, and choose one Report View, it will popup a ADempiere report screen together with JasperReport Viewer Screen, so just click it to unchoosed.

Parameters

In the parameter tab of the Report & Process window select new.

Field "Name": select a parameter name as you please.

Field "DB Column Name": the name must be identical to the parameter name defined in the Jasper Report (file jrxml). It is not the name of the DB column or view, but the name of the parameter in the Jasper Report. Otherwise, you will get an empty report. If you want to avoid problems, define the column name as Parameter name and select it as "DB Column Name", too.

It is irrelevant wether the paramenter has been defined as promptable in iReports or not.

In the SQL statement of the .jrxml file you have to refer to the parameter in the following way: $P{MY_PARAMETER} or $P!{MY_PARAMETER}. This way, if you have defined a parameter as XXX, you refer to it in the Japer Report SQL statement as $P{XXX} or $P!{XXX}. The last type is for string substitution.

When you run an Application Server Deployment, and you have a Subreport, then the Parameter SUBREPORT_DIR for establishing the location of the subreport must have a value like the one described in the section "Deploy reports on the application server":http://adempiereservename.domain/webApp/standalone.jrxml, or http://adempiereservename.domain:adempierewebport/webApp/standalone.jrxml, if the web port is other than 80.

Menu Creation

Go to Menu Window, from System Admin > System Rules > Menu

Open the Menu Window and Create a new...

Fill and Save.

Ok, now Logout and Login again to see the new Process Standalone in the ADempiere main Menu...

Testing the New Process

The file go in $ADEMPIERE_HOME/reports be careful with some Java Web Start installations because if the ADEMPIERE_HOME is not set you might get something like c:\Document And Settings\user\Desktop\null as ADEMPIERE_HOME It is not a problem for testing this you just have to start the process without putting the report you hear a sound marking an error.

Just close the window and go to the Tools/preference Menu Select the Errors Tab and you can see exactly where ADempiere is looking for your Report.

Ok, it’s your Report. Congratulations!

Security Access to new menus

In order to get access to your new Standalone report from your your target Client Role, you need to allow access to process. Then, Login as your client Admin Role:

Go to Role window:

and open the window...

Add a role accessing your process in the Process Access Tab and Save.

Exit from ADempiere and Login again.

Ok, it’s done!.

Report deployment Strategy

It is now easy to deploy reports in $ADEMPIERE_HOME/reports or directly on a web server. Just specify the full URL in the Report & Process/JasperReport field and the report wil be downloaded from the web server. Note that the source jrxml is automatically stored in the local temp directory and replaced if a new version is present on the server. Note also that the compiled version is also kept in the temp directory so the compilation is only done one time.

Deploy reports on the application server

It is probably the best method for deploying reports. You need to create a package containing your report. The best way is to create an EAR deployement package and put it in the jboss/server/adempiere/deploy directory of the application server. Jboss will detect that you add a package and deploy it automatically, if you remove it removes.

You can find on svn sourceforge a webApp.ear package wich include standalone.jrxml report deployed as

http://adempiereservename.domain/webApp/standalone.jrxml, or as

http://adempiereservename.domain:adempierewebport/webApp/standalone.jrxml, if web port is other than 80

If you do not know how to put your reports in WebApp or you do not want to put them there then you may simply zip them in a file lets say CustomReports.war (it has to have the .war extension .zip won't work) and copy CustomReports.war to ADEMPIERE_HOME/jboss/server/adempiere/deploy. The full path to the report that you have to specify in the application dictionary will then be http://yourserverip:8080/CustomReports/yourreportname.

Deploy reports as attachment

This feature is added after release 3.2 (and patched in 3.2.1). You can now deploy the jasper report source (.jrxml) or compile file (.jasper) as attachment to the jasper report process. Use the special syntax attachment:reportFileName ( for e.g, attachment:standalone.jrxml ) to indicate the jasper report file that should be loaded from the process's attachment. If subreport and properties resource file is use, you need to upload those as attachment to the same process as well.

Deploy reports to a shared folder

You can now deploy the jasper report to a directory using file:/ convention in the file name.

Deploy reports as resource

You can now deploy the jasper report as a resource in a jar file included in the classpath - i.e. customization.jar - using resource: convention in the file name. (New on 3.4)

Tips

Stored Procedures

Maybe you need use a Jasper Report with Stored Procedures, but it is unable to call Oracle stored procedures directly......, here is a HowTo.

Parameters passed from Adempiere available to your report

Adempiere passes a number of parameters that become available to your reports. As a matter of fact is passes the current context which contains a bunch of parameters along with the parameters you specified that your report will accept and the following:

• RECORD_ID - this is the primary key of the current record. This is useful when you want to hook a JasperReport to the print button on a record (Invoice for example) then this field indicates the id of the selected invoice. This is passed as an Integer.
• AD_PINSTANCE_ID - the id of the current process. This parameter is passed as an Integer.
• CURRENT_LANG - The current language. Useful when you want to create multilingual documents.

The parameters you specified when you created your report process in Adempiere's Application Dictionary you have to follow these rules:

Naming: Adempiere assigns names to your parameters based on what you specified as DB Column name in the parameter tab of the Report and Process window. Also if the parameter that you specify there is bound to a System Element say M_Warehouse_ID then DB Column Name and thus your parameter name has to also be named M_Warehouse_ID.

Datatype: Adempiere passes parameters as either BigDecimal, String or Timestamp with the exceptions mentioned above (RECORD_ID etc.)

Example: We have a JasperReport which we have hooked up for a standalone process. The reports needs to accept two parameters: Warehouse id and quantity. We will specify the parameters in the following way:

1. Quantity

       Name: p_qty (this does not really matter), 
       DB Column Name: p_qty (this is the name of the parameter we should reference in our JasperReport)
       System Element: None
       Reference: Integer (Even though we specify Integer the parameter is passed as BigDecimal do not be misled here)

2. Warehouse id

       Name: M_Warehouse_ID (this does not really matter), 
       DB Column Name: M_Warehouse_ID (this is the name of the parameter we should reference in our JasperReport and also it must be exactly the same
       as the system element name)
       System Element: M_Warehouse_ID
       Reference: Search (the parameter is passed as BigDecimal do not be mislead here)
       Dynamic Validation: M_warehouse Org (apply a restriction for the parameter)

For more information about what parameters are passed and how take a look at ReportStarter.startProcess.

iReport version

If you use iReport to edit and compile the report, you must have care what version of JasperReports does your iReport use. If you use iReport to generate the .jasper file (the report compiled file) sometimes it cannot be loaded by ReportServer if the versions are different. You need to use the same JasperReport version when you compile the report and on ADempiere when you start the report; otherwise you can see a error message in the Jboss console like as:.

2006-08-25 08:39:05,406 INFO [STDOUT] java.io.InvalidClassException: net.sf.jasperreports.engine.base.JRBaseReport; 
local class incompatible: stream classdesc serialVersionUID = 10200, local class serialVersionUID = 608

Please take a look in the next Tip, because the error could be different if you're using subreports.

Problems Compiling the report

The reports you deploy are usually downloaded and compiled on the client machine. If you see an error message like this:

RException; e.getMessage()= Error compiling report java source files

Check that you have jdk(jre is not enough) installed and that you have javac in your path. Another solution is to compile the jasper report package the .jasper file and give as the name of your report when you are defining it in the application dictionary the .jasper file instead of the .jrxml file.

If you get and error message complaining that the class net.sf.jasperreports.engine.JasperCompileManager cannot be found then check whether you have CompiereJasperReqs.jar in your classpath. If you have deployed your jasper report on the Adempiere server then the CompiereJasperReqs.jar is downloaded from the server and is usually placed in your temp folder. If you are still getting the error then try to copy CompiereJasperReqs.jar in a directory on your client machine and hardcode the full path to it in the place where the java classpath is set in the ReportStarter.JWScorrectClassPath.

Problems exporting to PDF

If your report seems ok when opened with the swing viwer but it looks like gibberish when you export it to pdf check the following:

1. fontName is the same as pdfFontName in your report.

2. PDF Encoding - on every text field in your report there is a property PDF Encoding make sure that it is correct. For example if the text on your report is in Cyrillic most likely the correct value would be CP1251 (Cyrillic).

3. The font specified in pdfFontName is somewhere where jasperreports can find it or you specify the full name to the font. For example if I want to use Arial as the pdfFontName in my jasperreport then I will have as pdfFontName C:\Windows\Fonts\Arial.ttf If you are looking for an OS neutral solution you may add the font to your WebApp.war alongwith the .jrxml file and specify as pdfFontName the fullpath to it. For example if I have placed arial.ttf in WebApp.war then I will specify as pdfFontName something like: http://192.168.2.109:8080/WebApp/arial.ttf. If you are going to deploy your fonts on the webserver a good tip for managing the path to the reports is to create a style (look in the bottom right corner in iReport and click on Styles Library and click the right mouse button and then create style). In the created style specify your font and pdf font name (Font property) and then assign the style to all fields on your report. If your later need to change the font or the path to it you simply do it in the style.

Problems exporting to XLS

If you cannot export to xls then make sure that you have the apache poi(poi-2.0-final-20040126)library somewhere where java can find it.

Problem with subreports

When you have a report which contain a subreport into, you need to use the last with the compiled files (it’s .jasper file). Sometimes, if you use iReport to compile/edit your reports, maybe you compile the subreport with this tool. When you test the report from iReport, it work fine; but when you run the report from ADempiere, you can’t see nothing.

...and the report is not displayed.

Please take a look into the Preference > Errors window, and you can see something like:

And from the console as:

14:06:24.109 ReportStarter.addProcessParameters:  [13]
14:06:24.109 ReportStarter.httpDownloadedReport:  report deployed to http://work/webApp/Socios.jrxml  [13]
14:06:24.140 ReportStarter.isRequestedonAS: Got work/192.168.0.55 for http://work/webApp/Socios.jrxml as address 
             #0 [13]
14:06:24.140 ReportStarter.isRequestedonAS: Requested report is on application server host [13]
14:06:24.140 ReportStarter.isMD5HomeInterfaceAvailable: EJB client for MD5 remote hashing is present [13]
14:06:24.343 ReportStarter.ejbGetRemoteMD5: MD5 for http://work/webApp/Socios.jrxml is lumYIJ3VWbVpCtoOaF7Ftg== 
             [13]
14:06:24.343 ReportStarter.httpDownloadedReport: MD5 for local file is lumYIJ3VWbVpCtoOaF7Ftg== [13]
14:06:24.343 ReportStarter.httpDownloadedReport:  no need to download: local report is up-to-date [13]
14:06:24.343 ReportStarter.processReport: reportFile.getAbsolutePath() = C:\DOCUME~1\ALEJAN~1\CONFIG~1\Temp\Socios.
             jrxml [13]
14:06:24.343 ReportStarter.processReport:  no need to compile use C:\DOCUME~1\ALEJAN~1\CONFIG~1\Temp\Socios.jasper 
             [13]
===========> ReportStarter.startProcess: ReportStarter.startProcess: Can not run report -
             Error loading object from URL : http://work/webApp/Socios1.jasper [13]

But the compiled subreport file is there!

• Note: the source files for JasperReports have .jrxml extension, and the compiled files have .jasper extension.

Ok, we will see some steps you can do to resolve the problem.

In the example we have a report, called Socios.jrxml and a subreport called Socios1.jasper (as compiled file; the source file is Socios1.jrxml). When we run the report from iReport, we can see the report is working and displayed ok:

Note: You can see the report in blue colour, and the subreport in red colour.

The sources files (from iReport) are:

In summary, what we'll do is compile the subreport into ADempiere.

Then, we go by steps:

• Step 1: Create a new process for the subreport

Login into ADempiere with System Administrator role, then go to Report & Process and add a new Record:

fill the fields, but in the JasperReport field you will fill with the subreport source file name (in our example: Socios1.jrxml).'Save and close the window.

• Step 2: Add to Menu the new process

Go to the Menu window and add a new record:

fill as a normal report and Save.

• Step 3: Run the new report

Logout and login again, then you will see the new process recently created:

Ok, now run the Only for compile subreport process...

press Ok to start...

...then you can see the subreport output. Here the data is not important, the issue is when you ran the process, the subreport was compiled!

• Step 4: Copy the new subreport compiled file

Now you must copy the new compiled file to the location as you need. In the example we will copy

From: C:\Documents and Settings\Alejandro\Configuración local\Temp\

It's my Temp User folder.

To  : D:\Adempiere\jboss\server\adempiere\deploy\WebApp.ear\webApp.war\

Note: the last is our http://adempiereservename.domain/webApp/ location; you must change for your.

• Step 5: Test your Report

Ok, now we'll test the report...

then we'll run it..

Ok, work fine now. It's all!

Amount In Words

• Your JasperReports can easily show the AmtInWords string as taught by CarlosRuiz here:

Well, I did some jasper testings, these are the instructions for the tests:

In iReport:
Options -> Classpath
Add Jar -> C:\Adempiere\lib\Adempiere.jar
Add Jar -> C:\Adempiere\lib\oracle.jar
Add Jar -> C:\Adempiere\lib\postgresql.jar

Define and test your connection from Data -> Connections / DataSources
Set your active connection with Data -> Set Active Connection

Start a new report

In Report Query I put this:
SELECT grandtotal FROM C_INVOICE WHERE c_invoice_id = 109

Then View -> Variables -> New
Name -> AmtInWords
Type -> java.lang.String
Variable Expression ->
org.compiere.util.Msg.getAmtInWords(org.compiere.util.Language.getLoginLanguage(), java.lang.String.format("%-32.2f", new Object[] {$F{GRANDTOTAL}}))

Then added a Text Field on the detail band pointing to the variable AmtInWords

Saved and run the project and voila!
It showed me the amount of the invoice in words - english in my tests - I suppose running from within Adempiere will get the correct login language.

Regards,

Carlos Ruiz - globalqss http://globalqss.com

Links

• JasperReports Project Home
• iReport Project Home
• How to use AmtInWords_XX in JasperReports
• Latest Workshop:Integration von JasperReports in ADempiere in German
• Latest movie showing simple setup for JasperReports