Totaling up costs and Displaying each item

Question

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}

Are you familiar with `GROUP BY`? Your query is pretty sophisticated, so I would think that you are. — Gordon Linoff, Mar 11 '16 at 20:53
You like to have all the row but SOFTT-W only displayed once?, or do you like to group and sum, can you put an image or table of expected output — Petter Friberg, Mar 11 '16 at 20:54
@GordonLinoff I am actually just now learning those. I am still confused on them a bit sadly :/ — Ashton, Mar 11 '16 at 21:09
@PetterFriberg please take a look at my new question please! http://stackoverflow.com/questions/36486850/tables-are-relational-but-report-does-not-run?noredirect=1#comment60583115_36486850 — Ashton, Apr 07 '16 at 20:57

score 1 · Accepted Answer · answered Mar 11 '16 at 20:54

1

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:

Variable Setting:

answered Mar 11 '16 at 20:54

Mamof

181
5
13

1

The variable and group should be on the sub-report with all of your main data. Running the parent report with just the company name will work just fine. – Mamof Mar 14 '16 at 18:59
1

If you want to show all rows but only have the part show up once you can either put the part number in the group header band or you can uncheck the "Print Repeated Values" option on the part number field. Either way you'll want to put the grand total in the group footer band to have it show up at the end of the part group. – Mamof Mar 15 '16 at 14:35
1

With that you can go through and put the part number and the totals in the group footer and remove all other fields from the bands. – Mamof Mar 15 '16 at 14:46
1

What band do you have the part number? If you have it in the group footer it should only be displaying once as well. – Mamof Mar 15 '16 at 15:00
Oh!! I had it in a detail band! Question- are you getting increased reputation when I upvote the comments? If not let me know and I will repost my question to make sure you are. I really appreciate all of the guidance! – Ashton Mar 15 '16 at 15:05
1

Just up voting questions get counted. Once we get the question answered comments are the best way to go as each of your other questions are directly related to the answer provided. If I provide an answer that doesn't cover the primary question then the answer should be updated to make sure it's addressed fully. – Mamof Mar 15 '16 at 16:39
Mamof please take a look at my new question please! http://stackoverflow.com/questions/36486850/tables-are-relational-but-report-does-not-run?noredirect=1#comment60583115_36486850 – Ashton Apr 07 '16 at 20:57

Totaling up costs and Displaying each item

1 Answers1

Linked