Create reports using JasperSoft Studio
This guide explains how to use JasperSoft Studio to create complex reports and print-outs for use by the JasperReports engine embedded in iDempiere. This is an alternative to using the standard built-in reports described here.
Introduction
The standard reporting mechanism is suitable for simple reports, but creating complex reports with sophisticated layouts can be difficult and time-consuming using the built-in Print Format tools, so rather use JasperSoft Studio to do the design of sophisticated reports and print-outs with professionally designed layouts, fonts, colours and logos, then load the JRXML report definition into iDempiere for execution by the JasperReports engine.
The guide is based on this video by Evenos.
How to do it
JasperSoft Studio
If you have not already done so, install JasperSoft Studio:
- Download and install the Community Edition of JasperSoft Studio from the download page (note: don't download the older iReport Designer as this has been deprecated in favour of JasperSoft Studio);
- Follow the installation instructions here. JasperSoft Studio is built on the same framework as the Eclipse IDE so the installation and usage is the same.
Create a report
Database Adapter
The first step is to create a database adapter to connect JasperSoft Studio to your instance of the iDempiere database server:
- Start up JasperSoft Studio
- Delete the two unnecessary Data Adapters (called One Empty Record and Sample DB)
- Create a new Data Adapter to connect to the iDempiere database: Create Data Adapter > Database JDBC Connection > Next >
- Name: iDempiere Data Adapter
- JDBC Driver: org.postgresql.Driver
- JDBC URL: jdbc:postgresql://localhost:5432/idempiere (use localhost if the database is on the same machine as JasperSoft Studio, otherwise replace localhost with the name of the database server)
- Username: adempiere (the same username that was used when iDempiere was installed)
- Password: *********
- Click on Test to test the connection parameters
- Click Finish if all is well
This only has to be done once. Thereafter you will use the same adapter to develop your reports.
Design the report
Now you can create the JasperReport:
- Click on the New JasperReport icon
- Select a Report Template (e.g. Blank Format A4)
- Parent directory: MyReports (in the JaspersoftWorkspace), or create a new JasperReports Project
- File name: Invoice.jrxml (JRXML is the XML format used by JasperReports)
- Open the adempiere (CURRENT) schema to see all the tables and views that can be used in the report (note that you can also create additional database views in the schema for your own reports)
- In the Texts box (to the right of the schemas), write a SQL SELECT statement to assemble the items for the report, for example:
SELECT a.documentno, a.docstatus, b.name, b.logo_id FROM adempiere.c_invoice a, adempiere.c_bpartner b WHERE a.c_bpartner_id=b.c_bpartner_id
- The SELECT can be as detailed and as complex as you require
- Click Next > to choose the fields from the SELECT that you want to see in the report
- Click Next > and add any GROUP BY clauses if required
- Click Finish to generate the basic report definition
- Use the Preview tab to run the report, then Save it regularly while you work on it.
Follow the video to complete the design of the report, and see the JasperSoft Studio documentation for more details.
Compile the report
When you are done, compile the report for use by iDempiere:
- Select the Design Tab
- Click on the Compile Report button (above the report design)
This will generate the report binary file Invoice.jasper from the resource Invoice.jrxml. This is the file that you will upload to iDempiere (see below). Note that you can upload either the binary .jasper or the XML description .JRXML because iDempiere understands both, but the binary may be more efficient, and it may avoid version inconsistencies between the JasperReports compiler in iDempiere and the one in JasperSoft Studio.
Installing the report
Once you are happy with the report, you can install it in iDempiere as follows:
- Login as System/System
- Select Report & Process and create a new entry:
- Search Key: InvoiceJasperReport
- Name: InvoiceJasperReport
- Data Access Level: Client+Organisation (it is not useful to the System user)
- Report: check the box for completeness
- Class Name: org.compiere.report.ReportStarter (note that this is case sensitive)
- Jasper Report: attachment:Invoice.jasper (this is the binary that was created above)
- Save the record
- Now click on the Attachment icon (the paperclip on the toolbar) and load the binary from the JaspersoftWorkspace. This ensures that the binary is stored in the database with the Report Process record and will get backed up with the database.
To try out the report:
- Logout of System and into the client (e.g. GardenWorld Admin)
- Select Invoice and find an invoice that will use the new report format
- Click on the Print button (or Alt-P)
- When the preview opens, click on Customize Report to open the Print Format Window
- Find the field Jasper Report and use the dropdown to select the Report that you created above
- Save the updated Print Format and reprint the document with the new report layout.
