Report Data Structure - CUBA Platform. Report Generator

1.1. Report Data Structure

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

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.
Orientation - band orientation: Horizontal or Vertical. Horizontal bands are copied downwards, vertical - to the right. 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.

Supported dataset types are provided below:

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

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

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.

Groovy - the 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:

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 - parent band as an object of the com.haulmont.cuba.core.Persistence type. You can use this object to get a parent band field value using the EntityManager method, for example:

def tx = persistence.createTransaction()
try {
    def em = persistence.getEntityManager()
    def query = em.createQuery('select g from sec$Group g')
...
    tx.commit()
} finally {
    tx.end()
}

For working with an additional data store, pass its name as a parameter to createTransaction() and getEntityManager() methods. By default, the main database is used.

def tx = persistence.createTransaction('myStore')
try {
    def em = persistence.getEntityManager('myStore')
...
    tx.commit()
} finally {
    tx.end()
}

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

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

Tip	You can use static methods of the `AppBeans` class to access any Spring beans of the middleware tier, for example: def dataWorker = com.haulmont.cuba.core.global.AppBeans.get('cuba_DataWorker')

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

Entity - the 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.
List of entities - the 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.