Report Data Structure

Defining Data Structure

When creating or editing a report, Report structure tab is shown by default:

Figure 1. Report Data Structure

General report properties

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.

Report bands definition

The important 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, may contain sub-bands
- Vertical – bands are copied to the right
- Crosstab – bands are copied to the right and downwards as a matrix.
Parent band – parent band.

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.

Field names can be used in the report template and will be replaced with corresponding values from the dataset when the report is produced. When describing datasets, you can use report external parameters and fields from other bands – this allows creation of 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.

Dataset Types

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 jmxrpr_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 jmxrpr_user u
where u.group_id = ${group.groupId}
    and u.enabled = ${enabled}
    and u.delete_ts 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 jmxrpr_User u
where u.group.id = ${group.groupId}
    and u.enabled = ${enabled}

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

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 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')
```
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('jmxrpr_User')
```
dataManager – an object of the io.jmix.core.DataManager type that provides CRUD functionality. For example:
```
def book = dataManager.load(Book).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 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. 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 and Formats tab. The value in the Entity 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 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 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 Parameters and Formats tab of the report editor and open the formatter editor. For example, for the field bookPublication.dateTime Groovy-script will look like this:

import java.text.SimpleDateFormat

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

return simpleNewDateFormat.format(oldDate)

Was this page helpful?

Thank you for your feedback

Something went wrong