总计成本并显示每个项目 [英] Totaling up costs and Displaying each item

问题描述

我正在撰写报告，我正在显示所有商品的总费用，具体取决于日期范围已选中。

I am writing a report and I am displaying the total cost of all items depending on the date range selected.

目前这些物品都显示完全正常，但如果你发现有些物品正在显示多次（默认情况下很好）。

At the moment the items are all displaying perfectly fine but if you notice some items are displaying multiple times (which is by default fine).

我想要按每个项目计算一次，并且每次只显示一次。

I am wanting to make a total by each item and display them each only once.

示例：

在我的照片中，您可以看到 SOFTT-W 部分显示5次。我想让它只出现一次，但它们的总计和显示的 Cost 。我也希望所有项目都能发生同样的事情。

In my photo you can see that the part SOFTT-W is displayed 5 times. I am wanting to have it only appear one time but have the Cost of them all totaled and displayed. Also I want the same thing to happen with all items.

有没有办法可以做到这一点？

Is there a way I can do this?

我将发布我的SQL代码并附上截图。

I will post my SQL code and attach a screenshot.

SELECT
 COMPANY."NAME" AS COMPANY_NAME,
 CUSTOMER."NAME" AS CUSTOMER_NAME,
 SO."NUM" AS SO_NUM,
 SOITEM."STATUSID" AS SOITEM_STATUSID,
 PRODUCT."ID" AS PRODUCT_ID,
 PART."NUM" AS PART_NUM,
 SOITEM."QTYFULFILLED" AS SOITEM_QTYFULFILLED,
 SO."COST" AS SO_COST,
 PART."ID" AS PART_ID,
 SO."DATECOMPLETED" AS SO_DATECOMPLETED,
 CUSTOMER."ID" AS CUSTOMER_ID,
 SOSTATUS."ID" AS SOSTATUS_ID,
 PARTCOST."TOTALCOST" AS PARTCOST_TOTALCOST,
 SOITEM."TOTALCOST" AS SOITEM_TOTALCOST,
 SO."CUSTOMERID" AS SO_CUSTOMERID
FROM
 "CUSTOMER" CUSTOMER INNER JOIN "SO" SO ON CUSTOMER."ID" = SO."CUSTOMERID"
 INNER JOIN "SOITEM" SOITEM ON SO."ID" = SOITEM."SOID"
 INNER JOIN "SOSTATUS" SOSTATUS ON SO."STATUSID" = SOSTATUS."ID"
 INNER JOIN "PRODUCT" PRODUCT ON SOITEM."PRODUCTID" = PRODUCT."ID"
 INNER JOIN "PART" PART ON PRODUCT."ID" = PART."DEFAULTPRODUCTID"
 AND PART."ID" = PRODUCT."PARTID"
 INNER JOIN "PARTCOST" PARTCOST ON PART."ID" = PARTCOST."PARTID",
 "COMPANY" COMPANY
WHERE
 SO."STATUSID" = 60
 AND (PART."ID" != 947)
 AND CUSTOMER."ID" = $P{customerID}
 AND SO."DATECOMPLETED" BETWEEN $P{From} AND $P{To}

---

---

推荐答案

最简单的解决方案是使用在你的陈述结束时命令。然后，您可以根据partnum在iReport中使用组，并总计总成本。

The easiest solution would be to use an order by at the end of your statement. You can then use a group within iReport based on the partnum and sum the total costs.

SELECT
    COMPANY.NAME AS COMPANY_NAME,
    CUSTOMER.NAME AS CUSTOMER_NAME,
    SO.NUM AS SO_NUM,
    SOITEM.STATUSID AS SOITEM_STATUSID,
    PRODUCT.ID AS PRODUCT_ID,
    PART.NUM AS PART_NUM,
    SOITEM.QTYFULFILLED AS SOITEM_QTYFULFILLED,
    SO.COST AS SO_COST,
    PART.ID AS PART_ID,
    SO.DATECOMPLETED AS SO_DATECOMPLETED,
    CUSTOMER.ID AS CUSTOMER_ID,
    SOSTATUS.ID AS SOSTATUS_ID,
    PARTCOST.TOTALCOST AS PARTCOST_TOTALCOST,
    SOITEM.TOTALCOST AS SOITEM_TOTALCOST,
    SO.CUSTOMERID AS SO_CUSTOMERID
FROM
    CUSTOMER CUSTOMER INNER JOIN SO SO ON CUSTOMER.ID = SO.CUSTOMERID
    INNER JOIN SOITEM SOITEM ON SO.ID = SOITEM.SOID
    INNER JOIN SOSTATUS SOSTATUS ON SO.STATUSID = SOSTATUS.ID
    INNER JOIN PRODUCT PRODUCT ON SOITEM.PRODUCTID = PRODUCT.ID
    INNER JOIN PART PART ON PRODUCT.ID = PART.DEFAULTPRODUCTID
    AND PART.ID = PRODUCT.PARTID
    INNER JOIN PARTCOST PARTCOST ON PART.ID = PARTCOST.PARTID,
    COMPANY COMPANY
WHERE
    SO.STATUSID = 60
    AND (PART.ID != 947)
    AND CUSTOMER.ID = $P{customerID}
    AND SO.DATECOMPLETED BETWEEN $P{From} AND $P{To}
ORDER BY customer.name, part.num

组设置：

Group setting:

变量设定：

这篇关于总计成本并显示每个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！