Why do I have wrong number printing out on report

2019-08-14 02:19发布

站内问答 / 后端开发
1489 1 6

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.

enter image description here

enter image description here

enter image description here

enter image description here

1条回答
劳资没心，怎么记你
2楼-- · 2019-08-14 02:59

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

evaluationTime

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>
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)