Creating a report in the system involves two dependent elements: visual presentation template and description of the data extracted for the report. The template is created in XLS(X), DOC(X), ODT, HTML formats using external tools, and the description of report data is created in the report designer screen.

Depending on the template and report parameters, the generated report can be in PDF, XLS(X), DOC(X) or HTML.

The report data structure can either be described in the report designer by creating bands, queries and other elements, or programmed in a Java class that implements a special interface. The report can take parameters from the user or from the calling code. You can specify users who will have access to the report, and system screens where it should appear.

The main components of the report generator are shown in the following diagram:

=== Report Data Structure

The Report structure tab of the report editor is described below:

The top part contains fields to enter general report properties:

The main element of the report data structure is the band hierarchy - Report bands.

A report band has the following parameters:

Each band includes one or more datasets. At the moment when a report is run, datasets are transformed into lists of rows, where each row contains a map of name-value pairs. A band appears in the report as many times as there are rows in its longest dataset. Field names are specified in the report template and are replaced with corresponding values from the dataset when the report is produced. When describing datasets, you can use external parameters of the report as well as fields from other bands - this allows creating linked bands.

Each report has the Root band. You can create datasets in it and refer to their fields from other bands, but you cannot use the Root band in the report template.

The Dataset name column value is used for user convenience only.

Supported dataset types are provided below:

=== Report Template

You can create several templates for a single report in the Templates tab of the report editor. One of them must be selected as the default template in the Report structure tab.

Below is the form to add a template:

==== XLSX and XLS Templates

You can create XLSX and XLS templates using Microsoft Office or OpenOffice / LibreOffice.

Each report band must have a corresponding region in the template, which is named as the band. To create named regions, select the desired cell ranges and enter the name in the field in the application top left corner. To edit existing named regions, use the Formulas → Name Manager menu command in Microsoft Office, and the Insert → Names → Manage command in OpenOffice.

The bands are output in the order specified in the report structure.

Horizontal bands may contain sub-bands. Therefore, for sub-bands, you need to create named regions directly under the regions that correspond to the parent bands.

The band dataset fields are placed in the template using strings having ${field_name} format, where field_name is the corresponding band field name. For example:

Cells may contain formatting as well as multiple fields inside. To output images or formulas, you need to place them entirely into the corresponding named region.

==== DOCX, DOC and ODT Templates

You can create an ODT template using OpenOffice / LibreOffice. In order to create DOC and DOCX templates, you can use OpenOffice or Microsoft Office.

A template of these types can include document text and optionally one or more tables. The document text outputs the data from the first rows of arbitrary bands. In a table, you can output an arbitrary number of band rows.

To place a field in the document text, you should use a string having ${band_name.field_name} format, where band_name is the band name, field_name - name of a band’s field.

In order to output data into a table, you should tie it to a band. This is done by specifying ##band=band_name in the table’s first cell, where band_name is the band name. The table fields are placed using ${field_name} format, where field_name is the field name of the band associated with the table. You can use band name prefix to access fields of other bands, same as in the document text fields. You can output several fields in a single table cell.

Below is an example of a template which outputs a report consisting of two bands, Book and Authors. The first band outputs a book name and genre, and the second outputs a list of authors of this book.

==== HTML Template

An HTML template is defined in an .html file (UTF-8 encoding without BOM). FreeMarker tags are used to place data (FreeMarker documentation is available at freemarker.org/docs).

The FreeMarker document model has the following structure:

Band {
      bands [ bandName : [ band, .. ], .. ]
      fields [ fieldName : fieldValue, .. ]
}

For example, you should use the following expression to access the name field in a row having index 0 of the band band:

Root.bands.band[0].fields.name

You may variables for convenience, e.g.:

<#assign headerRow = Root.bands.Header[0]>
<p>Date: ${headerRow.fields.reportDate}</p>

Below is an example of a template which outputs a report consisting of two bands, Book and Authors. The first band outputs a book name and genre, and the second outputs a list of authors of this book.

<!doctype html>
<html>
<head></head>
<body>
   <#assign book = Root.bands.Book[0] />
    <#assign authors = Root.bands.Authors />

    <p>Name: ${book.fields.name}</p>
    <p>Genre: ${book.fields.literatureType.name}</p>
    <table border="1" cellpadding="5" cellspacing="0" width="200">
        <thead>
            <tr>
                <td>First name</td>
                <td>Last name</td>
            </tr>
        </thead>
        <tbody>
        <#list authors as author>
            <tr>
                <td>${author.fields.firstName}</td>
                <td>${author.fields.lastName}</td>
            </tr>
        </#list>
        </tbody>
    </table>
</body>
</html>

Below is a more complex example. Let us assume we have the following bands structure:

Root {
    HeaderBand {
        query = return [[ "name" : "Column1" ],[ "name" : "Column2" ]]
    }
    Band1 {
        query = return [
            ["field1" : "Value 11", "field2" : "Value 12"],
            ["field1" : "Value 21" , "field2" : "Value 22"]
        ]
    }
    Band2 {
        query = return [[ "header" : "Header1" ], [ "header" : "Header2" ]]
        SubBand1 {
            query = return [["header" : 'SubHeader1'] , [ "header" : 'SubHeader2' ]]
        }
    }
}

<!doctype html>
<html>
    <head>
        <title> Simple template </title>
    </head>
    <body>
        <#assign Tree1 = Root.bands.Band2>
        <h1> Header </h1>
        <p>
            ${Tree1[1].bands.SubBand1[0].fields.header}
        </p>
    </body>
</html>

<!doctype html>
<html>
    <head>
        <title> List </title>
    </head>
    <body>
        <#assign Table1Header = Root.bands.HeaderBand>

        <#if Table1Header?has_content>
        <ol>
            <#list Table1Header as header>
            <li> ${header.fields.name} </li>
            </#list>
        </ol>
        </#if>
    </body>
</html>

<!doctype html>
<html>
    <head>
        <title> Table </title>
    </head>
    <body>
        <#assign Table1Header = Root.bands.HeaderBand>
        <#assign Table1 = Root.bands.Band1>
        <table border="1" cellpadding="5" cellspacing="0" width="200">
            <thead>
                <tr>
                <#list Table1Header as header>
                    <td> ${header.fields.name} </td>
                </#list>
                </tr>
            </thead>
            <tbody>
            <#list Table1 as row>
                <tr>
                    <td>
                        ${row.fields.field1}
                    </td>
                    <td>
                        ${row.fields.field2}
                    </td>
                </tr>
            </#list>
            </tbody>
        </table>
    </body>
</html>

<!doctype html>
<html>
    <head>
        <title> Multi-level list </title>
    </head>
    <body>
        <#assign Tree1 = Root.bands.Band2>
        <ul>
            <#list Tree1 as item>
            <li>
                <h2> ${item.fields.header} </h2>
                <#if item.bands.SubBand1?has_content>
                <ul>
                    <#list item.bands.SubBand1 as subitem>
                    <li>
                        <h3> ${subitem.fields.header} </h3>
                    </li>
                    </#list>
                </ul>
                </#if>
            </li>
            </#list>
        </ul>
    </body>
</html>

===== Converting HTML to PDF

Reports which have a template in HTML format and the PDF output format do not always properly display fonts. To resolve this, add cuba/fonts subdirectory with required .ttf fonts to the Middleware configuration directory (tomcat/conf/app-core in default deployment configuration). Additionally, you can use existing operating system fonts by specifying their path in the cuba.reporting.fontsDir application property.

In order to resolve the fonts issue on a Ubuntu server, you should do the following:

<html>
<head>
    <style type="text/css">
        * {
            font-family: Times New Roman;
        }
    </style>

==== Class-Defined Template

Class-defined templates are used when it is too difficult or impossible to select data using SQL, JPQL or Groovy. They are used, for example, when the report is a result of combining several other reports.

The class defining the template must implement the com.haulmont.yarg.formatters.CustomReport interface. In the class, you need to define the createReport() method, which returns an array of bytes and takes the following input parameters:

Below is an example of a simple class-defined template. It creates an HTML document showing the name of a book selected as report parameter:

package com.sample.library.report;

import com.haulmont.yarg.formatters.CustomReport;
import com.haulmont.yarg.structure.BandData;
import com.haulmont.yarg.structure.Report;
import com.sample.library.entity.Book;
import java.util.Map;

public class BookReport implements CustomReport {
    @Override
    public byte[] createReport(Report report, BandData rootBand, Map<String, Object> params) {
        Book book = (Book) params.get("book");
        String html = "<html><body>";
        html += "<p>Name: " + book.getName() + "</p>";
        html += "</body></html>";
        return html.getBytes();
    }
}

==== Chart Template

Chart output type is available if the application project depends on the charts base project. Resulting chart is displayed in the Reports → Show Charts screen of your web application.

Two types of diagrams are supported: Pie chart and Serial chart. Each type has its own set of parameters.

Pie chart:

Serial chart:

At least one row definition must be added for the serial chart:

==== Output format compliance matrix

¹ - OpenOffice must be installed for output.

² - depending on the cuba.reporting.openoffice.docx.useOfficeForPdfConversion application property, the output can be performed with or without OpenOffice. In the latter case, you need to provide required fonts, as described in Chapter 1, Creating Reports.

=== External Report Parameters

External parameters are passed from the outside when running a report and can be used as conditions in datasets. All external parameters become fields for each report band, so you can use them directly in the template as dataset fields. If any dataset contains field with the same name, it overrides the external parameter in the corresponding band and the report uses the dataset field value.

You can describe the external parameters in the Parameters and Formats tab of the report editor. The form for adding parameters is provided below:

Properties tab:

In the Localization tab, you can define the parameter name for different locales. In order to do this, you should enter the locale_name = parameter_name pairs, for example:

ru = Книга

=== Field Value Formats

You can specify the formatting for any field output by the report in the Parameters and Formats tab of the report editor. Below is the form to add a format:

With the help of formats, it is possible to insert images and HTML blocks into the document.

=== Report Access Rights

You can define user rights to access the report as well as report availability in certain application screens in the Roles and Screens tab of the report editor.

If the report roles list contains at least one role, the report will be available only to users with this role. If no roles are specified, the report is available to everyone.

The screens list allows to specify, which screens the report will be available in when invoking RunReportAction, TablePrintFormAction or EditorPrintFormAction actions. If no screen is specified, the report will be available from any screen.

=== Report Name Localization

You can localize the report name - show the name in the language, which the user is logged in with, in the report list. In order to do this, you need to go to the Localization tab and enter pairs, locale_name = report_name, as separate lines in the text field, for example:

en = Books by author
ru = Книги по автору

This section describes how to run created reports.

=== Running from the Generic Reports Browser

The easiest way to run reports is from the generic browser, which is available in the Reports -> Run Reports screen. The user must have the right to access this screen. The list will contain all reports that are available to the user in accordance with his role. If the report has external parameters, they will be requested in a special form when running the report.

=== Running Reports from Screens

You can run reports from arbitrary screens using special actions and associated buttons or component context menu items. In such case report availability in this screen will also be checked in addition to user role.

Action types and examples of their use are provided below.

In this chapter we will consider the structure of one of reports from the sample Library application, which source code is available at www.cuba-platform.com/download.

Open the Reports → Reports screen and click on the Import button to import the report. Choose Reports.zip in the project root directory. Two reports will appear in the table, one of them will be Books by author. This report displays the list of book publications for selected author; books will be grouped by book name and publisher. The output format is XLS.

You can run report from the generic browser in the Reports → Run Reports screen.

Reports wizard is a visual tool for quick report creation including both data structure and template design. Click Create -> Using wizard in the Reports screen to run the wizard.

Three types of reports can be created using the wizard:

Report design is a three steps process:

The created report can be modified in the usual manner using the report editor and run through the generic report browser, or using TablePrintFormAction and EditorPrintFormAction actions.

The use cases below illustrate how to use the wizard. All the examples are based on the sample Library application which is available in the CUBA Studio samples. The Library application or any other sample CUBA application can be downloaded by clicking the Samples button in the project selection window.

=== Report for a Single Entity

Let us assume that we want to get an information about the particular publication of a book, i.e. an instance of library$BookPublication entity.

First, run the reports wizard and specify the report details:

Next, specify the report type: Report for single entity.

Then click Next button; the Select attributes for the simple report region window appears. Specify the attributes of the BookPublication entity and linked entities that should be reflected in the report (Publication.Book.Name, Publication.Publisher.Name, Publication.Year and Publication.City.Name). In order to do this, select them in the left column and move them to the right clicking or by double-click.

The order of the attributes in the report will correspond the order specified in the right hand side list. To change the display order, move the attributes up and down by clicking /.

Click ОК to move to the second stage - report regions editing.

The appeared screen contains a list of named regions - bands that display related data. The wizard enables adding several plain-text regions to the template in order to display different data sets.

A set of entity attributes loaded to a particular region can be modified by clicking the link represented as the list of the selected attributes. You can also add a new region by clicking Add simple region.

If the entity contains collection attributes, Add tabulated region, button will appear. It enables adding a region for tabular data display.

In both cases, the selection dialog will appear with a list of attributes of the library$BookPublication entity, allowing you to add or remove attributes from the set.

At this stage, we already can run the report and see how the report looks. Click Run button, pick the instance of library$BookPublication and see the result..

When all report regions are configured you can move on to the third stage: saving the report. At this point, you can view the complete report template, or change the name and format of the output file to any of three available types: DOCX, HTML, PDF.

After clicking the Save button, the standard report editor comes up. Now you can fine-tune the report in the usual manner. Once editing is complete, click Save and close in the report editor.

The report is now added to the General reports group in the reports browser, where you can run it from by clicking the Run buttons.

Additionally, we can enable the report run on the publications browser. In order to do this, we want to add the bookpublication-browse.xml button to the Print details screen descriptor:

<groupTable id="bookPublicationTable"
    ...
    <buttonsPanel>
        ...
        <button id="printDetails"
        caption="msg://printDetails"/>

Then we need to implement TablePrintFormAction in the controller:

@Inject
private Button printDetails;

@Override
public void init(Map<String, Object> params) {
    TablePrintFormAction action = new TablePrintFormAction("report", this, bookPublicationTable);
          bookPublicationTable.addAction(action);
          printDetails.setAction(action);
    }

Now you can run the report for any publication by selecting it in the table and simply pressing the Print details button.

The output is as follows:

=== Report for a List of Entities

Reports wizard allows you to create two types of reports for a list of entity instances:

Let us have a look at the first report type. Let us assume we want to obtain a list of all book instances in the library (library$BookInstance entity) with titles and library departments they belong to.

At the first stage, we specify the report details:

After that, select the type of the report (Report for list of entities) and click Next.

As per the task, we select BookItem.Publication.Book.Name`and `BookItem.LibraryDepartment.Name in the attributes selection window.

Click ОК and move to the second stage of report regions editing.

The report template for a list of entities restricted to have only one region that displays data in tabular form. Adding new regions is not allowed, but you can edit an existing set of data by clicking on the link with the list of attributes, or remove an existing region and recreate it.

In this case, we do not need to make any changes. Click Next -> Save to save the report. The report looks as follows in the report editor:

Once the report is saved, you can run it from the reports browser.

In addition, we can add a button to run the report from the book items browser, which can be opened by clicking the Show items button in the publications browser. In order to do this, we will set the multiselect attribute for the book instances table to true to be able to specify a set of records for the report and then add the source code of the button:

      <table id="bookInstanceTable"
             multiselect="true">
             ...
                  <buttonsPanel>
                  ...
                      <button id="printList"
                      caption="msg://printList"/>

After that, inject the Button component in the screen controller:

@Inject
private Button printList;

Next, add the following implementation within the overridden init() method:

TablePrintFormAction action = new TablePrintFormAction("report", this, bookInstanceTable);
    bookInstanceTable.addAction(action);
    printList.setAction(action);

Now the report can be run from the book items browser by selecting items for the report in the table and pressing the Print list button. Print selected option exports the selected items, Print all option prints all instances selected by the current filter.

The output is as follows:

=== Report for a List of Entities Filtered by Query

Now let us have a look at the second Chapter 4, Reports Wizard for a list of entities filtered by query. To demonstrate the use case of this report type use we will being complications to our task. As before the report should contain a list of books (with their titles and department names), but only added after a certain date.

Let us set the details of the report like in the previous case:

Then select the Report for list of entities, selected by query report type.

The selected report type enables us to select the list of entities that match the specified conditions. In order to set the query, click Set query link below.

The Define query window appears. As you can see the window is similar to the generic filter window. Here you specify conditions, combine them into AND/OR groups and configure their settings.

In order to add new query conditions, click Add. Select Created at attribute in the appeared window. Now the attribute is added to the query conditions tree and the right hand side panel displays its properties. Select an operator (>=).

After saving the query, click Next and move to library$BookInstance attributes selection. We move BookItem.Publication.Book.Name and BookItem.LibraryDepartment.Name. After saving the query, click Next and move to library$BookInstance attributes selection. We move BookItem.Publication.Book.Name and BookItem.LibraryDepartment.Name attributes to the right. Click OK to move on to accomplish the first stage.

Press Next -> Save to save the report. The report will look as follows:

The editor allows making the report structure more sophisticated by adding new bands and data sets, as well as configuring the report template design, localization, access rights.

For instance, we can switch to Parameters and Values tab and modify the query parameter in the Parameters list: Date instead of the standard CreateTs1.

After all, let us add the Report button that runs the report right from the library departments browser.

In order to do this, we need to define a button in the librarydepartment-browse.xml screen descriptor:

<table id="libraryDepartmentTable"
    ...
    <buttonsPanel id="buttonsPanel">
        ...
        <button id="reportBtn"
         caption="msg://reportBtn"/>
     </buttonsPanel>
</table>

After that, inject the button in the screen controller:

@Inject
private Button reportBtn;

and assign RunReportAction to the button in the overridden init() method:

reportBtn.setAction(new RunReportAction("report", this));

The Report button will appear in the library departments browser, displaying the list of all reports available in the system in one click. In order to run the report, select Recently added book items in the list, specify the date and click Run report.

The output is as follows:

cuba.reporting.openoffice.path = C:/Program Files (x86)/OpenOffice.org 3/program

cuba.reporting.openoffice.path = /usr/lib/libreoffice/program

cuba.reporting.displayDeviceUnavailable = true