Basic Reporting with iDempiere

Overview

• What you’ll learn: How to generate and customize reports in iDempiere using print formats, report views, and JasperReports, including built-in financial reports, export options, and report scheduling.
• Prerequisites: Lesson 1 (Introduction to iDempiere), Lesson 3 (Navigating the Interface), Lesson 7 (Chart of Accounts Setup), Lesson 10 (Document Processing Basics)
• Estimated reading time: 20 minutes

Introduction

Data in an ERP system is only valuable if you can extract it as meaningful information. iDempiere provides a robust reporting framework that ranges from simple, configuration-driven print formats to powerful, design-rich JasperReports. Whether you need a quick list of open invoices, a detailed financial statement, or a beautifully formatted customer invoice to send by email, iDempiere’s reporting tools have you covered.

This lesson introduces the three primary reporting mechanisms in iDempiere, walks you through creating a simple report step by step, surveys the built-in financial reports, and explains how to export and schedule reports for routine business needs.

Types of Reports in iDempiere

iDempiere offers three distinct approaches to reporting, each suited to different use cases:

1. Print Formats (AD_PrintFormat)

Print formats are the native, built-in reporting mechanism of iDempiere. They are entirely configuration-driven — no coding or external tools required. A print format defines how data from a specific table (or view) is laid out for printing or on-screen display. Every window in iDempiere has a default print format that controls what appears when you click the Print or Report button.

Print formats are best suited for:

• Standard transactional documents (invoices, orders, shipments, receipts)
• Simple tabular listings (product lists, business partner directories)
• Quick, ad-hoc reports that administrators can build without developer tools

The key advantage of print formats is accessibility — any administrator can create and modify them directly within the iDempiere interface. The trade-off is limited formatting flexibility compared to JasperReports.

2. Report Views (AD_ReportView)

Report views provide a data aggregation layer that sits between the raw database tables and the print format presentation. A report view defines a SQL-based data source — typically joining multiple tables and including computed columns — that can then be rendered by a print format.

Report views are used when:

• You need to aggregate or summarize data across multiple tables
• The standard table structure does not provide the columns you need
• You want to create reports with grouping, subtotals, or cross-table calculations

Think of a report view as a “virtual table” that gives the print format access to exactly the data it needs. For example, a sales analysis report might need data from orders, order lines, business partners, and products — a report view joins these tables and presents them as a single data source.

3. JasperReports

JasperReports is a widely-used open-source reporting engine that iDempiere integrates through a plugin. JasperReports provides pixel-perfect report design with advanced features including:

• Sophisticated visual layouts with precise positioning
• Charts, graphs, and barcodes embedded directly in reports
• Sub-reports for master-detail presentations
• Conditional formatting and complex expressions
• Cross-tabs (pivot table-style reports)

JasperReports are designed using external tools such as Jaspersoft Studio (an Eclipse-based visual report designer) and then deployed to iDempiere as .jrxml or compiled .jasper files. This approach requires more technical skill than print formats but delivers professional-grade output suitable for customer-facing documents and executive dashboards.

Working with Print Formats

Print Format Structure

A print format record (AD_PrintFormat) contains the following key elements:

• Name: A descriptive name for the print format (e.g., “Standard Invoice,” “Product Listing by Category”).
• Table: The database table (or report view) that serves as the data source. All available columns come from this source.
• Report View: Optionally, a report view that replaces the direct table as the data source.
• Is Form: When checked, the print format renders as a form (one record per page, like an invoice). When unchecked, it renders as a list (multiple records per page, like a report).
• Is Default: Whether this is the default print format for the associated table.
• Header/Footer Margin: Page layout settings.

Print Format Items (Columns)

The real work of a print format happens in its items (stored in AD_PrintFormatItem). Each item represents one element on the report — typically a column from the data source. For each item, you configure:

• Name: The column header or label displayed on the report.
• Print Area Type: Where the item appears — Content (the main body), Header, or Footer.
• Sequence: The display order of the column (left to right for lists, or positional for forms).
• Is Printed: Whether this item actually appears on the output. Unprinted items can still be used for sorting or grouping.
• Is Order By: Whether to sort the report by this column. Multiple columns can be marked for sorting, with the sequence determining the sort priority.
• Sort Order (Ascending/Descending): The direction of the sort.
• Is Group By: Whether to group records by this column’s value, creating section breaks in the report.
• Is Sum: Whether to display a running total or sum for numeric columns, useful for financial reports.
• Is Count: Whether to display a count of records.
• Display Logic: A conditional expression that controls whether the item appears based on runtime context variables. For example, you might show a “Discount” column only when discounts exist.
• Max Width/Height: Size constraints for the column.
• Format Pattern: Number or date formatting pattern (e.g., #,##0.00 for numbers with two decimal places).

Creating a Simple Print Format: Step by Step

Let us walk through creating a simple report that lists all products with their categories and prices:

1. Navigate to Print Format: Go to System Admin → General Rules → Print Format.
2. Create a New Record: Click the New button. Enter a name such as “Product Price Report.” Select M_Product as the Table.
3. Save the Header: Save the record. iDempiere will automatically generate print format items for all columns in the selected table.
4. Configure the Items: Navigate to the Print Format Item tab. You will see entries for every column in the M_Product table. For most columns, uncheck “Is Printed” — you only want to display a few fields. Enable printing for:
   • Search Key (Value): Sequence 10, Is Printed = Yes, Is Order By = Yes (Ascending).
   • Name: Sequence 20, Is Printed = Yes.
   • Product Category (M_Product_Category_ID): Sequence 30, Is Printed = Yes, Is Group By = Yes (to group products by category).
   • UOM (C_UOM_ID): Sequence 40, Is Printed = Yes.
   • Product Type: Sequence 50, Is Printed = Yes.
5. Test the Report: Open the Product window, and from the toolbar select Print/Report using your new print format. You can switch between print formats using the print format dropdown if multiple formats exist for the same table.

This example produces a basic listing. For a price-inclusive report, you would base the print format on a report view that joins M_Product with M_ProductPrice to include price columns.

Report Views for Advanced Data

Creating and Using Report Views

A Report View (AD_ReportView) defines the data source for more complex reports. The key fields are:

• Name: A descriptive name.
• Table: The primary table for the view.
• Where Clause: An optional SQL WHERE clause to filter records (e.g., IsActive='Y').
• Order By Clause: An optional SQL ORDER BY clause.

Report views can reference database views (created by DBAs) that join multiple tables. For instance, you might create a database view called RV_OrderSummary that joins C_Order, C_OrderLine, C_BPartner, and M_Product, then create a report view pointing to this database view, and finally build a print format on top of it.

Drill-Down Capabilities

One of the most powerful features of iDempiere’s reporting system is drill-down. When viewing a report on screen (in HTML mode), you can click on linked values to navigate to the underlying records. For example, in an invoice listing report, clicking on an invoice’s Document No will open the actual invoice record. Drill-down works automatically for foreign key columns — any column that references another table becomes a clickable link.

This capability turns reports from static output into interactive navigation tools, allowing users to move from summary data to transaction detail with a single click.

Introduction to JasperReports in iDempiere

What Are JasperReports?

JasperReports is an open-source Java reporting library that has become the de facto standard for report generation in Java-based applications. Reports are designed as XML template files (.jrxml) that define the layout, data fields, grouping, formatting, and expressions. These templates are compiled into .jasper files that the report engine processes at runtime, combining the template with live data to produce the final output.

How JasperReports Integrate with iDempiere

The integration works through the iDempiere process framework:

1. Report Design: A developer or report designer creates the report template in Jaspersoft Studio, defining the SQL query (or using a data source provided by iDempiere), the layout bands (title, page header, column header, detail, group header/footer, page footer, summary), and all visual elements.
2. Deployment: The .jrxml file (or compiled .jasper file) is placed in the iDempiere server’s file system, or attached to a process record, or stored in a report format record.
3. Process Configuration: In the Application Dictionary, a Process record (AD_Process) is configured to point to the JasperReport file. Parameters defined on the process record become the report’s input parameters (e.g., date range, business partner filter).
4. Execution: When a user runs the process (from the menu, toolbar, or a window button), iDempiere passes the parameters to the Jasper engine, which executes the report’s query, populates the template, and returns the rendered output.

Many community-contributed and commercial iDempiere plugins include JasperReport templates for standard documents (invoices, purchase orders, delivery notes, checks). These can serve as starting points for your customizations.

When to Use JasperReports vs. Print Formats

Use print formats when:

• You need a quick report with minimal formatting requirements
• You want non-technical users to be able to create and modify reports
• The report is a simple list or form based on a single table or view

Use JasperReports when:

• You need precise visual control over the output (pixel-perfect layout)
• The report includes charts, barcodes, or complex visual elements
• You need sub-reports (e.g., an invoice with line items as a sub-report)
• The report will be sent to customers or external stakeholders and must look professional
• You need cross-tab (pivot) reports or complex conditional formatting

Built-In Financial Reports

iDempiere ships with several essential financial reports that draw data from the Fact_Acct table (the general ledger). These are accessible from the Performance Analysis or Financial Reports menu:

Balance Sheet

Presents the organization’s financial position at a point in time, showing Assets, Liabilities, and Equity. The report is driven by the Financial Report configuration, which defines row sets (account groups) and column sets (period definitions). The Balance Sheet uses accounts marked with the Account Type of Asset (A), Liability (L), and Owner’s Equity (O).

Profit and Loss Statement (Income Statement)

Shows revenue, expenses, and net income over a period. It uses Revenue (R) and Expense (E) account types. The P&L report helps you understand whether the business is profitable and where the money is going.

Trial Balance

Lists all accounts with their debit and credit balances for a given period. The total debits must equal total credits (hence “trial”). This report is a fundamental auditing tool used to verify that the books are in balance before producing the Balance Sheet and P&L.

Aging Report

Analyzes outstanding receivables or payables by age — how long invoices have been unpaid. Typical aging buckets are Current, 1-30 days, 31-60 days, 61-90 days, and over 90 days. This report is critical for cash flow management and collections prioritization.

Other Standard Reports

Additional built-in reports include:

• General Ledger: Detailed journal entry listing for specific accounts or periods.
• Transaction Detail: Shows every transaction for a selected business partner, product, or account.
• Open Items: Lists all unpaid invoices and unallocated payments.
• Inventory Valuation: Shows the current value of inventory by product, warehouse, or category.

Export Formats

Reports in iDempiere can be exported in multiple formats to suit different consumption needs:

• PDF: The standard format for distributable, print-ready reports. PDFs preserve exact formatting and are universally readable. This is the default output format for JasperReports and the most common format for emailing documents to customers and vendors.
• Excel (XLS/XLSX): Exports report data into a spreadsheet format. This is invaluable when users need to perform additional analysis, create pivot tables, or manipulate the data. Print format reports export cleanly to Excel with column headers intact.
• HTML: The default on-screen viewing format. HTML reports support drill-down navigation and are ideal for interactive use within the iDempiere web interface.
• CSV: A plain-text, comma-separated format suitable for data exchange with external systems, bulk data analysis, or import into other applications.
• XML: Available for JasperReports, providing structured data output for system integrations.

The export format can typically be selected at the time of report generation. For print formats, the Report button in the toolbar generates HTML by default, with export options available from the report viewer. For JasperReports, the output format may be configured in the process definition or selected by the user at runtime.

Report Parameters and Filtering

Effective reports allow users to focus on the data they need through parameters and filters:

• Process Parameters: Reports configured as processes can define input parameters — date ranges, business partner selections, product category filters, document status filters, and more. These parameters are presented to the user in a dialog before the report runs and are passed to the report query to filter results.
• Print Format Filters: When viewing a print format report, users can apply column-level filters directly in the report viewer. Click on a column header to sort, or use the filter row to restrict results (e.g., show only products where Category = “Finished Goods”).
• Smart Browse Reports: iDempiere also offers Smart Browse, a tool for creating interactive, filterable reports with selection capabilities. Smart Browses combine a report view with user-definable filter criteria and can even trigger batch processing on selected records.

Scheduling Reports

For reports that need to run on a recurring basis, iDempiere provides a scheduling mechanism through the Scheduler window (AD_Scheduler):

• Schedule Definition: Specify the frequency — daily, weekly, monthly, or a custom cron expression.
• Process Assignment: Select the report process to run.
• Parameter Values: Pre-define the parameter values for the scheduled run (e.g., always run for “last month” using relative date expressions).
• Notification: Configure email recipients who should receive the report output automatically. The report is generated as a PDF or Excel attachment and emailed on schedule.

Scheduling is particularly useful for daily sales summaries, weekly inventory status reports, monthly financial statements, and aging reports sent to the collections team every Monday. By automating report delivery, you ensure that stakeholders always have up-to-date information without manual effort.

Key Takeaways

• iDempiere offers three reporting mechanisms: Print Formats (configuration-driven, built-in), Report Views (data aggregation layer), and JasperReports (advanced, design-rich external templates).
• Print formats are the easiest to create and modify — ideal for simple lists and standard documents. They are defined through the AD_PrintFormat and AD_PrintFormatItem tables.
• Report views join multiple tables into a single data source, enabling more complex reports while still using the print format rendering engine.
• JasperReports provide pixel-perfect output with charts, sub-reports, and advanced formatting — best for customer-facing documents and executive reports.
• Built-in financial reports (Balance Sheet, P&L, Trial Balance, Aging) draw from the Fact_Acct table and are essential for financial management.
• Reports can be exported to PDF, Excel, HTML, CSV, and XML to support different use cases from printing to data analysis.
• The Scheduler automates recurring reports, delivering them by email on a defined schedule.
• Drill-down capability transforms on-screen reports into interactive navigation tools, linking summary data to underlying transactions.

What’s Next

In Lesson 12, you will learn about User Roles and Security — how to configure role-based access control so that each user sees only the windows, processes, and data they are authorized to access. Security is the final foundational topic before you move on to intermediate iDempiere operations.