Report View
In iDempiere you can define your own views for given data tables via configuration in the Application Dictionary. This happens in the window Report View (Window ID-180).
What is the Report View for? The greatest flexibility is offered (of course) to create a native view directly in the database. The method presented here using a report view, however, helps in many cases, to compile data for various analyzes more closely and adds to the flexibility of native views. The greatest strength really lies in the combination of both methods.
This article is written by Thomas Bayen . My articles are basically never "done", but always an invitation to improve them. I invite everyone to change anything for improvement. Those who wish can also contact me.
Which procedure for which kind of data?
To get a comfortable and flexible system for statistics and reports, I recommend to work in several steps.
Step 1: tables
The actual data in iDempiere is written to normal data tables. These are normalized, meaning that there is a lot of information linked by references. We obtain on the one hand a very concise data structure, but unfortunately this leads to some interesting data fields missing in the base tables shown by iDempiere. So if a business partner contains a reference to the business partner group, one can not output easy the name or other fields of this group in a report.
Step 2: native database view
You can set up a native database view using SQL "JOIN" clauses in the PostgreSQL database to merge data from different tables. Such a view produces a flat version of the database and forms our formerly structured data into a single table, which then has a lot of columns. This table gets its own table definition in the Application Dictionary of iDempiere, which allows us to access this data.
Step 3: narrowing by report view
The here presented functionality of the "Report View" now serves to narrow the existing data. In such a "report view", only exactly those columns are selected, which we really want to use in our report. In addition, the report view allows to narrow down the data by means of a SQL WHERE clause, as well as to compress with a GROUP Clause (and corresponding aggregate functions).
The advantage of this approach is that these narrowing commands are sent directly as a SELECT statement to the database. Thus, the database optimizer can (and will) take the opportunity to do a performant select on the database and has much less data to work on and transmit. (The PostgreSQL optimizer works pretty well.) This saves a lot of time in the database, in the transmission to iDempiere and especially in the persistence layer of iDempiere.
When creating a report, the data collected here is copied into a report table, which then represents the displayed report. This report table is then the object on which the report generator is used eg. with the print format.
Step 4: output by a print format (or JasperReports)
Now you can specify in the print format again, to not print or group specific rows and/or columns. A grouping here (in contrast to gruping earlier in the report-view) has the advantage that the summary of the data can be turned on and off flexible and the disadvantage that the whole big datatable is provided first, which can be very slow. So you can trade the optimized data access to the database against the flexibility of the display.
Of course you can also use a Jasper Report a print format. Here, however, applies basically the same thing. A properly implemented Jasper Report in iDempiere will also use the report prepared table, and thus subject to the same laws as an internal report. (Anyone who uses in his Jasper Report the possibility to direct access to all tables with SQL may find arguments for special ways here.)
How is this done?
Print Format Window
Basically a report view can be specified when creating a print format. Even independent reporting processes (for example, be called from the menu) which allow the specification of a report view allow that. You can not use the print format creation from inside a window but you have to create ith through the window [[1]] to set a report view.
Report View Window
If you open Report View (Window ID-180), you should first set a senseful name. This can should begin with an own prefix in order to find one's views more easily later. Also you can speciify the entity type accordingly (or "User Maintained", if you like). As table you should now specify the base table to be "slimmed down" by this view. In this "table" field, you can choose - of course - both a "real" table as well as a database view. Both are established almost equal in iDempiere.
WHERE clause
The first - and most important - constraint is now specifying a WHERE clause. Here the data displayed may be restricted line by line. At this point also environment variables can be used, so that e.g. you can view only those customers which belong to the currently logged in user.
Report rows
Below there is now a detailed register "Report View Rows". This register can be completely 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 formula. 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 JavaEngine.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 culumns of the underlying table). (It is eventually possible to reuse an existing but unused column for such a purpose.)
Grouping
Grouping is the to the combination of several lines to one. There are some special remarks for that. 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.
Example
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.
That's it. :-)
Example Screenshot showing how to set the checkmarks for grouping
Thanks to Carlos Ruiz for this Screenshot:
