J'essaie de créer un rapport dans Jasper Reports à l'aide d'une requête croisée PostgreSQL avec des paramètres.

La requête Crosstab fonctionne lorsque je l'exécute dans pgAdmin 4 avec des valeurs statiques. Lorsque je le copie dans la boîte de dialogue de requête dans le générateur de rapport et que j'ajoute des paramètres de rapport, cela ne fonctionne pas.

Versions : serveur PostgreSQL 12 TIBCO Jaspersoft® Studio 6.6.0

Voici le code:

Requête croisée fonctionnant dans pgAdmin 4:

select 
    cedula,
    apellidos,
    nombres,    
    aporteIndividual,
    aporteAdicional,
    aporteAdicional5,
    aporteSeguro,
    aporteIndividual::numeric + aporteAdicional::numeric + 
    aporteAdicional5::numeric + aporteSeguro::numeric as total
from (
SELECT * FROM crosstab
(
'select
    p.id,
    a.id,
    COALESCE (p.primer_apellido, '''') || '' '' ||
    COALESCE (p.segundo_apellido, '''') as apellidos,
    COALESCE (p.primer_nombre, '''') || '' '' ||
    COALESCE (p.segundo_nombre, '''') as nombres,
    p.numero_documento, 
    ad.tipo_aporte,
    ad.valor
from
    sch_participantes.participante as p,
    sch_participantes.aportes as a,
    sch_participantes.aporte_detalles as ad
where
    p.id = a.id_participe
    and a.id = ad.id_aporte
    and p.filial = 1084
    and p.estado = 1
    and a.mes = 1
    and a.anio = 2020
order by p.primer_apellido',
    'select id from sch_participantes.tipo_aporte ta
order by ta.id'
    )
AS
(
        id_participe integer,
        id_aporte integer,
        apellidos text,
        nombres text,
        cedula text,
        aporteIndividual text,
        aporteAdicional text,
        aporteAdicional5 text,
        aporteSeguro text
)
union all
select
    p.id,
    null,
    COALESCE (p.primer_apellido, '') || ' ' ||
    COALESCE (p.segundo_apellido, '') as apellidos,
    COALESCE (p.primer_nombre, '') || ' ' ||
    COALESCE (p.segundo_nombre, '') as nombres,
    p.numero_documento,
    null,
    null,
    null,
    null
from
    sch_participantes.participante as p
where
    p.id not in
    (
        select
            a.id_participe
        from
            sch_participantes.aportes as a
        where
            a.mes = 1
            and a.anio = 2020
    )
    and p.filial = 1084
    and p.estado = 1
) as todo
order by todo.apellidos

Et le code du rapport :

<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="aportes-general" pageWidth="595" pageHeight="842" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="da69e8ed-7cd6-42e2-9d96-8f8d86be6137">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w1" value="205"/>
    <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w2" value="786"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="erp_pruebas_michel"/>
    <property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w1" value="666"/>
    <property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w2" value="320"/>
    <parameter name="filial" class="java.lang.Integer" evaluationTime="Early">
        <defaultValueExpression><![CDATA[1084]]></defaultValueExpression>
    </parameter>
    <parameter name="año" class="java.lang.Integer" evaluationTime="Early">
        <defaultValueExpression><![CDATA[2020]]></defaultValueExpression>
    </parameter>
    <parameter name="mes" class="java.lang.Integer" evaluationTime="Early">
        <defaultValueExpression><![CDATA[1]]></defaultValueExpression>
    </parameter>
    <parameter name="crosstab_query" class="java.lang.String" evaluationTime="Early">
        <defaultValueExpression><![CDATA["'select" +
"    p.id," +
"   a.id," +
"    COALESCE (p.primer_apellido, '''') || '' '' ||" +
"   COALESCE (p.segundo_apellido, '''') as apellidos," +
"   COALESCE (p.primer_nombre, '''') || '' '' ||" +
"   COALESCE (p.segundo_nombre, '''') as nombres," +
"   p.numero_documento, " +
"    ad.tipo_aporte," +
"    ad.valor" +
" from" +
"    sch_participantes.participante as p," +
"    sch_participantes.aportes as a," +
"    sch_participantes.aporte_detalles as ad" +
" where" +
"    p.id = a.id_participe" +
"    and a.id = ad.id_aporte" +
"    and p.filial = " + $P{filial} +
"    and p.estado = 1" +
"    and a.mes = " + $P{mes} +
"    and a.anio = " + $P{año} +
" order by p.primer_apellido'," +
"    'select id from sch_participantes.tipo_aporte ta" +
" order by ta.id'"]]></defaultValueExpression>
    </parameter>
    <queryString language="SQL">
        <![CDATA[select 
    cedula,
    apellidos,
    nombres,    
    aporteIndividual,
    aporteAdicional,
    aporteAdicional5,
    aporteSeguro,
    aporteIndividual::numeric + aporteAdicional::numeric + 
    aporteAdicional5::numeric + aporteSeguro::numeric as total
from (
SELECT * FROM crosstab ($P{crosstab_query})
AS
(
        id_participe integer,
        id_aporte integer,
        apellidos text,
        nombres text,
        cedula text,
        aporteIndividual text,
        aporteAdicional text,
        aporteAdicional5 text,
        aporteSeguro text
)
union all
select
    p.id,
    null,
    COALESCE (p.primer_apellido, '') || ' ' ||
    COALESCE (p.segundo_apellido, '') as apellidos,
    COALESCE (p.primer_nombre, '') || ' ' ||
    COALESCE (p.segundo_nombre, '') as nombres,
    p.numero_documento,
    null,
    null,
    null,
    null
from
    sch_participantes.participante as p
where
    p.id not in
    (
        select
            a.id_participe
        from
            sch_participantes.aportes as a
        where
            a.mes = $P{mes}
            and a.anio = $P{año}
    )
    and p.filial = $P{filial}
    and p.estado = 1
) as todo
order by todo.apellidos]]>
    </queryString>
    <field name="cedula" class="java.lang.String"/>
    <field name="apellidos" class="java.lang.String"/>
    <field name="nombres" class="java.lang.String"/>
    <field name="aporteIndividual" class="java.math.BigDecimal"/>
    <field name="aporteAdicional" class="java.math.BigDecimal"/>
    <field name="aporteAdicional5" class="java.math.BigDecimal"/>
    <field name="aporteSeguro" class="java.math.BigDecimal"/>
    <field name="total" class="java.math.BigDecimal"/>
    <background>
        <band/>
    </background>
    <title>
        <band height="72">
            <frame>
                <reportElement mode="Opaque" x="-20" y="-20" width="595" height="92" backcolor="#006699" uuid="0dc47f7a-395a-4b8d-87b4-e927fca8fa61"/>
                <staticText>
                    <reportElement x="20" y="20" width="410" height="30" forecolor="#FFFFFF" uuid="3c6488eb-bfbc-4fb6-b9b2-3637a1cf0c9d"/>
                    <textElement>
                        <font size="19" isBold="true"/>
                    </textElement>
                    <text><![CDATA[Reporte general de aportes]]></text>
                </staticText>
                <textField>
                    <reportElement x="20" y="50" width="300" height="30" forecolor="#FFFFFF" uuid="4a131c99-d637-4199-9335-493f0e7a7750"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <textFieldExpression><![CDATA["Período:" + $P{mes} + "-" + $P{año}]]></textFieldExpression>
                </textField>
            </frame>
        </band>
    </title>
    <pageHeader>
        <band height="13"/>
    </pageHeader>
    <columnHeader>
        <band height="18">
            <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.spreadsheet.SpreadsheetLayout"/>
            <staticText>
                <reportElement x="0" y="0" width="50" height="18" uuid="4d23b483-a6f5-44f1-b4b0-e0c92d8736b0">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fdf238bc-d6fa-41f7-a846-a09be881e2a8"/>
                </reportElement>
                <text><![CDATA[cedula]]></text>
            </staticText>
            <staticText>
                <reportElement x="50" y="0" width="130" height="18" uuid="b2c90d08-f6a7-441c-92c3-3487aed4a04f">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="87c434fd-7660-4c3e-8ca4-97bb9499349b"/>
                </reportElement>
                <text><![CDATA[apellidos]]></text>
            </staticText>
            <staticText>
                <reportElement x="180" y="0" width="120" height="18" uuid="4aca3646-0e0e-4253-83f3-7903cb60b048">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="289c8d74-6077-45d8-9373-c238a4598284"/>
                </reportElement>
                <text><![CDATA[nombres]]></text>
            </staticText>
            <staticText>
                <reportElement x="300" y="0" width="50" height="18" uuid="3e268eea-8ecb-4fb1-8bfe-6f99f39caccb">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="8d1cedc0-4fd2-4f06-8725-b17630f08437"/>
                </reportElement>
                <text><![CDATA[aporteIndividual]]></text>
            </staticText>
            <staticText>
                <reportElement x="350" y="0" width="50" height="18" uuid="5f5e3710-7eef-48c5-a962-6120bb9fa67b">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fa8014b3-1b03-4443-bf80-167703b8f1cb"/>
                </reportElement>
                <text><![CDATA[aporteAdicional]]></text>
            </staticText>
            <staticText>
                <reportElement x="400" y="0" width="50" height="18" uuid="00653733-6788-45b6-ab57-d37d12293a6d">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="d066fefc-d2fb-49b9-bde1-f706ea220cb9"/>
                </reportElement>
                <text><![CDATA[aporteAdicional5]]></text>
            </staticText>
            <staticText>
                <reportElement x="450" y="0" width="50" height="18" uuid="cf37f752-5ba2-4950-8200-099943713cd7">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="e434b3c8-20bc-4631-b59b-a4939675978f"/>
                </reportElement>
                <text><![CDATA[aporteSeguro]]></text>
            </staticText>
            <staticText>
                <reportElement x="500" y="0" width="50" height="18" uuid="623db75c-ff28-4aab-8fc3-190f479cd8c5">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5cd0ec9d-c45b-4e7b-9fbc-2bb616521f0e"/>
                </reportElement>
                <text><![CDATA[total]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30">
            <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.spreadsheet.SpreadsheetLayout"/>
            <textField>
                <reportElement x="0" y="0" width="50" height="30" uuid="2a352a63-f1fe-4a2e-bb74-937d2e3f335b">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fdf238bc-d6fa-41f7-a846-a09be881e2a8"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{cedula}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="50" y="0" width="130" height="30" uuid="970896d3-426b-41f2-b097-784d83a2e172">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="87c434fd-7660-4c3e-8ca4-97bb9499349b"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{apellidos}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="180" y="0" width="120" height="30" uuid="0c2e4d66-7faa-4baa-a77a-dfbe60dfe466">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="289c8d74-6077-45d8-9373-c238a4598284"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{nombres}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="300" y="0" width="50" height="30" uuid="0c6e4a8e-5b0e-4a07-9d73-282781817c95">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="8d1cedc0-4fd2-4f06-8725-b17630f08437"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{aporteIndividual}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="350" y="0" width="50" height="30" uuid="d2af8ebf-cbc9-407e-8dbe-f1af49525966">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fa8014b3-1b03-4443-bf80-167703b8f1cb"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{aporteAdicional}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="400" y="0" width="50" height="30" uuid="a78a3c65-c069-447d-a3ae-84087e3f4977">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="d066fefc-d2fb-49b9-bde1-f706ea220cb9"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{aporteAdicional5}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="450" y="0" width="50" height="30" uuid="5f22b066-53b8-47dd-b2c9-bb026eb69dae">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="e434b3c8-20bc-4631-b59b-a4939675978f"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{aporteSeguro}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="500" y="0" width="50" height="30" uuid="1f698fc0-da4d-4aa9-b21e-6ba74dfe8df0">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5cd0ec9d-c45b-4e7b-9fbc-2bb616521f0e"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{total}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band/>
    </columnFooter>
    <pageFooter>
        <band height="17">
            <textField>
                <reportElement mode="Opaque" x="0" y="4" width="515" height="13" backcolor="#E6E6E6" uuid="0b71f3fa-21ce-4aa4-8fbc-84c015c403e3"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report">
                <reportElement mode="Opaque" x="515" y="4" width="40" height="13" backcolor="#E6E6E6" uuid="837ccbf7-57ea-4894-a13f-415d2472630f"/>
                <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
            </textField>
            <textField pattern="EEEEE dd MMMMM yyyy">
                <reportElement x="0" y="4" width="100" height="13" uuid="ae716933-1e7e-4a7c-b470-d58ad1afcd72"/>
                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
    <summary>
        <band height="47">
            <staticText>
                <reportElement x="270" y="0" width="100" height="30" uuid="5042bbb1-6dc0-4b69-a4f6-49f75dc42a25"/>
                <text><![CDATA[Total Aportes]]></text>
            </staticText>
        </band>
    </summary>
</jasperReport>

Lorsque j'exécute l'aperçu, j'obtiens cette erreur de syntaxe :

Causé par : org.postgresql.util.PSQLException : ERREUR : erreur de sintaxis en o cerca de « 'select p.id, a.id, COALESCE (p.primer_apellido, '''') || '' '' || COALESCE (p.segundo_apellido, '''') comme apellidos, COALESCE (p.primer_nombre, '''') || '' '' || SE FONDRE (p.segundo_nombre, '''') comme nombres, p.numero_documento,
ad.tipo_aporte, ad.valor de sch_participantes.participante comme p, sch_participantes.aportes en tant que,
sch_participantes.aporte_detalles comme annonce où p.id = a.id_participe et a.id = ad.id_aporte et p.filial = 1084 et p.estado = 1
et a.mes = 1 et a.anio = 2020 commander par p.primer_apellido'» à org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) à org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) à org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) à org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) à org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) à org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) à net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 6 de plus

À des fins de test, j'ai remplacé la requête SQL par une requête interne de tableau croisé fixe comme celle-ci :

<queryString language="SQL">
        <![CDATA[select 
    cedula,
    apellidos,
    nombres,    
    aporteIndividual,
    aporteAdicional,
    aporteAdicional5,
    aporteSeguro,
    aporteIndividual::numeric + aporteAdicional::numeric + 
    aporteAdicional5::numeric + aporteSeguro::numeric as total
from (
SELECT * FROM crosstab (
'select
    p.id,
    a.id,
    COALESCE (p.primer_apellido, '''') || '' '' ||
    COALESCE (p.segundo_apellido, '''') as apellidos,
    COALESCE (p.primer_nombre, '''') || '' '' ||
    COALESCE (p.segundo_nombre, '''') as nombres,
    p.numero_documento, 
    ad.tipo_aporte,
    ad.valor
from
    sch_participantes.participante as p,
    sch_participantes.aportes as a,
    sch_participantes.aporte_detalles as ad
where
    p.id = a.id_participe
    and a.id = ad.id_aporte
    and p.filial = 1084
    and p.estado = 1
    and a.mes = 1
    and a.anio = 2020
order by p.primer_apellido',
    'select id from sch_participantes.tipo_aporte ta
order by ta.id'
)
AS
(
        id_participe integer,
        id_aporte integer,
        apellidos text,
        nombres text,
        cedula text,
        aporteIndividual text,
        aporteAdicional text,
        aporteAdicional5 text,
        aporteSeguro text
)
union all
select
    p.id,
    null,
    COALESCE (p.primer_apellido, '') || ' ' ||
    COALESCE (p.segundo_apellido, '') as apellidos,
    COALESCE (p.primer_nombre, '') || ' ' ||
    COALESCE (p.segundo_nombre, '') as nombres,
    p.numero_documento,
    null,
    null,
    null,
    null
from
    sch_participantes.participante as p
where
    p.id not in
    (
        select
            a.id_participe
        from
            sch_participantes.aportes as a
        where
            a.mes = $P{mes}
            and a.anio = $P{año}
    )
    and p.filial = $P{filial}
    and p.estado = 1
) as todo
order by todo.apellidos]]>
    </queryString>

Et l'aperçu du rapport s'est bien exécuté.

Tout conseil est vraiment apprécié.

0
Diego Moreno 21 févr. 2020 à 23:07

1 réponse

Meilleure réponse

JasperReports traduit $P{..} dans les requêtes de rapport en paramètres d'instruction préparés JDBC. Autrement dit, si vous avez WHERE column = $P{..} dans la requête de rapport, le rapport s'exécutera WHERE column = ? en tant qu'instruction préparée avec la valeur du paramètre de rapport envoyée en tant que paramètre d'instruction.

Les paramètres d'instruction préparés représentent des valeurs uniques dans la requête, donc crosstab($P{..}) ne fonctionnera pas, même si les valeurs des paramètres contiennent une virgule. Ce qui pourrait fonctionner, c'est crosstab($P{first_query}, $P{second_query}), notez simplement que les paramètres d'instruction préparés sont fournis sous forme de valeurs brutes, vous ne devez donc pas mettre les valeurs entre guillemets ou guillemets d'échappement à l'intérieur des valeurs.

Mais si vous souhaitez simplement insérer textuellement la valeur du paramètre dans la requête, vous pouvez utiliser $P!{..} au lieu de $P{..}. Étant donné que la valeur du paramètre dans votre rapport ressemble à un fragment de requête, l'utilisation de SELECT * FROM crosstab ($P!{crosstab_query}) dans la requête de rapport devrait fonctionner.

0
dada67 22 févr. 2020 à 11:02