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 screens.

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:

sample1 result
Figure 1. Books by author
  1. Report data structure.

    sample1 structure
    Figure 2. 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:

      [['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 JMXRPR_BOOK b
          join JMXRPR_AUTHORS_BOOKS ba on ba.book_id = b.id
          join JMXRPR_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 JMXRPR_BOOK_PUBLICATION bp
          join JMXRPR_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 JMXRPR_BOOK_INSTANCE bi
          join JMXRPR_BOOK_PUBLICATION bp on bp.id = bi.book_publication_id
          join JMXRPR_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.

  2. Report parameters.

    The Parameters and Formats tab contains one declared report external parameter – Author:

    sample1 param
    Figure 3. Report parameters

    When running the report, the user will have to enter this parameter. The author selection will be performed via the Author.browse screen, available in the application.

  3. Report templates.

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

    sample1 template
    Figure 4. Report templates
  4. Report Name Localization.

    The Localization tab contains the report name for the Russian locale:

    ru = Книги по автору

You can run the report from the generic browser in the ReportsRun Reports screen.

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 screens.

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:

crosstab result
Figure 5. Crosstab result

To create the report, add a band and select Crosstab band orientation on the Report structure tab of the report editor. This orientation automatically adds three datasets to the band:

  1. <band_name>_dynamic_header – the data from this dataset is copied to the right like a vertical band with table columns header.

  2. <band_name>_master_data – the data from this dataset is copied downwards like a horizontal band with table rows header.

  3. <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:

crosstab structure
Figure 6. Crosstab Report
  1. Report data structure. There are three datasets:

    • The bi_dynamic_header dataset will return the list of months names:

      bi_dynamic_header dataset
      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:

      bi_master_data dataset
      select name as name, id as department_id
      from JMXRPR_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.

      bi dataset
      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 JMXRPR_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)
  2. Report parameters.

    The Parameters and Formats tab contains three declared report external parameters – selected_departments, start_date, end_date:

    crosstab external params
    Figure 7. Report parameters

    When running the report, the user will have to enter these parameters. The department selection will be performed via the jmxrpr_LibraryDepartment.browse screen, available in the application.

  3. 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 generic browser in the ReportsRun Reports screen.

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 screens.

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

sample jasper result
Figure 8. Report result
  1. Report data structure.

    sample jasper structure
    Figure 9. Report data structure

    Let’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.company.jmixreports.entity.LiteratureType
      
      def result = []
      
      def ltList = dataManager.load(LiteratureType).all().list();
      ltList.each(lt->{
          def count = dataManager.loadValue("select sum(bi.bookCount) from jmxrpr_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 jmxrpr_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.

  2. Report parameters.

    The Parameters and Formats tab contains one declared report external parameter – Department:

    sample jasper paramters
    Figure 10. Report parameters

    When running the report, the user will have to enter this parameter. The department selection will be performed via the jmxrpr_LibraryDepartment.browse screen, available in the application.

  3. Report template.

    Create the new JRXML file or download BookAvailability.jrxml 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 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:

    sample jasper template
    Figure 11. Report templates

You can run the report from the generic browser in the ReportsRun Reports screen.

Sample HTML/PDF Report with Paging, Headers and Footers

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

In this example, we now want to create a report about book summaries 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:

example html result
Figure 12. Report result
  1. The report data structure

    Let’s create a simple report without parameters. The JPQL query will select all books with their local attributes: name and summary.

    example html structure
    Figure 13. Report data structure
  2. Report template.

    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: always CSS property. It will generate a page break before each book info block.

    As you can see, we use FreeMarker statements to insert data to our template. See complete FreeMarker reference documentation.

    <body>
        <h1>Books report</h1>
    
        <!-- Custom HTML header -->
        <div class="header">
            Library book summaries
        </div>
    
        <!-- Custom HTML footer -->
        <div class="footer">
            Address: William Road
        </div>
    
        <#assign books=Root.bands.Books />
    
        <#list books as book>
            <div class="custom-page-start" style="page-break-before: always;">
                <h2>Book</h2>
    
                <p>Name: ${book.fields.title}</p>
                <p>Summary: ${book.fields.summary}</p>
            </div>
        </#list>
    </body>
  3. CSS rules

    We will use the following CSS code to set header/footer postions:

    div.header {
        display: block;
        text-align: center;
        position: running(header);
        width: 100%;
    }
    
    div.footer {
        display: block;
        text-align: center;
        position: running(footer);
        width: 100%;
    }

    And following CSS code to tune our PDF page representation, as well as paddings of the main content to prevent content and header/footer overlapping:

    body {
        font: 12pt Georgia, "Times New Roman", Times, serif;
        line-height: 1.3;
        padding-top: 50px;
    }
    
    @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;
    }

    So, the complete BookSummary.html file 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>Books report</h1>
    
        <!-- Custom HTML header -->
        <div class="header">
            Library book summaries
        </div>
    
        <!-- Custom HTML footer -->
        <div class="footer">
            Address: William Road
        </div>
    
        <#assign books=Root.bands.Books />
    
        <#list books as book>
            <div class="custom-page-start" style="page-break-before: always;">
                <h2>Book</h2>
    
                <p>Name: ${book.fields.title}</p>
                <p>Summary: ${book.fields.summary}</p>
            </div>
        </#list>
    </body>
    
    </html>
  4. Create the report template, here we select Freemarker template type:

    example html template
    Figure 14. Template editor

    You can run the report from the generic browser in the ReportsRun Reports screen.

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 screens.

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

html groovy result
Figure 15. Report result
  1. Create a report with JPQL dataset:

    html groovy structure
    Figure 16. Report data structure

    The BookPublications band outputs the list of book’s publications by running the following JPQL query:

    BookPublications dataset
    select
    b.name as "book",
    p.name as "publisher"
    from jmxrpr_BookPublication bp
    left join bp.book b
    left join bp.publisher p
     where bp.town.id = ${town}

    This query uses the external report parameter – town. The parameter has the Entity type; however, in JPQL queries you can compare it directly with entity identifier fields; the conversion will be done automatically.

  2. Describe the report parameter:

    The Parameters and Formats tab contains one declared report external parameter – Town:

    html groovy parameter
    Figure 17. The report parameter

    When running the report, the user will have to enter this parameter. The town selection will be performed via the jmxrpr_Town.browse screen, available in the application.

  3. Create a report template

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

    PublicationsTemplate
    <?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 town </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.town.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.town.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:

    html groovy template
    Figure 18. Report template editor

You can run the report from the generic browser in the ReportsRun Reports screen.