Is there way to generate table with subtotal in Jasper Report?

Question

I already get some reference for subtotal using querystring. I want to use subDataset and JRBeanCollectionDataSource. How can I do in template for subtotal?

template

<subDataset name="dataSource">
    <field name="product" class="java.lang.String"/>
    <field name="qty" class="java.lang.Integer"/>
    <field name="date" class="java.lang.Date"/>
</subDataset>
<parameter name="TableDataSource" class="net.sf.jasperreports.engine.JRDataSource"/>
....
<jr:table ...>
    <datasetRun subDataset="dataSource">
        <dataSourceExpression><![CDATA[$P{TableDataSource}]]></dataSourceExpression>
    </datasetRun>
    ......  
</jr:table>

program

list.add(new Product("AAA", 100, date));
list.add(new Product("AAA", 100, date));
list.add(new Product("AAA", 100, date));
list.add(new Product("BBB", 200, date));
list.add(new Product("BBB", 150, date));
list.add(new Product("BBB", 100, date));
new JRBeanCollectionDataSource(list));

Expected output

+--------+-----------+-------+
|Product |    Date   |  Qty  |
+--------+-----------+-------+
|AAA     | 08-08-210 | 100   |
|        | 08-09-210 | 100   |
|        | 08-10-210 | 100   |
+--------+-----------+-------|
|           SubTotal | 300   |
+--------+-----+-------------+
|BBB     | 08-08-210 | 200   |
|        | 08-09-210 | 150   |
|        | 08-10-210 | 100   |
+--------+-----------+-------|
|           SubTotal | 450   |
+--------+-----+-------------+

yes, This is what I want. I only get reference with `querystring`. I would like to use `JRBeanCollectionDataSource`. — Zaw Than oo, Sep 11 '14 at 11:36
Are you using iReport? If so, try create a group by product or description. — cassiomolin, Sep 11 '14 at 11:46
@CássioMazzochiMolin, I use it. It cannot create because of these field is inside of `subDataset'. — Zaw Than oo, Sep 12 '14 at 05:18

score 0 · Answer 1 · answered Sep 11 '14 at 11:33

I think it's simple to calculate it out of the report and place in the DataSource

list.add(new Product("AAA", 100, date));
list.add(new Product("AAA", 100, date));
list.add(new Product("AAA", 100, date));
list.add(new Product("TOTAL:", 300, date)); //total AAA
list.add(new Product("BBB", 200, date));
list.add(new Product("BBB", 150, date));
list.add(new Product("BBB", 100, date));
list.add(new Product("TOTAL:", 450, date)); //total BBB
new JRBeanCollectionDataSource(list));

UPDATE: You can calculate the same on DB level

select 0 as ord, * from t
union all
select 1 as ord, 'Total' as product, sum(Qty) as Qty, null as date from t group by
order by ord, product

Plz, check my update post, Report template have to autocratically column span for name and Date... — Zaw Than oo, Sep 11 '14 at 11:44

Darshan Lila · Answer 2 · 2014-09-12T05:19:09.873

0

You can use report grouping. Here's what you should be doing.

Take a sub report move your data there.
Take a group which changes on product name i.e. AAA in sub report.
Take a variable that resets on group change and that calculates the sum Qty.

For more reference on report groups visit this link.

EDIT :

Alternatively you can use report scriplets to accomplish it. Visit here for more on report scriplets.

I suggest you use report scriplets this would save you from creating sub reports and report froups.

edited Sep 12 '14 at 05:19

answered Sep 12 '14 at 05:05

Darshan Lila

5,772
2
24
34

thank you. please check my post, I already get it. Which is used `queryString`, `field` tag is outside. If so, `group` can bind it. My `field` is inside of `subDataset` tag. It cannot be done. Have you try with `subDataset`? Not `queryString` – Zaw Than oo Sep 12 '14 at 05:16

Is there way to generate table with subtotal in Jasper Report?

2 Answers2