Search code examples
jasper-reports

Jasper report : sum in group is always 'null'

I have to sum field based on a group, that seems quite easy.

Here are the steps I did with sources

create group 'GroupeCR'

<group name="GroupeCR">
        <groupExpression><![CDATA[$F{LB_CR}]]></groupExpression>
        <groupHeader>
            <band height="20">
                <textField>
                    <reportElement mode="Opaque" x="0" y="0" width="125" height="20" backcolor="#D4D4D4" uuid="7b15b1e9-d4c5-4dea-9a54-436bef6d270b"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$F{LB_CR}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="430" y="0" width="100" height="20" uuid="4ece930a-2d32-40f3-a7e4-0ce40f712c79"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$V{PREVUES1}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="535" y="0" width="95" height="20" uuid="1ed652f3-b919-4435-abcb-498262510300"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$V{PREVUES2}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
        <groupFooter>
            <band height="4"/>
        </groupFooter>
    </group>

create variables that sum field for each 'GroupeCR'

<variable name="PREVUES1" class="java.math.BigDecimal" resetType="Group" resetGroup="GroupeCR" calculation="Sum">
    <variableExpression><![CDATA[$F{PREVUES}]]></variableExpression>
</variable>
<variable name="PREVUES2" class="java.math.BigDecimal" resetType="Group" resetGroup="GroupeCR" calculation="Sum">
    <variableExpression><![CDATA[$F{PREVUES}]]></variableExpression>
</variable>

Add variable textfield in the GroupeCR header

    <groupHeader>
        <band height="20">
            <textField>
                <reportElement mode="Opaque" x="0" y="0" width="125" height="20" backcolor="#D4D4D4" uuid="7b15b1e9-d4c5-4dea-9a54-436bef6d270b"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{LB_CR}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="430" y="0" width="100" height="20" uuid="4ece930a-2d32-40f3-a7e4-0ce40f712c79"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$V{PREVUES1}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="535" y="0" width="95" height="20" uuid="1ed652f3-b919-4435-abcb-498262510300"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$V{PREVUES2}]]></textFieldExpression>
            </textField>
        </band>
    </groupHeader>

Problem: the sum values are always 'null'.

I tried many solutions find online (changing evalution time, evalution type etc..)

Here is the complete source:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1  -->
<!-- 2018-08-21T10:15:43 -->
<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="PrévisionsBudgetaires" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ae38e860-9a01-4f95-ad32-a4e26073a696">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="TEST"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <queryString>
        <![CDATA[SELECT
     ecr.exe_ordre                exe_ordre,
     eb.id_adm_eb,
     eb.org_etab || '/' || eb.org_ub || '/' || eb.org_cr || '/' || eb.org_souscr lb,
     eb.org_pere,
     eb.org_etab || '/' || eb.org_ub  || '/' || eb.org_cr lb_cr,
     ecr.id_adm_nature_rec        id_adm_nature_rec,
     n.code                       nature,
     n.libelle                    libellenature,
     ecr.id_adm_origine_recette   id_adm_origine_recette,
     o.code                       origine,
     o.libelle                    libelleorigine,
     ecr.ecr_type                 ecr_type,
     NVL(SUM(ecr.rec_credit) , 0) prevues,
     NVL(SUM(ecr.rec_debit) , 0)  percues
 FROM
     gfc.cptbud_ecriture ecr
     INNER JOIN gfc.adm_eb eb ON ecr.id_adm_eb = eb.id_adm_eb
     INNER JOIN gfc.adm_nature_rec n ON n.id_adm_nature_rec = ecr.id_adm_nature_rec
     INNER JOIN gfc.adm_origine_recette o ON o.id_adm_origine_recette = ecr.id_adm_origine_recette
 WHERE
     ( ecr.rec_credit IS NOT NULL
       OR ecr.rec_debit IS NOT NULL )
 GROUP BY
     ecr.exe_ordre,
     eb.id_adm_eb,
     eb.org_pere,
     eb.org_etab,
     eb.org_ub,
     eb.org_cr,
     eb.org_souscr,
     ecr.ecr_type,
     ecr.id_adm_nature_rec,
     n.code,
     n.libelle,
     ecr.id_adm_origine_recette,
     o.code,
     o.libelle
 ORDER BY
     1,
     2,
     3,
     5,
     8]]>
    </queryString>
    <field name="EXE_ORDRE" class="java.math.BigDecimal"/>
    <field name="ID_ADM_EB" class="java.math.BigDecimal"/>
    <field name="LB" class="java.lang.String"/>
    <field name="ORG_PERE" class="java.math.BigDecimal"/>
    <field name="LB_CR" class="java.lang.String"/>
    <field name="ID_ADM_NATURE_REC" class="java.math.BigDecimal"/>
    <field name="NATURE" class="java.lang.String"/>
    <field name="LIBELLENATURE" class="java.lang.String"/>
    <field name="ID_ADM_ORIGINE_RECETTE" class="java.math.BigDecimal"/>
    <field name="ORIGINE" class="java.lang.String"/>
    <field name="LIBELLEORIGINE" class="java.lang.String"/>
    <field name="ECR_TYPE" class="java.lang.String"/>
    <field name="PREVUES" class="java.math.BigDecimal"/>
    <field name="PERCUES" class="java.math.BigDecimal"/>
    <variable name="PREVUES1" class="java.math.BigDecimal" resetType="Group" resetGroup="GroupeCR" calculation="Sum">
        <variableExpression><![CDATA[$F{PREVUES}]]></variableExpression>
    </variable>
    <variable name="PREVUES2" class="java.math.BigDecimal" resetType="Group" resetGroup="GroupeCR" calculation="Sum">
        <variableExpression><![CDATA[$F{PREVUES}]]></variableExpression>
    </variable>
    <group name="GroupeCR">
        <groupExpression><![CDATA[$F{LB_CR}]]></groupExpression>
        <groupHeader>
            <band height="20">
                <textField>
                    <reportElement mode="Opaque" x="0" y="0" width="125" height="20" backcolor="#D4D4D4" uuid="7b15b1e9-d4c5-4dea-9a54-436bef6d270b"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$F{LB_CR}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="430" y="0" width="100" height="20" uuid="4ece930a-2d32-40f3-a7e4-0ce40f712c79"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$V{PREVUES1}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="535" y="0" width="95" height="20" uuid="1ed652f3-b919-4435-abcb-498262510300"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$V{PREVUES2}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
        <groupFooter>
            <band height="4"/>
        </groupFooter>
    </group>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="54" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement mode="Opaque" x="530" y="0" width="100" height="20" backcolor="#CCFF99" uuid="7308f98a-e026-4b7a-abc8-b5891586dd59"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Montant HT Recette]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="125" y="0" width="305" height="20" backcolor="#CCFF99" uuid="03dd2058-5702-431c-9177-0713dfd8c8b7"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[BI]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="0" y="0" width="125" height="20" backcolor="#CCFF99" uuid="757c40a3-c3d3-4286-b53b-9d9b9b0a73ae"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[ETAB/CR/SCR]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="430" y="0" width="100" height="20" backcolor="#CCFF99" uuid="03bad235-aace-49aa-9ebc-6249df8a9bdf"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[PREVUES]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="125" height="20" uuid="d9a1eb9f-ee46-4bf6-9bcd-dd785a8bc957"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{LB}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="125" y="0" width="305" height="20" uuid="e58a5e8f-dd30-4468-9aac-d14b77f654d8"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{LIBELLENATURE}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="530" y="0" width="100" height="20" uuid="d063a904-9924-4921-a7a9-8cbaf76e3dbf"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{PERCUES}]]></textFieldExpression>
            </textField>
            <textField isBlankWhenNull="true">
                <reportElement x="430" y="0" width="100" height="20" uuid="25bc4561-6a26-4db0-ace6-316d9530c6fb"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{PREVUES}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>
Solution

  • Turns out it's a jasper studio issue. close/open Jasper Studio solved it.