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.
open-source , , .
|
|
|
|
|
BIRT The Business Intelligence and Reporting Tools (BIRT) |
Eclipse Foundation |
Eclipse Public License |
4.5.0 ( 24, 2015) .. ; Eclipse IDE; BIRT XML , JDO, JFire, Plain Old Java Object, SQL, database, Web Service XML; , ; ; |
; web-; Eclipse; , ; |
JasperReports |
Jaspersoft |
GNU Lesser General Public License |
6.2.2 (6 2016 ) , , PDF, RTF,HTML, XLS, CSV XML; JavaScript Groovy ; (charts) JFreeChart; (subreports) ; (crosstabs); |
|
Pentaho Reporting JFreeReport |
Project Corporation |
Pentaho Community Edition (CE): Apache version 2.x; Pentaho Enterprise Edition (EE): Commercial License |
; ; HTML, Excel, csv, xml, PDF, ; |
; Hitachi Group Company; |
YARG |
CUBA |
Apache 2.0 License |
PDF; : DOC, ODT, XLS, DOCX,XLSX, HTML; XLS XLSX : , , ..; HTML-; XML; standalone , Java- ( PHP); IoC- (Spring, Guice). |
; UI, CUBA; |
, JasperReports. open-source , , , - REST API. JasperReports , xml-. , , https://habr.com/ru/company/croc/blog/244085/ Jasper. «JasperSoft . , ». , jasper , , , , , .
- Jasper reports
, , jasper Jasper Server. JasperReports Server – . - , , .
, , , , iframe . .
, Jasper Server. /, Jasper session_id . , JasperServer JavaScript, , session_id . , Jasper , jasper , . , , , JasperServer, session_id . JasperServer , session_id, c session_id « « JasperServer». , . , Jasper server, IP , localhost. , , , Jasper Server . .
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;
}
URL , . URL jasperServer, session_id . jasper html-. html- iframe , url, . , .
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);
}
}
}
}
html 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 - , URL session_id.
C JasperReports
Jasper. jasper , : JasperSoft Studio, eclipse. , , , . , , . . JasperStudio.
, , . Jasper xml- jrxml. jrxml : , , , .
:
<?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"/>
, SQL-
<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>
, $P{date_to_param}, , Jasper.
. .
<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>
Jasper - . , - , , ,
<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>
() . , JasperServer , Jasper REST API. API JasperSoft . , Jasper Server , API GET- . API jasper , , ,
JasperSoft . Jasper , .