¡Hola a todos! En este artículo, queremos hablar sobre cómo generamos informes en la plataforma Unidata. Cualquier trabajo con datos conduce inevitablemente a la construcción de informes especializados en los que los usuarios pueden procesar de manera eficiente estos datos y tomar decisiones comerciales en base a ellos.
¿Cómo eligió un sistema de informes?
No es una tarea trivial y costosa crear un módulo para generar informes en la plataforma, por lo que se hizo necesario encontrar un conjunto de herramientas adecuado para generar informes. Nuestros principales criterios fueron:
Uso gratuito de software en proyectos comerciales de código abierto
La herramienta para la construcción de datos debe trabajar con los principales formatos de las fuentes de datos, así como directamente con la base de datos.
Uso de Java para crear informes
El software debe ser compatible y actualizado con soporte para compatibilidad con versiones anteriores.
El generador de informes debe ser conveniente y comprensible.
La herramienta debería permitirle crear plantillas de informes en todos los formatos principales: excel, csv, pdf, html, etc.
Visualización enriquecida y creación de paneles de control.
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 , .