CODEWITHSUNDEEP
birt
Miker Irick
Miker Irick

Reputation: 13

Compute value based on next row in BIRT

I am creating a BIRT Report where each row is a receipt matched with a purchase order. There are usually more than one receipt per purchase order. My client wants the qty_remaining on the purchase order to show only on the last receipt for each purchase order. I am not able to alter the data before BIRT gets it. I see two possible solutions, but I am unable to find how to implement either. This question will deal with first possible solution.

If I can compare the purchase order number(po_number) with the next row, then I can set the current row's qty_remaining to 0 if the po_numbers match else show the actual qty_remaining. Is it possible to access the next row?

Edit

The desired look is similar to this:

| date | receipt_number | po_number | qty_remaining | qty_received | |------|----------------|-----------|---------------|--------------| | 4/9 | 723 | 6026 | 0 | 985 | | 4/9 | 758 | 6026 | 2 | 1 | | 4/20 | 790 | 7070 | 58 | 0 | | 4/21 | 801 | 833 | 600 | 0 |

But I'm currently getting this:

| date | receipt_number | po_number | qty_remaining | qty_received | |------|----------------|-----------|---------------|--------------| | 4/9 | 723 | 6026 | 2 | 985 | | 4/9 | 758 | 6026 | 2 | 1 | | 4/20 | 790 | 7070 | 58 | 0 | | 4/21 | 801 | 833 | 600 | 0 |

Upvotes: 1

Views: 1051

Answers (1)

Jeroen
Jeroen

Reputation: 1638

I think you looking at this the wrong way. If you want behavior that resembles for loops you should use grouping and aggregate functions. You can build quite complex stuff by using (or not using) the group headers and footers.

In your case I would try to group the receipts on po_number. Order them by receipt_number then have a aggregate function like MAX or LAST on the receipts_number and name it 'last_receipt'. It should aggregate on the group, not the whole table. This 'total' is available on every row within the group. Then you can use the visibitly setting to only show the qty_remaining when the row['receipt_number'] == row['last_receipt']

Upvotes: 0

Related Questions