CODEWITHSUNDEEP
jasper-reportsireport
Sergiu Costas
Sergiu Costas

Reputation: 560

Manipulate the date base in iReport

hope someone could help me to understand how to solve my question. I have in my datebase table PAYMENTS like this:

| PAYMENTS.PAYMENT | PAYMENTS.TOTAL   |  
| CASH             |1000              |  
| DEBT             |500               | 
| DEBTPAID         |200               |

The problem is: CASH is calculated as cash from sales plus DEBTPAID. So I need to have this data individualy (CASH-DEBTPAID AS CASH) in my report.

How can I do in iReport to modify PAYMENTS.PAYMENT as I describe above. I much appreciate your support! Thank you in advance!

Upvotes: 1

Views: 462

Answers (4)

Tom
Tom

Reputation: 6663

Sorry, I keep going around and around on this.

If you are looking for this:

| PAYMENTS.PAYMENT | PAYMENTS.TOTAL |
| CASH |800 |
| DEBT |500 |

This will give you that:

SELECT CASE WHEN PAYMENTS.PAYMENT IN ('CASH', 'DEBTPAID') THEN 'CASH' ELSE PAYMENTS.PAYMENT END PAYMENT,
       SUM(CASE WHEN PAYMENTS.PAYMENT= 'DEBTPAID' THEN -PAYMENTS.TOTAL ELSE PAYMENTS.TOTAL END) TOTAL

FROM   PAYMENTS

GROUP BY CASE WHEN PAYMENTS.PAYMENT IN ('CASH', 'DEBTPAID') THEN 'CASH' ELSE PAYMENTS.PAYMENT END

Upvotes: 1

Tom
Tom

Reputation: 6663

This should do it.

SELECT SUM(CASE WHEN PAYMENTS.PAYMENT= 'CASH' THEN PAYMENTS.TOTAL ELSE 0 END)
        -
       SUM(CASE WHEN PAYMENTS.PAYMENT= 'DEBTPAID' THEN PAYMENTS.TOTAL ELSE 0 END) AS CASH,
       SUM(CASE WHEN PAYMENTS.PAYMENT = 'DEBT' THEN PAYMENTS.TOTAL ELSE 0 END) AS DEBT

FROM   PAYMENTS

Upvotes: 1

Tom
Tom

Reputation: 6663

This SQL would give you a separate record for CASH and DEBT:

SELECT CASE WHEN PAYMENTS.PAYMENT IN ('CASH', 'DEBTPAID') THEN 'CASH' ELSE PAYMENTS.PAYMENT END AS PAYMENT,
       SUM(PAYMENTS.TOTAL) AS TOTAL

FROM   PAYMENTS

GROUP BY CASE WHEN PAYMENTS.PAYMENT IN ('CASH', 'DEBTPAID') THEN 'CASH' ELSE PAYMENTS.PAYMENT END

Upvotes: 1

Tom
Tom

Reputation: 6663

This SQL statement will return you the total CASH and total DEBT in a single record.

SELECT SUM(CASE WHEN PAYMENTS.PAYMENT IN ('CASH', 'DEBTPAID') THEN PAYMENTS.TOTAL ELSE 0 END)
       AS CASH,
       SUM(CASE WHEN PAYMENTS.PAYMENT = 'DEBT' THEN PAYMENTS.TOTAL ELSE 0 END) AS DEBT

FROM   PAYMENTS

This would give you the correct totals based on the data you gave us.

If you wanted separate records it would have to be done a little differently (I could show you).

Upvotes: 1

Related Questions