Search code examples
mysqljasper-reports

Set a repeating value to 0 while leaving the first value alone

I am currently writing a report and noticed that I am getting repeated values. The issue is not that the values are repeating but that I am getting values that should be 0 after the first initial value.

For example:

My report is displaying the estimated shipping cost which might be $700 and since the order was broken up into 3 shipments, the report prints $700 3 times. This is not true because the estimated shipping should only be calculated one time.

I am using iReport

My question is: How do I keep the first repeating value the same but every value after it set to '0' ?

Here is an image to better explain. enter image description here

This is my SQL

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
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

UPDATE

Now my variable shipPriceDif is not able to calculate my new variable estimatedShipPrice. Even after setting the variable to the group.

Here is a picture to explain further-

enter image description here

enter image description here

enter image description here

enter image description here

Solution

  • Add to your sql SOITEM."SOID" (we check if this is duplicated), you get a field

    <field name="SOID" class="java.lang.Integer"/>

    Create a group on the SOID

    <group name="SOID">
    <groupExpression><![CDATA[$F{SOID}]]></groupExpression>
</group>

    Then create a variable that reset's with the group counting the occurrence of SOID

    <variable name="SOID_Count" class="java.lang.Integer" resetType="Group" resetGroup="SOID" calculation="Count">
    <variableExpression><![CDATA[$F{SOID}]]></variableExpression>
</variable>

    The sequence in the jrxml of this code will be

    <field name="SOID" class="java.lang.Integer"/>
<variable name="SOID_Count" class="java.lang.Integer" resetType="Group" resetGroup="SOID" calculation="Count">
    <variableExpression><![CDATA[$F{SOID}]]></variableExpression>
</variable>
<group name="SOID">
    <groupExpression><![CDATA[$F{SOID}]]></groupExpression>
</group>

    This variable can now be used to evaluate if you have duplicated SOID, it will be >1 when duplicated SOID

    Example of textField that display 0 if duplicated SOID

    <textField pattern="###0">
    <reportElement x="143" y="0" width="105" height="20" uuid="a0e2ae10-906e-4d0f-aebd-30fc0c694aca">
    </reportElement>
    <textElement textAlignment="Right" verticalAlignment="Middle"/>
    <textFieldExpression><![CDATA[$V{SOID_Count}<=1?$F{SOITEM_UNITPRICE}:0]]></textFieldExpression>
</textField>

    The day you like to improve your report, just add a groupHeader band in your group and then put the textField's in this band