Report Development with Jasper

Overview

• What you’ll learn:
  • How JasperReports architecture works (JRXML design, compilation, filling, and export) and how to set up JasperReports Studio
  • How to design reports with bands, fields, parameters, grouping, subreports, charts, and cross-tabs connected to the iDempiere database
  • How to deploy reports as iDempiere plugins, register them in the Application Dictionary, and pass parameters from iDempiere processes
• Prerequisites: Lesson 15 — Building Your First Plugin, Lesson 2 — iDempiere Architecture Overview, basic SQL knowledge
• Estimated reading time: 25 minutes

Introduction

Every business needs printed documents and formatted reports — invoices sent to customers, purchase orders sent to vendors, inventory reports for warehouse staff, financial statements for management. While iDempiere’s built-in reporting capabilities handle many needs, JasperReports provides the pixel-level control required for professional document design. iDempiere has native JasperReports integration, making it the standard tool for custom report development in the iDempiere ecosystem.

This lesson walks you through the complete JasperReports workflow: from installing the design tool and connecting to your database, through report design fundamentals, to deploying finished reports as iDempiere plugins. By the end, you will have designed and deployed a complete invoice report.

JasperReports Architecture

JasperReports uses a four-stage pipeline to produce formatted output from data:

1. Design (JRXML): You create a report template in XML format (JRXML file) using a visual designer. The template defines the layout, data fields, parameters, formatting, and logic.
2. Compile (Jasper): The JRXML template is compiled into a binary .jasper file. This compilation step validates the template and optimizes it for execution. You can distribute either the JRXML (compiled at runtime) or the pre-compiled .jasper file.
3. Fill: The compiled template is filled with data from a data source — typically a JDBC database connection. Parameters are passed in (date ranges, document IDs, etc.), queries execute, and the report engine processes the data through the template to produce a filled report object.
4. Export: The filled report is exported to the desired output format: PDF, Excel (XLSX), HTML, CSV, or others. iDempiere typically exports to PDF for printed documents and Excel for data analysis reports.

JRXML (design) --compile--> .jasper (binary) --fill(data)--> JasperPrint --export--> PDF/Excel/HTML

Setting Up JasperReports Studio

TIBCO JasperReports Studio (formerly JasperSoft Studio, and before that iReport) is the visual report designer. It is built on Eclipse and provides a WYSIWYG interface for creating JRXML templates.

Installation

1. Download TIBCO JasperReports Studio from the TIBCO community website. Choose the standalone version for your operating system.
2. Extract and run the application. It launches as a standalone Eclipse-based IDE.
3. The workspace will contain a default project. You can create a new project for your iDempiere reports.

Connecting to the iDempiere Database

Create a Data Adapter to connect JasperReports Studio to your iDempiere PostgreSQL database:

1. In the Repository Explorer, right-click Data Adapters and select Create Data Adapter.
2. Choose Database JDBC Connection.
3. Configure the connection:
   • Name: iDempiere Development
   • JDBC Driver: org.postgresql.Driver
   • JDBC URL: jdbc:postgresql://localhost:5432/idempiere
   • Username: adempiere
   • Password: adempiere
4. Click Test to verify the connection, then Finish.

If the PostgreSQL JDBC driver is not available, download postgresql-xx.x.x.jar and add it to JasperReports Studio’s classpath via the Driver Classpath tab.

Report Design Fundamentals

Report Bands

A JasperReports template is divided into horizontal bands, each serving a specific purpose. Understanding bands is fundamental to report layout:

• Title: Appears once at the very beginning of the report. Use for the report title, company logo, and generation date.
• Page Header: Appears at the top of every page. Use for column headers that repeat across pages.
• Column Header: Appears at the top of each column (for multi-column reports). For single-column reports, it is similar to Page Header.
• Detail: Repeats once for each row in the dataset. This is where individual record data goes — one row per invoice line, order line, inventory item, etc.
• Column Footer: Appears at the bottom of each column.
• Page Footer: Appears at the bottom of every page. Use for page numbers and confidentiality notices.
• Last Page Footer: Replaces Page Footer on the last page only. Use for signature lines or final notices.
• Summary: Appears once at the very end of the report. Use for grand totals, summary tables, and charts.
• Group Header/Footer: Appears when a group value changes. Essential for grouped reports (e.g., orders grouped by customer).

Fields, Parameters, and Variables

These three elements are the building blocks of report data:

Fields

Fields correspond to columns in your SQL query result set. They represent the raw data from the database:

<field name="DocumentNo" class="java.lang.String"/>
<field name="DateInvoiced" class="java.sql.Timestamp"/>
<field name="GrandTotal" class="java.math.BigDecimal"/>
<field name="BPartnerName" class="java.lang.String"/>

Parameters

Parameters are values passed into the report at runtime — such as a date range or document ID. iDempiere passes parameters from the AD_Process definition:

<parameter name="C_Invoice_ID" class="java.lang.Integer">
    <defaultValueExpression><![CDATA[0]]></defaultValueExpression>
</parameter>
<parameter name="DateFrom" class="java.sql.Timestamp"/>
<parameter name="DateTo" class="java.sql.Timestamp"/>

Variables

Variables hold calculated values — running totals, counts, averages, and custom expressions:

<!-- Running total of line amounts -->
<variable name="TotalAmount" class="java.math.BigDecimal" calculation="Sum">
    <variableExpression><![CDATA[$F{LineNetAmt}]]></variableExpression>
</variable>

<!-- Line counter -->
<variable name="LineCount" class="java.lang.Integer" calculation="Count">
    <variableExpression><![CDATA[$F{C_InvoiceLine_ID}]]></variableExpression>
</variable>

<!-- Custom calculation -->
<variable name="DiscountPercent" class="java.math.BigDecimal">
    <variableExpression><![CDATA[
        $F{PriceList}.compareTo(BigDecimal.ZERO) != 0
        ? $F{PriceList}.subtract($F{PriceActual}).divide($F{PriceList}, 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100))
        : BigDecimal.ZERO
    ]]></variableExpression>
</variable>

The Report Query

Every report has a main SQL query that retrieves data from the database. Parameters are referenced in the query using the $P{} syntax:

<queryString>
    <![CDATA[
    SELECT
        i.DocumentNo,
        i.DateInvoiced,
        i.GrandTotal,
        bp.Name AS BPartnerName,
        bp.Value AS BPartnerValue,
        il.Line,
        il.Description AS LineDescription,
        p.Name AS ProductName,
        il.QtyInvoiced,
        il.PriceActual,
        il.LineNetAmt
    FROM C_Invoice i
    JOIN C_BPartner bp ON i.C_BPartner_ID = bp.C_BPartner_ID
    JOIN C_InvoiceLine il ON i.C_Invoice_ID = il.C_Invoice_ID
    LEFT JOIN M_Product p ON il.M_Product_ID = p.M_Product_ID
    WHERE i.C_Invoice_ID = $P{C_Invoice_ID}
    ORDER BY il.Line
    ]]>
</queryString>

Passing Parameters from iDempiere

iDempiere passes parameters to JasperReports through the AD_Process system. When a user runs a report, iDempiere collects the process parameters and forwards them to the Jasper engine.

Standard Parameters

iDempiere automatically provides these parameters to every Jasper report:

• AD_Client_ID — the current client
• AD_Org_ID — the current organization
• AD_Language — the user’s language
• AD_PInstance_ID — the process instance ID
• RECORD_ID — the current record ID (when the report is run from a window toolbar)

Custom Parameters

Define custom parameters in the AD_Process window, and iDempiere will prompt the user for values before running the report. The parameter names in AD_Process must match the parameter names declared in your JRXML file:

1. Navigate to Application Dictionary > Report & Process.
2. Create a new process with Jasper Report checked.
3. Set the Jasper Report Path to your JRXML file location.
4. Add parameters on the Parameter tab with column names matching your JRXML parameters.

Subreports and Sub-Datasets

Complex documents often require nested data that cannot be expressed in a single flat query. Subreports and sub-datasets address this need.

Subreports

A subreport is a separate JRXML file embedded within a parent report. Use subreports for:

• Tax breakdown tables on invoices
• Payment history sections
• Multiple independent data sections in a single document

<!-- In the parent report's Detail or Summary band -->
<subreport>
    <reportElement x="0" y="0" width="555" height="100"/>
    <subreportParameter name="C_Invoice_ID">
        <subreportParameterExpression><![CDATA[$F{C_Invoice_ID}]]></subreportParameterExpression>
    </subreportParameter>
    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
    <subreportExpression><![CDATA["invoice_tax_subreport.jasper"]]></subreportExpression>
</subreport>

The subreport file (invoice_tax_subreport.jrxml) has its own query, fields, and layout. It receives the invoice ID as a parameter and queries the tax lines independently.

Sub-Datasets

Sub-datasets are alternative data sources within the same JRXML file. They are commonly used with chart components and cross-tabs:

<subDataset name="MonthlySales">
    <parameter name="AD_Client_ID" class="java.lang.Integer"/>
    <queryString>
        <![CDATA[
        SELECT date_trunc('month', DateInvoiced) AS Month,
               SUM(GrandTotal) AS Total
        FROM C_Invoice
        WHERE IsSOTrx='Y' AND AD_Client_ID=$P{AD_Client_ID}
        GROUP BY date_trunc('month', DateInvoiced)
        ORDER BY Month
        ]]>
    </queryString>
    <field name="Month" class="java.sql.Timestamp"/>
    <field name="Total" class="java.math.BigDecimal"/>
</subDataset>

Grouping and Aggregation

Grouping organizes report data into sections based on a field value, with group headers and footers for subtotals:

<group name="InvoiceGroup">
    <groupExpression><![CDATA[$F{C_Invoice_ID}]]></groupExpression>
    <groupHeader>
        <band height="60">
            <!-- Invoice header: document number, date, customer -->
            <textField>
                <reportElement x="0" y="0" width="200" height="20"/>
                <textFieldExpression><![CDATA["Invoice: " + $F{DocumentNo}]]></textFieldExpression>
            </textField>
        </band>
    </groupHeader>
    <groupFooter>
        <band height="30">
            <!-- Invoice total -->
            <textField pattern="#,##0.00">
                <reportElement x="400" y="5" width="155" height="20"/>
                <textFieldExpression><![CDATA[$V{InvoiceTotal}]]></textFieldExpression>
            </textField>
        </band>
    </groupFooter>
</group>

<!-- Variable that resets with each group -->
<variable name="InvoiceTotal" class="java.math.BigDecimal"
    resetType="Group" resetGroup="InvoiceGroup" calculation="Sum">
    <variableExpression><![CDATA[$F{LineNetAmt}]]></variableExpression>
</variable>

Charts and Visualizations

JasperReports supports embedded charts for visual data presentation. Charts are typically placed in the Summary band or a group footer:

<!-- Bar chart in the Summary band -->
<barChart>
    <chart evaluationTime="Report">
        <reportElement x="0" y="10" width="555" height="250"/>
        <chartTitle>
            <titleExpression><![CDATA["Monthly Sales"]]></titleExpression>
        </chartTitle>
    </chart>
    <categoryDataset>
        <dataset>
            <datasetRun subDataset="MonthlySales">
                <datasetParameter name="AD_Client_ID">
                    <datasetParameterExpression><![CDATA[$P{AD_Client_ID}]]></datasetParameterExpression>
                </datasetParameter>
                <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
            </datasetRun>
        </dataset>
        <categorySeries>
            <seriesExpression><![CDATA["Sales"]]></seriesExpression>
            <categoryExpression><![CDATA[new SimpleDateFormat("MMM yyyy").format($F{Month})]]></categoryExpression>
            <valueExpression><![CDATA[$F{Total}]]></valueExpression>
        </categorySeries>
    </categoryDataset>
    <barPlot>
        <plot/>
    </barPlot>
</barChart>

Cross-Tabs for Pivot-Style Reports

Cross-tabs (crosstabs) create pivot-table-style reports with dynamic rows and columns. They are ideal for summary reports where you want to compare values across two dimensions:

<!-- Sales by Product Category and Month -->
<crosstab>
    <reportElement x="0" y="0" width="555" height="200"/>
    <crosstabDataset>
        <dataset>
            <datasetRun subDataset="SalesPivot">
                <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
            </datasetRun>
        </dataset>
    </crosstabDataset>
    <rowGroup name="Category" width="120">
        <bucket class="java.lang.String">
            <bucketExpression><![CDATA[$F{CategoryName}]]></bucketExpression>
        </bucket>
        <crosstabRowHeader>
            <cellContents>
                <textField>
                    <reportElement x="0" y="0" width="120" height="20"/>
                    <textFieldExpression><![CDATA[$V{Category}]]></textFieldExpression>
                </textField>
            </cellContents>
        </crosstabRowHeader>
    </rowGroup>
    <columnGroup name="Month" height="30">
        <bucket class="java.lang.String">
            <bucketExpression><![CDATA[$F{MonthName}]]></bucketExpression>
        </bucket>
        <crosstabColumnHeader>
            <cellContents>
                <textField>
                    <reportElement x="0" y="0" width="80" height="30"/>
                    <textFieldExpression><![CDATA[$V{Month}]]></textFieldExpression>
                </textField>
            </cellContents>
        </crosstabColumnHeader>
    </columnGroup>
    <measure name="SalesTotal" class="java.math.BigDecimal" calculation="Sum">
        <measureExpression><![CDATA[$F{LineNetAmt}]]></measureExpression>
    </measure>
    <crosstabCell width="80" height="20">
        <cellContents>
            <textField pattern="#,##0.00">
                <reportElement x="0" y="0" width="80" height="20"/>
                <textFieldExpression><![CDATA[$V{SalesTotal}]]></textFieldExpression>
            </textField>
        </cellContents>
    </crosstabCell>
</crosstab>

Conditional Formatting

Apply dynamic styling based on data values using conditional expressions:

<!-- Red text for overdue amounts -->
<textField pattern="#,##0.00">
    <reportElement x="400" y="0" width="155" height="20">
        <style forecolor="#000000">
            <conditionalStyle>
                <conditionExpression><![CDATA[$F{DaysOverdue} > 30]]></conditionExpression>
                <style forecolor="#FF0000" isBold="true"/>
            </conditionalStyle>
            <conditionalStyle>
                <conditionExpression><![CDATA[$F{DaysOverdue} > 0 && $F{DaysOverdue} <= 30]]></conditionExpression>
                <style forecolor="#FF8C00"/>
            </conditionalStyle>
        </style>
    </reportElement>
    <textFieldExpression><![CDATA[$F{OpenAmount}]]></textFieldExpression>
</textField>

<!-- Alternating row colors -->
<frame>
    <reportElement x="0" y="0" width="555" height="20"
        backcolor="#F5F5F5"
        mode="Opaque">
        <printWhenExpression><![CDATA[$V{REPORT_COUNT} % 2 == 0]]></printWhenExpression>
    </reportElement>
</frame>

Internationalization (i18n) in Reports

For multi-language deployments, use resource bundles to externalize report text:

<!-- In the JRXML -->
<jasperReport ... resourceBundle="com.example.reports.invoice">

<!-- Use $R{} to reference resource bundle keys -->
<textField>
    <textFieldExpression><![CDATA[$R{invoice.title}]]></textFieldExpression>
</textField>
<textField>
    <textFieldExpression><![CDATA[$R{invoice.date.label}]]></textFieldExpression>
</textField>

Create property files for each language:

# invoice.properties (default - English)
invoice.title=INVOICE
invoice.date.label=Invoice Date
invoice.total.label=Grand Total

# invoice_es.properties (Spanish)
invoice.title=FACTURA
invoice.date.label=Fecha de Factura
invoice.total.label=Total General

# invoice_de.properties (German)
invoice.title=RECHNUNG
invoice.date.label=Rechnungsdatum
invoice.total.label=Gesamtbetrag

iDempiere passes the AD_Language parameter, which you can use to set the report locale.

Deploying Reports

Plugin-Based Deployment

The recommended deployment method is to include Jasper reports in your OSGi plugin:

1. Create a jasper/ directory in your plugin project root.
2. Place your JRXML files (and compiled .jasper files) in this directory.
3. Include subreport files, images, and resource bundles in the same directory.
4. Reference the report path in the AD_Process definition using the bundle path: bundle://com.example.reports/jasper/invoice.jrxml

Registering Reports in the Application Dictionary

To make a report accessible to users:

1. Navigate to Application Dictionary > Report & Process.
2. Create a new record:
   • Search Key: Custom_Invoice_Report
   • Name: Custom Invoice Report
   • Data Access Level: Client+Organization
   • Entity Type: User Maintained
   • Jasper Report: (checked)
   • Jasper Report Path: bundle://com.example.reports/jasper/invoice.jrxml
3. Add parameters on the Parameter tab matching your JRXML parameter names.
4. Assign the process to a menu item or window toolbar button.

Toolbar Button Integration

To run a report from a window’s toolbar (e.g., print an invoice from the Invoice window):

1. In the Report & Process definition, ensure the report is properly configured.
2. In the Table & Column window, find the relevant table (e.g., C_Invoice).
3. Link the process to the table via the Print Format or process assignment.
4. When users click the Print button on the Invoice window, iDempiere passes the current C_Invoice_ID as the RECORD_ID parameter.

Practical Example: Designing an Invoice Report

Let us design a complete invoice report step by step.

Step 1: Create the Main Query

SELECT
    i.DocumentNo, i.DateInvoiced, i.GrandTotal, i.TotalLines,
    i.Description AS InvoiceDescription,
    bp.Name AS CustomerName, bp.Value AS CustomerCode,
    bpl.Phone, bpl.Fax,
    l.Address1, l.Address2, l.City, l.Postal, l.RegionName,
    co.Name AS Country,
    il.Line, il.Description AS LineDescription,
    COALESCE(p.Name, il.Description) AS ProductName,
    il.QtyInvoiced, il.PriceActual, il.LineNetAmt,
    uom.Name AS UOMName,
    it.TaxAmt, t.Name AS TaxName, t.Rate AS TaxRate
FROM C_Invoice i
JOIN C_BPartner bp ON i.C_BPartner_ID = bp.C_BPartner_ID
LEFT JOIN C_BPartner_Location bpl ON i.C_BPartner_Location_ID = bpl.C_BPartner_Location_ID
LEFT JOIN C_Location l ON bpl.C_Location_ID = l.C_Location_ID
LEFT JOIN C_Country co ON l.C_Country_ID = co.C_Country_ID
JOIN C_InvoiceLine il ON i.C_Invoice_ID = il.C_Invoice_ID
LEFT JOIN M_Product p ON il.M_Product_ID = p.M_Product_ID
LEFT JOIN C_UOM uom ON il.C_UOM_ID = uom.C_UOM_ID
LEFT JOIN C_InvoiceTax it ON i.C_Invoice_ID = it.C_Invoice_ID
LEFT JOIN C_Tax t ON it.C_Tax_ID = t.C_Tax_ID
WHERE i.C_Invoice_ID = $P{RECORD_ID}
ORDER BY il.Line

Step 2: Design the Layout

• Title band: Company logo (image element), company name, “INVOICE” heading.
• Page Header band: Invoice number, date, customer name and address (positioned in a two-column layout using absolute positioning).
• Column Header band: Table headers — Line, Product, Quantity, Unit Price, Amount.
• Detail band: One row per invoice line — line number, product name, quantity, price, line total.
• Summary band: Subtotal, tax breakdown (using a subreport or sub-dataset), grand total, payment terms text.
• Page Footer band: Page number expression: "Page " + $V{PAGE_NUMBER} + " of " + $V{PAGE_NUMBER} (with evaluationTime=”Report” for total pages).

Step 3: Add Finishing Touches

• Number formatting: Use #,##0.00 pattern for currency amounts.
• Date formatting: Use dd/MM/yyyy or locale-appropriate patterns.
• Null handling: Use isBlankWhenNull="true" on text fields to show empty string instead of “null”.
• Page breaks: Set isStartNewPage="true" on the group header if printing multiple invoices in batch.

Summary

JasperReports is a powerful and mature reporting engine that integrates seamlessly with iDempiere. You learned the four-stage report pipeline, how to set up the design tool and connect it to your database, how to design reports using bands, fields, parameters, variables, grouping, subreports, charts, and cross-tabs, and how to deploy your reports as plugins registered in the Application Dictionary. The invoice example provides a template you can adapt for purchase orders, delivery notes, financial statements, and any other business document your organization needs. In the next lesson, you will learn testing and debugging techniques for building reliable iDempiere plugins.