How to repeat report pages while a variable is > 0 ?

0

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.

bertrand.cedric77's picture
Joined: Mar 27 2020 - 9:48am
Last seen: 2 months 2 weeks ago

1 Answer:

0

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
*/

jgust's picture
248
Joined: Jun 10 2010 - 6:39am
Last seen: 3 hours 5 min ago
Feedback
randomness