Clever Geek Handbook

Travailler avec des rapports dans un système de gestion de données

Bonjour à tous! Dans cet article, nous voulons parler de la façon dont nous générons des rapports dans la plate-forme Unidata. Tout travail avec des données conduit inévitablement à la construction de rapports spécialisés dans lesquels les utilisateurs peuvent traiter efficacement ces données et prendre des décisions commerciales en fonction de celles-ci.





Comment avez-vous choisi un système de reporting

Ce n'est pas une tâche triviale et coûteuse de créer un module pour créer des rapports dans la plate-forme, il est donc devenu nécessaire de trouver une boîte à outils appropriée pour créer des rapports. Nos principaux critères étaient:





  • Utilisation gratuite de logiciels dans les projets commerciaux open source





  • L'outil de création de données doit fonctionner avec les principaux formats de sources de données, ainsi que directement avec la base de données.





  • Utilisation de Java pour créer des rapports





  • Le logiciel doit être pris en charge et mis à jour avec prise en charge de la compatibilité descendante





  • Le générateur de rapport doit être pratique et compréhensible





  • L'outil devrait vous permettre de créer des modèles de rapports dans tous les principaux formats: excel, csv, pdf, html, etc.





  • Visualisation riche et création de tableaux de bord.





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