Hi all,
I'm trying to create a report which is composed by several same pages while a variable is > 0. The use case is to generate pallet identification paper.
I have an SQL query which return just one row. In this row, I have the global quantity of product to produce and the maximum quantity of product that a pallet can contains. So for each page of my report, a need a variable containing the remaining quantity and while that quantity is > 0, I need to create a new page (new pallet identification paper).
For example : Quantity to produce = 9500, max quantity per pallet = 2000.
First pallet identification paper will show the quantity on the first pallet : 2000 => remaining quantity = 7500
Second pallet will show 2000 = > remaining quantity 5500
Third pallet will show 2000 => remaining quantity 3500
Fourth pallet will show 2000 => remaining quantity 1500
Fifth pallet will show 1500 => remaining quantity = 0
End of the report.
I tried creating a variable with initial value of the global quantity, with expression $V{variable} - $F{maxQuantityPerPallet} and to create a group on that variable but I only have one page, even if I see that the second value of the variable is different, an other group is not created...
How can I achieve that ?
I hope that I'm clear.
Thank you.
1 Answer:
What I would do is force the data to reflect the number of pages you need. In this case, you need five rows.
The below example is for Oracle. I'm sure you can find an equivalent in other languages using a temp table and CROSS JOIN to simulate the forced row aspect.
WITH FiftyRows AS ( SELECT LEVEL AS PALLET , 2000 AS QUANTITY , 2000 * LEVEL AS TOTAL_QUANITY FROM DUAL CONNECT BY LEVEL <= 50 ) SELECT * FROM FiftyRows WHERE TOTAL_QUANITY <= 9500 UNION ALL SELECT MAX(PALLET)+1 AS PALLET , (9500 - MAX(TOTAL_QUANITY)) AS QUANTITY , (9500 - MAX(QUANTITY)) + MAX(QUANTITY) AS TOTAL_QUANITY FROM FiftyRows WHERE TOTAL_QUANITY <= 9500 ; /* PALLET QUANTITY TOTAL_QUANITY 1 2000 2000 2 2000 4000 3 2000 6000 4 2000 8000 5 1500 9500 */
