Why is my expression off by 1 record?

2019-08-09 15:46发布

站内文章 / 移动开发
21 0 0

问题:

I have a report that i am making using iReport and I have an equation that is helping me find duplicate Estimated Shipping variables and make the initial number stay the same but the ones displayed after it appear as 0. I want this because the report shows the changes made to the Sales Order but I dont want the report showing that the person was charged the initial Estimated Shipping ammount- every time.

The equation I am using to find the duplicates is

$V{SOID_Count}<=1?$F{SOITEM_UNITPRICE}:0

This works but if you notice in this picture the numbers are behind by one. It is subtracting the Estimated Shipping amount that was above it- from the current Actual Shipping amount that you are on.

So how can I alter my counter equation to fix this?

Thank you for any help!

This is my XML

<?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="ShippingPriceDifference" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="97636279-f825-4b72-b9f8-8ce9740dce34">
<property name="ireport.zoom" value="1.5"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<parameter name="To" class="java.util.Date">
    <parameterDescription><![CDATA[asdf]]></parameterDescription>
</parameter>
<parameter name="From" class="java.util.Date"/>
<queryString>
    <![CDATA[SELECT
 CUSTOMER."NAME" AS CUSTOMER_NAME,
 SO."NUM" AS SO_NUM,
 COMPANY."NAME" AS COMPANY_NAME,
 SHIPCARTON."FREIGHTAMOUNT" AS SHIPCARTON_FREIGHTAMOUNT,
 SHIP."SOID" AS SHIP_SOID,
 SOITEM."UNITPRICE" AS SOITEM_UNITPRICE,
 PRODUCT."PARTID" AS PRODUCT_PARTID,
 SHIP."DATESHIPPED" AS SHIP_DATESHIPPED,
 SOITEM."SOID" AS SOITEM_SOID
FROM
 "CUSTOMER" CUSTOMER INNER JOIN "SO" SO ON CUSTOMER."ID" = SO."CUSTOMERID"
 INNER JOIN "SHIP" SHIP ON SO."ID" = SHIP."SOID"
 INNER JOIN "SOITEM" SOITEM ON SO."ID" = SOITEM."SOID"
 INNER JOIN "PRODUCT" PRODUCT ON SOITEM."PRODUCTID" = PRODUCT."ID"
 INNER JOIN "SHIPCARTON" SHIPCARTON ON SHIP."ID" = SHIPCARTON."SHIPID",
 "COMPANY" COMPANY
WHERE
 PRODUCT."PARTID" = 947
 AND SHIP."DATESHIPPED" BETWEEN $P{From} AND $P{To}
 AND SHIPCARTON."FREIGHTAMOUNT" != 0
ORDER BY
 2 ASC]]>
</queryString>
<field name="CUSTOMER_NAME" class="java.lang.String"/>
<field name="SO_NUM" class="java.lang.String"/>
<field name="COMPANY_NAME" class="java.lang.String"/>
<field name="SHIPCARTON_FREIGHTAMOUNT" class="java.lang.Double"/>
<field name="SHIP_SOID" class="java.lang.Integer"/>
<field name="SOITEM_UNITPRICE" class="java.lang.Double"/>
<field name="PRODUCT_PARTID" class="java.lang.Integer"/>
<field name="SHIP_DATESHIPPED" class="java.sql.Timestamp"/>
<field name="SOITEM_SOID" class="java.lang.Integer"/>
<variable name="shipPriceDif" class="java.lang.Double">
    <variableExpression><![CDATA[($F{SHIPCARTON_FREIGHTAMOUNT})-    ($V{estimatedShipping})]]></variableExpression>
</variable>
<variable name="totalShipPriceDif" class="java.lang.Double"  calculation="Sum">
    <variableExpression><![CDATA[$V{shipPriceDif}]]></variableExpression>
</variable>
<variable name="SOID_Count" class="java.lang.Integer" resetType="Group" resetGroup="SOID" calculation="Count">
    <variableExpression><![CDATA[$F{SOITEM_SOID}]]></variableExpression>
</variable>
<variable name="estimatedShipping" class="java.lang.Double">
    <variableExpression><![CDATA[$V{SOID_Count}<=1?$F{SOITEM_UNITPRICE}:0]]>    </variableExpression>
</variable>
<group name="SOID">
    <groupExpression><![CDATA[$F{SOITEM_SOID}]]></groupExpression>
</group>
<background>
    <band splitType="Stretch"/>
</background>
<title>
    <band height="27" splitType="Stretch">
        <textField>
            <reportElement x="0" y="0" width="555" height="25" uuid="64a1704a-b221-48a4-8186-1b855ead5acf"/>
            <textElement textAlignment="Center">
                <font fontName="SansSerif" size="16" isBold="true"/>
            </textElement>
            <textFieldExpression><![CDATA[$F{COMPANY_NAME}]]></textFieldExpression>
        </textField>
    </band>
</title>
<pageHeader>
    <band height="59" splitType="Stretch">
        <staticText>
            <reportElement x="0" y="0" width="555" height="25" uuid="e20cae54-5d75-4e81-800c-55390d1ec465"/>
            <textElement textAlignment="Center">
                <font fontName="SansSerif" size="14"/>
            </textElement>
            <text><![CDATA[Shipping Price Difference ]]></text>
        </staticText>
        <staticText>
            <reportElement x="0" y="25" width="167" height="20" uuid="41be06d8-8fdf-4e3b-b526-1aea092ea734"/>
            <textElement textAlignment="Right">
                <font fontName="SansSerif" size="14" isBold="false"/>
            </textElement>
            <text><![CDATA[Date Range:]]></text>
        </staticText>
        <staticText>
            <reportElement x="267" y="25" width="20" height="20" uuid="a402544b-73b3-4132-8161-be4c202e9b14"/>
            <textElement textAlignment="Center">
                <font fontName="SansSerif" size="14" isBold="false"/>
            </textElement>
            <text><![CDATA[-]]></text>
        </staticText>
        <textField>
            <reportElement x="287" y="25" width="100" height="20" uuid="85e7fe2b-8149-4b18-8410-16b026cdb0f3"/>
            <textElement verticalAlignment="Middle">
                <font fontName="SansSerif" size="14"/>
            </textElement>
            <textFieldExpression><![CDATA[$P{To}]]></textFieldExpression>
        </textField>
        <textField>
            <reportElement x="167" y="25" width="100" height="20" uuid="ea618bea-9201-4eba-abff-cfdc891ac76f"/>
            <textElement textAlignment="Right" verticalAlignment="Middle">
                <font fontName="SansSerif" size="14"/>
            </textElement>
            <textFieldExpression><![CDATA[$P{From}]]></textFieldExpression>
        </textField>
    </band>
</pageHeader>
<columnHeader>
    <band height="21" splitType="Stretch">
        <staticText>
            <reportElement x="0" y="0" width="140" height="20" uuid="82883a63-752d-4265-8158-a5129b617b7d"/>
            <textElement>
                <font size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[Customer Name]]></text>
        </staticText>
        <staticText>
            <reportElement x="140" y="0" width="75" height="20" uuid="2d302854-c68c-4e5a-b94b-900c88937b9d"/>
            <textElement textAlignment="Center">
                <font size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[SO #]]></text>
        </staticText>
        <staticText>
            <reportElement x="455" y="0" width="100" height="20" uuid="50c6ba0f-a5df-4aa3-8a47-282271055053"/>
            <textElement textAlignment="Center">
                <font size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[Difference]]></text>
        </staticText>
        <line>
            <reportElement x="0" y="19" width="555" height="1" uuid="be790d1c-d6d6-4169-b743-a98654c025e9"/>
        </line>
        <staticText>
            <reportElement x="215" y="0" width="120" height="20" uuid="ffbe1333-86b8-435f-b914-46bb1c747683"/>
            <textElement textAlignment="Center">
                <font size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[Estimated Shipping]]></text>
        </staticText>
        <staticText>
            <reportElement x="335" y="0" width="120" height="20" uuid="3f683f6b-19f6-49dd-9ee1-787b216aaa2f"/>
            <textElement textAlignment="Center">
                <font size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[Actual Shipping]]></text>
        </staticText>
    </band>
</columnHeader>
<detail>
    <band height="21" splitType="Stretch">
        <textField isBlankWhenNull="true">
            <reportElement x="0" y="0" width="140" height="20" isRemoveLineWhenBlank="true" uuid="fdd69c30-9da5-4bd2-a7b8-44a506e2d7ff"/>
            <textFieldExpression><![CDATA[$F{CUSTOMER_NAME}]]></textFieldExpression>
        </textField>
        <textField isBlankWhenNull="true" hyperlinkType="Reference">
            <reportElement x="140" y="0" width="75" height="20" isRemoveLineWhenBlank="true" forecolor="#000099" uuid="19db6cb8-af37-46e6-9227-f1b55fc49f22"/>
            <textElement textAlignment="Center"/>
            <textFieldExpression><![CDATA[$F{SO_NUM}]]></textFieldExpression>
            <anchorNameExpression><![CDATA["SONum"]]></anchorNameExpression>
            <hyperlinkReferenceExpression><![CDATA[$F{SO_NUM}]]></hyperlinkReferenceExpression>
        </textField>
        <textField pattern="#,##0.00" isBlankWhenNull="true">
            <reportElement x="335" y="0" width="120" height="20" isRemoveLineWhenBlank="true" uuid="006475e5-d36f-4482-97cf-2c2eda2d5a66"/>
            <textElement textAlignment="Center"/>
            <textFieldExpression><![CDATA[$F{SHIPCARTON_FREIGHTAMOUNT}]]></textFieldExpression>
        </textField>
        <textField pattern="#,##0.00" isBlankWhenNull="true">
            <reportElement x="215" y="0" width="120" height="20" isRemoveLineWhenBlank="true" uuid="376d9c76-1d96-48ea-ac4a-40770ba28867"/>
            <textElement textAlignment="Center"/>
            <textFieldExpression><![CDATA[$V{estimatedShipping}]]></textFieldExpression>
        </textField>
        <textField pattern="#,##0.00;-#,##0.00" isBlankWhenNull="true">
            <reportElement x="455" y="0" width="100" height="20" isRemoveLineWhenBlank="true" uuid="c236c087-e6f4-4de6-a3d6-8abb8b798346"/>
            <textElement textAlignment="Center"/>
            <textFieldExpression><![CDATA[$V{shipPriceDif}]]></textFieldExpression>
        </textField>
    </band>
</detail>
<pageFooter>
    <band height="22" splitType="Stretch">
        <textField>
            <reportElement x="435" y="0" width="80" height="20" uuid="64e6c2aa-58eb-4994-83c3-16cb70197fef"/>
            <textElement textAlignment="Right"/>
            <textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
        </textField>
        <textField evaluationTime="Report">
            <reportElement x="515" y="0" width="40" height="20" uuid="09e2e08b-c9b1-4a5b-95fd-1c0a0dba17a2"/>
            <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
        </textField>
    </band>
</pageFooter>
<summary>
    <band height="22" splitType="Stretch">
        <textField pattern="#,##0.00;-#,##0.00">
            <reportElement x="150" y="1" width="75" height="20" uuid="8ab3526d-e89e-4043-86dd-52d89a2b7b21"/>
            <textElement verticalAlignment="Top">
                <font size="12" isBold="true"/>
            </textElement>
            <textFieldExpression><![CDATA[$V{totalShipPriceDif}]]></textFieldExpression>
        </textField>
        <staticText>
            <reportElement x="140" y="1" width="10" height="20" uuid="110043fb-4dde-414e-b07f-f79cbb8839ab"/>
            <textElement textAlignment="Right">
                <font size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[$]]></text>
        </staticText>
        <line>
            <reportElement x="0" y="0" width="555" height="1" uuid="e81602ee-7eab-43f3-90c1-cf69086623a9"/>
        </line>
        <staticText>
            <reportElement x="0" y="1" width="140" height="20" uuid="095559e8-33e4-4a1a-9537-f8dff2b533d6"/>
            <textElement>
                <font size="12" isBold="true"/>
            </textElement>
            <text><![CDATA[Total Cost Difference :]]></text>
        </staticText>
    </band>
</summary>
 </jasperReport>

回答1:

Your problem is that the $V{estimatedShipping} is evaluated after your variable $V{shipPriceDif} since its after in your variable definition, or move it above or dump it...

Just calculate the Difference in the textField

<textField pattern="#,##0.00;-#,##0.00" isBlankWhenNull="true">
    <reportElement x="455" y="0" width="100" height="20" isRemoveLineWhenBlank="true" uuid="c236c087-e6f4-4de6-a3d6-8abb8b798346"/>
    <textElement textAlignment="Center"/>
    <textFieldExpression><![CDATA[$V{estimatedShipping}.doubleValue()-$F{SHIPCARTON_FREIGHTAMOUNT}.doubleValue()]]></textFieldExpression>
</textField>

The output of this would be:

+-------+-----------+--------+------------+
|  SO#  | Estimated | Actual | Difference |
+-------+-----------+--------+------------+
| 46137 | 700       | 58,38  | 641,62     |
| 46137 | 0         | 57,12  | -57.12     |
| 46137 | 0         | 28,29  | -28,29     |
| 46148 | 0,85      | 267,32 | -266,47    |
+-------+-----------+--------+------------+


标签： jasper-reports
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~