Working with reporting in a data management system

Hello everyone! In this article, we want to talk about how we generate reporting in the Unidata platform. Any work with data inevitably leads to the construction of specialized reports in which users can efficiently process this data and make business decisions based on it.

How did you choose a reporting system

It is not a trivial and expensive task to create a module for building reports in the platform, so it became necessary to find a suitable toolkit for building reports. Our main criteria were:

  • Free use of software in commercial open source projects

  • The tool for building data should work with the main formats of data sources, as well as directly with the database.

  • Using Java to Build Reports

  • The software must be supported and updated with support for backward compatibility

  • The report builder should be convenient and understandable

  • The tool should allow you to create report templates in all major formats: excel, csv, pdf, html, etc.

  • Rich visualization and building dashboards.

We conducted a study of some of the most famous open-source reporting systems, and we collected the results of our study in a table that we want to share.

Name

Manufacturer

License

Features and benefits

disadvantages

BIRT

The Business Intelligence and Reporting Tools (BIRT)

Eclipse Foundation

Eclipse Public License

Latest version 4.5.0 (June 24, 2015) i.e. the project is alive;

There is a visual report editor in the Eclipse IDE;

The generated BIRT reports are saved in XML and have access to a variety of different data sources, including the JDO datastore, JFire, Plain Old Java Object, SQL, database, Web Service, and XML;

Contains charting technology that is fully integrated into the report designer and can be used stand-alone to integrate charts into the application;

Lots of documentation;

A crude and awkward editor;

It is installed as a separate web application;

Eclipse is required to use;

Reports created in different versions are incompatible;

JasperReports

Jaspersoft

GNU Lesser General Public License

Latest version 6.2.2 (May 6, 2016) reports can be displayed on screen, printer, or in PDF, RTF, HTML, XLS, CSV and XML formats;

Using dynamic languages ​​JavaScript and Groovy when implementing the logic of the report;

Implementation of charts based on the JFreeChart library;

Implementation of subreports with unlimited nesting depth;

Implementation of crosstabs;

Pentaho Reporting JFreeReport

Project Corporation

Pentaho Community Edition (CE): Apache version 2.x; Pentaho Enterprise Edition (EE): Commercial License

Flexible positioning of dashboard elements on the layout;

Advanced visualization tools for reports;

Ability to output reports in HTML, Excel, csv, xml, PDF, text formats;

Little information about the application;

All major features are implemented in the commercial version from Hitachi Group Company;

YARG

CUBA

Apache 2.0 License

Generate a report in template format or convert the result to PDF;

Create report templates in familiar and common formats: DOC, ODT, XLS, DOCX, XLSX, HTML;

Create complex XLS and XLSX templates: with nested data areas, graphs, formulas, etc .;

Use images and HTML markup in reports;

Store the structure of reports in XML format;

Run a standalone application for generating reports, which makes it possible to use the library outside the Java ecosystem (for example, for generating reports in PHP);

Integrate with IoC frameworks (Spring, Guice).

There is no intelligible editor;

There is a UI provided by the CUBA platform;

As you can see from our little research, the most suitable tool for us was JasperReports. This open-source tool is supported by the presence of a very convenient visual report design editor, a rich set of visualizations, including cross-tables, and, most importantly, the presence of a REST API. Designing report layouts in JasperReports does not require much in-depth knowledge, and the results of the project are saved in xml format. We also focus on the experience of colleagues, for example, our partners CROC in their article https://habr.com/ru/company/croc/blog/244085/ recommend using Jasper. “JasperSoft is most suitable for building fixed reporting. Interesting for any company that needs data analysis tools. ” Of course, jasper has certain problems when you need to make a flexible template, for example, when you need to make a flexible display of fields in a table, but in our practice we usually come across fixed reports, which are designated by the customer.

Client-Server Interaction with Jasper reports

We think it might be interesting how we embed jasper reports directly into the platform without unnecessary requests to the Jasper Server. JasperReports Server is the main component of the system. Its task is to store reports that will be built into the platform, as well as provide the ability to view reports directly through a special interface. Here is an example of how it looks in the platform

When building reports, our task is to get the report parameters from the user, collect data from sources based on the passed parameters, build a data visualization, and integrate the finished data visualization in the form of an iframe on the interface or upload it to a file. A description of this mechanism is presented in the diagram below.

In order to get the report, you need to log into the Jasper Server. Authorization occurs by passing a login / password pair, and in response Jasper creates a session and saves the session_id in cookies. In the usual case, in order to directly interact with the JasperServer from JavaScript, we need to log in, get the session_id from the cookie and request the report itself. However, in practice, we faced the problem that in the case of using a cluster of servers with duplicated Jasper on all instances, jasper reports are either available or not available to the user. The problem is that the balancer, upon receiving a request from the client, requests a response from different JasperServers, but uses the session_id of only one instance. That is, we logged into JasperServer on the first instance, received a session_id from it, then we go to another instance with the same session_id and get an access error “with the message“ Log in to JasperServer ”. To solve this problem, we use a special proxy, which is essentially an extension of the platform backend and is installed on all nodes of the cluster. Since the proxy is installed on the same server as the Jasper server, it does not need to access the node via IP, but it is enough to contact localhost. Thus, the balancer, transmitting a request from the client to a particular node, asks the proxy for authorization already in place and the Jasper Server is guaranteed to return a response. Below is the proxy code.

public Response getJasperReport(@QueryParam("url") String url) throws UnsupportedEncodingException {
    url = url.replaceAll(";;", "&").replaceAll(" ","%20").replaceAll(""","%22");
 
    Client client = ClientBuilder.newClient();
    Response authResponse = client
            .target(jasperUrlLogin)
            .queryParam("j_username", jasperLogin)
            .queryParam("j_password", jasperPassword)
            .request()
            .header("Content-Type", "application/x-www-form-urlencoded")
            .header("charset", "utf-8")
            .post(Entity.json(""));
    NewCookie sessionIdCookie = null;
    if (authResponse.getStatus() == 200) {
        Map<String, NewCookie> cookies = authResponse.getCookies();
        sessionIdCookie = cookies.get("JSESSIONID");
    } else {
        LOGGER.warn("Cant auth JasperServer");
        return null;
    }
 
    String requestUrl = jasperReportUrl + url;
 
    Response response = client
            .target(requestUrl)
            .request()
            .cookie(sessionIdCookie)
            .header("Content-Type", "text/html")
            .get();
    return response;
}

The proxy receives a certain report URL as input, which is collected from parameters on the client. This URL is used for authorization in jasperServer, then the proxy takes the session_id out of the cookie and requests a response directly from the report itself. The response from jasper comes in the form of an html page. It is this html page that we pass to the iframe for rendering on the client, and not the url, as is usually the case. Thus, we request a report once, then all work with it goes directly to the client of the platform.

Create an Iframe

{
    xtype: 'component',
    margin: '20 0 0 0',
    reference: 'report',
    maxWidth: 1200,
    height:485,
    autoEl: {
        tag: 'iframe',
        src: '',
        frameBorder: 0,
        marginWidth: 0,
    },
    listeners: {
        load: {
            element: 'el',
            fn: function () {
                var panel = this.getParent().component;
                panel.setLoading(false, panel.body);
            }
        }
    }
}

Submitting html page from Jasper Server

generateReport: function () {
        var report_url = this.generateReportUrl('html');
        if (report_url) {
            var panel = this.view;
            panel.setLoading(true, panel.body);
            this.getHtmlAndUpdateReport(report_url);
        }
    }

generateReportUrl is a method that generates a URL with the required parameters for the report and session_id.

Generating a report in JasperReports

Next, let’s talk about directly creating reports and dashboards in Jasper. Creation of a jasper report consists of a set of visualizations arranged on a single layout: To create a report layout, we use the visual editor JasperSoft Studio, which can be a standalone application or a plug-in for eclipse. You can easily find information about this tool in the documentation and open sources, but it is important for us to highlight the fact that in this editor you can easily build a dashboard, and the editor itself is quite convenient and understandable. It is enough to select the required visualizations, drag them onto the layout, fill in the parameters and functional elements. Building dashboards does not require programming skills and everyone can figure them out in a fairly short time. Below is an example of a simple report in JasperStudio.

After creating the report layout, we proceed to building the logic of the report itself. Jasper report presents an xml file in a special jrxml format. The structure of the jrxml file can be conventionally divided into three parts: in the first part, the report parameters are determined, in the second part, a request to the data is written, in the third part, functional blocks of the layout are described, in which the query results are processed and the data is displayed in the layout.

Start of file structure: example of report parameters

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.9.0.final using JasperReports Library version 6.9.0-cb8f9004be492ccc537180b49c026951f4220bf3  -->
<jasperReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://jasperreports.sourceforge.net/jasperreports" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="pubsub_diagram" pageWidth="1150" pageHeight="550" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="e8ef4a20-ab26-44c0-8b4d-316411f7d350">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="postgres_local.xml"/>
    <property name="net.sf.jasperreports.export.xls.ignore.graphics" value="false"/>
    <parameter name="date_from_param" class="java.lang.String"/>
    <parameter name="date_to_param" class="java.lang.String"/>
    <parameter name="systems_param" class="java.util.Collection"/>
    <parameter name="status_param" class="java.util.Collection"/>
    <parameter name="entities_param" class="java.util.Collection"/>
    <parameter name="count_details_param" class="java.lang.Integer"/>
    <parameter name="group_param" class="java.lang.String"/>

Further, suppose that the data source is in the second part, after the parameters, we write an SQL query

 <queryString>
        <![CDATA[SELECT * FROM (
Cnjbn with main_table AS
(SELECT T3.status as status, count(T3.id) as count_status, (count(T3.id) / (to_date($P{date_to_param}, 'DD_MM_YYYY') - to_date($P{date_from_param}, 'DD_MM_YYYY') + 1)) as avg_count_status  FROM
(SELECT T1.id,T2.status
    FROM public.history
     AS T1
LEFT JOIN
(SELECT DISTINCT ON (history_id) history_id, status FROM public.track
WHERE createdate >= to_date($P{date_from_param}, 'DD_MM_YYYY')
    AND DATE(createdate) <= to_date($P{date_to_param}, 'DD_MM_YYYY')
ORDER BY history_id, createdate DESC NULLS LAST
) AS T2
ON T1.id = T2.history_id
WHERE T2.status IS NOT NULL
AND $X{IN,T1.unidatasourcesystem, systems_param} AND $X{IN,T1.entity, entities_param}
AND T1.createdate >= to_date($P{date_from_param}, 'DD_MM_YYYY')
AND DATE(T1.createdate) <= to_date($P{date_to_param}, 'DD_MM_YYYY')
AND $X{IN,T2.status, status_param}
) AS T3
GROUP BY T3.status)
SELECT main_table.*, round((count_status * 100) / (SELECT SUM(count_status) FROM main_table), 2) AS percent_status FROM main_table
) AS t_result order by status]]>
    </queryString>

It is worth noting that in the request we use the parameters $ P {date_to_param}, which are dynamically received from the client, which is also a Jasper feature.

The following describes the body of the entire report by functional section. More details about the description of all sections can be found in the documentation. Below is an example of how it might look

<columnHeader>
    <band height="35">
        <staticText>
            <reportElement x="0" y="0" width="150" height="30" uuid="1972f653-13ec-41b8-987a-a1f25940e053"/>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="Arial" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[Статус]]></text>
        </staticText>
        <staticText>
            <reportElement x="150" y="0" width="150" height="30" uuid="bde4e86c-d3d8-4538-a278-44eae4cda528"/>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="Arial" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[Количество сообщений]]></text>
        </staticText>
        <staticText>
            <reportElement x="300" y="0" width="160" height="30" uuid="ab26081d-2c0b-45b3-8c43-5707e2b555e7"/>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="Arial" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[Среднее в день]]></text>
        </staticText>
    </band>
</columnHeader>
<detail>
    <band height="35" splitType="Stretch">
        <textField>
            <reportElement x="0" y="0" width="150" height="30" uuid="ea66974c-f627-4096-86c3-fc0f921a88d2"/>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="Arial" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[$F{status}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="150" y="0" width="150" height="30" uuid="a820021d-95d6-4ee5-a5a4-887aca484efb"/>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="Arial" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[$F{count_status}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="300" y="0" width="160" height="30" uuid="e7927fa9-5b8f-43ff-bea7-1d74d8a3ce27"/>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="Arial" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[$F{avg_count_status}]]></textFieldExpression>
        </textField>
    </band>
</detail>
<summary>
    <band height="370">
        <staticText>
            <reportElement x="0" y="0" width="150" height="30" uuid="d93b83c8-b168-4766-91d8-b9545e3239a7"/>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="Arial" size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[ИТОГО]]></text>
        </staticText>
        <textField>
            <reportElement x="150" y="0" width="150" height="30" uuid="6e306a81-3522-437d-a973-0dcf8646aa5f"/>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="Arial" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[$V{sum_status}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="300" y="0" width="160" height="30" uuid="67d24b52-4d3e-47ae-a35d-dc98a9b230f5"/>
            <textElement textAlignment="Center" verticalAlignment="Middle">
                <font fontName="Arial" size="12"/>
            </textElement>
            <textFieldExpression><![CDATA[$V{sum_avg_count_status}]]></textFieldExpression>
        </textField>
        <pieChart>
            <chart evaluationTime="Report">
                <reportElement x="0" y="40" width="400" height="320" uuid="bf9f29b3-51c1-472d-822b-e7e4b20fa160"/>
                <chartTitle/>
                <chartSubtitle/>
                <chartLegend/>
            </chart>
            <pieDataset>
                <keyExpression><![CDATA[$F{status}]]></keyExpression>
                <valueExpression><![CDATA[$F{count_status}]]></valueExpression>
                <labelExpression><![CDATA["" + $F{percent_status} + "% " + $F{status}]]></labelExpression>
            </pieDataset>
            <piePlot>
                <plot>
                    <seriesColor seriesOrder="0" color="#33F54A"/>
                    <seriesColor seriesOrder="1" color="#EB73C1"/>
                    <seriesColor seriesOrder="2" color="#433DF2"/>
                    <seriesColor seriesOrder="3" color="#FAEC52"/>
                    <seriesColor seriesOrder="4" color="#FFC342"/>
                    <seriesColor seriesOrder="5" color="#D9D2D8"/>
                    <seriesColor seriesOrder="6" color="#DE522F"/>
                </plot>
                <itemLabel/>
            </piePlot>
        </pieChart>
    </band>
</summary>

Separately, it is worth noting another useful feature of Jasper reports – the presence of subreports. In the case when we want to reuse any report in another report with the same parameters, we do not need to write from scratch, but it is enough to refer to the finished report by its identifier, passing the new parameter values

<subreport>
    <reportElement x="460" y="40" width="650" height="320" uuid="e0d58e35-b1da-4bcc-9978-fbda3028ff5a"/>
    <subreportParameter name="date_from_param">
        <subreportParameterExpression><![CDATA[$P{date_from_param}]]></subreportParameterExpression>
    </subreportParameter>
    <subreportParameter name="date_to_param">
        <subreportParameterExpression><![CDATA[$P{date_to_param}]]></subreportParameterExpression>
    </subreportParameter>
    <subreportParameter name="systems_param">
        <subreportParameterExpression><![CDATA[$P{systems_param}]]></subreportParameterExpression>
    </subreportParameter>
    <subreportParameter name="status_param">
        <subreportParameterExpression><![CDATA[$P{status_param}]]></subreportParameterExpression>
    </subreportParameter>
    <subreportParameter name="entities_param">
        <subreportParameterExpression><![CDATA[$P{entities_param}]]></subreportParameterExpression>
    </subreportParameter>
    <subreportParameter name="group_param">
        <subreportParameterExpression><![CDATA[$P{group_param}]]></subreportParameterExpression>
    </subreportParameter>
    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
    <subreportExpression><![CDATA["repo:pubsub_grapf.jrxml"]]></subreportExpression>
</subreport>

The key issue in building a report is the transfer of parameters (filters) of the report from the client to the server. In order not to send the user to JasperServer and fill in all report parameters in the platform in a convenient way, Jasper suggests using its own REST API. Having such a powerful API was a decisive argument in favor of choosing JasperSoft for reporting automation. Instead of creating resources and filling in parameters in the Jasper Server environment, we will simply use the methods provided by the API and pass the parameters with a GET request from the client. The jasper API allows not only parameterizing the data used in reports, but also the reports themselves, which allows very flexible display of the required dashboards

Total

Summarizing all of the above, we recommend using JasperSoft when there is a need to create flexible reports according to the customer’s templates. For this, Jasper provides all the necessary tools, and working in it is quite simple and convenient.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *