Why do I have wrong number printing out on report while trying to SUM a group variable?

Question

I have made a custom report in iReport and after trying the report I have noticed that the variable I have Grand Total Quantity is not printing out the correct quantity number.

I have all of my fields placed into a group called "product" and the group's purpose is to group up all of the productNumbers (names) and then sum up each of their quantities. The variable storing the total quantities per item is sumTotalQuantity

The issue is that outside of the Product Group Footer I made a variable called sumGrandTotalQuantity to SUM up the values in the sumTotalQuantity variable in the group band.

For what ever reason though the number being returned into the sumGrandTotalQuantity variable is more then the group band variable.

Why is my variable not returning the correct variable? Is it the way I am trying to total up the group variable that is already SUMing up stuff?

As you can see in the last photo- the actual totled quantities showing up is 173 not the 175 that is being displayed.

    <?xml version="1.0" encoding="UTF-8"?>
<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="SalesOrderItemsByZip" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="d4cb0e75-8b4b-4aba-9324-9be9518ac4c7">
    <property name="ireport.zoom" value="1.5"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <parameter name="From" class="java.util.Date"/>
    <parameter name="To" class="java.util.Date"/>
    <parameter name="ZipCode" class="java.lang.String" isForPrompting="false"/>
    <parameter name="State" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT
    soitem.PRODUCTNUM, so.SHIPTOZIP as ZIP, soitem.QTYTOFULFILL as QTY, so.SHIPTOSTATEID as stateID, COALESCE(stateconst.code,'') AS stateabbr, so.DATECOMPLETED as soDATE, soitem.TOTALPRICE as totalAmount, soitem.typeid, company.NAME AS companyName
FROM so
    JOIN STATECONST on STATECONST.ID = so.SHIPTOSTATEID
    JOIN soitem on soitem.SOID = so.ID,
    "COMPANY" COMPANY
WHERE
    so.DATECOMPLETED between $P{From} and $P{To}
    AND (UPPER(COALESCE(stateconst.code,'')) = UPPER($P{State}) OR so.SHIPTOZIP = $P{ZipCode})
    AND soitem.PRODUCTNUM != 'SHIPPING'
    AND soitem.PRODUCTNUM != 'SAMPLE'
    AND soitem.PRODUCTNUM != 'SHIP TO CONTACT'
    AND (soitem.typeid = 10 OR soitem.typeid = 20)
ORDER BY 1]]>
    </queryString>
    <field name="PRODUCTNUM" class="java.lang.String"/>
    <field name="ZIP" class="java.lang.String"/>
    <field name="QTY" class="java.lang.Double"/>
    <field name="STATEID" class="java.lang.Integer"/>
    <field name="STATEABBR" class="java.lang.String"/>
    <field name="SODATE" class="java.sql.Timestamp"/>
    <field name="TOTALAMOUNT" class="java.lang.Double"/>
    <field name="TYPEID" class="java.lang.Integer"/>
    <field name="COMPANYNAME" class="java.lang.String"/>
    <variable name="sumTotalQuantity" class="java.lang.Double" resetType="Group" resetGroup="product" calculation="Sum">
        <variableExpression><![CDATA[$F{QTY}]]></variableExpression>
    </variable>
    <variable name="sumGrandTotalQuantity" class="java.lang.Double" calculation="Sum">
        <variableExpression><![CDATA[$V{sumTotalQuantity}]]></variableExpression>
        <initialValueExpression><![CDATA[0d]]></initialValueExpression>
    </variable>
    <variable name="sumGrandTotalAmount" class="java.lang.Double" calculation="Sum">
        <variableExpression><![CDATA[$F{TOTALAMOUNT}]]></variableExpression>
    </variable>
    <group name="product">
        <groupExpression><![CDATA[$F{PRODUCTNUM}]]></groupExpression>
        <groupFooter>
            <band height="50">
                <textField evaluationTime="Group" evaluationGroup="product" pattern="###0.00">
                    <reportElement x="175" y="0" width="50" height="20" uuid="915c3f87-127a-4ec7-8b54-136612672c46"/>
                    <textElement textAlignment="Center"/>
                    <textFieldExpression><![CDATA[$V{sumTotalQuantity}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="375" y="0" width="75" height="20" uuid="4d241f98-7272-4c6e-bdc4-d395de916ede"/>
                    <textElement textAlignment="Center"/>
                    <textFieldExpression><![CDATA[$F{ZIP}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="479" y="0" width="75" height="20" uuid="3885c024-0db1-44f3-83c0-7f82a9d43a6c"/>
                    <textElement textAlignment="Center"/>
                    <textFieldExpression><![CDATA[$F{STATEABBR}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="0" y="0" width="150" height="50" uuid="16730885-6808-471e-b14c-cce62c865307"/>
                    <textFieldExpression><![CDATA[$F{PRODUCTNUM}]]></textFieldExpression>
                </textField>
                <textField pattern="¤ #,##0.00">
                    <reportElement x="250" y="0" width="100" height="20" uuid="1cc304ae-ca15-40d4-ab0c-122b6909677b"/>
                    <textElement textAlignment="Center"/>
                    <textFieldExpression><![CDATA[$F{TOTALAMOUNT}]]></textFieldExpression>
                </textField>
            </band>
        </groupFooter>
    </group>
    <title>
        <band height="50" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="555" height="25" uuid="02aceea0-791a-430c-9bb4-b6c0a62bb29a"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="16" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{COMPANYNAME}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="25" width="555" height="25" uuid="044c5bea-f482-4329-93d0-64433f0714a9"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="15" isBold="true"/>
                </textElement>
                <text><![CDATA[Sales Order Items By Zip]]></text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band height="20">
            <staticText>
                <reportElement x="301" y="0" width="20" height="20" uuid="e3eeac2b-12bb-43af-b133-1faeabdb0260"/>
                <textElement textAlignment="Center">
                    <font size="14"/>
                </textElement>
                <text><![CDATA[-]]></text>
            </staticText>
            <textField pattern="MM/dd/yyyy">
                <reportElement x="226" y="0" width="75" height="20" uuid="1c3c18a3-d58c-41a9-b157-9baecdc04a70"/>
                <textElement textAlignment="Right">
                    <font size="14"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{From}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="1" y="0" width="225" height="20" uuid="98522dcd-468c-4fa9-829c-e3a9349a07a4"/>
                <textElement textAlignment="Right">
                    <font size="14"/>
                </textElement>
                <text><![CDATA[Date Range:]]></text>
            </staticText>
            <textField pattern="MM/dd/yyyy">
                <reportElement x="321" y="0" width="75" height="20" uuid="29ec56c7-3aff-4119-9fcb-f533608bc9ab"/>
                <textElement>
                    <font size="14"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{To}]]></textFieldExpression>
            </textField>
        </band>
    </pageHeader>
    <columnHeader>
        <band height="25" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="150" height="20" uuid="b2e517f0-bd17-4b5c-a958-a6f71ecc6f3d"/>
                <textElement>
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Product #]]></text>
            </staticText>
            <staticText>
                <reportElement x="175" y="0" width="50" height="20" uuid="fffdc077-159e-446e-b110-a8c3f8b2e3a0"/>
                <textElement textAlignment="Center">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Qty]]></text>
            </staticText>
            <staticText>
                <reportElement x="375" y="0" width="75" height="20" uuid="fd05ad98-c3d1-43ff-a56a-9cfe47c56a7e"/>
                <textElement textAlignment="Center">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[ZIP]]></text>
            </staticText>
            <staticText>
                <reportElement x="479" y="0" width="75" height="20" uuid="a15d76c6-9a14-4089-bcb8-a6f58122004f"/>
                <textElement textAlignment="Center">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[State]]></text>
            </staticText>
            <staticText>
                <reportElement x="250" y="0" width="100" height="20" uuid="30e32f6b-9477-41f9-8eef-ef27995f5cb4"/>
                <textElement textAlignment="Center">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Sales Amount]]></text>
            </staticText>
            <line>
                <reportElement x="0" y="24" width="555" height="1" uuid="eaf8a705-5cbb-4809-9345-517f1128b919"/>
            </line>
        </band>
    </columnHeader>
    <summary>
        <band height="45">
            <staticText>
                <reportElement x="0" y="5" width="126" height="20" uuid="dc471d6e-e9a6-4351-9464-07b485708f0d"/>
                <textElement verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Grand Total Quantity:]]></text>
            </staticText>
            <staticText>
                <reportElement x="0" y="25" width="126" height="20" uuid="0d772f98-e1ef-449e-9dd0-7df1c0e4071d"/>
                <textElement verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Grand Total Sales:]]></text>
            </staticText>
            <textField evaluationTime="Report" pattern="#,##0">
                <reportElement x="126" y="5" width="75" height="20" uuid="450ee5e0-459b-4f7b-8990-68db5732c39d"/>
                <textElement textAlignment="Right" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$V{sumGrandTotalQuantity}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report" pattern="¤ #,##0.00">
                <reportElement x="126" y="25" width="75" height="20" uuid="9286c874-dc26-4aff-bf27-15b2dea28b9c"/>
                <textElement textAlignment="Right" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$V{sumGrandTotalAmount}]]></textFieldExpression>
            </textField>
            <line>
                <reportElement x="0" y="0" width="555" height="1" uuid="01b925b2-ff3c-4df3-a5bd-ca11defc4bf2"/>
            </line>
        </band>
    </summary>
</jasperReport>

Here is my query, screenshots of the variables and design view.

I can't see your jrxml, have you set correct evaluationTime on textField? — Petter Friberg, Apr 12 '16 at 19:53
@PetterFriberg No have not set the evaluation time. Should i? I'll try to post the JRXML too — Ashton, Apr 12 '16 at 19:56
@PetterFriberg I just posted a brand new question that is more of an SQL question and it's a tough one that I dont even think can be done. If you get time please check it out! http://stackoverflow.com/questions/37079467/place-an-insert-statement-into-a-select-statement — Ashton, May 06 '16 at 18:51

Petter Friberg · Accepted Answer · 2016-04-14T19:07:18.390

1

You need correct evaluationTime on your textField

$V{sumTotalQuantity}, needs to be evaluate when your group has been terminated

<textField pattern="###0.00"  evaluationTime="Group" evaluationGroup="product">          
   <reportElement x="175" y="0" width="50" height="20" uuid="915c3f87-127a-4ec7-8b54-136612672c46"/>
   <textElement textAlignment="Center"/>
   <textFieldExpression><![CDATA[$V{sumTotalQuantity}]]></textFieldExpression>
</textField>

$V{sumGrandTotalAmount}, should have evalutationTime="Report" (it currently works beacuse it is in the summary band, but if you move it to another band you need to set the evaluationTime.

<textField pattern="¤ #,##0.00" evaluationTime="Report">
    <reportElement x="126" y="25" width="75" height="20" uuid="9286c874-dc26-4aff-bf27-15b2dea28b9c"/>
    <textElement textAlignment="Right" verticalAlignment="Middle"/>
    <textFieldExpression><![CDATA[$V{sumGrandTotalAmount}]]></textFieldExpression>
</textField>

In iReport, select textField and set properties

EDIT: Don't sum on other variables sum on fields

<variable name="sumGrandTotalQuantity" class="java.lang.Double" calculation="Sum">
    <variableExpression><![CDATA[$F{QTY}]]></variableExpression>
    <initialValueExpression><![CDATA[0d]]></initialValueExpression>
</variable>

edited Apr 14 '16 at 19:07

answered Apr 13 '16 at 07:14

Petter Friberg

21,252
9
60
109

I set my QTY textField to evaluation time Group and evaluation goup product but it is still not working :/ – Ashton Apr 14 '16 at 17:04
It seems I am still getting the wrong quantities and I have no clue why because i have done what you said :/ – Ashton Apr 14 '16 at 17:56
1

Are you having problem with grandtotal or group total? – Petter Friberg Apr 14 '16 at 18:22
My issue is with the GrandTotalQuantity. I am not sure if it is the QTY text field or my grand total honestly. All i know is that the answer printed by the grand total is always way smaller – Ashton Apr 14 '16 at 18:28
1

On grand total you need resetType="Report" on variable and evaluationTime="Report" on textField – Petter Friberg Apr 14 '16 at 18:30
I have those set like that and still nothing :/ – Ashton Apr 14 '16 at 18:37
Try to set initial value to 0d (do not not why but I always do since I do not like null or int 0 : ), in your jrxml I can not see resetType on variable – Petter Friberg Apr 14 '16 at 18:41
1

Ping when you have done it.. in the mean time I will do some reviewing – Petter Friberg Apr 14 '16 at 18:56
Added the new JRXML and thank you for all of your help Petter! – Ashton Apr 14 '16 at 18:58
1

ok, do not do this <![CDATA[$V{sumTotalQuantity}]]> sum on the field $F{QTY} – Petter Friberg Apr 14 '16 at 19:00
so remove that from my JRXML? – Ashton Apr 14 '16 at 19:02
1

never sum on other variables, sum on the fields. – Petter Friberg Apr 14 '16 at 19:03
1

See the edit, just sum on the $F{QTY} with resetType report and you should be ok – Petter Friberg Apr 14 '16 at 19:08
Perfect! Now I know not to ever sum on a variable! Thank you Petter for taking the time and for helping/teaching me! – Ashton Apr 14 '16 at 19:10
Your welcome, back to reviewing some close votes, have fun – Petter Friberg Apr 14 '16 at 19:10
Petter I have another question if you could help me. I wanted to ping to see if you were available but my issue is with the same report. It seems my QTY are including the returned items so I wanted to see if there was a way to write a conditional statement with SQL to minus any item that has a soitem.status of 50 (credit return) and minus that QTY from being added. Is this posible? If so I will post a new question. Thank you! – Ashton Apr 15 '16 at 19:01
1

@Ashton I can't really understand but probably you can do it jasper report as well with if statement. – Petter Friberg Apr 15 '16 at 19:26
Petter please check out my new question with more information please! http://stackoverflow.com/questions/36655921/subtraction-between-2-conditions-in-sql-ireport – Ashton Apr 15 '16 at 20:03

Why do I have wrong number printing out on report while trying to SUM a group variable?

1 Answers1

Linked