15

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
aaa--aa@yahoo.com--1--20.00
bbb--bb@yahoo.com--2--30.00
ccc--cc@gmai.com--1--35.00
ddd--dd@gmai.com--2-- 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  aa@yahoo.com 20.00
ccc  cc@gmai.com  35.00
------------------------------------
Total for group two: 70.00
Name email         amount
bbb  bb@yahoo.com  30.00
ddd  dd@gmai.com   40.00

Can JasperReports solve this problem? how?

Alex K
  • 22,315
  • 19
  • 108
  • 236
viet thang
  • 161
  • 1
  • 3
  • 8
  • You can use a group by on the SQL query to group your data by pledge type. Add a Report Group and set the footer to be 0px high and the header to be however high you need. Then put your 'Total for group X' fields in there. Set the report group expression to be the `pledgeType` field. This is how I do it in Jasper 1.3.x. You can either do the totals in Java where you extract the data or in a report scriptlet. – jonny2k9 Jan 22 '13 at 03:39
  • can i ask you one more question? To change the label of the header depends on value : IF [PledgeType] <> 1 AND 2 THEN heading = “Purpose” so the heading is textField component or static text component? – viet thang Jan 22 '13 at 04:18
  • If the heading is variable, then use a `textField` component, otherwise a `staticTextField` will work. – jonny2k9 Jan 22 '13 at 04:24

1 Answers1

26

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.

report output

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, 'aa@yahoo.com' as email, 1 as PledgeType, 20.00 as amount
union select 'bbb', 'bb@yahoo.com' ,2, 30.00
union select 'ccc', 'cc@gmai.com' ,1, 35.00
union select 'ddd', 'dd@gmai.com' ,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>
MrsTang
  • 3,119
  • 1
  • 19
  • 24
  • the order by hint was very helpful, thanks, I forgot about that! – WizardsOfWor Jan 09 '20 at 22:41
  • What about when your field is already a collection and you need to group by one field of the collection POJO? Something like `Collection` inside a `JRBeanCollectionDataSource` and I need to group by the user e-mail for example. – Philippe Gioseffi Jul 16 '20 at 04:29