jasper-reports
Why is my expression off by 1 record?
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>
Solution
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 |
+-------+-----------+--------+------------+
- jasperreports: vertical arrangement in jr:list doesn't work and results in overlapping elements
- Jasper Reports throws Exception when running JAR but working PERFECTLY in IntelliJ IDE
- Filling a Jasper Report but getting nulls
- The subreport repeats itself in Detail band
- JasperReport: How to get date from the "oracle.sql.TIMESTAMP' field?
- jrxml uuid attribute not allowed with JasperReports 6.21.0
- cvc-complex-type.3.2.2 attribute 'uuid' is not allowed to appear in element 'jasperreport'
- Jasper soft report expression to get comma separated string from object array parameter
- Extra blank page at the end of the report JasperReports
- How to display in Jasper BLOB Image?
- How to get date six month before current date in single line in Java?
- Jasper Report : Errors were encountered when compiling report expressions class file On server
- How to use date range type parameter as normal java.util.date in jasper
- JsonQLDataSource cannot be resolved to a type
- How to print page number to other language in Jasper Report by Jaspersoft Studio?
- jasper text field getting truncated
- How to show column at Crosstab even the data is absent
- Pass Fields to Subreports in Jasper Reports
- How to add table of content in Jasper report
- Grouping in Jasper Reports
- Jasper Reports Show "Page X of Y" using a single text field
- JasperReports (6.20.1) dependency not satisfied in Java project : Failure to find com.github.librepdf:openpdf:jar:1.3.30.jaspersoft.1
- How can I use parameter for lineColor in JasperReports?
- Jasper Reports Excel Output Problems
- Jasper Reports can't locate "package net.sf.jasperreports.engine does not exist" while running jar but everything working fine in IDE
- Enable Jasper Studio Webservice plugin
- Jaspersoft studio adjust position of fields relative to height of other fields
- How to add DynamicJasper report to Jasper Report as sub-report Dynamically
- Jasper Reports: JRBeanCollectionDataSource cannot be resolved to a type
- How to export to multiple sheets in excel from jasper report