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 Create → Using wizard in the Reports screen.
Three types of reports can be created using the wizard:
-
Report for a single entity.
-
Report for a list of given entities.
-
Report for a list of entities filtered by query.
Report design is a three steps process:
-
Creating a data structure of a report.
-
Editing of report regions.
-
Saving the report.
The created report can be modified in the usual manner using the report editor and run through the generic report browser, or using 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:
Next, specify the report type: Report for 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 
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 
/
.
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.
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.
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.
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.
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:
Now you can run the report for any publication by selecting it in the table and simply clicking the Print details button.
The output is as follows:
2.2. Report for a List of Entities
Reports wizard allows you to create two types of reports for a list of entity instances:
-
Report for manually selected instances of a particular entity
-
Report for entity instances filtered by a certain request.
Let us have a look at the first report type. Let us assume we want to obtain a list of all book instances in the library (library$BookInstance entity) with titles and library departments they belong to.
At the first stage, we specify the report details:
-
Entity - report entity -
library$BookInstance. -
Template type - output format - XSLX.
-
Report name - report name -
'Book items location'.
After that, select the type of the report (Report for list of entities) and click Next.
As per the task, we select BookItem.Publication.Book.Name and BookItem.LibraryDepartment.Name in the attributes selection window.
Click ОК and move to the second stage of report regions editing.
The report template for a list of entities restricted to have only one region that displays data in tabular form. Adding new regions is not allowed, but you can edit an existing set of data by clicking on the link with the list of attributes, or remove an existing region and recreate it.
In this case, we do not need to make any changes. Click Next → Save to save the report. The report looks as follows in the report editor:
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:
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.
The output is as follows:
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.
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 (>=).
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.
Press Next → Save to save the report.
The report will look as follows:
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.
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.
The output is as follows:
2.4. Report with Chart Output
Using report wizard, you can create reports with chart output in the same way as any other report types. The only difference is that you will need to configure the chart template after finishing the report in the wizard.
-
Start creating a report as described in the previous chapters.
Figure 25. Chart Template in Wizard - Step 1 -
For the chart report, select countable entity attributes - they will be later used for the value axis.
Figure 26. Chart Template in Wizard - Select Attributes -
Complete the next step of the report creation.
Figure 27. Chart Template in Wizard - Step 2 -
At the last step, select the chart type - either Pie or Serial - and save the report.
Figure 28. Chart Template in Wizard - Step 3 -
Finally, configure the chart axis on the Templates tab of the report editor.
Figure 29. Chart Template ConfigurationFor more details on charts configuration, see the Charts documentation.
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:
-
YARG- framework, which is the core of the report generator. -
Report Engineintegrates YARG into CUBA platform and provides additional functionality such as access rights to reports and integration with screens. -
Report Designeris 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:
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 uYou 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
GStringTemplateEnginethat 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
createTs2report parameter is passed you need to select one of the query conditions:e.create_ts < ${createTs2}ore.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
createTs2parameter 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_timestampIf
createTs2is 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 thecom.haulmont.cuba.core.global.DataManagertype that provides CRUD functionality. For example:def book = dataManager.load(Book.class).id(bookId).view("book.edit").one; -
metadata- an object of thecom.haulmont.cuba.core.global.Metadatatype, 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 thecom.haulmont.yarg.structure.BandDatatype. This object allows you to get a parent band field value by invoking thegetParameterValue()method, for example:def groupId = parentBand.getParameterValue('groupId') -
persistence- an object of thecom.haulmont.cuba.core.Persistencetype 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 thecom.haulmont.cuba.core.global.Securitytype 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 thecom.haulmont.cuba.core.global.TimeSourcetype 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 currentEntityManagerbecomes 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
activeexternal 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 thecom.haulmont.cuba.security.global.UserSessiontype associated with the currently authenticated user. For example:def user = userSession.currentOrSubstitutedUser -
userSessionSource- an object of thecom.haulmont.cuba.core.global.UserSessionSourcetype which is used to obtain current user session object. For example:def locale = userSessionSource.locale
|
Tip
|
You can use static methods of the |
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:
-
Groovy script
The script provided by the user is supposed to return JSON data as a string.
For example:
return ''' { "items": [ { "name": "Java Concurrency in practice", "price": 15000 }, { "name": "Clear code", "price": 13000 }, { "name": "Scala in action", "price": 12000 } ] } ''' -
URL
The report engine will perform a GET HTTP query against the URL.
For example:
https://jsonplaceholder.typicode.com/users -
Parameter of a String type
The report external parameter of String type which contains the JSON data must be described in the Parameters and Formats tab.
The received JSON tree is queried using the JsonPath query. For example, you can use the $.store.book[*] JsonPath to return all books from the following JSON tree:
{
"store": {
"book": [
{
"category": "reference",
"author": "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{
"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 12.99,
"isbn": "0-553-21311-3"
}
],
"bicycle": {
"color": "red",
"price": 19.95
}
}
}For more details on JsonPath expressions see http://goessner.net/articles/JsonPath/.
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:
-
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, wheretitleis 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.
Figure 33. Alterable Output
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 Formulas → Name Manager menu command in Microsoft Office, and the Insert → Names → Manage command in OpenOffice. Vice versa, each part of the sheet you want to show should be a band in 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:
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.
Figure 35. 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.
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.
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.
|
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 into |
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.nameYou 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\imagesfolder 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
srcattribute. This approach allows you to use variables for theFileDescriptorattributes 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.
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-installerpackage:$ 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 intoByteArrayInputStreamand 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:
|
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 thecom.haulmont.yarg.structure.Reporttype. -
rootBand- root band data of thecom.haulmont.yarg.structure.BandDatatype. -
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:
3.2.7. Chart Template
Chart output type is available if the application project includes the charts component. Resulting chart is displayed in the Reports → Show Charts screen of your web application.
Two types of diagrams are supported: Pie chart and Serial chart. Each type has its own set of parameters.
Pie chart:
-
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:
-
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.
Pivot Table output can only be used as an additional report output, that’s why it is not available in the report wizard. To use the pivot table formatter, switch to the Templates tab in the report editor, click Create and select Pivot Table as the output type of the new template. After that, configure the template settings as described below.
The resulting table is displayed in the Reports → Show Pivot Tables screen of your web application.
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.
- 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.
-
- 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.
-
- 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.
-
-
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.
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.
3.2.10. Output format compliance matrix
| Template / Output | XLSX | XLS | CSV | DOCX | DOC | 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:
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,TimeorDate 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
paramValuealias, the parameters map is available by aliasparams. For example:return "%" + paramValue + "%"You can also use one predefined transformations that add wildcards for text (String) parameters:
-
Starts with, -
Ends with, -
Contains.
Figure 52. Input parameters 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.
-
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 theDataManagertype that provides CRUD functionality. -
metadata- an object of theMetadatatype that provides access to the application metadata. -
security- an object of theSecuritytype used to check user access rights to different objects in the system. -
userSession- an object of theUserSessiontype associated with the currently authenticated user.
Figure 53. Input parameters validation
-
-
Cross-parameter validation can be enabled by checking the Validate checkbox in the Cross parameters validation section of the Parameters and Formats tab. The validation logic is specified by a Groovy script. The script should check whether or not parameter values make sense in relation to each other and call the
invalid()method if they do not. This method will show the user an alert with the given message about the report validation errors.In addition to the variables listed above, the
paramsvariable is passed into the script to access the external report parameters map.
Figure 54. 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:
-
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.DecimalFormatrules, 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 theFileDescriptoridand the link to theFileDecriptorinstance 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 withUTF-8. CSS and thestyleattribute 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 Reports → Run Reports screen. The user must have the right to access this screen. The list will contain all reports that are available to the user in accordance with their role. If the report has external parameters, they will be requested in a special form when running the report.
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.EditorPrintFormActionan 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 = trueThus, the window with the progress bar and the Cancel button will be displayed:
You can also set the processing timeout using the reporting.backgroundReportProcessingTimeoutMs property:
reporting.backgroundReportProcessingTimeoutMs = 30000When the time is up, the task will be canceled regardless the result, and the user will receive an error message:
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 Reports → Reports screen and click on the Import button to import the report. Choose Reports.zip in the project root directory. Two reports will appear in the table, one of them will be Books by author. This report displays the list of book publications for selected author; books will be grouped by book name and publisher. The output format is XLS.
-
Figure 58. Report data structureLet 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_idas 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.nameThis query uses both parent bands fields as parameters -
book_idandpublisher_id. -
-
Report parameters.
The Parameters and Formats tab contains one declared report external parameter - Author:
Figure 59. Report parametersWhen running the report, the user will have to enter this parameter. The author selection will be performed via the
library$Author.lookupscreen, available in the application. -
Report templates.
The Templates tab contains a single defined XLS template, loaded from
BooksByAuthor.xls
Figure 60. Report templates -
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 Reports → Run 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:
-
<band_name>_dynamic_header - the data from this dataset is copied to the right like a vertical band with table columns header. -
<band_name>_master_data - the data from this dataset is copied downwards like a horizontal band with table rows header. -
<band_name>- the dataset named the same as the band it belongs to. It is the main content band that will fulfill the matrix cells.
For example, a crosstab report for the Order entity of the Sales sample application may have the following structure:
-
Here, the
orders_dynamic_headerdataset will return the list of months names:orders_dynamic_header datasetimport 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_datadataset returns names and identifiers of the customers selected by the user as an external report parameter:orders_master_data datasetselect name as name, id as customer_id from SALES_CUSTOMER where id in (${selected_customers}) -
The
ordersdataset will provide data for the matrix cells, which is the order amounts sum for the particular month and customer. It takesorders_master_data@customer_id(the customer id) as the Y-coordinate of the cell, andorders_dynamic_header@header_id(the month name) as the X-coordinate, and fills the matrix cell with theamountvalue.In the example below the report has two more external parameters: the
start_dateand theend_datethat 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 datasetselect 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.
As a result, the report is extended both vertically and horizontally and aggregates the order amounts for each customer and each month:
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 Reports → Reports screen and click on the Import button to import the report. Choose Reports.zip in the project root directory. Two reports will appear in the table, one of them will be Book availability in department. This report displays the list of book publications in the selected department; the default output format is XLS. Let’s create the new JasperReports template for this report.
-
Figure 65. Report data structureLet’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.
-
-
Report parameters.
The Parameters and Formats tab contains one declared report external parameter - Department:
Figure 66. Report parametersWhen running the report, the user will have to enter this parameter. The department selection will be performed via the
library$LibraryDepartment.lookupscreen, available in the application. -
Report template.
The Templates tab contains a single defined XLS template, loaded from
BookAvailability.xls.Create the new JRXML file with the following content:
BookAvailability.jrxml<?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.4.0.final using JasperReports Library version 6.4.1 --> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="books" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"> <property name="template.engine" value="tabular_template"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/> <style name="Table_TH" mode="Opaque" backcolor="#066990"> <box> <topPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Table_CH" mode="Opaque" forecolor="#FFFFFF" backcolor="#06618F" hTextAlign="Center" fontSize="12"> <box> <topPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF" hTextAlign="Center"> <box> <topPen lineWidth="0.5" lineColor="#000000"/> <bottomPen lineWidth="0.5" lineColor="#000000"/> </box> </style> <subDataset name="Data"> <field name="literature_type_name" class="java.lang.String"/> <field name="books_instances_amount" class="java.lang.Long"/> <field name="reference_books_instances_amount" class="java.lang.Long"/> </subDataset> <field name="library_department_name" class="java.lang.String"/> <title> <band height="72"> <frame> <reportElement mode="Opaque" x="-20" y="-20" width="595" height="92" backcolor="#006699"/> <staticText> <reportElement x="20" y="10" width="555" height="30" forecolor="#FFFFFF"/> <textElement textAlignment="Center"> <font size="20" isBold="true"/> </textElement> <text><![CDATA[Book availability in department]]></text> </staticText> <textField> <reportElement x="20" y="50" width="555" height="30" forecolor="#FFFFFF"/> <box> <pen lineWidth="1.0" lineColor="#FFFFFF"/> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font fontName="SansSerif" size="20" isBold="true"/> </textElement> <textFieldExpression><![CDATA[$F{library_department_name}]]></textFieldExpression> </textField> </frame> </band> </title> <detail> <band height="204"> <componentElement> <reportElement x="0" y="4" width="555" height="200" forecolor="#FFFFFF"> <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/> <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/> <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/> <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/> <property name="net.sf.jasperreports.export.headertoolbar.table.name" value=""/> <property name="com.jaspersoft.studio.components.autoresize.proportional" value="true"/> </reportElement> <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd"> <datasetRun subDataset="Data"> <dataSourceExpression><![CDATA[$P{REPORTING}.dataset("Data")]]></dataSourceExpression> </datasetRun> <jr:column width="188"> <jr:columnHeader style="Table_CH" height="30"> <staticText> <reportElement x="0" y="0" width="188" height="30" forecolor="#FFFFFF"/> <box> <pen lineColor="#FFFFFF"/> </box> <textElement textAlignment="Center" verticalAlignment="Middle"> <font fontName="SansSerif" size="12" isBold="true"/> </textElement> <text><![CDATA[Literature Type]]></text> </staticText> </jr:columnHeader> <jr:detailCell style="Table_TD" height="30"> <textField> <reportElement x="0" y="0" width="188" height="30"/> <textElement textAlignment="Center" verticalAlignment="Middle"> <font fontName="SansSerif" size="12"/> </textElement> <textFieldExpression><![CDATA[$F{literature_type_name}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="186"> <jr:columnHeader style="Table_CH" height="30"> <staticText> <reportElement x="0" y="0" width="186" height="30" forecolor="#FFFFFF"/> <textElement textAlignment="Center" verticalAlignment="Middle"> <font fontName="SansSerif" size="12" isBold="true"/> </textElement> <text><![CDATA[Book Amount]]></text> </staticText> </jr:columnHeader> <jr:detailCell style="Table_TD" height="30"> <textField> <reportElement x="0" y="0" width="186" height="30"/> <textElement textAlignment="Center" verticalAlignment="Middle"> <font size="12"/> </textElement> <textFieldExpression><![CDATA[$F{books_instances_amount}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> <jr:column width="181"> <jr:columnHeader style="Table_CH" height="30"> <staticText> <reportElement x="0" y="0" width="181" height="30" forecolor="#FFFFFF"/> <textElement textAlignment="Center" verticalAlignment="Middle"> <font fontName="SansSerif" size="12" isBold="true"/> </textElement> <text><![CDATA[Reference Book Amount]]></text> </staticText> </jr:columnHeader> <jr:detailCell style="Table_TD" height="30"> <textField isBlankWhenNull="false"> <reportElement x="0" y="0" width="181" height="30" forecolor="#000000"/> <textElement textAlignment="Center" verticalAlignment="Middle"> <font size="12"/> </textElement> <textFieldExpression><![CDATA[$F{reference_books_instances_amount}]]></textFieldExpression> </textField> </jr:detailCell> </jr:column> </jr:table> </componentElement> </band> </detail> <pageFooter> <band height="17"> <textField> <reportElement mode="Opaque" x="0" y="4" width="515" height="13" backcolor="#E6E6E6"/> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression> </textField> <textField evaluationTime="Report"> <reportElement mode="Opaque" x="515" y="4" width="40" height="13" backcolor="#E6E6E6"/> <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression> </textField> <textField pattern="M/d/yy"> <reportElement x="0" y="4" width="280" height="13"/> <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression> </textField> </band> </pageFooter> </jasperReport>The table in this template is bound to a subDataset. The
titleelement uses the Header band data directly. You can open the template file in JasperReports visual designer to see the report layout.Upload the new template to the application, selecting any output type, and make it default:
Figure 67. Report templates
Run the report to make sure it works:
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.
-
Data model
Our report will display information on the
Cliententity. It contains two String attributes:titleandsummary, 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; ... } -
Let’s create a simple report without parameters. The JPQL query will select all clients with their local attributes:
titleandsummary.
Figure 69. Report data structure -
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:alwaysCSS 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> -
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.htmlfile will look as below:<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Invoice</title> <style type="text/css"> body { font: 12pt Georgia, "Times New Roman", Times, serif; line-height: 1.3; padding-top: 50px; } div.header { display: block; text-align: center; position: running(header); width: 100%; } div.footer { display: block; text-align: center; position: running(footer); width: 100%; } @page { /* switch to landscape */ size: landscape; /* set page margins */ margin: 0.5cm; @top-center { content: element(header); } @bottom-center { content: element(footer); } @bottom-right { content: counter(page) " of " counter(pages); } } .custom-page-start { margin-top: 50px; } </style> </head> <body> <h1>Clients report</h1> <!-- Custom HTML header --> <div class="header"> Annual Report of our Company </div> <!-- Custom HTML footer --> <div class="footer"> Address: William Road </div> <#assign clients = Root.bands.Clients /> <#list clients as client> <div class="custom-page-start" style="page-break-before: always;"> <h2>Client</h2> <p>Name: ${client.fields.title}</p> <p>Summary: ${client.fields.summary}</p> </div> </#list> </body> </html> -
Upload the template file and run the report.
Figure 70. Template editorAs you can see, the report contains the title page and page breaks before each client’s page. There are headers and footers on each page as well.
Figure 71. Report result
6. Reporting REST API
The universal REST API provides the following functionality for the Reporting Add-on:
-
Getting a list of reports.
-
Getting the details on a specific report.
-
Running a report and getting the result.
-
Getting Swagger documentation.
REST API uses the OAuth2 protocol for authentication and supports anonymous access.
To make a report available through the REST API, select the Visible for REST API checkbox on the Roles and Screens tab:
Below we provide a formal description of some features specific to reporting. For more information on how to get an OAuth token and other REST API features, see Developer’s Manual.
- Getting the list of existing reports
-
The list of existing reports can be retrieved with the following GET request:
/rest/reports/v1/reportFor example:
GET http://localhost:8080/app/rest/reports/v1/report HTTP/1.1 Authorization: Bearer f5a2b4b1-a121-4563-9519-dd3c0b116689 Content-Type: application/jsonThe response body will contain the brief information on the reports marked as Visible for REST API:
{ "id": "2dd27fbf-8830-416a-899f-339543f8f27a", "name": "Books by author" }, { "id": "2f07c9fe-5d6d-48cf-876f-8c02ac1f6c3c", "name": "Book availability in department" }
- Getting the report info
-
The detailed information on a specific report is retrieved with the following GET request:
/rest/reports/v1/report/{id}The last part of the query here is the report identifier, for example:
GET http://localhost:8080/app/rest/reports/v1/report/2dd27fbf-8830-416a-899f-339543f8f27a HTTP/1.1The returned JSON object will contain the following information on the passed report:
-
ID
-
name
-
system code (if exists)
-
parameters and their types
{ "id": "2dd27fbf-8830-416a-899f-339543f8f27a", "name": "Books by author", "templates": [ { "code": "DEFAULT", "outputType": "XLS" } ], "inputParameters": [ { "name": "Author", "alias": "author", "type": "ENTITY", "required": true, "hidden": false, "entityMetaClass": "library$Author" } ] } -
- Running a report
-
To run a report, send the following POST request:
/rest/reports/v1/run/{id}The last part of the query here is the report identifier, for example:
POST http://localhost:8080/app/rest/reports/v1/run/2dd27fbf-8830-416a-899f-339543f8f27a HTTP/1.1The report parameters are passed in the request body:
{parameters: [{name: 'author',value: '4b3a21b0-d6b7-4161-b0b6-55f118fbaac5'}]}To print a report with a non-default template, pass the template code in the request body:
{template: 'Template_1', parameters: [{name: 'author',value: '4b3a21b0-d6b7-4161-b0b6-55f118fbaac5'}]}
- Getting the Swagger documentation
-
The complete Swagger documentation on the Reporting Add-on can be retrieved with the GET request on the address:
http://localhost:8080/app/rest/reports/v1/docs/swagger.json
Appendix A: Installing and Configuring OpenOffice
The report generator uses the OpenOffice / LibreOffice package to output reports in PDF and DOC formats. The installation to the computer containing the application server and configuration of this package is described below.
Installing and Configuring OpenOffice for Microsoft Windows
-
Download the application at http://openoffice.org.
-
Install the application.
-
In the reporting.openoffice.path application property in the
app.propertiesfile of the core module, specify the path to OpenOffice.org, for example:
reporting.openoffice.path = C:/Program Files (x86)/OpenOffice.org 3/programInstalling and Configuring LibreOffice for Microsoft Windows
-
Download the application at http://www.libreoffice.org/download/download/.
-
Install the application.
-
In the reporting.openoffice.path application property in the
app.propertiesfile of the core module, specify the path to LibreOffice, for example:
reporting.openoffice.path = C:/Program Files (x86)/LibreOffice 5/programInstalling and Configuring LibreOffice on Ubuntu Server
-
Install the
libreofficepackage, for example, by running the following command:$ sudo apt-get install libreoffice` -
In the reporting.openoffice.path application property in the
app.propertiesfile of the core module, 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 After that, you should change the owner of this folder: |
Installing and Configuring LibreOffice for macOS
-
Download the application at https://www.libreoffice.org/get-help/install-howto/os-x/.
-
Install the application.
-
In the reporting.openoffice.path application property, specify the path to LibreOffice.app, for example:
reporting.openoffice.path = /Applications/LibreOffice.appAppendix B: Application Properties
This section describes the application properties related to the report generator in the alphabetical order.
- reporting.enableTabSymbolInDataSetEditor
-
Defines whether TAB key should be handled as
\tsymbol instead of focus navigation in Script fields of the report editor.Default value:
falseUsed 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.docFormatterTimeout
-
Sets the LibreOffice timeout in seconds for converting DOCX/XLSX to HTML/PDF.
When the time is up, the user will receive an error message.
Default value:
20Used 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:
falseUsed 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:
trueUsed in the Middleware block.