How to sum all values in a column in Jaspersoft iReport Designer

ireportjasper-reports

I have below similar report in Jaspersoft iReport Designer, but not able to figure out how to sum all values present in vertical column "Doctor Payment" to get a total of "1601"? Length of this columns is variable (i.e. no of rows vary according to size of database & its update frequency ).

Is there any variable like $V{COLUMN_COUNT} (actually gives no of rows, here 5),
which gives sum of all values in a column? If no, how to do sum?

Doctor ID   Doctor Payment
 A1             123
 B1             223
 C2             234
 D3             678
 D1             343
Total          1601

Best Answer

It is quite easy to solve your task. You should create and use a new variable for summing values of the "Doctor Payment" column.

In your case the variable can be declared like this:

<variable name="total" class="java.lang.Integer" calculation="Sum">
    <variableExpression><![CDATA[$F{payment}]]></variableExpression>
</variable>
  • the Calculation type is Sum;
  • the Reset type is Report;
  • the Variable expression is $F{payment}, where $F{payment} is the name of a field contains sum (Doctor Payment).

The working example.

CSV datasource:

doctor_id,payment
A1,123
B1,223
C2,234
D3,678
D1,343

The template:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ...>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <field name="doctor_id" class="java.lang.String"/>
    <field name="payment" class="java.lang.Integer"/>
    <variable name="total" class="java.lang.Integer" calculation="Sum">
        <variableExpression><![CDATA[$F{payment}]]></variableExpression>
    </variable>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="100" height="20"/>
                <box leftPadding="10"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="10" isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Doctor ID]]></text>
            </staticText>
            <staticText>
                <reportElement x="100" y="0" width="100" height="20"/>
                <box leftPadding="10"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="10" isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[Doctor Payment]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="20"/>
                <box leftPadding="10"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{doctor_id}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="100" y="0" width="100" height="20"/>
                <box leftPadding="10"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{payment}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <summary>
        <band height="20">
            <staticText>
                <reportElement x="0" y="0" width="100" height="20"/>
                <box leftPadding="10"/>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Total]]></text>
            </staticText>
            <textField>
                <reportElement x="100" y="0" width="100" height="20"/>
                <box leftPadding="10"/>
                <textElement>
                    <font isBold="true" isItalic="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{total}]]></textFieldExpression>
            </textField>
        </band>
    </summary>
</jasperReport>

The result will be:

Generated report via iReport's preview

You can find a lot of info in the JasperReports Ultimate Guide.

Related Topic