将重复值设置为0，同时不保留第一个值 [英] Set a repeating value to 0 while leaving the first value alone

问题描述

我目前正在写报告，注意到我得到了重复的值.问题不是值重复，而是我得到的值在第一个初始值之后应该为0.

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.

例如:

我的报告显示的估计运输成本可能为$700，并且由于该订单被分为3批装运，因此该报告打印$700 3次.这是不正确的，因为预计的运输量应该只计算一次.

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.

我正在使用iReport

I am using iReport

我的问题是: 我如何保持第一个重复值相同，但将每个重复值设置为"0"后呢?

这是一幅可以更好地说明的图像.

Here is an image to better explain.

这是我的SQL

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

更新

现在我的变量shipPriceDif无法计算我的新变量estimatedShipPrice.即使在将变量设置为组之后.

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-

推荐答案

添加到您的SQL SOITEM."SOID"(我们检查是否重复)，您将获得一个字段

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

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

在SOID

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

然后创建一个变量，该组将重置发生计数的SOID

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>

此代码的jrxml中的顺序将为

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>

此变量现在可用于评估您是否有重复的SOID，当重复SOID

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

示例，如果重复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>

您希望改善报告的一天，只需在组中添加一个groupHeader带，然后将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

这篇关于将重复值设置为0，同时不保留第一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！