Sample Reports

Sample XLS Report

The example is based on the sample Library application, please follow the instruction to set up your project then create entities and views.

In this example, we will generate a report for a book author. Given an author, the report will list his books, the publisher of each book, in which library department the book was stored, and how many books were stored in each department. The result looks like this:

Report data structure
Let us consider report bands.
- header band - report header. It contains the dataset with the Groovy script which outputs the report external parameters values:
  
  return [['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 BOOK b join BOOK_AUTHOR_LINK ba on ba.book_id = b.id join 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 the book, outputs the book publishers by running the following SQL query:
  
  select p.name as publisher, bp.year_, p.id as publisher_id from BOOK_PUBLICATION bp join PUBLISHER p on p.id = bp.publisher_id where bp.book_id = ${book.book_id}
  
  This query uses the parent band field book_id as a parameter. This provides dependency between the parent and child bands.
- The publication band, which is a child of the publisher band, outputs the book publications by running the following SQL query:
  
  select ld.name as department, sum(bi.book_count) as amount from BOOK_INSTANCE bi join BOOK_PUBLICATION bp on bp.id = bi.book_publication_id join LIBRARY_DEPARTMENT ld on ld.id = bi.library_department_id where bp.publisher_id = ${publisher.publisher_id} and bp.book_id = ${book.book_id} group by ld.name
  
  This query uses both parent bands fields as parameters - book_id and publisher_id.
Report parameters.

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

When running the report, the user will have to enter this parameter. The author selection will be performed via the Author.list view, available in the application.
Report templates.

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

You can run the report from the Reports → Run Reports view.

Sample Crosstab Report

The example is based on the sample Library application, please follow the instruction to set up your project then create entities and views.

In this example, we will create a crosstab report for the library departments, to list how many books each department purchased every month. The report is extended both vertically and horizontally and aggregates the book amounts for each department and each month:

To create the report, add a band and select Crosstab band orientation on the Bands tab of the report detail view. 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.

These datasets can have any of available dataset types: SQL, JPQL, Groovy, and so on.

For example, a crosstab report for the BookInstance entity of the Library sample application has the following structure:

Report data structure. There are three datasets:

The bi_dynamic_header dataset will return the list of months names:

import java.text.DateFormatSymbols

List result = new ArrayList()
DateFormatSymbols dateFormatSymbols = DateFormatSymbols.getInstance(Locale.ENGLISH)
for (i in 0..dateFormatSymbols.months.length - 1) {
    result.add(["header_id" : i + 1, "month_name" : dateFormatSymbols.months[i]])
}
return result

The bi_master_data dataset returns names and identifiers of the library departments selected by the user as an external report parameter:
```
select name as name, id as department_id
from LIBRARY_DEPARTMENT
where id in (${selected_departments})
```
The bi dataset will provide data for the matrix cells, which is the book amounts sum for the particular month and department. It takes bi_master_data@department_id (the department id) as the vertical coordinate of the cell, and bi_dynamic_header@header_id (the month name) as the horizontal coordinate, and fills the matrix cell with the amount value.

In the example below the report has two more external parameters: the start_date and the end_date that define the range of book instance creation dates. It would be a good idea to set up cross-parameter validation to make sure the range makes sense.
```
select bi.library_department_id as bi_master_data@department_id,
       month(bi.created_date) as bi_dynamic_header@header_id,
       sum(bi.book_count) as "amount"
from BOOK_INSTANCE bi
where bi.created_date >= ${start_date} and bi.created_date<= ${end_date}
and bi.library_department_id in (${bi_master_data@department_id})
and month(bi.created_date) in (${bi_dynamic_header@header_id})
group by bi.library_department_id,month(bi.created_date)
order by bi.library_department_id,month(bi.created_date)
```

Report parameters.

The Parameters tab contains three declared report external parameters - selected_departments, start_date, end_date:

When running the report, the user will have to enter these parameters. The department selection will be performed via the LibraryDepartment.list view, available in the application.
Report template.

Then you can create a XLS report template using Microsoft Office or LibreOffice.

The DepartmentBooks.xls is an example of a template which outputs the list of Departments vertically and Books for each department horizontally grouped by the months the books were created.

This report template contains 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 bi_header.

You can run the report from the Reports → Run Reports view.

Sample JasperReports Report

The example is based on the sample Library application, please follow the instruction to set up your project then create entities and views.

In this example, we will create a JRXML report to display the list of book publications in the selected department:

On the Report details tab, define the report name: Books availability.

Switch to the Bands tab:

Let’s consider report bands.

Header band - the report header. It contains the dataset with the Groovy script which outputs the report external parameters value:
```
return [['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.company.library.entity.LiteratureType

def result = []

def ltList = dataManager.load(LiteratureType).all().list();
ltList.each(lt->{
    def count = dataManager.loadValue("select sum(bi.bookCount) from BookInstance bi where bi.libraryDepartment = :department and bi.bookPublication.book.literatureType = :lt ", Long)
            .parameter("department", params['library_department']).parameter("lt", lt)
            .one();

    def refCount = dataManager.loadValue("select sum(bi.bookCount) from BookInstance bi where bi.libraryDepartment = :department and bi.bookPublication.book.literatureType = :lt and bi.isReference = true", Long)
            .parameter("department", params['library_department']).parameter("lt", lt)
            .one();

    result.add(['literature_type_name': lt.name,
                'books_instances_amount': count,
                'reference_books_instances_amount': refCount])
});
return result;

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 tab contains one declared report external parameter - Department:

When running the report, the user will have to enter this parameter. The department selection will be performed via the LibraryDepartment.list view, available in the application.

Report template.

Create the new JRXML file or download BookAvailability.jrxml with the following content:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.0.final using JasperReports Library version 6.4.1 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="books" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <property name="template.engine" value="tabular_template"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <style name="Table_TH" mode="Opaque" backcolor="#066990">
        <box>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_CH" mode="Opaque" forecolor="#FFFFFF" backcolor="#06618F" hTextAlign="Center" fontSize="12">
        <box>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF" hTextAlign="Center">
        <box>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <subDataset name="Data">
        <field name="literature_type_name" class="java.lang.String"/>
        <field name="books_instances_amount" class="java.lang.Long"/>
        <field name="reference_books_instances_amount" class="java.lang.Long"/>
    </subDataset>
    <field name="library_department_name" class="java.lang.String"/>
    <title>
        <band height="72">
            <frame>
                <reportElement mode="Opaque" x="-20" y="-20" width="595" height="92" backcolor="#006699"/>
                <staticText>
                    <reportElement x="20" y="10" width="555" height="30" forecolor="#FFFFFF"/>
                    <textElement textAlignment="Center">
                        <font size="20" isBold="true"/>
                    </textElement>
                    <text><![CDATA[Book availability in department]]></text>
                </staticText>
                <textField>
                    <reportElement x="20" y="50" width="555" height="30" forecolor="#FFFFFF"/>
                    <box>
                        <pen lineWidth="1.0" lineColor="#FFFFFF"/>
                        <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                        <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                        <bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                        <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                    </box>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font fontName="SansSerif" size="20" isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{library_department_name}]]></textFieldExpression>
                </textField>
            </frame>
        </band>
    </title>
    <detail>
        <band height="204">
            <componentElement>
                <reportElement x="0" y="4" width="555" height="200" forecolor="#FFFFFF">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
                    <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
                    <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
                    <property name="net.sf.jasperreports.export.headertoolbar.table.name" value=""/>
                    <property name="com.jaspersoft.studio.components.autoresize.proportional" value="true"/>
                </reportElement>
                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                    <datasetRun subDataset="Data">
                        <dataSourceExpression><![CDATA[$P{REPORTING}.dataset("Data")]]></dataSourceExpression>
                    </datasetRun>
                    <jr:column width="188">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="188" height="30" forecolor="#FFFFFF"/>
                                <box>
                                    <pen lineColor="#FFFFFF"/>
                                </box>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font fontName="SansSerif" size="12" isBold="true"/>
                                </textElement>
                                <text><![CDATA[Literature Type]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="188" height="30"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font fontName="SansSerif" size="12"/>
                                </textElement>
                                <textFieldExpression><![CDATA[$F{literature_type_name}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="186">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="186" height="30" forecolor="#FFFFFF"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font fontName="SansSerif" size="12" isBold="true"/>
                                </textElement>
                                <text><![CDATA[Book Amount]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="186" height="30"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font size="12"/>
                                </textElement>
                                <textFieldExpression><![CDATA[$F{books_instances_amount}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="181">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="181" height="30" forecolor="#FFFFFF"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font fontName="SansSerif" size="12" isBold="true"/>
                                </textElement>
                                <text><![CDATA[Reference Book Amount]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField isBlankWhenNull="false">
                                <reportElement x="0" y="0" width="181" height="30" forecolor="#000000"/>
                                <textElement textAlignment="Center" verticalAlignment="Middle">
                                    <font size="12"/>
                                </textElement>
                                <textFieldExpression><![CDATA[$F{reference_books_instances_amount}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </detail>
    <pageFooter>
        <band height="17">
            <textField>
                <reportElement mode="Opaque" x="0" y="4" width="515" height="13" backcolor="#E6E6E6"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report">
                <reportElement mode="Opaque" x="515" y="4" width="40" height="13" backcolor="#E6E6E6"/>
                <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
            </textField>
            <textField pattern="M/d/yy">
                <reportElement x="0" y="4" width="280" height="13"/>
                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
</jasperReport>

The table in this template is bound to a subDataset. The title element uses the Header band data directly. You can open the template file in JasperReports visual designer to see the report layout.

Upload the new template to the application, selecting any output type, and make it default:

You can run the report from the Reports → Run Reports view.

HTML Report with Groovy Template Engine

The example is based on the sample Library application, please follow the instruction to set up your project then create entities and views.

Let’s create a report that displays the list of book publications for the selected city. The output format is HTML:

Create a report with JPQL dataset:

The BookPublications band outputs the list of book’s publications by running the following JPQL query:
```
select b.name as "book",
p.name as "publisher"
from BookPublication bp
left join bp.book b
left join bp.publisher p
where bp.city = ${city}
```
This query uses the external report parameter - city. The parameter has the Entity type.
Describe the report parameter:

The Parameters tab contains one declared report external parameter - City:

When running the report, the user will have to enter this parameter. The city selection will be performed via the City.list view, available in the application.

Create a report template

Create the new HTML file or download PublicationByCity.html with the following content:

<?xml version="1.0" encoding="utf-8"?>
<!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" xml:lang="ru">

<head>
    <title> Publications by city </title>
    <style type="text/css">
        body {
            font: 12pt Georgia, "Times New Roman", Times, serif;
            line-height: 1.3;
            padding-top: 30px;
        }

        tbody tr {
            height: 40px;
            min-height: 20px
        }
    </style>
</head>

<body>
<h1>Publications, published in <% out << "${Root.fields.city.name}" %>
</h1>
<% def bookPublications=Root.bands.BookPublications.fields %>
<table class="report-table" border="1" cellspacing="2">
    <thead>
    <tr>
        <th>Book</th>
        <th>Publisher</th>
    </tr>
    </thead>
    <tbody>
    <% bookPublications.title.eachWithIndex{ elem, index ->
    out << "<tr><td> ${bookPublications.book[index]} </td><td> ${bookPublications.publisher[index]} </td></tr>"
    } %>
    </tbody>
</table>
</body>

</html>

The value of the input parameter is used to generate the report title: ${Root.fields.city.name}.

The variable bookPublications is defined below:

<% def bookPublications=Root.bands.BookPublications.fields %>

This variable is used in the table’s body to display the report fields.

<% bookPublications.title.eachWithIndex{ elem, index ->
out << "<tr><td> ${bookPublications.book[index]} </td><td> ${bookPublications.publisher[index]} </td></tr>"
} %>

Upload the new template to the application, select HTML output type, select Groovy template in the Template type radiobutton and make it default:

You can run the report from the Reports → Run Reports view.

Was this page helpful?

Thank you for your feedback

Something went wrong