jasper reports - Grouping records in JasperReports

Question

I have a problem with JasperReports. I want to group the records depending on one specific column's value.

For example input data:

Name--email--PledgeType--amount
[email protected]
[email protected]
[email protected]
[email protected] 40.00

The output report will be grouped by the "PledgeType" value (1, 2, ... number):

Total for group one: 55.00
Name email         amount
aaa  [email protected] 20.00
ccc  [email protected]  35.00
------------------------------------
Total for group two: 70.00
Name email         amount
bbb  [email protected]  30.00
ddd  [email protected]   40.00

Can JasperReports solve this problem? how?

Answer 1

You can define grouping in JasperReports. JasperReports calculates the total for you, there is comfortable way to add groups and totals. Here an overview what you need to do in iReport.

To add the group

• modify your query to order by pledgeType - JasperReports requires the data sorted according to your grouping.
• right click on the report in the report inspector and choose Add Report Group.
• Follow the wizard, set as group name PledgeType and choose Group by the following report object where you select the field PledgeType. Click next. Check Add the group header and click Finish.

To add the total

• right click on variables in the report inspector and choose Add Variable.
• In the properties panel choose this configuration: Variable class: BigDecimal, Calculation: Sum, ResetType: Group, ResetGroup PledgeType, Variable Expression: $F{amount}.
• Drag & drop the variable into the group header in the report designer. Click on the field and change: Text field expression: "Total for group " + $F{PledgeType} + ": " + $V{totalPledge}, Expression Class: java.lang.String. Evaluation time: Group. Evaluation Group: PledgeType.

Info: The evaluation time decides when a variable gets evaluated, i.e. when the sum of the calculation will be shown. If you set it to group it means 'once the group processing is completed'.

Attached the generated report and the JRXML.

The JRXML is created with iReport 5.0 - however, if you follow the steps above it should work with JR v 2+

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report2" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce08fe1c-1543-4460-8613-7f03b200082b">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <queryString>
        <![CDATA[select * from
(select 'aaa' as Name, '[email protected]' as email, 1 as PledgeType, 20.00 as amount
union select 'bbb', '[email protected]' ,2, 30.00
union select 'ccc', '[email protected]' ,1, 35.00
union select 'ddd', '[email protected]' ,2, 40.00) tbl
order by PledgeType]]>
    </queryString>
    <field name="Name" class="java.lang.String"/>
    <field name="email" class="java.lang.String"/>
    <field name="PledgeType" class="java.lang.Long"/>
    <field name="amount" class="java.math.BigDecimal"/>
    <variable name="totalPledge" class="java.math.BigDecimal" resetType="Group" resetGroup="PledgeType" calculation="Sum">
        <variableExpression><![CDATA[$F{amount}]]></variableExpression>
    </variable>
    <group name="PledgeType">
        <groupExpression><![CDATA[$F{PledgeType}]]></groupExpression>
        <groupHeader>
            <band height="61">
                <textField evaluationTime="Group" evaluationGroup="PledgeType">
                    <reportElement uuid="401c7b3b-af73-4d40-8982-9c1692eb7085" x="0" y="21" width="555" height="20"/>
                    <textElement/>
                    <textFieldExpression><![CDATA["Total for group " + $F{PledgeType} + ": " + $V{totalPledge}]]></textFieldExpression>
                </textField>
                <staticText>
                    <reportElement uuid="87cd0d21-014d-4e6c-a54a-006165a38414" x="0" y="41" width="185" height="20"/>
                    <textElement/>
                    <text><![CDATA[Name]]></text>
                </staticText>
                <staticText>
                    <reportElement uuid="bd0fc2f5-4963-4c9d-a9be-3659be06e436" x="185" y="41" width="185" height="20"/>
                    <textElement/>
                    <text><![CDATA[email]]></text>
                </staticText>
                <staticText>
                    <reportElement uuid="5d5d7ce1-5353-4f83-91b4-57725b0c922b" x="370" y="41" width="185" height="20"/>
                    <textElement/>
                    <text><![CDATA[amount]]></text>
                </staticText>
            </band>
        </groupHeader>
    </group>
    <detail>
        <band height="20">
            <textField>
                <reportElement uuid="5b325da6-7c56-4357-8808-911dad16ec53" x="0" y="0" width="185" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{Name}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="0bc06b28-7b8c-4af9-997a-714d1599def1" x="185" y="0" width="185" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{email}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="e5504bb9-c3c0-4135-94c6-7ea935f97cb6" x="370" y="0" width="185" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{amount}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>