Revision as of 08:58, 5 December 2011

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.

• Step 1. Log in as System Administrator role.
• Step 2. Open window Report & Process and Select New Record...

• Step 3. Fill the fields and Save.

• Notes:

1. 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.
2. In ADempiere 3.6.0 LTS, you can leave Classname field and Report checkbox empty, just enter the path in JasperReport field.

Parameters

Now we are going to pass parameter from Report & Process to JasperReports

• Select tab Parameter of the Report & Process window, then create NEW parameter.

• Field "Name":
  • select a parameter name as you like.
• Field "DB Column Name":
  • This name has to be EXACTLY the same as the name of parameter you defined in the JasperReports (file .jrxml or .jasper). Otherwise, you will get an empty report.
  • It is irrelevant whether the parameter has been defined as promptable in iReports or not.

• Quote the parameters
  • You can quote the parameter in SQL query in your JasperReports like: $P{MY_PARAMETER} or $P!{MY_PARAMETER}. The last type is for string substitution.

• SUBREPORT_DIR
  • If you hava a subreport, you can do it this way:
  1. Leave the parameter SUBREPORT_DIR empty in your master-report.
  2. Compile the subreport to .jasper format.
  3. Put your master-report and subreport(.jasper format) in the path $ADEMPIERE_HOME/reports/.
  • This solution is tested OK for both Client/Server and Browser/Server deployment.

Menu Creation

• Step 1. Open Menu Window. From System Admin > System Rules > Menu
• Step 2. Create a new...

• Step 3. Fill and Save.
• Step 4. Log out and then Log in 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.

• Step 1.Log in as Role: GardenWorld Admin
• Step 2.Open window Role

• Step 3. Add a role accessing your process in the Process Access Tab and Save.
• Step 4. 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.

Add file .jasper like attachment

type with special syntax

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. Some times we need that Adempiere passes parameters to jasper as a value range , for example an accounting date (From: , to:). If we named the parameter as DateAcct in Adempiere, this parameter in our Jasper Report would be like : DateAcct1 and DateAcct2.

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 misled 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 are using iReport or JasperReports to compile the report ( generate .jasper file), you have to use the same JasperReports/iReport version as the integrated version in ADempiere;
• For ADempiere 3.6.0 LTS, you can user iReport 3.7.3 and JasperReports 3.7.3.
• Otherwise it may not be loaded by ADempiere, and you will get error messages in the Jboss console like:.

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.

Any way, whatever jasper or ireport new version just add the new jasper version to source code lib and remove the old jasper version with dealing with some errors may comes from the versions modifications the system will working properly also do some build file modification to worked through the web and web start.

I will put the build modifications at another time.

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 exportd PDF report looks like gibberish, while it was pretty good in the swing view of client, you can follow this method:

Step 1. Prepare your TTF font

• You have to get the files of the fonts you are using, like arial.ttf, wqy-zenhei.ttf, ...

Step 2. Set PDF attribute

• Open your .jrxml file. You can edit it with notepad or iReport.
• Set PDF attributes for each elements

1. Pdf Font name: Simply write the file name like "wqy-zenhei.ttf", no need to specify the full path.
2. Pdf Embedded: you can leave it empty or not, it doesn't matter.
3. Pdf Encoding: select Identity-H (Unicode with horizontal writing). "Identity-H" works well for Chinese. You can try the best one for your own language.

Step 3. Copy TTF Font

• Copy your fonts into the two paths:

1. $ADEMPIERE_HOME/
2. $ADEMPIERE_HOME/reports/

• Explanation:
  • Client/Server reads font file from $ADEMPIERE_HOME/
  • Browser/Server reads font file from $ADEMPIERE_HOME/reports/

Step 4. Test It

• Test your JasperReports in Client/Server and Browser/Server.

Tips - Using Style

• It is inconvenient to set PDF attributes one by one for each object. Now you can create styles to handle it.

1. Create a new style.
2. Specify Pdf Font name and Pdf Encoding for the new style.
3. Assign the style to each objects on your report. Don't forget to do it for your subreport.

• If your later want to change the font or the path, you simply do it in the style. That is easy!

Another Approach - Add fonts to package

• There is a way someone personally used to overcome this problem. All the problems of export to PDF is mostly a problem of lack of an appropriate font, or modification of certain properties. Just add the fonts compatible with the PDF format to any Adempiere package and build a new Adempiere_342.zip with these fonts.

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.

- If you cannot export to xls then make sure that you have the apache poi(poi-2.0-final-20040126 or higher)library somewhere where java can find it and to worked with web modified build.xml file into install project to load this new jar to client classpath.

Problem with subreports

• About the old method:
  • The old method of dealing with subreports is outdated, you can check it in Talk:ADempiere/Compiere JasperReports Integration HowTo
  • ADempiere already set path to ADEMPIERE_HOME/reports so you need not set any further path unless you want to give a fully qualified path (in case something is wrong with the preset path).
• Right now, you can do it this way, simple and easy!

1. Leave the parameter SUBREPORT_DIR empty in your master-report.
2. Compile the subreport to .jasper format. You can do it with iReport or JasperReports.
3. Put your master-report and subreport(.jasper format) in the path $ADEMPIERE_HOME/reports/.
4. Open window Report and Process, set the path in field JasperReport like example.jasper. You can simply put the file name here, no need to specify a full path.

• This solution is tested OK for both Client/Server and Browser/Server deployment.

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

Oracle users and IReports 3.5.2

1.) Before creating the Database: Download your classes12.jar from Oracle and place it in the "iReport-nb-3.5.2\ireport\libs directory. Then from iReport click on Tools->Options and select the CLASSPATH tab. Click "Add JAR" and select the classes12.jar file you placed in the "libs" directory.

2.) In the same window click on the "General" tab and look for the "Compatibility" tab within it. Select JasperReports 2.0.4 which seems to work fine so far.

3.) The real thing that you must make sure of is having JDK (using version 11 as later versions seem to cause problems) installed.

4.) Setting a PATH to the JAVA javac.exe is very important. You will get compiling errors if you do not! So right click on "My Computer" and select "Properties" then "Advanced" tab then "Environmental Variables" Edit the PATH statement at the top to include the path for javac.exe. For example C:\Program Files\Java\jdk1.6.0_11\bin which was added to the end of the current path statement.

Resource bundle problem

If you get an error like "Can't find bundle for base name...". If you're using a resource bundle in a subreport, this can, for some reason, not be found. Then try to pass the resource bundle from the main report with a parameter. In the xml-file this would look something like this (if you use the same resource bundle in the main report as in the subreport):

				<subreportParameter name="REPORT_RESOURCE_BUNDLE">
					<subreportParameterExpression><![CDATA[$P{REPORT_RESOURCE_BUNDLE}]]></subreportParameterExpression>
				</subreportParameter>

Links

• HOWTO Use Jasper On Financial Reports
• How to use AmtInWords_XX in JasperReports
• HOWTO Create Jasper C_Order Register
• Latest movie showing simple setup for JasperReports
• Latest Workshop:Integration von JasperReports in ADempiere in German
• Explore JasperReports with ADempiere in Chinese. by Peanut Blake.
• Integration of ADempiere and JasperReports PDF in Chinese. by Peanut Blake.
• JasperReports Project Home
• iReport Project Home