A newer version is available at the documentation home.

Preface

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

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

Further Reading

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

The core of the platform report generator is the YARG framework, which is distributed under the free Apache 2.0 license. For more details, see the article in our blog. 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 Libre/Open Office 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. Quick Start

This chapter provides the hands-on examples of using the Report generator. All the examples are based on the sample Library application which is available in CUBA Studio samples.

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 CreateUsing wizard in the Reports screen.

reports wizard main
Figure 1. Reports Wizard

Three types of reports can be created using the wizard:

  1. Report for a single entity.

  2. Report for a list of given entities.

  3. Report for a list of entities filtered by query.

Report design is a three steps process:

  1. Creating a data structure of a report.

  2. Editing of report regions.

  3. 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 TablePrintFormAction and EditorPrintFormAction actions.

2.1. Report for a Single Entity

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

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

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

single entity step 1
Figure 2. Report for a Single Entity

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

single entity attributes
Figure 3. 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, Add tabulated region, the 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.

single entity step 2
Figure 4. Report creation

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

single entity test running
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.

single entity step 3
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 buttons.

single entity reports list
Figure 7. Reports browser

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

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

Then we need to implement TablePrintFormAction in the controller:

@Inject
private Button printDetails;

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

Finally, 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.lookup screen from the dropdown list to the table below:

single entity screens
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.

single entity running
Figure 9. Print details

The output is as follows:

single entity result
Figure 10. Report result

2.2. Report for a List of Entities

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

  1. Report for manually selected instances of a particular entity

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

list of entities step 1
Figure 11. Report for a List of Entities

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

list of entities attributes
Figure 12. 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 NextSave to save the report. The report looks as follows in the report editor:

list of entities editor
Figure 13. Report data structure

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

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

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

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

@Inject
private Button printList;

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

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

Finally, 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.lookup screen from the screens dropdown to the table below:

list of entities screens
Figure 14. Add screen

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

list of entities running
Figure 15. Print selected

The output is as follows:

list of entities result
Figure 16. Report result

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

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

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 (>=).

query parameter
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.

list of entities attributes
Figure 19. Select Entity Attributes

Press NextSave to save the report.

query step 2
Figure 20. Entity attributes

The report will look as follows:

query editor
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.

query parameter rename
Figure 22. Parameters and Formats tab

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

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

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

After that, inject the button in the screen controller:

@Inject
private Button reportBtn;

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

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

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

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

query running
Figure 23. Run report

The output is as follows:

query result
Figure 24. Report result

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

reporting
  • YARG - framework, which is the core of the report generator.

  • Report Engine integrates YARG into CUBA platform 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.

3.1. Report Data Structure

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

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

3.1.1. SQL Dataset

SQL dataset is produced as 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
Warning

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 (see Developer’s Manual), 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}

3.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 the main database. If you want to query entities from an additional data store (see Developer’s Manual), set its name in the Data store field.

3.1.3. Groovy Dataset

Groovy dataset is produced as result of a Groovy script execution. The script must return 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 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
Tip

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')

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

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

3.1.6. JSON Dataset

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

  1. 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
                }
              ]
            }
            '''
  2. URL

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

    For example:

    https://jsonplaceholder.typicode.com/users
  3. 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/.

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

report template
  • 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 - 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 - 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.

    report template alterable

3.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 FormulasName Manager menu command in Microsoft Office, and the InsertNamesManage command in OpenOffice. Vice versa, each part of the sheet you want to show should be a band in 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:

report template xls

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 the PDF conversion.

csv output

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

csv template
Figure 25. CSV template example
csv report
Figure 26. 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.

csv report editor
Figure 27. CSV inline editor

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

Warning

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.

report template doc
Warning

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"

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

FreeMarker tags are used to place data (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>

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

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

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

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

Root {
    HeaderBand {
        query = return [[ "name" : "Column1" ],[ "name" : "Column2" ]]
    }
    Band1 {
        query = return [
                ["field1" : "Value 11", "field2" : "Value 12"],
                ["field1" : "Value 21" , "field2" : "Value 22"]
        ]
    }
    Band2 {
        query = return [[ "header" : "Header1" ], [ "header" : "Header2" ]]
        SubBand1 {
            query = return [["header" : 'SubHeader1'] , [ "header" : 'SubHeader2' ]]
        }
    }
}
  • 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. There are two ways to add images to the HTML-report:

  • 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"/>

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.

html report editor
Figure 28. HTML inline editor
3.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>

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

Tip

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.4.1.jar;yarg-2.0-SNAPSHOT.jar

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

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

  • report - report descriptor of the com.haulmont.yarg.structure.Report type.

  • rootBand - root band data of the com.haulmont.yarg.structure.BandData type.

  • params - 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 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:

class defined template

3.2.7. Chart Template

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

chart template pie
  • 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, colours will be chosen automatically.

  • Units - this text will be added to legend values.

Serial chart:

chart template serial
  • 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, colours will be chosen automatically.

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

To use the pivot table formatter, select Pivot Table as the output type in the report template editor. The resulting table is displayed in the ReportsShow Pivot Tables screen of your web application.

pivot template result

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.

pivot template renderer
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.

pivot template aggregation
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.

pivot template properties
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.

pivot template custom properties

3.2.9. Table Formatter

Table output does not require the report template, as the data will be displayed on a special screen of the application.

To use table formatter, select Table as the output type in the report template editor.

report table output

The report generator will get the band data and draw a sortable table for each band from 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.

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.

show report table

3.2.10. Output format compliance matrix

Template / Output XLSX XLS CSV DOCX DOC PDF HTML Chart

XLSX

+

+

+ 1

+ 1

XLS

+

+ 1

CSV

+

DOCX

+

+ 2

+ 2

DOC

+

+ 1

HTML

+

+

Chart

+

JRXML

+

+

+

+

+

+

+

1 - OpenOffice/LibreOffice must be installed for output.

2 - depending on the reporting.openoffice.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.

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

report parameter

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

report parameter transformation

In the Validation tab, you can define a Groovy scripts 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.

  1. You can enable validation of each parameter in the Validation tab of 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.

      report parameter validation
  2. 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.

    cross parameter validation

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

report formatter
  • Value name - report field name with the band prefix, for example Book.name.

  • Format string - field format. For number values, you must specify the format according to the java.text.DecimalFormat rules, for dates - java.text.SimpleDateFormat.

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

  • In order to insert an image, you must 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 the FileDescriptor, you can 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.

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

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

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

4. Running Reports

This section describes how to run created reports.

4.1. Running from the Generic Reports Browser

The easiest way to run reports is from the generic browser, which is available in the ReportsRun 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.

4.2. Running Reports from Screens

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

Action types and examples of their use are provided below.

  • com.haulmont.reports.gui.actions.RunReportAction - action showing the list of all available reports. When a user selects a report from the list, the parameters input form is displayed (if any were defined) and the report is run.

    Below is an example of using the action together with a button declared in the screen XML descriptor:

    • XML-descriptor

      <layout>
          <table id="bookTable">
              ...
              <buttonsPanel id="buttonsPanel">
                  ...
                  <button id="reportButton"
                          icon="PRINT"/>
              </buttonsPanel>
          </table>
    • Controller

      @Inject
      private Button reportButton;
      
      @Override
      public void init(Map<String, Object> params) {
          reportButton.setAction(new RunReportAction("report"));
      }
    • messages.properties

      report = Report
  • com.haulmont.reports.gui.actions.TablePrintFormAction - an action associated with a table displaying a list of entity instances. 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 table. 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 table is passed into it.

    • If the parameter has the Entity type, and the table 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 table 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 action in a button and the table context menu:

    • XML descriptor

      <layout>
          <table id="bookTable">
              ...
              <buttonsPanel id="buttonsPanel">
                  ...
                  <button id="reportButton"
                          icon="PRINT"/>
              </buttonsPanel>
          </table>
    • Controller

      @Inject
      private Button reportButton;
      
      @Inject
      private Table bookTable;
      
      @Override
      public void init(Map<String, Object> params) {
          TablePrintFormAction action = new TablePrintFormAction("report", bookTable);
          bookTable.addAction(action);
          reportButton.setAction(action);
      }
    • messages.properties

      report = Report
  • 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="windowActionsBox">
          ...
          <hbox id="windowActionsBox"
                spacing="true">
              <iframe id="windowActions"
                      screen="editWindowActions"/>
              <button id="reportButton"/>
          </hbox>
      </layout>
    • Controller

      @Inject
      private Button reportButton;
      
      @Override
      public void init(Map<String, Object> params) {
          reportButton.setAction(new EditorPrintFormAction("report", this, null));
      }
    • messages.properties

      report = Report

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

run cancel

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:

run cancel 2

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

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

5. Sample Reports

5.1. Sample XLS Report

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

To use reports in your project, activate the reports item in the App components list on the Project properties page of CUBA Studio.

Open the ReportsReports 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.

  1. Report data structure.

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

  2. Report parameters.

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

    sample1 param

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

  3. Report templates.

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

    sample1 template
  4. Report Name Localization.

    The Localization tab contains the report name for the Russian locale:

    ru = Книги по автору

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

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

  1. <band_name>_dynamic_header - the data from this dataset is copied to the right like a vertical band with table columns header.

  2. <band_name>_master_data - the data from this dataset is copied downwards like a horizontal band with table rows header.

  3. <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 may have the following structure:

crosstab structure
  • 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 Y-coordinate of the cell, and orders_dynamic_header@header_id (the month name) as the X-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_)

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.

crosstab template 2
crosstab names regions

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

crosstab result

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

5.3. Sample JRXML Report

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

To use reports in your project, activate the reports item in the App components list on the Project properties page of CUBA Studio.

Open the ReportsReports 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.

  1. Report data structure.

    sample jasper

    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.id = ?1 and bi.bookPublication.book.literatureType.id = ?2 ''')
                      .setParameter(1, params['library_department'])
                      .setParameter(2, lt)
                      .getSingleResult()
              def refCount = em.createQuery(''' select count(bi) from library$BookInstance bi where bi.libraryDepartment.id = ?1 and bi.bookPublication.book.literatureType.id = ?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.

  2. Report parameters.

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

    sample jasper 2

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

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

    sample jasper 3

Run the report to make sure it works:

sample jasper 4

5.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 are also available on CUBA GitHub.

  1. 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;
    
        ...
    }
  2. Creating report

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

    example html 1
  3. 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 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>
  4. 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>
  5. Upload the template file and run the report.

    example html 3

    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.

    example html 2

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

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

Installing and Configuring LibreOffice for Microsoft Windows

reporting.openoffice.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.openoffice.path application property, specify the path to LibreOffice:

    reporting.openoffice.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.displayDeviceUnavailable application property:

    reporting.displayDeviceUnavailable = true
  • 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
Tip

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

reporting.openoffice.path = /Applications/LibreOffice.app

Appendix B: Application Properties

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

reporting.displayDeviceUnavailable

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

Path to the fonts directory for converting HTML to PDF.

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

Used in the Middleware block.

reporting.openoffice.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.openoffice.path

Sets the path to OpenOffice.

Default value: /

Used in the Middleware block.

reporting.openoffice.ports

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

For example: reporting.openoffice.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.

. . .