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

Question

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 

Answer 1

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:

---

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