Bands and Datasets

The internal report structure is composed of bands. A band represents a section of the report that may appear zero or more times in the final document.

A band is populated with data provided by one or multiple datasets associated with the band.

In the report template, the bands are marked up in a way that depends on the file format. The report definition includes the hierarchical structure of bands, corresponding to the template bands.

The picture below shows an example of bands used in a report:

Figure 1. Sample report bands

The Root band in the report definition is predefined, however, it may not be used in a template.

You can find examples of various bands and datasets in the Report Generation guide.

Bands

Bands Tab

The Bands tab of the runtime report editor allows you to define the band hierarchy and datasets for each band.

A report band has the following parameters:

Name — band name, unique within the report. It must contain only Latin letters, numbers, and underscores.

If the band name starts with header, its data will not be printed when using the table formatter.
Orientation — band orientation: Horizontal, Vertical or Crosstab.
- Horizontal bands are copied downwards. They may contain sub-bands.
- Vertical bands are copied to the right.
- Crosstab bands are copied to the right and downwards as a matrix.
Parent — parent band.
Multiple Datasets — select this checkbox if you want to define more than one dataset for the band.
Dataset type — the type of dataset.

Each band includes one or more datasets. When running a report, 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 the number of rows in its longest dataset.

Fields defined in the report template are replaced with corresponding values from the dataset when the report is executed. When defining datasets, you can use report external parameters and fields from other bands, which lets you link bands.

Each report has the Root band. You can create datasets in it and refer to their fields from other bands.

Note that the Dataset name column, available when working with multiple datasets, is for user convenience only.

@BandDef Annotation

The @BandDef allows you to define bands when creating a report at design time. It has the following attributes:

name — band name, unique within the report. It must contain only Latin letters, numbers, and underscores.
parent — name of the parent band. Can be omitted only for the Root band.
root — must be set to true only for the Root band. false by default.
orientation — band’s orientation defined by the Orientation enum: HORIZONTAL, VERTICAL, CROSS. It’s HORIZONTAL by default.
dataSets — datasets defined by the array of @DataSetDef annotations.

The report must include the definition of the Root band with name = "Root" and root = true attributes.

The order of @BandDef annotations in the report definition Java class is important. It affects the printing order of bands on the same level of the hierarchy.

Datasets

SQL Dataset

SQL dataset is produced as a result of SQL query execution. It is recommended to use aliases for query result fields using 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 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 enabled parameter:

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

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

Merge multiple datasets: 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.

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

Query preprocessing in bands

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

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

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

In this case, the query should look like this:

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}

JPQL Dataset

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

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

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

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

When using a JPQL query as a data source for a report, please note that the query is executed directly against the database and bypasses DataManager. This means that the system’s regular data access security checks (row-level and resource) are not applied automatically. You must ensure that your JPQL queries only retrieve data that the user is authorized to access. It is your responsibility to implement any necessary security checks in your queries.

You can also use Link field to merge multiple datasets, use Data store to query additional data store or activate query preprocessing by checking the Preprocess query as Groovy template checkbox below the band editor.

Groovy Dataset

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

The following objects are passed into the script:

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 io.jmix.reports.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')
```
currentAuthentication - an object of the io.jmix.core.security.CurrentAuthentication type associated with the currently authenticated user. For example:
```
def user = currentAuthentication.getUser()
```
metadata - an object of the io.jmix.core.Metadata type, providing access the application metadata. For example:
```
def metaClass = metadata.getClass('User')
```
dataManager - an object of the io.jmix.core.DataManager type that provides CRUD functionality. For example:
```
def book = dataManager.load(Book.class).id(bookId).fetchPlan("book.edit").one()
```
timeSource - an object of the io.jmix.core.TimeSource type used to obtain the current time. For example:
```
def currentDate = timeSource.currentTimestamp()
```
applicationContext - an object of the org.springframework.context.ApplicationContext type that provides access to managed beans. For example:
```
def accountService = applicationContext.getBean(AccountService);
```

Entity Dataset

Entity dataset consists of a single row and is produced using attributes of a single JPA 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 tab. The value in the Entity parameter name field must match the parameter name.

The report template must contain fields with entity attribute names. Attribute names can use names in the existing fetch plan by checking Use existing fetch plan or can be selected by the Select entity attributes button.

As the reporting engine always reloads entities in the Entity dataset from the database, you cannot use it for DTO entities or JPA entity instances not saved to the database. Instead, use the Groovy dataset and access the entity passed as a parameter in the following way:

def entity = params['entity']

return [['title': entity.title,
        'description': entity.description]]

List of Entities Dataset

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

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

As the reporting engine always reloads entities in the List of entities dataset from the database, you cannot use it for DTO entities or JPA entity instances not saved to the database. Instead, use the Groovy dataset and access the list of entities passed as a parameter in the following way:

return params['entities'].collect {[
    'title': it.title,
    'description': it.description
]}

JSON Dataset

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

Groovy script

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

For example:

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

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

For example:
https://jsonplaceholder.typicode.com/users
Parameter

The report external parameter of String type which contains the JSON data must be described in the Parameters 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 the documentation.

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

To do this, switch to the Value formats tab of the report detail view and open the formatter dialog. For example, for the field bookPublication.dateTime Groovy-script will look like this:

import java.text.SimpleDateFormat

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

return simpleNewDateFormat.format(oldDate)

@DataSetDef Annotation

The @DataSetDef annotation allows you to define datasets when creating a report at design time. It has the following attributes:

name — dataset name. It is used in datasets of the DELEGATE type to link the dataset definition with the method that returns the delegate.

In a cross-tab band (@BandDef.orientation = Orientation.CROSS), the datasets must have the following names:
- Column headers: ${band_name}_dynamic_header
- Row headers: ${band_name}_master_data
- Main content: ${band_name}
type — dataset type defined by the DataSetType enum. Values of this enum correspond to the types of runtime datasets described above.

Usually, design-time reports use datasets of the DELEGATE type. In this case, they are linked by name with methods annotated with @DataSetDelegate annotation.

@DataSetDelegate Annotation

The @DataSetDelegate annotation allows you to define a method that returns a delegate for executing a dataset logic. It has the name attributes, which should be equal to the name of the corresponding dataset.

The annotated method must not have any parameters and should return the ReportDataLoader functional interface.