How to use a postgreSQL crosstab query in Jasper Reports?

0
votes

I'm trying to make a report in Jasper Reports using a PostgreSQL crosstab query with parameters.

Crosstab query is working when I execute it in pgAdmin 4 with static values. When I copy it into Query dialog in the report builder, and add report parameters, it doesn't work.

Versions: PostgreSQL server 12 TIBCO Jaspersoft® Studio 6.6.0

Here's the code:

Crosstab query working in 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

and report code:

<!-- 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>

When I execute preview, I get this syntax error:

Caused by: org.postgresql.util.PSQLException: ERROR: error de sintaxis en o cerca de «'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'» at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 6 more

For testing purposes, I replaced the SQL query with a fixed crosstab inner query like this:

<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>

and the report preview executed well.

Any advice is truly appreciated.

1
sqlpostgresqljasper-reports
Did you check the resulting query, for example with help of textField? – Alex K
@alex-k How can I do that? – Diego Moreno

1 Answers

0
votes

JasperReports translates $P{..} in report queries to JDBC prepared statement parameters. That is, if you have WHERE column = $P{..} in the report query, the report will run WHERE column = ? as a prepared statement with the report parameter value sent as statement parameter.

Prepared statement parameters stand for single values in the query, so crosstab($P{..}) will not work, even if the parameter values contains a comma. What might work is crosstab($P{first_query}, $P{second_query}), just note that prepared statement parameters are provided as raw values so you should not enclose the values in quotes or escape quotes inside the values.

But if you just want to textually insert the parameter value in the query, you can use $P!{..} instead of $P{..}. Since the parameter value in your report looks like a query fragment, using SELECT * FROM crosstab ($P!{crosstab_query}) in the report query should work.