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.

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, you must install the 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.

JasperReport

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

• Follow the video to create the report, or see the JasperSoft Studio documentation