In iDempiere you can define your own reports based on given database tables by configuring Report Views in the Application Dictionary using Report View (Window ID-180).

This article was originally written by Thomas Bayen. He invites you to change anything for improvement and you can also contact him.

Introduction

The raw data in iDempiere is stored in data tables in the usual normalized format used by relational databases, meaning that the data is dis-aggregated into many tables, linked by references. This makes for a very concise and flexible data structure, but it means that the raw data in the base tables must be reassembled for reporting purposes. So, for example, if you want to include a reference to the Business Partner Group in a report about Business Partners, you must create a database join between the two tables.

You can do this in one of two ways:

Create a "Report View" of the data in a database table which uses a SQL SELECT statement that is sent directly to the database for execution. You can narrow the data selection by selecting specific fields with relevant JOIN and WHERE clauses, as well as GROUP BY and aggregate functions. The database server will use its optimizer to do a performant select on the database, reducing the data load. The Jaspersoft report generator then uses the resulting dataset to produce the report.
Alternatively, you can create a more complex database view in the iDempiere schema with the standard SQL statement CREATE VIEW AS using your favourite SQL tool (e.g. psql or phpPgAdmin for PostgreSQL, and SQLPlus or Oracle SQL Developer for Oracle). The view should contain all the fields and joins to other tables that you need for the report. Give the name of the view a distinctive prefix so that you easily find it amongst all the other views. You then create a definition of the view in the iDempiere Application Dictionary to get access to this data.

See this video from Chuck Boecking that explains how Menus, Reports, Report views, and Print Formats are related to each other.

Creating reports

Using a Report View

You create a Report View as follows:

Log in as System/System with the System Administrator role;
Find and open the Report View Window using the Menu search field and create a new record with the following details:
- Name: give the Report View a sensible name with a prefix to distinguish it as your own;
- Description: describe the use of the Report View;
- Entity Type: User maintained;
- Table: the name of the database table that is the source of the data;
- Sql WHERE: fully qualified SQL WHERE clause for record selection (fully qualified means "tablename.columnname"). The WHERE clause will filter the records to restrict the selection to the criteria you specify. Environment variables can be used here, for example, to select only those customers which belong to the currently logged in user.
- Sql ORDER BY: fully qualified ORDER BY clause to sort the records in the right order.
Now find and open the Report & Process Window using the Menu search field and create a new record with the following details:
- Name: a name that is consistent with the Report View;
- Description: describe the process;
- Help: helpful tip for the user;
- Entity Type: User Maintained;
- Data Access Level: here you can specify whether the report is used globally, or system-only, or restricted to a specific client and/or organization;
- Report: make sure this box is checked;
- Report View: select the Report View that was created above.
Save the Report Process;
In the Tabs at the bottom of the Window, create Parameters for the report if required, and use the Report Access Tab to restrict the Report Process to specific Roles.

You can then create a Menu item to run the report:

Open the Menu Window and create a new Menu record:
- Name: name of the menu item;
- Action: Report;
- Process: name of the report

By default, the new Menu entry will be added to the bottom of the hierarchy of menu items on the left of the Menu Window, so drag it up the list to wherever it rightfully belongs.

Use the Print_Format_(Window_ID-240) to format the report.

Using a database view

A Report View represents one database table at a time. If you want to include data from more than one table, for example, a list of Invoices with the contact details of the Business Partner from another table, you must create a database view first. You can create a view of any complexity using standard SQL functionality, including GROUP BY, Aggregate Functions, and Window Functions, using all the power of PostgreSQL or Oracle to build rich management information reports.

Using your favourite SQL tool, create a new database view, for example:

 CREATE VIEW adempiere.my_new_view 
 AS 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;

Add the view to the Application Dictionary:
- Log in as System/System
- Select the Table and Column Window
- Create a new record for the newly created view, with name and description, and Entity Type 'User Maintained' so that it does not get overwritten by a system upgrade
- Save it
- Click on the Process gear-wheel and select Create Columns from DB

This will add the columns defined by the SELECT statement of the new view to the Column Tab at the bottom of the screen.

Now continue as described above to create a Report View for this more complex query.

Report View Columns

When you create a Report View, iDempiere includes a Tab Report View Columns at the bottom where you can define any columns which will be overridden in the generation of the SQL SELECT statement. This can be left empty, which means that all rows are read in the underlying table for the report. However, if you enter one line here, all lines used must be specified explicitly.

Formulas

In each row you have to enter a Function Column. Within this formula, you set the wildcard "@" for the column value itself. If your intention is only to limit the number of loaded columns, it is enough to use these anywhere. Here you can also specify SQL formulas and do some calculations with the @-value, etc. There must be (as the code is implemented at the moment in DataEngine.java), exactly one '@' in the formula, which then will be replaced by the column name.

Unfortunately, one can not add additional columns (eg a sum of two other columns) in this way because each entry has to be defined with one of the given column-definitions (the selection box contains the columns of the underlying table). It is possible to reuse an existing but unused column for such a purpose but you have to include exactly one '@' into the formula. This allows for example a formula in column AmtAcctDr as 'FactAcct.AmtAcctCr+@' that gives as result the value of Dr+Cr. As an alternative you should be able to use virtual columns (SQL formula fields) in the table definition for these kind of calculations.

Grouping

Grouping is the combination of several lines into one. There are some special remarks about this. Firstly, you have to specify the columns by which you want to group. As a column function you use here also the easiest possible value: "@". The columns whose values you now want to aggregate within the group get a function in the formula field. Here you can use SQL aggregate functions. For example, to calculate a sum, the formula is "SUM (@)". These aggregated fields have to have a check mark in the "grouping function" field. It is important that you set these checkmarks at all fields that contain aggregate functions and do not set at all fields that contain fields by which you want to group.

For grouped fields you have to make sure that the view is ordered by the fields which are used for grouping. You should also sort the print format for the same value to avoid error messages.

As an example I would like to summarize from the the accounting data the sales of one customer:

As a base table I take "RV_Fact_Acct". This is a natural view ("RV_" means "Report View") that combines the Table of the accounting details with some other (referenced) tables.
Using the WHERE field I limit the data on the lines that book on my liabilities account (I compare this with the account number in the "account value").
I create two rows for the customer number and customer name (both columns in RV_Fact_Acct) and set the checkmark for the grouping function off. As a formula I use each "@".
Then I create a third column with the formula "SUM (@)". There, I set the checkmark for a grouping function.
Now I have to make sure I sort by the first two columns even when creating the print format.

Example of checkmarks

Thanks to Carlos Ruiz for this Screenshot showing how to set the checkmarks for grouping:

tip:  use TRUNC(@,'monthly’) if you want group data based on date column monthtly basis ! tested by @norbertbede at 20 nov. 2022 v10

References

Original Compiere guide to creating custom reports
https://idempiere.atlassian.net/browse/IDEMPIERE-1490: a JIRA issue that resulted in an update to the documentation about grouping

TBayen (talk) 20:39, 19 December 2013 (CET)