Clever Geek Handbook

Arbeiten mit der Berichterstellung in einem Datenverwaltungssystem

Hallo zusammen! In diesem Artikel möchten wir darüber sprechen, wie wir Berichte auf der Unidata-Plattform generieren. Jede Arbeit mit Daten führt unweigerlich zur Erstellung spezieller Berichte, in denen Benutzer diese Daten effizient verarbeiten und darauf basierende Geschäftsentscheidungen treffen können.





Wie haben Sie ein Berichtssystem ausgewählt?

Es ist keine triviale und teure Aufgabe, ein Modul zum Erstellen von Berichten auf der Plattform zu erstellen. Daher wurde es erforderlich, ein geeignetes Toolkit zum Erstellen von Berichten zu finden. Unsere Hauptkriterien waren:





  • Kostenlose Nutzung von Software in kommerziellen Open Source-Projekten





  • Das Tool zum Erstellen von Daten sollte mit den Hauptformaten von Datenquellen sowie direkt mit der Datenbank funktionieren.





  • Verwenden von Java zum Erstellen von Berichten





  • Die Software muss unterstützt und aktualisiert werden, um die Abwärtskompatibilität zu gewährleisten





  • Der Berichtsersteller sollte bequem und verständlich sein





  • Mit dem Tool sollten Sie Berichtsvorlagen in allen wichtigen Formaten erstellen können: Excel, CSV, PDF, HTML usw.





  • Umfangreiche Visualisierung und Erstellung von 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 , .







More articles:


All Articles