Jasper report: two detail sections?

Question

I've got a jasper report to do, with data like this:

Item  | Quantity  | Color
------+-----------+--------
A001  | 1         | Red
A001  | 1         | Green
B002  | 3         | Red
B002  | 3         | Purple

The report is grouped by Item/Quantity, e.g.

Item: A001,  Qty: 1,  Colors: Red,Green
Item: B002,  Qty: 3,  Colors: Red,Purple

Now I've got this Jasper report that already groups as such - i.e. shows a heading with the item and quantity, with a list of colors underneath.

The question now is, underneath this group I need to display a number of horizontal lines (for someone to write something in), equal to the qty of the item. e.g. underneath the A001 group I need to display one line, and under the B002 group I need to display three lines, like so:

Item A001, Qty 1, Colors Red, Green
          _________________________

Item B002, Qty 3, Colors Red, Purple
          _________________________
          _________________________
          _________________________

I've tried looking at jasper scripts, but it seems they can only manipulate report parameters/variables.

Does anyone have an idea of how I could do this?

Answer 1

Hmm, interesting.

Here is what you could do:

Using this source data (MySQL):

create table items ( 
item varchar(4), 
quantity number,   
color varchar(10),
);

(insert data...)

create table numbers (i integer)

(insert data 0, 1, 2 .... MySQL 5.1 has stored procedures that could do it, earlier versions would need an external script to populate it. Go from 0 to the largest quantity you'd have).

Then, the trick is to craft the right sort of query. I came up with this:

select i.*, n.i from
(
select concat(i.item, ' ', i.quantity) as grouping, i.item, i.quantity,group_concat(distinct color) as colors
from items i 
GROUP BY item, quantity
) i
cross join numbers n 
where quantity > n.i;

E.g. If I populate my numbers table, and the populate the items table with your example data, and then run the query I get:

+----------+------+----------+------------+------+
| grouping | item | quantity | colors     | i    |
+----------+------+----------+------------+------+
| A001 1   | A001 |        1 | Red,Greem  |    0 |
| B002 3   | B002 |        3 | Red,Purple |    0 |
| A001 1   | A001 |        1 | Red,Greem  |    1 |
| B002 3   | B002 |        3 | Red,Purple |    1 |
| A001 1   | A001 |        1 | Red,Greem  |    2 |
| B002 3   | B002 |        3 | Red,Purple |    2 |
| A001 1   | A001 |        1 | Red,Greem  |    3 |
| B002 3   | B002 |        3 | Red,Purple |    3 |
+----------+------+----------+------------+------+

Then in your Jasper Report, the trick is to create a group/band that works off the 'grouping' column, and put your heading in that:

Item A001, Qty 1, Colors Red, Green

And then, in the detail section just have a line as the only thing in the detail.

Doing this generates the report you want for me.

Note that the numbers table is a little silly, but is a standard data warehousing technique, though I suspect some database (e.g. Oracle) would have clever recursive procedures or other functions that would exclude the need for it.

Answer 2

OK, in the end, this was done using a subdataset and a crosstab in the detail footer section. Works nicely - thanks for everyone who contributed :)