Search code examples
mysqlsqljasper-reports

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.

Example:

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?

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}

enter image description here

Solution

  • 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: group

    Variable Setting:

    variable