JasperReports’ iReport – generating a grand total

ireportjasper-reports

I have a variable datatype set up in the group footer band that generates a subtotal for the counts in each group in my report. Works great.
I would like a grand total to generate on the last page of my report, simply summing up the subtotal values. This has been harder to figure out.
Any suggestions?

Best Answer

You can use two variables with different resetType - for calculating sum in group (with resetType="Group" resetGroup="groupName" calculation="Sum" properties) and for calculating total sum for whole report (with resetType="Report" calculation="Sum" properties).

The sample (jrxml file):

<?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="grand_total_sample" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <queryString>
        <![CDATA[SELECT DOCUMENTID, POSITIONNO, PRODUCTID, QUANTITY FROM POSITIONS WHERE PRODUCTID < 10 AND POSITIONNO > 18 ORDER BY POSITIONNO]]>
    </queryString>
    <field name="DOCUMENTID" class="java.lang.Integer"/>
    <field name="POSITIONNO" class="java.lang.Integer"/>
    <field name="PRODUCTID" class="java.lang.Integer"/>
    <field name="QUANTITY" class="java.lang.Integer"/>
    <variable name="quantitySumInGroup" class="java.lang.Integer" resetType="Group" resetGroup="positionNoGroup" calculation="Sum">
        <variableExpression><![CDATA[$F{QUANTITY}]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <variable name="quantityTotalSum" class="java.lang.Integer" calculation="Sum">
        <variableExpression><![CDATA[$F{QUANTITY}]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <group name="positionNoGroup">
        <groupExpression><![CDATA[$F{POSITIONNO}]]></groupExpression>
        <groupHeader>
            <band height="20">
                <textField>
                    <reportElement x="0" y="0" width="400" height="20"/>
                    <box>
                        <topPen lineWidth="1.0"/>
                        <leftPen lineWidth="1.0"/>
                        <bottomPen lineWidth="1.0"/>
                        <rightPen lineWidth="1.0"/>
                    </box>
                    <textElement textAlignment="Center">
                        <font isBold="true" isItalic="true" isUnderline="false"/>
                    </textElement>
                    <textFieldExpression><![CDATA["Position no: " + $F{POSITIONNO}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
        <groupFooter>
            <band height="20">
                <textField>
                    <reportElement x="0" y="0" width="400" height="20"/>
                    <textElement>
                        <font isBold="false" isItalic="true" isUnderline="false"/>
                    </textElement>
                    <textFieldExpression><![CDATA["Sum in group: " + $V{quantitySumInGroup}]]></textFieldExpression>
                </textField>
            </band>
        </groupFooter>
    </group>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{DOCUMENTID}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="100" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{PRODUCTID}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="200" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{QUANTITY}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <lastPageFooter>
        <band height="20">
            <textField>
                <reportElement x="0" y="0" width="400" height="20"/>
                <textElement>
                    <font isBold="true" isItalic="true" isUnderline="false"/>
                </textElement>
                <textFieldExpression><![CDATA["Total sum: " + $V{quantityTotalSum}]]></textFieldExpression>
            </textField>
        </band>
    </lastPageFooter>
</jasperReport>

The result will be:

The result in iReport preview

Related Solutions

How to increment a variable with value of another variable in JasperReports

Add attribute calculation="Sum" to variable name="grandCount"

or pass grandCount to subreport as parameter

<subreportParameter name="grandCount">
<subreportParameterExpression><![CDATA[$P{grandCount}]]></subreportParameterExpression>
</subreportParameter>

in subreport declare variable countItems with initialValue of parameter grantCount

<variable name="countItems" .... >
   <variableExpression><![CDATA[$P{itemCount} + $P{grandCount}]]></variableExpression>
   <initialValueExpression><![CDATA[$P{grandCount}]]></initialValueExpression>
</variable>

and return

<returnValue subreportVariable="countItems" toVariable="grandCount" calculation="Sum"/>

How to build a table of contents in iReport/JasperReports

I found an approach that worked, similar to one of my ideas above. First of all, I could NOT get a subreport to return a value to the calling report, and then retrieve that value using getVariableValue from inside a scriptlet. I tried forever, and I just could not get that to work.

Instead, I went with the approach where I attached the same scriptlet to both the master and subreports. The scriptlet has a class variable where I can keep a running tally of all the product numbers I encounter in the detail, and this code goes inside the afterDetailEval() method of the scriptlet. Note that I only want this code to run for the subreport, so I do a check to see if a particular detail field, which will never be null, is present. If it's not, I don't run the code. So when that event is called by the master report, the code doesn't run.

Now for the part that I learned from the example. The admirable side-effect programming in the example is that there is a line of dimensions 1x1 that contains a call to a custom scriptlet method in the "Print When Expression" property. That's a great idea! So this custom method returns false no matter what, and the line never prints. I did the same thing in the Page Footer band, and this method collects the values from the class variable, determines the page, and stores the results in a hash. Then it resets the class variable.

In the Report Summary band, I have another line that will never print, that calls another custom method. All I do here is iterate through a map of product numbers and show all the pages each one appears on. Done!

Best Answer

Related Solutions

How to increment a variable with value of another variable in JasperReports

How to build a table of contents in iReport/JasperReports

Related Topic