CUBA Platform. Report Generator

Preface

This document provides guidance for creating reports in applications built on the CUBA platform.

Target Audience

This manual is intended for developers and administrators of CUBA applications. In order to work successfully with the report generator, you should understand relational database principles and be able to write SQL queries. Familiarizing yourself with JPQL and Groovy is also useful because in some cases it is easier to extract report data using these languages.

Additional Materials

This Manual and the accompanying documentation related to the CUBA platform can be found at https://www.cuba-platform.com/documentation.

The core of the report generator is the YARG framework, which is distributed under the free Apache 2.0 license. See the article in our blog for more details. The framework documentation is available at https://github.com/cuba-platform/yarg/wiki.

Feedback

If you have any suggestions for improving this manual, feel free to report issues in the source repository on GitHub. If you see a spelling or wording mistake, a bug or inconsistency, don’t hesitate to fork the repo and fix it. Thank you!

1. Overview

The Reporting add-on is designed to simplify the report generation in CUBA applications. It enables creating report templates in most popular editors like Microsoft Office and LibreOffice/OpenOffice and defining data sources at runtime, using CUBA data model, SQL, JPQL or scripts.

The add-on supports:

Building report templates visually at runtime with a step-by-step wizard;
Generating reports in DOC, DOCX, ODT, XLS, XLSX, HTML or in arbitrary text formats;
Creating complex XLS(X) reports: multi-tiered reports, reports with data aggregation, crosstab reports;
Using diagrams and formulas in XLS(X) reports;
Converting reports in office formats or HTML to PDF.

2. Installation

To install the add-on in your project follow the instruction below.

Double-click Add-ons in the CUBA project tree.
Select Marketplace tab and find Reporting add-on.
Click Install button and then Apply & Close.
Click Continue in the dialog.

The add-on corresponding to the used platform version will be installed.

3. Quick Start

This chapter provides the hands-on examples of using the Report generator. Most of the examples are based on the sample Library application, which source code is available at GitHub.

Learn also Introduction to Reporting and Document generation guide, which shows how to embed the reporting capabilities into the application.

3.1. Project Setup

Download and unzip the source repository of the Library application, or clone it using git:
```
  git clone https://github.com/cuba-platform/sample-library-cuba7
```
Open the Library project as described in the Opening an Existing Project section of the CUBA Studio User Guide.
Add the Reporting addon to your project via CUBA Add-Ons window as described in the Installation section.
Create the database on the local HyperSQL server: go CUBA → Create database in the main menu.
Run the application by clicking the button next to the selected CUBA Application configuration in the main toolbar.
Click the link in the Runs at… section of the CUBA project tree.
Log in to the Library application with username and password admin / admin.

For an easy start, the report generator comes with the Reports wizard – a visual tool for quick report creation, including both data structure and template design. Having created a report with the wizard, you can then decompose it and learn how the datasets are created, explore the report parameters, modify the template or the output type, following the further sections of this manual.

To run the wizard, click Create → Using wizard in the Reports screen.

Figure 1. Call the Report wizard

Three types of reports can be created using the wizard:

Report for a single entity.
Report for a list of given entities.
Report for a list of entities filtered by query.

Report design is a three steps process:

Creating a data structure of a report.
Editing of report regions.
Saving the report.

The created report can be modified in the usual manner using the report editor and run through the generic report browser or using special actions (ListPrintFormAction, EditorPrintFormAction, etc.).

3.2. Report for a Single Entity

Let us assume that we want to get 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:

Entity – entity to be shown in the report – library$BookPublication.
Template type – report template format that defines the format of the output – DOCX. Note that XSLX, HTML, CSV and Chart formats are also available.
Report name – Publication details.

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

Figure 2. Report for a Single Entity: step 1

Then click the 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 attributes_selection_arrow or by double-click.

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

Figure 3. Report for a Single Entity: select Entity Attributes

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, the 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.

Figure 4. Report for a Single Entity: step 2

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

Figure 5. Test Run

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 one of the available types.

Figure 6. Report output type

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 button.

Figure 7. Reports browser

Additionally, we can enable the report run on the publications browser. In order to do this, we will declare a standard ListPrintFormAction in the bookpublication-browse.xml screen descriptor:

<groupTable id="bookPublicationsTable"
            width="100%"
            dataContainer="bookPublicationsDc">
    <actions>
        <action id="create" type="create"/>
                <!-- -->
        <action id="list" type="listPrintForm" caption="msg://printDetails"/>
    </actions>
    <columns>
        <!-- -->
    </columns>
    <rowsCount/>
    <buttonsPanel id="buttonsPanel"
                  alwaysVisible="true">
        <button id="createBtn" action="bookPublicationsTable.create"/>
        <!-- -->
        <button id="listBtn" action="bookPublicationsTable.list"/>
    </buttonsPanel>
</groupTable>

Then we should link our report with the BookPublication browser. Open the report editor, switch to the Roles and Screens tab and add the library$BookPublication.browse screen from the dropdown list to the table below:

Figure 8. Add screen

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

Figure 9. Print details

The output is as follows:

Figure 10. Report result

3.3. Report for a List of Entities

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

Report for manually selected instances of a particular entity
Report for entity instances filtered by a certain request.

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:

Entity – report entity – library$BookInstance.
Template type – output format – XSLX.
Report name – report name – Book items location.

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

Figure 11. Report for a List of Entities: step 1

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

Figure 12. Report for a List of Entities: select Entity Attributes

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:

Figure 13. Report data structure

Once the report is saved, you can run it through the generic report 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 declare a standard ListPrintFormAction:

<groupTable id="bookInstancesTable"
            multiselect="true"
                        ...>
    <actions>
        <action id="create" type="create"/>
                <!-- -->
        <action id="list" type="listPrintForm" caption="msg://printList"/>
    <columns>
        <!-- -->
    </columns>
    <rowsCount/>
    <buttonsPanel id="buttonsPanel"
                  alwaysVisible="true">
        <button id="createBtn" action="bookInstancesTable.create"/>
        <!-- -->
        <button id="listBtn" action="bookInstancesTable.list"/>
    </buttonsPanel>
</groupTable>

Then we should link the Book items location report with the book items browser. Open the report editor, switch to the Roles and Screens tab and add the library$BookInstance.browse screen from the screens dropdown to the table below:

Figure 14. Add screen

Now the report can be run from the book items browser. To get the list of items in the report, go first to the Library → Accession Register screen and generate items for one of the books from the drop-down list. Then, go to the Library → Publications screen, select the book for which you’ve generated items before and click the Show items button. In the BookInstances screen select items for the report in the table and click the Print list button. Print selected option exports the selected items, Print all option prints all instances selected by the current filter.

Figure 15. Print selected

The output is as follows:

Figure 16. Report result

3.4. Report for a List of Entities Filtered by Query

Now let’s have a look at the last report type suggested by the wizard: the report for a list of entities filtered by a query. To demonstrate this use case, we will complicate the previous example. As before, the report will 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:

Entity – report entity – library$BookInstance.
Template type – output file format – XSLX.
Report name – Recently added book items.

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

Figure 17. Report for a List of Entities Filtered by Query: step 1

The selected report type allows 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 a new query condition, 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.

In the properties panel, you can set the default parameter value. If the report logic is not supposed to be changed, you can make this attribute hidden by checking the Hidden checkbox. In this case, the user will not be requested to input this parameter when running the report.

Select an operator (>=).

Figure 18. Query Parameter

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.

Figure 19. Select Entity Attributes

Press Next → Save to save the report.

Figure 20. Entity attributes

The report will look as follows:

Figure 21. Report data structure

The editor enables 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 Formats tab and modify the query parameter in the Parameters list: Date instead of the standard CreateTs1. Save the changes and close the report editor.

Figure 22. Parameters and Formats tab

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

In order to do this, we need to define a new RunReportAction standard action and a button for it in the librarydepartment-browse.xml screen descriptor:

<groupTable id="libraryDepartmentsTable"
            width="100%"
            dataContainer="libraryDepartmentsDc">
    <actions>
        <action id="create" type="create"/>
        <action id="edit" type="edit"/>
        <action id="remove" type="remove"/>
        <action id="run" type="runReport"/>
    </actions>
    <!-- -->
    <buttonsPanel id="buttonsPanel"
                  alwaysVisible="true">
        <!-- -->
        <button id="runReportBtn" action="libraryDepartmentsTable.run"/>
    </buttonsPanel>
</groupTable>

For each report, add library$LibraryDepartment.browse to the screens list on the Roles and Screens tab of the report editor, as we did in the previous examples.

The Run 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.

Figure 23. Run report

The output is as follows:

Figure 24. Report result

3.5. Report with Chart Output

Using the report wizard, you can create reports with chart output in the same way as any other report types. The only difference is that you will need to configure the chart template after finishing the report in the wizard.

This example is based on the petclinic application, which source code is available at GitHub.

Add the Charts addon to your project via CUBA Add-Ons window as described in the Studio documentation.
Start creating a report as described in the previous chapters.

Figure 25. Chart Template in Wizard – Step 1
For the chart report, select countable entity attributes – they will be later used for the value axis.

Figure 26. Chart Template in Wizard – Select Attributes
Complete the next step of the report creation.

Figure 27. Chart Template in Wizard – Step 2
At the last step, select the chart type – either Pie or Serial – and save the report.

Figure 28. Chart Template in Wizard – Step 3
Finally, configure the chart axis on the Templates tab of the report editor.

Figure 29. Chart Template Configuration

For more details on charts configuration, see the Charts documentation.

4. Creating Reports

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), 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), CSV, DOC(X), HTML, Chart, Table or Pivot table format.

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:

Figure 30. Report Generator Components

YARG – framework, which is the core of the report generator.
Report Engine integrates YARG into CUBA framework and provides additional functionality such as access rights to reports and integration with screens.
Report Designer is a tool for describing and storing reports. It includes the infrastructure for storing report descriptions and templates, as well as screens to create and manage reports.
Report – report data structure descriptor, including Bands (report bands) and Datasets (datasets which are being output in the bands).
Report Template – report visual presentation template.

4.1. Report Data Structure

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

Figure 31. Report Data Structure

The top part contains fields to enter general report properties:

Name – report name. The name can be localized in the Localization tab.
Group – report group, which is used for grouping in the standard report browser.
Default template – report output template.
System code – optional code, which you may use to identify the report in the application code.

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

A report band has the following parameters:

Band name – unique band name within the report. It must contain only Latin letters, numbers and underscores. Moreover, if the band name starts with header, its data will not be printed in the table output.
Orientation – band orientation: Horizontal, Vertical or Crosstab. Horizontal bands are copied downwards, vertical – to the right, crosstab – to the right and downwards as a matrix. Horizontal bands may contain sub-bands.
Parent band – parent band.

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.

The Link field is used to merge data from multiple datasets inside one band. It can be used when the whole data for the report row could not be received by a single query or a Groovy script.

Supported dataset types are described below.

4.1.1. SQL Dataset

SQL dataset is produced as a result of SQL query execution. It is recommended to use aliases for query result fields with the help of the as operator. It is also recommended to enclose the aliases in double quotes to prevent possible case conversion by the DBMS:

select u.name as "userName", u.login as "userLogin"
from sec_user u

You can use report input parameters and parent bands fields in the query. Parameters should be addressed by name enclosed in ${}, for example, ${dateFrom}. Parent band fields should be addressed similarly, by adding the band name in front of the field name: ${band1.field1}.

Below is an example of an SQL query with a groupId parameter, obtained from the group parent band and an external active parameter:

select u.name as "userName", u.login as "userLogin"
from sec_user u
where u.group_id = ${group.groupId}
    and u.active = ${active}
    and u.delete_ts is null

You should manually include conditions to filter soft deleted records for SQL queries.

By default, SQL queries are executed on the main database. If you want to query an additional data store, set its name in the Data store field.

Query preprocessing in bands

If you need to modify the SQL/JPQL query dynamically depending on the report input parameters or the parameter values from the parent band, you can use the SQL preprocessing. The template engine enables you to modify SQL/JPQL queries using Groovy. In order to activate it, check the Preprocess query as Groovy template checkbox below the band editor. The resulting query will be processed by GStringTemplateEngine that will have access to:

the report parameters: ${<parameter_name>},
values from parent bands: ${<band_name>.<parameter_name>}.

Let’s consider the following example: depending on whether the createTs2 report parameter is passed you need to select one of the query conditions: e.create_ts < ${createTs2} or e.create_ts < current_timestamp.

In this case the query should look like:

select e.create_ts, e.id, e.vin from ref_car e
where
e.create_ts >= \${createTs1}
and
<% out << (createTs2 != null  ? 'e.create_ts < ${createTs2}' : 'e.create_ts < current_timestamp')%>

Thus, if the createTs2 parameter is not passed, the initial query will be transformed into the following resulting query:

select e.create_ts, e.id, e.vin from ref_car e
where
e.create_ts >= \${createTs1}
and
e.create_ts < current_timestamp

If createTs2 is passed, the following resulting query will be used for the band:

select e.create_ts, e.id, e.vin from ref_car e
where
e.create_ts >= \${createTs1}
and
e.create_ts < ${createTs2}

4.1.2. JPQL Dataset

JPQL dataset is produced as result of JPQL query execution. The resulted query fields must have aliases provided using the as operator. You can use report input parameters and parent bands fields in the JPQL query, similar to SQL query.

Below is an example of a JPQL query with a groupId parameter, obtained from the group parent band and an external active parameter:

select u.name as userName, u.login as userLogin
from sec$User u
where u.group.id = ${group.groupId}
    and u.active = ${active}

JPQL queries automatically support soft deletion and return only records which are not deleted.

You can also activate query preprocessing by checking the Preprocess query as Groovy template checkbox below the band editor.

By default, JPQL queries use entities mapped to the main database. If you want to query entities from an additional data store, set its name in the Data store field.

4.1.3. Groovy Dataset

Groovy dataset is produced as a result of Groovy script execution. The script returns an object of the List<Map<String, Object>> type. Each element of this list – an object of the Map<String, Object> type – corresponds to one dataset record.

The following objects are passed into the script:

dataManager – an object of the com.haulmont.cuba.core.global.DataManager type that provides CRUD functionality. For example:
```
def book = dataManager.load(Book.class).id(bookId).view("book.edit").one;
```
metadata – an object of the com.haulmont.cuba.core.global.Metadata type, providing access the application metadata. For example:
```
def metaClass = metadata.getClassNN('sec$User')
```
params – external report parameters map. Below is an example to get a parameter value:
```
def active = params['active']
```
parentBand – parent band as an object of the com.haulmont.yarg.structure.BandData type. This object allows you to get a parent band field value by invoking the getParameterValue() method, for example:
```
def groupId = parentBand.getParameterValue('groupId')
```
persistence – an object of the com.haulmont.cuba.core.Persistence type that allows you to get datasources.

By default, the main data store is used. For working with an additional data store, pass its name as a parameter to the getDataSource() method:
```
def sql = new Sql(persistence.getDataSource('myStore'))
def rows = sql.rows('select e.name from SEC_GROUP e')
```
security – an object of the com.haulmont.cuba.core.global.Security type used to check user access rights to different objects in the system. For example:
```
if (security.isEntityOpPermitted(Book.class, EntityOp.READ) {
    ...
}
```
timeSource – an object of the com.haulmont.cuba.core.global.TimeSource type used to obtain the current time. For example:
```
def currentDate = timeSource.currentTimestamp()
```

transactional – a method that takes a closure, which should be executed in a new transaction, as parameter. The current EntityManager becomes the closure parameter. For example:

transactional { em ->
    def query = em.createQuery('select g from sec$Group g')
    ...
}

Below is an example of the Groovy script which extracts users by the group which is output in the parent band and by the active external parameter:

def result = []
transactional { em ->
    def query = em.createQuery('select u from sec$User u where u.group.id = ?1 and u.active = ?2')
    query.setParameter(1, parentBand.getParameterValue('groupId'))
    query.setParameter(2, params['active'])
    query.resultList.each { user ->
        result.add(['userLogin': user.login, 'userName': user.name])
    }
}
return result

userSession – an object of the com.haulmont.cuba.security.global.UserSession type associated with the currently authenticated user. For example:
```
def user = userSession.currentOrSubstitutedUser
```
userSessionSource – an object of the com.haulmont.cuba.core.global.UserSessionSource type which is used to obtain current user session object. For example:
```
def locale = userSessionSource.locale
```

You can use static methods of the AppBeans class to access any Spring beans of the middleware tier, for example:

def myService = com.haulmont.cuba.core.global.AppBeans.get('sample_MyService')

4.1.4. Entity Dataset

Entity dataset consists of a single row and is produced using attributes of a single entity instance and the entities related to it.

The data source is produced from an external parameter of the Entity type, which must be described in the Parameters and Formats tab. The value in the Entity parameter name field must match the parameter name.

The report template must contain fields with entity attribute names. Attributes used in the template should be listed in the special window, which is invoked by the Select entity attributes button.

4.1.5. List of Entities Dataset

List of entities dataset is produced using a list of entity instances.

The data source is produced using an external parameter being a List of entities, which must be described in the Parameters and Formats tab. The value in the Entity parameter name field must match the parameter alias.

The report template must contain fields with entity attribute names. Attributes used in the template should be listed in the special window, which is invoked by the Entity attributes button.

4.1.6. JSON Dataset

JSON dataset is generated from JSON data. This data can be received from the following sources:

Groovy script

The script provided by the user is supposed to return JSON data as a string.

For example:

return '''
        {
          "items": [
            {
              "name": "Java Concurrency in practice",
              "price": 15000
            },
            {
              "name": "Clear code",
              "price": 13000
            },
            {
              "name": "Scala in action",
              "price": 12000
            }
          ]
        }
        '''

URL
The report engine will perform a GET HTTP query against the URL.

For example:
https://jsonplaceholder.typicode.com/users
Parameter of a String type

The report external parameter of String type which contains the JSON data must be described in the Parameters and Formats tab.

The received JSON tree is queried using the JsonPath query. For example, you can use the $.store.book[*] JsonPath to return all books from the following JSON tree:

{
    "store": {
    "book": [
            {
                "category": "reference",
                "author": "Nigel Rees",
                "title": "Sayings of the Century",
                "price": 8.95
            },
            {
                "category": "fiction",
                "author": "Evelyn Waugh",
                "title": "Sword of Honour",
                "price": 12.99,
                "isbn": "0-553-21311-3"
            }
    ],
    "bicycle": {
        "color": "red",
        "price": 19.95
    }
}
}

For more details on JsonPath expressions see http://goessner.net/articles/JsonPath/.

Fields outputted by the report that have Date, DateTime, or Time data types do not support the format defined by java.text.SimpleDateFormat. To set the correct format, you should write a Groovy script.

To do this, switch to the Parameters and Formats tab of the report editor and open the formatter editor. For example, for the field bookPublication.dateTime Groovy-script will look like this:

import java.text.SimpleDateFormat

def simpleOldDateFormat = new SimpleDateFormat('yyyy-MM-dd HH:mm')
def simpleNewDateFormat = new SimpleDateFormat('dd/MM/yyyy HH:mm')
def oldDate = simpleOldDateFormat.parse(value)

return simpleNewDateFormat.format(oldDate)

4.2. 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:

Figure 32. Template Editor

Template code – template code for its identification.
Template file – template file, which is loaded from the file system and saved to the database along with the report structure description.
Output type – report output type. It should be consistent with the template file type according to the rules described in Output format compliance matrix.
Output name pattern – optional file name pattern, which will be used for the produced report download. It can either be a constant string or contain report parameter as a variable, e.g. ${header.authorName}.xlsx. More sophisticated patterns with several parameters and string concatenation can also be created as a script in any band of report structure, e.g. ${Root.title}.xlsx, where title is a result of the script:
```
[['title' : ('Report for '+params['author'].firstName+' '+params['author'].lastName)]]
```
Is custom – the flag to use custom formatting logic not covered by any provided formatter.
Defined by – the way the custom template is defined: by a class, script or URL.
Custom definition – the fully qualified name of the Java class, the path to the Groovy script in the core module, or the URL used to create the template.
Is alterable output – the flag to let the user choose the output type of a report at runtime in a dialog window.

If the flag is on, a dialog with output type selection will be shown when running a report. If the report has more than one template, the template selection dropdown will be shown as well.

Figure 33. Alterable Output

4.2.1. XLSX and XLS Templates

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

Each report band must have a corresponding region in the template, which is named as the band. For instance, a report has two bands – Header and Data. This means that template should also have Header and Data named ranges. 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. Vice versa, each part of the sheet you want to show should be a band in a report (at least an empty band).

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

Bands can be horizontally and vertically oriented. If the band is horizontal, suitable named region will grow downward, vertical will grow rightward. Horizontal bands may be organized in tree-like structure and contain sub-bands (nested or children bands). Therefore, for sub-bands, you need to create named regions directly under the regions that correspond to the parent bands. XLSX formatter render children bands using the following algorithm:

Write the first row of parent band →
Write all first row’s children rows →
Write the next row of parent band.

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:

Figure 34. XLS File Template

You can add variables to the reporting template. Variables should be inserted into sheet names or the header/footer of XLSX template having ${<BandName>.<variableName>} format.

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 linked with the band.

Formulas can reference the cells of the same band or another band. To be processed by formatter, the formulas should use either range of cells in the band, or direct cells coordinates, e.g. (A1*B1) or ($B:$B).

To process the data as an Excel chart, create an empty band in the report structure and a named region with the same name in the template. Then create a chart inside this named region and make a reference to related bands using Select data button in the chart context menu. If your chart data is in a continuous range of cells, select any cell in that range. Your chart will include all the data in the range. If your data isn’t in a continuous range, select nonadjacent cells or ranges.

Converting XLSX to PDF and CSV: XLSX reports can be automatically converted to CSV and PDF format. OpenOffice/LibreOffice should be installed for PDF conversion.

Figure 35. CSV Output

4.2.2. CSV Templates

You can create CSV templates using Microsoft Office or LibreOffice.

Bands in CSV template should be horizontally oriented, so the suitable named region will grow downward. Also, bands should belong to the first level of data, i.e. be the children of the Root band. In all other respects, the same principles as for XLS/XLSX templates should be used.

Figure 36. CSV Template Example

Figure 37. CSV Output

Inline editor

CSV templates support inline editing. You can edit the template directly in the Template editor window and see the changes without having to re-upload the template file.

Figure 38. CSV Inline Editor

4.2.3. DOCX and DOC Templates

You can create DOC and DOCX templates using Microsoft Office or OpenOffice / LibreOffice.

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 link 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.

Horizontal bands in DOCX and DOC cannot contain sub-bands. If you need sub-bands, use XLS(X) format.

The table must contain either one or two rows. If the table has two rows, then the corresponding band fields must be in the second row. The first row should contain the marker with the corresponding band name and, if necessary, a static text or other band fields.

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.

Figure 39. DOCX Template

Cells data format is not supported in DOCX and DOC templates. To avoid numbers or dates formatting problems due to user’s locale, such as unnecessary numeric separators, try to cast your data to string. For example, wrap

select e.year as "year"

into

select cast(e.year as varchar(4)) as "year"

4.2.4. HTML Template

An HTML template is defined in an .html file (UTF-8 encoding without BOM). You can use HTML/CSS features of Flying Saucer library; its main guide is available at http://flyingsaucerproject.github.io/flyingsaucer/r8/guide/users-guide-R8.html

To control page size, page headers and footers, use special CSS rules and properties. You can find an example of a report with repeatable header and footer blocks in the Sample Reports section.

There are two ways to place data in a template:

Using FreeMarker tags.
Using Groovy templating engine.

By default, the Report wizard generates an HTML template with FreeMarker tags.

Use the Template type radiobutton in the template editor to switch between the methods.

Figure 40. HTML Template Editor

Groovy templating engine

You can preprocess HTML report template as Groovy template. It will be processed by GStringTemplateEngine.

The template engine uses JSP style <% %> script and <%= %> expression syntax or GString style expressions. The variable out is bound to the writer that the template is being written to. Thus, the template can use any code on Groovy if it is properly defined. GStringTemplateEngine has access to:

external parameters: BandName.fields.ParamName;
bands: BandName.bands.ChildBandName;
fields: BandName.fields.FieldName.

You may use variables for convenience, e.g.:

<% def headerRow = Root.bands.HeaderRow %>
<p>Date: ${headerRow.fields.reportDate}</p>

Below is an example of a template which outputs a report for a single user.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="ru">
    <head>
        <title> Report User </title>
        <style type="text/css">
 body {font-family: 'Charis SIL', sans-serif;} tbody tr {height:20px; min-height:20px}
        </style>
    </head>
    <body>
        <% def user = Root.bands.User %>
        <p>Login: ${user.fields.login.first()}</p>
        <p>Active: ${user.fields.active.first()}</p>
    </body>
</html>

You can find an example of a report with Groovy templating in the Sample Reports section.

FreeMarker

FreeMarker documentation is available at https://freemarker.apache.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 use variables for convenience, e.g.:

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

You can also use such functions as getMessage() and getMainMessage() to get localized values:

getMessage() helps you to get localized enum values or, in case you pass two string parameters (msgPack, key), searches for the value by the key in the given message pack:
```
${getMessage(order.status)}
```
getMainMessage(), in turn, takes one string parameter key and returns the value from the main message pack:
```
${getMessage(outOfStockMessage)}
```

<!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' ]]
        }
    }
}

Inserting a field:

<!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>

Inserting a list:

<!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>

Inserting a table:

<!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>

Inserting a multi-level list:

<!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>

Embedded pictures

At the moment, CUBA Reporting add-on does not provide means of inserting images into HTML-reports similarly to DOCX/XLSX reports. Images still can be embedded with the img tag and the link to the picture in the src attribute. You can add images to the HTML-report in one of the following ways.

by URL

An image can be hosted on the Tomcat server or any external hosting up to the local file reference. For example, the image hosted in the deploy\tomcat\webapps\ROOT\images folder can be inserted like:

<img src="http://localhost:8080/images/SomeImage.jpg" height="68" width="199" border="0" align="right"/>

by Bitmap

An image is added as a byte array within the src attribute. This approach allows you to use variables for the FileDescriptor attributes of the entities. The byte array can even be added directly to the template, even though this approach is not recommended:

<img alt="SomePicture.png" src="data:image/png;base64,iVBORw0K ..... AcEP9PwxD0hNKK1FCAAAAAElFTkSuQmCC"/>

by custom prefixes:
- You can use the core:// and web:// prefixes to reference to image resources from the web or core module without having to hard-code webPort and webContextName in the template. For example, the image hosted in the deploy\tomcat\webapps\app\VAADIN\images folder can be inserted like:
  <img src="web://VAADIN/images/SomeImage.jpg" height="68" width="199" border="0" align="right"/>
- fs:// prefix allows inserting an image by specifying FileDescriptor ID. For example:
  <img src="fs://fede432a-4f5d-3bab-71a0-b98133759b0f" height="68" width="199" border="0" align="right"/>

Inline editor

HTML templates support inline editing. You can edit the template directly in the Template editor window and see the changes without having to re-upload the template file.

Figure 41. HTML Inline Editor

4.2.4.1. 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 reporting.fontsDir application property.

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

Install the ttf-mscorefonts-installer package:

$ sudo apt-get install ttf-mscorefonts-installer

Set the reporting.fontsDir application property:

reporting.fontsDir = /usr/share/fonts/truetype/msttcorefonts

Explicitly specify fonts in HTML templates, for example:

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

Another thing to mention is parsing of special characters. To avoid errors when converting HTML to PDF, it is recommended to wrap your fields in <![CDATA[ ]]> construction in your HTML template file:

<tr>
        <td> <![CDATA[${(row.fields('book_name'))!?string!}]]> </td>
        <td> <![CDATA[${(row.fields('author'))!?string!}]]> </td>
</tr>

4.2.5. JasperReports Template

JasperReports formatter allows you to use JasperReports templates to output the information extracted by CUBA reporting. The template will be processed by the report engine, providing an output of defined type, see Output format compliance matrix.

You can create JRXML templates using JasperReports tools (for example, Jaspersoft Studio) or in a simple text editor. Each report band defined in the report structure must have a corresponding band element in the template which is, in turn, placed within one of the standard JasperReports report sections (which are also named as the bands in JasperReports terms): title, pageHeader, columnHeader, detail, etc.

The report engine puts all report band data in one datasource: JRBandDataDataSource, which contains data as a tree with the Root band as the root, and passes the CubaJRFunction instance to the template as the main data source that can be addressed using the REPORTING parameter. Declaring this parameter in the report template is not mandatory, it will be added automatically if omitted, but if you want to compile the template in JasperReports IDE, you need to declare this parameter explicitly.

The REPORTING parameter supports two functions:

dataset – gets the sub-datasource from the main data source that can be used, for example, in tables or in subreports as subDataset. This method searches among the children of the root band for the band with specified name and creates a new data source with this band data as a new root. For example:
```
<subDataset name="Product">
        <field name="name" class="java.lang.String"/>
        <field name="price" class="java.lang.Long"/>
</subDataset>
...
<dataSourceExpression><![CDATA[$P{REPORTING}.dataset("Product")]]></dataSourceExpression>
```
bitmap – transforms the given byte array into ByteArrayInputStream and can be used for embedding images into the report. For example:

<field name="Main.image" class="java.lang.Object"/> //image from DB as byte array
...
<imageExpression><![CDATA[$P{REPORTING}.bitmap($F{Main.image})]]></imageExpression>

Each report band can be used in the template only once, so if you need to represent the same data in different forms within one report (for example, as a table and a chart), you need to create as many bands as the band elements in the template. Nested bands are not supported, all bands should be direct children of the Root band.

The data can be retrieved from the datasource using the following syntax: $F{<field name>}. For example:

<textField>
    <textFieldExpression><![CDATA[$F{library_department_name}]]></textFieldExpression>
</textField>

You can find an example of a report with JasperReports template in the Sample Reports section.

If your application uses the UberJAR deployment option, do the following to run JasperReports with the UberJAR:

Copy jasperreports-.jar and yarg-.jar to uber JAR directory,
Create a file jasperreports.properties in the uber JAR directory,
Add the net.sf.jasperreports.compiler.classpath property in this file with the names of the copied JARs as the property value, for example:

net.sf.jasperreports.compiler.classpath = jasperreports-6.9.0.jar;yarg-2.0-SNAPSHOT.jar

4.2.6. 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.

Place the class defining the template in the core module and implement the com.haulmont.yarg.formatters.CustomReport interface. Define the createReport() method, which returns an array of bytes and takes the following input parameters:

report – report descriptor of the com.haulmont.yarg.structure.Report type.
rootBand – root band data of the com.haulmont.yarg.structure.BandData type.
params – a map of external report parameters.

Below is an example of a simple class-defined template. It creates an HTML document showing the name of a book selected as a 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();
    }
}

In the template editor check the Is custom checkbox, select Class in the Defined by field and set the fully qualified name of the Java class as the custom definition:

Figure 42. Class-Defined Template

4.2.7. Chart Template

Chart output type is available if the application project includes the charts component. 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:

Figure 43. Pie Chart Template

Band name – a band providing data for the chart.
Title field – a field from which segment names will be taken.
Value field – a field from which segment values will be taken.
Color field – a field from which segment colors will be taken. The color value should be specified in the web format. If not defined, colors will be chosen automatically.
Units – this text will be added to legend values.

Serial chart:

Figure 44. Serial Chart Template

Band name – a band providing data for the chart.
Category field – a field from which category names will be taken.
Category axis caption – a caption for the horizontal axis.
Value axis caption – a caption for the vertical axis.
Value axis units – this text will be added to values.

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

Value field – a field from which row values will be taken.
Type – row display type.
Color field – a field from which segment colors will be taken. The color value should be specified in the web format. If not defined, colors will be chosen automatically.

4.2.8. Pivot Table Formatter

Pivot Table output type is available if the application project includes the charts component. For more information on Pivot Table see the Charts manual.

Pivot Table output can only be used as an additional report output, that’s why it is not available in the report wizard. To use the pivot table formatter, switch to the Templates tab in the report editor, click Create and select Pivot Table as the output type of the new template. After that, configure the template settings as described below.

The resulting table is displayed in the Reports → Show Pivot Tables screen of your web application.

Figure 45. Pivot Table Formatter

The report generator will get the band data and draw a table with drag-and-drop functionality, aggregation, and summary. Only one band can be used in the pivot report, nested bands are not supported.

Renderer options: This tab enables defining the collection of rendering functions that should be displayed in the list of available renderers in the UI and setting the default one.

Figure 46. Renderer Options

Aggregation options

The Aggregation options tab allows you to define the list of table aggregators. Aggregation attributes:

Mode enables setting one of the predefined aggregation functions,
Caption is a localized value to be displayed in the UI,
Custom function – if not empty, the mode value is ignored in favor of the custom JavaScript code.

Figure 47. Aggregation Options

Properties options

The pivot table displays all properties for all types of datasets. To be processed correctly, the aliases of reference attributes in SQL, JPQL and Groovy datasets should not contain periods, for example, select u.name as "userName". The selected properties should be set as the pivot table’s properties on the Properties options tab of the template editor:

Row, Column – a key-value map with the set of properties to be used in the pivot table columns and rows, where the key is the name of a dataset property and the value is its localized caption.
Aggregation – the properties that will be aggregated in each cell,
Derived property – can be used to add new attributes to the original data set, derived from the existing ones. This element is a key-value map, where the key is the name of a generated attribute, and the value is a JavaScript function that generates this attribute.

Figure 48. Properties Options

Custom options

Filter function – JavaScript function that will be used for filtering.
Sorters function – JavaScript function that will be used for rows and columns captions sorting.
Renderer options, depending on the selected renderer, enable setting a JavaScript function that will be used to customize the renderer’s appearance. Actually, only two renderer types can be customized:
- all kinds of heatmap: the cell colors can be set by the Javascript code,
- all kinds of charts: options can be used to set the chart’s size.

Figure 49. Custom Options

4.2.9. Table Formatter

When you select table output in the wizard, the system automatically creates a template. When running the report, data will be displayed on a special screen of the application.

You can also create a template manually for an existing report. To do this, select Table as the output type in the report template editor.

Figure 50. Table Formatter

In the Band column, add the names of the bands that you want to display in the resulting table. For each band, set a key-value map, where the key is the name of a dataset property, and the value is its localized caption.

The report generator will get the band data and draw a sortable table for each band from the first level of the hierarchy.

The table displays columns for all properties for SQL, JPQL datasets. If the Entity/List of Entities dataset is used, the table will display only the columns for selected attributes.

The resulting table is displayed in the Reports > Show Report Table screen of your web application. The Excel button enables you to download the displayed table as an Excel file.

Figure 51. Show Report Table

4.2.10. Output format compliance matrix

Template / Output

XLSX

XLS

CSV

DOCX

DOC

PDF

HTML

Chart

XLSX

+ ¹

XLS

+ ¹

CSV

DOCX

+ ²

DOC

+ ¹

HTML

Chart

JRXML

¹ - OpenOffice/LibreOffice must be installed for output.

² - depending on the reporting.office.docx.useOfficeForDocumentConversion application property, the output can be performed with or without OpenOffice/LibreOffice. In the latter case, you need to provide required fonts, as described in Converting HTML to PDF.

4.2.11. Executing a report on an external server

The platform provides the ability to run a report on an external server, such as Microsoft Reporting Services, by URL. To enable this feature, follow these steps:

In the template editor check the Is custom checkbox.
Select URL in the Defined by field.
In the Custom definition field, specify the URL for the report in MS Reporting Services. Report parameters can be passed to the URL as ${param Alias}.

Figure 52. Template editor
In the reporting.curl.path application property in the app.properties file of the core module, specify the path to the command line curl tool.

4.3. 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:

Figure 53. External Report Parameters

Properties tab:

Caption – parameter name, as it will appear in the parameter input form when running the report.
Parameter alias – parameter alias used to access it in datasets.
Parameter type – parameter type.
Hidden – flag that defines whether the request for parameter should be hidden from users.
Required parameter? – flag determining if the parameter is mandatory.
Entity – if the Entity or List of entities parameter type is selected, then you need to select the entity type in this field.
Entity selection screen – optional screen identifier, which will be used to select entity instances. If the screen is not specified, selection will be made from a special screen generic for all entities.
Enumeration – if the Enumeration parameter type is specified, then you need to select the enumeration type in this field.
Default value – defines the parameter value that will be used by default if no other value is selected by the user.
Default date(time) is current – if the temporal parameter type is specified (Date, Time or Date and time), this flag defines whether the current timestamp will be used as the default parameter value.

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 = Книга

Input parameters transformation

The Transformation tab allows you to apply a Groovy script on the parameter before using it in the report.

A Groovy script should return new parameter value. Current parameter value is available in the script by the paramValue alias, the parameters map is available by alias params. For example:

return "%" + paramValue + "%"

You can also use one predefined transformations that add wildcards for text (String) parameters:

Starts with,
Ends with,
Contains.

Figure 54. Input parameters transformation

In the Validation tab, you can define a Groovy script with some condition for the parameter validation, see the details below.

Input parameters validation

You can validate an input parameter and/or define the cross-parameter validation.

You can enable validation of each parameter in the Validation tab of the parameter editor by checking the Validate checkbox. The validation logic is specified by a Groovy script. The script should check the parameter value and call the invalid() method if the value is not valid. This method will show the user an alert with the given message about the report validation errors.

The following variables are passed into the script:
- value – the parameter value entered by the user.
- dataManager – an object of the DataManager type that provides CRUD functionality.
- metadata – an object of the Metadata type that provides access to the application metadata.
- security – an object of the Security type used to check user access rights to different objects in the system.
- userSession – an object of the UserSession type associated with the currently authenticated user.
  
  Figure 55. Input parameters validation
Cross-parameter validation can be enabled by checking the Validate checkbox in the Cross parameters validation section of the Parameters and Formats tab. The validation logic is specified by a Groovy script. The script should check whether or not parameter values make sense in relation to each other and call the invalid() method if they do not. This method will show the user an alert with the given message about the report validation errors.

In addition to the variables listed above, the params variable is passed into the script to access the external report parameters map.

Figure 56. Cross-parameter validation

4.4. 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:

Figure 57. Field Value Formats

Value name – report field name with the band prefix, for example, Book.year.
Format string – field format. For number values specify the format according to the java.text.DecimalFormat rules, for dates – java.text.SimpleDateFormat.
Groovy script checkbox – allow specifying a Groovy script to format the parameter. Using the value alias, the current parameter value is passed to the script, which can be formatted or converted to the desired format. A Groovy script should return the new value as a string.

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

In order to insert an image, specify the image URL as the field value, and the format string must be as follows: ${image:<Width>x<Height>}, for example, ${image:200x300}.

To work with the FileDescriptor, use the ${imageFileId:WxH} value formatter that accepts both the FileDescriptor id and the link to the FileDecriptor instance itself.
In order to insert an HTML block, you should return an HTML markup in the field, and select ${html} as the format string. In the output value, you may omit top-level tags up to <body> inclusive. If necessary, all missing top-level tags will be added automatically. All blocks should be encoded with UTF-8. CSS and the style attribute are not supported.

You can specify your own custom formats as well. To do this, type the new value in the field without opening the dropdown and press Enter. You can also choose any format from the dropdown, edit its name in the field and press Enter. Custom format will be saved in both cases.

4.5. 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.

Keep in mind that in the reports browser (the Reports→Reports menu item) all system reports are visible because this screen is considered administrative.

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

4.6. 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 = Книги по автору

5. Running Reports

This section describes how to run created reports.

5.1. 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 their role. If the report has external parameters, they will be requested in a special form when running the report.

5.2. Running Reports from Screens

You can run reports from arbitrary screens using special actions and associated buttons or component context menu items. In this case, the availability of the report directly in this screen is checked in addition to a user role.

Action types and examples of their use are provided below.

com.haulmont.reports.gui.actions.list.RunReportAction – a standard action that displays the list of all available reports. It should be defined for a Button or a list component (Table, DataGrid, etc.).

Below is an example of using the declarative action for the GroupTable:

<groupTable id="booksTable"
            width="100%"
            dataContainer="booksDc">
    <actions>
        <action id="create" type="create"/>
        <action id="edit" type="edit"/>
        <action id="remove" type="remove"/>
        <action id="run" type="runReport"/> (1)
    </actions>
    <columns>
        <!-- -->
    </columns>
    <rowsCount/>
    <buttonsPanel id="buttonsPanel"
                  alwaysVisible="true">
        <!-- -->
        <button id="runBtn" action="booksTable.run"/>
    </buttonsPanel>
</groupTable>

1	- the `type` attribute defines a specific `runReport` action type, provided by the framework.

Example of programmatically creating the action together with a button declared in the screen XML descriptor:

@Inject
protected Actions actions;

@Inject
protected Button runReportBtn;

@Subscribe
public void onInit(InitEvent event) {
    Action runReportAction = actions.create(RunReportAction.class, "runReport");
    runReportBtn.setAction(runReportAction);
}

When the action is performed, a modal Report Run dialog will open where reports related to the current screen will be displayed. When a user selects a report from the list, the parameters input form is displayed (if any were defined) and the report is run.

com.haulmont.reports.gui.actions.list.ListPrintFormAction – a standard action for printing reports for entity instances associated with a list component (Table, DataGrid, etc.).

The action only selects reports having an external parameter of the Entity or the List of entities type and where the parameter entity type matches the entity type displayed by the list component. If only one report is available as a result of selection, it is invoked immediately. If several reports are available, their list is offered to the user for selection.

The external parameter value is passed to the report using the following rules:

If the parameter has the List of entities type, the list of instances currently selected in the list component is passed into it.
If the parameter has the Entity type, and the list component has a single instance selected (one row is highlighted), then this instance is passed into the report.

If the parameter is of the Entity type, and the list component has several rows selected, then the report runs several times according to the number of selected instances. After execution, the user gets a single ZIP archive containing all generated reports.

Below is an example of using the declarative action for the GroupTable:

<groupTable id="authorsTable"
            width="100%"
            dataContainer="authorsDc">
    <actions>
        <action id="create" type="create"/>
        <action id="edit" type="edit"/>
        <action id="remove" type="remove"/>
        <action id="list" type="listPrintForm"/> (1)
    </actions>
    <columns>
        <!-- -->
    </columns>
    <rowsCount/>
    <buttonsPanel id="buttonsPanel"
                  alwaysVisible="true">
        <!-- -->
        <button id="listBtn" action="authorsTable.list"/>
    </buttonsPanel>
</groupTable>

1	- the `type` attribute defines a specific `listPrintForm` action type, provided by the framework.

Example of programmatically creating the action together with a button declared in the screen XML descriptor:

@Inject
protected Actions actions;

@Inject
protected Button listPrintBtn;

@Inject
private GroupTable<Author> authorsTable;

@Subscribe
public void onInit(InitEvent event) {
    Action listPrintFormAction = actions.create(ListPrintFormAction.class, "listPrintFormAction");
    authorsTable.addAction(listPrintFormAction);
    listPrintBtn.setAction(listPrintFormAction);
}

When the action is performed, if no entities were selected from the list component, a confirmation window will be displayed.

Figure 58. A confirmation window

After that, the modal Run reports dialog will open where reports related to the current screen will be displayed. From this modal screen, the user can run some report for the selected entity.

com.haulmont.reports.gui.actions.EditorPrintFormAction – an action associated with an entity editor screen. The action only selects reports having an external parameter of the Entity or the List of entities type and where the parameter entity type matches the edited entity type. If only one report is available as a result of selection, it is invoked immediately. If several reports are available, their list is offered to user for selection.

The external parameter value – edited entity instance – is passed into the report. If the parameter has the List of entities type, then a list containing a single item is passed.

Below is an example of using the action in a button located near the standard OK and Cancel buttons:
- XML descriptor
  
  <layout expand="editActions" spacing="true">  <hbox id="editActions" spacing="true"> <button action="windowCommitAndClose"/> <button action="windowClose"/> <button id="reportButton" icon="PRINT"/> </hbox> </layout>
- Controller
  
  @Inject private Button reportButton; @Subscribe public void onInit(InitEvent event) { reportButton.setAction(new EditorPrintFormAction(this,null)); }

com.haulmont.reports.gui.actions.list.ExecutionHistoryAction – a standard action for displaying the report execution history. It should be defined for a Button or a list component (Table, DataGrid, etc.).

To view the report execution history set the reporting.executionHistory.enabled application property to true in the Administration > Application Properties screen.

Below is an example of using the declarative action for the GroupTable:

<groupTable id="authorsTable"
            width="100%"
            dataContainer="authorsDc">
    <actions>
        <action id="create" type="create"/>
        <action id="edit" type="edit"/>
        <action id="remove" type="remove"/>
        <action id="history" type="executionHistory"/> (1)
    </actions>
    <columns>
        <!-- -->
    </columns>
    <rowsCount/>
    <buttonsPanel id="buttonsPanel"
                  alwaysVisible="true">
        <!-- -->
        <button id="historyBtn" action="authorsTable.history"/>
    </buttonsPanel>
</groupTable>

1	- the `type` attribute defines a specific `executionHistory` action type, provided by the framework.

Example of programmatically creating the action together with a button declared in the screen XML descriptor:

@Inject
protected Actions actions;

@Inject
protected Button execHistoryBtn;

@Subscribe
public void onInit(InitEvent event) {
    Action execHistoryAction = actions.create(ExecutionHistoryAction.class, "execHistoryReport");
    execHistoryBtn.setAction(execHistoryAction);
}

When the action is performed, a modal Execution history dialog will open where reports related to the current screen will be displayed. After clicking on the Execution History button, the execution history for the selected reports will be displayed. If no reports have been selected, then execution history will be displayed for all reports associated with the screen.

5.3. Cancelling Reports

If the report execution is running as a background task, it can be interrupted by the user.

To add the cancel option, set the reporting.useBackgroundReportProcessing property in the Administration > Application Properties screen.

reporting.useBackgroundReportProcessing = true

Thus, the window with the progress bar and the Cancel button will be displayed:

Figure 59. Cancel report

You can also set the processing timeout using the reporting.backgroundReportProcessingTimeoutMs property:

reporting.backgroundReportProcessingTimeoutMs = 30000

When the time is up, the task will be canceled regardless the result, and the user will receive an error message:

Figure 60. Report error

To cancel the report execution programmatically, use the cancelReportExecution() method of the ReportService interface that takes the identifiers of the user session and the report:

reportService.cancelReportExecution(userSessionId, report.getId());

6. Report Execution History

The platform provides a mechanism of a report execution history management with the following features:

Save execution history for each report. The system administrator can use the report execution history to find out how often a report is executed, how many seconds of processing time is spent on a report, by which user and when a report was run, what errors occurred during the execution.
Clean outdated report execution history.

The report execution history is disabled by default and can be enabled by setting the reporting.executionHistory.enabled application property to true. You can do it in the Administration > Application Properties screen.

The execution history screen is considered administrative and was not added to the main menu. To view the execution history, go to the reports browser (the Reports>Reports menu item) and click on the Execution history button.

If any report was selected in the Reports table, then the execution history is additionally filtered by the selected report. If no reports were selected in the Reports table, the execution history of all system reports is displayed on the execution history screen.

Figure 61. The execution history screen

"Cancelled" flag means that the user launched the report as a background task, and then canceled it.

Execution history is also recorded even for reports which are not yet saved to the database, but launched from the report editor (by clicking the Run button).

Output documents

The mechanism provides an ability to save output documents – report results files – in the file storage. This feature consumes a file storage disk space; it is configured separately and is disabled by default. To enable it, define the reporting.executionHistory.saveOutputDocument application property in the Administration > Application Properties screen:

reporting.executionHistory.saveOutputDocument = true

Now, if you select an item in the execution history table, the Download document button becomes available. Click the button to download a document that is a report result file.

Reports with output types chart, pivot table, and table do not have result files, so the execution history of such reports does not save any output documents.

If you run the report programmatically using the createAndSaveReport() method, it saves another copy of the same result document to the file storage. These two files are put to the file storage independently.

Cleanup the history

You can configure the execution history cleanup procedure to regularly remove unnecessary data.

Create and activate a scheduled task. Go to the Administration > Scheduled Tasks screen of your application. Create a new task and set the following parameters:
- Bean Name = reporting_ExecutionHistoryRecorder
- Method Name = cleanupHistory()
- Cron = nightly, e.g. 0 0 1 * * *
- Singleton – yes (this is important only for a cluster of middleware servers)
  
  Save the task and click Activate on it.
If you did not set up the execution of the scheduled tasks for this project before, nothing would happen on this stage – the task will not be executed until you start the whole scheduling mechanism with the cuba.schedulingActive application property.
Set up the configuration properties:
- reporting.executionHistory.cleanup.days – 730 days by default.
- reporting.executionHistory.cleanup.itemsPerReport – 1000 by default.
When the report execution history is cleaned up, the associated output document is also deleted from the file storage.

7. Sample Reports

Learn also Introduction to Reporting and Document generation guide, which shows how to embed the reporting capabilities into the application.

7.1. Sample XLS Report

In this chapter we will consider the structure of one of the reports from the sample Library application, which source code is available at GitHub.

First of all, open the Project Properties editor in CUBA Studio: click CUBA → Project Properties main menu item. Add the reports application component in the App components list. Then run the application.

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.

Report data structure.
Figure 62. Report data structure

Let us consider report bands.
- header band – report header. It contains the dataset with the Groovy script which outputs the report external parameters values:
[['authorName' : (params['author'].firstName + ' ' + params['author'].lastName)]]
- The book band outputs the list of books by running the following SQL query:
select b.name as book_name, b.id as book_id from library_book b join library_book_author_link ba on ba.book_id = b.id join library_author a on a.id = ba.author_id where a.id = ${author}
This query uses the external report parameter – author. The parameter has the Entity type, however, in SQL queries you can compare it directly with entity identifier fields; the conversion will be done automatically.
- The publisher band, which is a child band of the book, outputs the book publishers by running the following SQL query:
select p.name as publisher, bp.year, p.id as publisher_id from library_book_publication bp join library_publisher p on p.id = bp.publisher_id where bp.book_id = ${book.book_id}
This query uses the parent band field book_id as a parameter. This provides dependency between the parent and child bands.
- The publication band, which is a child of the publisher band, outputs the book publications by running the following SQL query:
select ld.name as department, count(bi.id) as amount from library_book_instance bi join library_book_publication bp on bp.id = bi.book_publication_id join library_library_department ld on ld.id = bi.library_department_id where bp.publisher_id = ${publisher.publisher_id} and bp.book_id = ${book.book_id} group by ld.name
This query uses both parent bands fields as parameters – book_id and publisher_id.
Report parameters.

The Parameters and Formats tab contains one declared report external parameter – Author:

Figure 63. Report parameters

When running the report, the user will have to enter this parameter. The author selection will be performed via the library$Author.browse screen, available in the application.
Report templates.

The Templates tab contains a single defined XLS template, loaded from BooksByAuthor.xls

Figure 64. Report templates
Report Name Localization.

The Localization tab contains the report name for the Russian locale:
```
ru = Книги по автору
```

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

7.2. Sample Crosstab Report

To create a crosstab report, select Crosstab band orientation on the Report structure tab of the report editor. This orientation automatically adds three datasets to the band:

<band_name>_dynamic_header – the data from this dataset is copied to the right like a vertical band with table columns header.
<band_name>_master_data – the data from this dataset is copied downwards like a horizontal band with table rows header.
<band_name> – the dataset named the same as the band, it belongs to. It is the main content band that will fulfill the matrix cells.

These datasets can have any of available dataset types: SQL, JPQL, Groovy, and so on.

For example, a crosstab report for the Order entity of the Sales sample application has the following structure:

Figure 65. Crosstab Report

Report data structure

Let us consider report bands.

Here, the orders_dynamic_header dataset will return the list of months names:

orders_dynamic_header dataset

import java.text.DateFormatSymbols

List result = new ArrayList()
DateFormatSymbols dateFormatSymbols = DateFormatSymbols.getInstance(Locale.ENGLISH)
for (i in 0..dateFormatSymbols.months.length - 1) {
    result.add(["header_id" : i + 1, "month_name" : dateFormatSymbols.months[i]])
}
return result

The orders_master_data dataset returns names and identifiers of the customers selected by the user as an external report parameter:
orders_master_data dataset
```
select name as name, id as customer_id
from SALES_CUSTOMER
where id in (${selected_customers})
```
The orders dataset will provide data for the matrix cells, which is the order amounts sum for the particular month and customer. It takes orders_master_data@customer_id (the customer id) as the vertical coordinate of the cell, and orders_dynamic_header@header_id (the month name) as the horizontal coordinate, and fills the matrix cell with the amount value.

In the example below the report has two more external parameters: the start_date and the end_date that define the range of order dates. It would be a good idea to set up cross-parameter validation to make sure the range makes sense.
orders dataset
```
select
        o.customer_id as orders_master_data@customer_id,
        month(o.date_) as orders_dynamic_header@header_id,
        sum(o.amount) as "amount"
from sales_order o
where o.date_ >= ${start_date} and o.date_ <= ${end_date}
and o.customer_id in (${orders_master_data@customer_id})
and month(o.date_) in (${orders_dynamic_header@header_id})
group by o.customer_id, month(o.date_)
order by o.customer_id, month(o.date_)
```

Report parameters.

The Parameters and Formats tab contains three declared report external parameters – selected_customers, start_date, end_date:

Figure 66. Report parameters

When running the report, the user will have to enter these parameters. The customer selection will be performed via the sales_Customer.browse screen, available in the application.
Report template.

Then you can create a report template using Microsoft Office or LibreOffice.

The report template should contain named regions for all three datasets of the crosstab band and, additionally, the named region for the column header: <band_name>_header. In our case it is orders_header.

Below is an example of a template which outputs the list of Customers vertically and Orders made by each customer horizontally grouped by the months the orders were placed.

Figure 67. Crosstab template

Figure 68. Named regions

As a result, the report is extended both vertically and horizontally and aggregates the order amounts for each customer and each month:

Figure 69. Crosstab result

If you want to add totals for the report, you should do it in separate bands with their own datasets.

7.3. Sample JRXML Report

The example is also based on the sample Library application, which source code is available on GitHub.

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 Book availability in department. This report displays the list of book publications in the selected department; the default output format is XLS. Let’s create the new JasperReports template for this report.

Report data structure.

Figure 70. Report data structure

Let’s consider report bands.

Header band – report header. It contains the dataset with the Groovy script which outputs the report external parameters value:
```
[['library_department_name' : params['library_department'].name]]
```

Data band outputs the list of book instances in the department passed as a parameter by running the following Groovy script:

import com.haulmont.cuba.core.global.AppBeans

def persistence = AppBeans.get('cuba_Persistence')
def tx = persistence.createTransaction()
try {
    def result = []

    def em = persistence.getEntityManager()
    def ltList = em.createQuery('select lt from library$LiteratureType lt').getResultList()
    ltList.each { lt ->
        def count = em.createQuery(''' select count(bi) from library$BookInstance bi where bi.libraryDepartment = ?1 and bi.bookPublication.book.literatureType = ?2 ''')
            .setParameter(1, params['library_department'])
            .setParameter(2, lt)
            .getSingleResult()
        def refCount = em.createQuery(''' select count(bi) from library$BookInstance bi where bi.libraryDepartment = ?1 and bi.bookPublication.book.literatureType = ?2 and bi.isReference = true''')
            .setParameter(1, params['library_department'])
            .setParameter(2, lt)
            .getSingleResult()

        result.add(['literature_type_name': lt.name,
            'books_instances_amount': count,
            'reference_books_instances_amount': refCount])
    }
    return result
} finally {
    tx.end()
}

This query uses the external report parameter – library_department. The parameter has the Entity type, however you can compare it directly with entity identifier fields; the conversion will be done automatically.

Report parameters.

The Parameters and Formats tab contains one declared report external parameter – Department:

Figure 71. Report parameters

When running the report, the user will have to enter this parameter. The department selection will be performed via the library$LibraryDepartment.browse screen, available in the application.

Report template.

The Templates tab contains a single defined XLS template, loaded from BookAvailability.xls.

Create the new JRXML file with the following content:

BookAvailability.jrxml

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.0.final using JasperReports Library version 6.4.1 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="books" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <property name="template.engine" value="tabular_template"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <style name="Table_TH" mode="Opaque" backcolor="#066990">
 <box> <topPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> </box>
    </style>
    <style name="Table_CH" mode="Opaque" forecolor="#FFFFFF" backcolor="#06618F" hTextAlign="Center" fontSize="12">
 <box> <topPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> </box>
    </style>
    <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF" hTextAlign="Center">
 <box> <topPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> </box>
    </style>
    <subDataset name="Data">
        <field name="literature_type_name" class="java.lang.String"/>
        <field name="books_instances_amount" class="java.lang.Long"/>
        <field name="reference_books_instances_amount" class="java.lang.Long"/>
    </subDataset>
    <field name="library_department_name" class="java.lang.String"/>
    <title>
        <band height="72">
            <frame>
                <reportElement mode="Opaque" x="-20" y="-20" width="595" height="92" backcolor="#006699"/>
                <staticText>
                    <reportElement x="20" y="10" width="555" height="30" forecolor="#FFFFFF"/>
                    <textElement textAlignment="Center">
                        <font size="20" isBold="true"/>
                    </textElement>
                    <text><![CDATA[Book availability in department]]></text>
                </staticText>
                <textField>
                    <reportElement x="20" y="50" width="555" height="30" forecolor="#FFFFFF"/>
                    <box>
                        <pen lineWidth="1.0" lineColor="#FFFFFF"/>
                        <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                        <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                        <bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                        <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    </box>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font fontName="SansSerif" size="20" isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{library_department_name}]]></textFieldExpression>
                </textField>
            </frame>
        </band>
    </title>
    <detail>
        <band height="204">
            <componentElement>
                <reportElement x="0" y="4" width="555" height="200" forecolor="#FFFFFF">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
                    <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
                    <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
                    <property name="net.sf.jasperreports.export.headertoolbar.table.name" value=""/>
                    <property name="com.jaspersoft.studio.components.autoresize.proportional" value="true"/>
                </reportElement>
                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                    <datasetRun subDataset="Data">
                        <dataSourceExpression><![CDATA[$P{REPORTING}.dataset("Data")]]></dataSourceExpression>
                    </datasetRun>
                    <jr:column width="188">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="188" height="30" forecolor="#FFFFFF"/>
                                <box>
                                    <pen lineColor="#FFFFFF"/>
                                </box>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font fontName="SansSerif" size="12" isBold="true"/>
                                </textElement>
                                <text><![CDATA[Literature Type]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="188" height="30"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font fontName="SansSerif" size="12"/>
                                </textElement>
                                <textFieldExpression><![CDATA[$F{literature_type_name}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="186">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="186" height="30" forecolor="#FFFFFF"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font fontName="SansSerif" size="12" isBold="true"/>
                                </textElement>
                                <text><![CDATA[Book Amount]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="186" height="30"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font size="12"/>
                                </textElement>
                                <textFieldExpression><![CDATA[$F{books_instances_amount}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="181">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="181" height="30" forecolor="#FFFFFF"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font fontName="SansSerif" size="12" isBold="true"/>
                                </textElement>
                                <text><![CDATA[Reference Book Amount]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField isBlankWhenNull="false">
                                <reportElement x="0" y="0" width="181" height="30" forecolor="#000000"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font size="12"/>
                                </textElement>
                                <textFieldExpression><![CDATA[$F{reference_books_instances_amount}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </detail>
    <pageFooter>
        <band height="17">
            <textField>
                <reportElement mode="Opaque" x="0" y="4" width="515" height="13" backcolor="#E6E6E6"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report">
                <reportElement mode="Opaque" x="515" y="4" width="40" height="13" backcolor="#E6E6E6"/>
                <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
            </textField>
            <textField pattern="M/d/yy">
                <reportElement x="0" y="4" width="280" height="13"/>
                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
</jasperReport>

The table in this template is bound to a subDataset. The title element uses the Header band data directly. You can open the template file in JasperReports visual designer to see the report layout.

Upload the new template to the application, selecting any output type, and make it default:

Figure 72. Report templates

Run the report to make sure it works:

Figure 73. Report result

7.4. Sample HTML/PDF Report with Paging, Headers and Footers

Let’s imagine that we now want to create a report with landscape orientation, page numbers, fixed header and footer on each page, configured using special CSS rules and properties. The output format is HTML exported to PDF.

This sample report with its demo project is also available on CUBA GitHub.

Data model

Our report will display information on the Client entity. It contains two String attributes: title and summary, we will use them in our report structure.

public class Client extends StandardEntity {

    @NotNull
    @Column(name = "TITLE", nullable = false)
    protected String title;

    @Lob
    @Column(name = "SUMMARY")
    protected String summary;

    ...
}

Creating a report

Let’s create a simple report without parameters. The JPQL query will select all clients with their local attributes: title and summary.

Figure 74. Report data structure

Report template.

Now let’s create the report template file. Here we define header and footer blocks that will be printed on each PDF page. Also we use special page-break-before: always CSS property. It will generate a page break before each client info block.

As you can see, we use FreeMarker statements to insert data to our template. See complete FreeMarker reference here: https://freemarker.apache.org/docs/.

<body>
<h1>Clients report</h1>

<!-- Custom HTML header -->
<div class="header">
    Annual Report of our Company
</div>

<!-- Custom HTML footer -->
<div class="footer">
    Address: William Road
</div>

<#assign clients = Root.bands.Clients />

<#list clients as client>
<div class="custom-page-start" style="page-break-before: always;">
    <h2>Client</h2>

    <p>Name: ${client.fields.title}</p>
    <p>Summary: ${client.fields.summary}</p>
</div>
</#list>
</body>

CSS rules

We will use the following CSS code to tune our PDF page representation:

body {
    font: 12pt Georgia, "Times New Roman", Times, serif;
    line-height: 1.3;
}
@page {
    /* switch to landscape */
    size: landscape;
    /* set page margins */
    margin: 0.5cm;
    @top-center {
        content: element(header);
    }
    @bottom-center {
        content: element(footer);
    }
    @bottom-right{
        content: counter(page) " of " counter(pages);
    }
}

This CSS code will set header/footer positions:

div.header {
    display: block;
    text-align: center;
    position: running(header);
    width: 100%;
}

div.footer {
    display: block;
    text-align: center;
    position: running(footer);
    width: 100%;
}

After that we need to fix paddings of the main content to prevent content and header/footer overlapping:

/* Fix overflow of headers and content */
body {
    padding-top: 50px;
}
.custom-page-start {
    margin-top: 50px;
}

So, the complete paging-template.html file will look as below:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Invoice</title>
    <style type="text/css">
 body { font: 12pt Georgia, "Times New Roman", Times, serif; line-height: 1.3; padding-top: 50px; } div.header { display: block; text-align: center; position: running(header); width: 100%; } div.footer { display: block; text-align: center; position: running(footer); width: 100%; } @page { /* switch to landscape */ size: landscape; /* set page margins */ margin: 0.5cm; @top-center { content: element(header); } @bottom-center { content: element(footer); } @bottom-right { content: counter(page) " of " counter(pages); } } .custom-page-start { margin-top: 50px; }
  </style>
</head>
<body>
<h1>Clients report</h1>

<!-- Custom HTML header -->
<div class="header">
    Annual Report of our Company
</div>

<!-- Custom HTML footer -->
<div class="footer">
    Address: William Road
</div>

<#assign clients = Root.bands.Clients />

<#list clients as client>
<div class="custom-page-start" style="page-break-before: always;">
    <h2>Client</h2>

    <p>Name: ${client.fields.title}</p>
    <p>Summary: ${client.fields.summary}</p>
</div>
</#list>
</body>
</html>

Upload the template file and run the report.

Figure 75. Template editor

As you can see, the report contains the title page and page breaks before each client’s page. There are headers and footers on each page as well.

Figure 76. Report result

7.5. HTML Report with Groovy template engine

The example is based on the sample Library application, which source code is available on GitHub. Let’s create a report that displays the list of book publications for the selected city. The output format is HTML.

Create a report with JPQL dataset:

Figure 77. Report data structure

The BookPublications band outputs the list of book’s publications by running the following JPQL query:
BookPublications dataset
```
select
book.name as "book",
publisher.name as "publisher"
from library$BookPublication e
left join e.book book
left join e.publisher publisher
 where e.town.id = ${city}
```
This query uses the external report parameter – city. The parameter has the Entity type; however, in JPQL queries you can compare it directly with entity identifier fields; the conversion will be done automatically.
Describe the report parameter:

The Parameters and Formats tab contains one declared report external parameter – City:

Figure 78. The report parameter

When running the report, the user will have to enter this parameter. The city selection will be performed via the library$Town.browse screen, available in the application.

Create a report template

The Templates tab contains a single defined HTML template, generated by default with FreeMarker tags.

Create the new HTML file with the following content:

PublicationsTemplate

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="ru">
   <head>
      <title> Publications by city </title>
      <style type="text/css">
 body {font: 12pt Georgia, "Times New Roman", Times, serif; line-height: 1.3; padding-top: 30px;} tbody tr {height:40px; min-height:20px}
      </style>
   </head>
   <body>
      <h1>Publications, published in <% out << "${Root.fields.city.name}"%></h1>
          <% def bookPublications = Root.bands.BookPublications.fields %>
      <table class="report-table" border="1" cellspacing="2" >
         <thead>
         <tr>
            <th>Book</th>
            <th>Publisher</th>
         </tr>
         </thead>
         <tbody>
            <% bookPublications.title.eachWithIndex{elem, index -> out << "<tr><td> ${bookPublications.book[index]} </td><td> ${bookPublications.publisher[index]} </td></tr>"}%>
         </tbody>
      </table>
   </body>
</html>

The value of the input parameter is used to generate the report title: ${Root.fields.city.name}.

The variable bookPublications is defined below:

<% def bookPublications = Root.bands.BookPublications.fields %>

This variable is used in the table’s body to display the report fields.

<% bookPublications.title.eachWithIndex{elem, index -> out << "<tr><td> ${bookPublications.book[index]} </td><td> ${bookPublications.publisher[index]} </td></tr>"}%>

Upload the new template to the application, select HTML output type, select Groovy template in the Template type radiobutton and make it default:

Figure 79. Report template editor

Run the report to make sure it works:

Figure 80. Report result

8. Reporting REST API

The universal REST API provides the following functionality for the Reporting Add-on:

Getting a list of reports.
Getting the details on a specific report.
Running a report and getting the result.
Getting Swagger documentation.

REST API uses the OAuth2 protocol for authentication and supports anonymous access.

To make a report available through the REST API, select the Visible for REST API checkbox on the Roles and Screens tab:

Figure 81. Visible for REST API checkbox

Below we provide a formal description of some features specific to reporting. For more information on how to get an OAuth token and other REST API features, see REST API add-on documentation.

Getting the list of existing reports

The list of existing reports can be retrieved with the following GET request:

/rest/reports/v1/report

For example:

GET http://localhost:8080/app/rest/reports/v1/report HTTP/1.1

Authorization: Bearer f5a2b4b1-a121-4563-9519-dd3c0b116689
Content-Type: application/json

The response body will contain the brief information on the reports marked as Visible for REST API:

{
    "id": "2dd27fbf-8830-416a-899f-339543f8f27a",
    "name": "Books by author"
},
{
    "id": "2f07c9fe-5d6d-48cf-876f-8c02ac1f6c3c",
    "name": "Book availability in department"
}

Getting the report info

The detailed information on a specific report is retrieved with the following GET request:

/rest/reports/v1/report/{id}

The last part of the query here is the report identifier, for example:

GET http://localhost:8080/app/rest/reports/v1/report/2dd27fbf-8830-416a-899f-339543f8f27a HTTP/1.1

The returned JSON object will contain the following information on the passed report:

ID
name
system code (if exists)
report templates
parameters and their types

{
    "id": "2dd27fbf-8830-416a-899f-339543f8f27a",
    "name": "Books by author",
    "templates": [
        {
            "code": "DEFAULT",
            "outputType": "XLS"
        }
    ],
    "inputParameters": [
        {
            "name": "Author",
            "alias": "author",
            "type": "ENTITY",
            "required": true,
            "hidden": false,
            "entityMetaClass": "library$Author"
        }
    ]
}

Running a report

To run a report, send the following POST request:

/rest/reports/v1/run/{id}

The last part of the query here is the report identifier, for example:

POST http://localhost:8080/app/rest/reports/v1/run/2dd27fbf-8830-416a-899f-339543f8f27a HTTP/1.1

The report parameters are passed in the request body:

{parameters: [{name: 'author',value: '4b3a21b0-d6b7-4161-b0b6-55f118fbaac5'}]}

To print a report with a non-default template, pass the template code in the request body:

{template: 'Template_1', parameters: [{name: 'author',value: '4b3a21b0-d6b7-4161-b0b6-55f118fbaac5'}]}

Getting the Swagger documentation

The complete Swagger documentation on the Reporting Add-on can be retrieved with the GET request on the address:

http://localhost:8080/app/rest/reports/v1/docs/swagger.json

Appendix A: Installing and Configuring OpenOffice

The report generator uses the OpenOffice / LibreOffice package to output reports in PDF and DOC formats. The installation to the computer containing the application server and configuration of this package is described below.

Installing and Configuring OpenOffice for Microsoft Windows

Download the application at http://openoffice.org.
Install the application.
In the reporting.office.path application property in the app.properties file of the core module, specify the path to OpenOffice.org, for example:

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

Installing and Configuring LibreOffice for Microsoft Windows

Download the application at http://www.libreoffice.org/download/download/.
Install the application.
In the reporting.office.path application property in the app.properties file of the core module, specify the path to LibreOffice, for example:

reporting.office.path = C:/Program Files (x86)/LibreOffice 5/program

Installing and Configuring LibreOffice on Ubuntu Server

Install the libreoffice package, for example, by running the following command:
```
$ sudo apt-get install libreoffice
```
In the reporting.office.path application property in the app.properties file of the core module, specify the path to LibreOffice:
```
reporting.office.path = /usr/lib/libreoffice/program
```
If the server does not have window interface installed, LibreOffice will start with the error, Caused by: java.awt.HeadlessException: No X11 DISPLAY variable was set, but this program performed an operation which requires it, or will simply terminate without error messages. To resolve this, set the reporting.displayDeviceAvailable application property:
```
reporting.displayDeviceAvailable = false
```

You can run the following command to diagnose errors when starting LibreOffice:

$ strace -e trace=signal /usr/lib/libreoffice/program/soffice.bin --headless --accept="socket,host=localhost,port=8100;urp" --nologo --nolockcheck

For Ubuntu users who installed tomcat using apt, it is necessary to copy ~/.config/libreoffice to $CATALINA_HOME. For tomcat8, it is /usr/share/tomcat8.

After that, you should change the owner of this folder:

sudo mkdir /usr/share/tomcat8/.config
sudo cp -pr ~/.config/libreoffice /usr/share/tomcat8/.config/
sudo chown -R tomcat8.tomcat8 /usr/share/tomcat8/.config/

Installing and Configuring LibreOffice for macOS

Download the application at https://www.libreoffice.org/get-help/install-howto/os-x/.
Install the application.
In the reporting.office.path application property, specify the path to LibreOffice.app, for example:

reporting.office.path = /Applications/LibreOffice.app/Contents/MacOS

Appendix B: Application Properties

This section describes the application properties related to the report generator in the alphabetical order.

reporting.backgroundReportProcessingTimeoutMs: Defines the processing timeout in milliseconds for the report execution if the reporting.useBackgroundReportProcessing is set to true.

Default value: 10000.

Stored in the database.

Used in the Middleware block.

reporting.curl.path: The report generator uses the cURL tool to create reports by URL. The property value represents the path to the tool.

Default value: curl.

Used in the Middleware block.

reporting.displayDeviceAvailable: false value allows running OpenOffice/LibreOffice in the server operating system without a window interface.

Default value: false

Used in the Middleware block.

reporting.enableTabSymbolInDataSetEditor: Defines whether TAB key should be handled as \t symbol instead of focus navigation in Script fields of the report editor.

Default value: false

Used in the client blocks.

reporting.executionHistory.cleanup.days: The scheduled task deletes all execution history records older than the specified number of days. If the property value is 0, the scheduled task does not consider this property when deleting records. See Cleanup the History for details on setting up the scheduled task.

Default value: 730

Stored in the database.

Used in the Middleware block.

reporting.executionHistory.cleanup.itemsPerReport: The scheduled task leaves no more than this number of execution history entries for every report. It is not recommended to use a number greater than 1000 as the value of this property. If the property value is 0, the scheduled task does not consider this property when deleting records. It is useful for frequently run reports such as email templates, invoicing document templates, etc. See Cleanup the History for details on setting up the scheduled task.

Default value: 1000.

Stored in the database.

Used in the Middleware block.

reporting.executionHistory.enabled: Activates the report execution history mechanism.

Default value: false.

Stored in the database.

Used in the Middleware block.

reporting.executionHistory.saveOutputDocument: If set to true, report results files are saved to the file store, if the reporting.executionHistory.enabled property is on. See Output documents for details.

Default value: false.

Stored in the database.

Used in the Middleware block.

reporting.fontsDir: Path to the fonts directory for converting HTML to PDF.

For example: reporting.fontsDir = C:/Windows/Fonts.

Used in the Middleware block.

reporting.docFormatterTimeout: Sets the LibreOffice timeout in seconds for converting DOCX/XLSX to HTML/PDF.

When the time is up, the user will receive an error message.

Default value: 20

Used in the Middleware block.

reporting.office.docx.useOfficeForDocumentConversion: Turns on using OpenOffice to convert the report having DOCX template to HTML/PDF, which significantly increases the conversion accuracy.

Default value: false

Used in the Middleware block.

reporting.office.path: Sets the path to OpenOffice.

Default value: /

Used in the Middleware block.

reporting.office.ports: Specifies the comma- or vertical bar-separated list of available ports for OpenOffice/LibreOffice.

For example: reporting.office.ports = 8100|8101|8102|8103|8104|8105.

Default value: 8100, 8101, 8102, 8103.

Used in the Middleware block.

reporting.putEmptyRowIfNoDataSelected: Sets the mode when the bands which datasets have returned no records are still displayed once.

Default value: true

Used in the Middleware block.

reporting.useBackgroundReportProcessing: Allows you to set the report execution as a background task. This property is used in order to add the cancel option.

Default value: false.

Stored in the database.

Used in the Middleware block.