how to do sum in ireport for example I have two column
from query i got column (ABC,EFG) in ireport i used Detail Band..i want to total field
ABC | EFG | Total |
200 | 0 | 200 |
500 | 300 | 400 |
0 | 100 | 300 |
700 | 200 | 800 |
200 | 0 | 1000 |
3 Answers:
Posted on December 27, 2012 at 7:40am
Create variable $V{ROW_SUM} and define variable expression for it as $F{ABC}+$F{EFG} and Calculation = 'Nothing'
See example test report (JR 4.1.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="report9" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> <queryString> <![CDATA[select 200 ABC, 0 EFG FROM DUAL UNION ALL SELECT 500, 300 FROM DUAL UNIoN ALL SELECT 700, 200 FROM DUAL]]> </queryString> <field name="ABC" class="java.math.BigDecimal"/> <field name="EFG" class="java.math.BigDecimal"/> <variable name="ABC_SUM" class="java.math.BigDecimal" calculation="Sum"> <variableExpression><![CDATA[$F{ABC}]]></variableExpression> </variable> <variable name="EFG_SUM" class="java.math.BigDecimal" calculation="Sum"> <variableExpression><![CDATA[$F{EFG}]]></variableExpression> </variable> <variable name="ROW_SUM" class="java.math.BigDecimal"> <variableExpression><![CDATA[$F{ABC}+$F{EFG}]]></variableExpression> </variable> <background> <band splitType="Stretch"/> </background> <title> <band height="79" splitType="Stretch"/> </title> <pageHeader> <band height="35" splitType="Stretch"/> </pageHeader> <columnHeader> <band height="20" splitType="Stretch"> <staticText> <reportElement x="0" y="0" width="100" height="20"/> <textElement textAlignment="Center"/> <text><![CDATA[ABC]]></text> </staticText> <staticText> <reportElement x="100" y="0" width="100" height="20"/> <textElement textAlignment="Center"/> <text><![CDATA[EFG]]></text> </staticText> <staticText> <reportElement x="200" y="0" width="100" height="20"/> <textElement textAlignment="Center"/> <text><![CDATA[ROW_SUM]]></text> </staticText> </band> </columnHeader> <detail> <band height="20" splitType="Stretch"> <textField> <reportElement x="0" y="0" width="100" height="20"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$F{ABC}]]></textFieldExpression> </textField> <textField> <reportElement x="100" y="0" width="100" height="20"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$F{EFG}]]></textFieldExpression> </textField> <textField> <reportElement x="200" y="0" width="100" height="20"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$V{ROW_SUM}]]></textFieldExpression> </textField> </band> </detail> <columnFooter> <band height="45" splitType="Stretch"/> </columnFooter> <pageFooter> <band height="54" splitType="Stretch"/> </pageFooter> <summary> <band height="42" splitType="Stretch"> <textField> <reportElement x="0" y="0" width="100" height="20"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$V{ABC_SUM}]]></textFieldExpression> </textField> <textField> <reportElement x="100" y="0" width="100" height="20"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$V{EFG_SUM}]]></textFieldExpression> </textField> <textField> <reportElement x="200" y="0" width="100" height="20"/> <box> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$V{ROW_SUM}]]></textFieldExpression> </textField> </band> </summary> </jasperReport>
Posted on January 8, 2013 at 9:50pm
Define variable $V{DIFFERENCE} with expression = {$F{ABC}-$F{EFG} calculation = Nothing
And define variable $V{SUM_DIFF} with expression = $V{DIFFERENCE} calculation = Sum
In report display $V{SUM_DIFF} in detail section.
Posted on January 10, 2013 at 11:13pm
<!--?xml version="1.0" encoding="UTF-8"?--> <jasperreport bottommargin="20" columnwidth="555" language="groovy" leftmargin="20" name="report9" pageheight="842" pagewidth="595" rightmargin="20" topmargin="20" uuid="7bce4802-e7e6-456e-9eaa-07aaa3d2348c" 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"> <property name="ireport.zoom" value="1.0"> <property name="ireport.x" value="0"> <property name="ireport.y" value="0"> <querystring> <!--[CDATA[select 200 ABC, 0 EFG FROM DUAL UNION ALL SELECT 500, 300 FROM DUAL UNION ALL SELECT 0, 100 FROM DUAL UNION ALL SELECT 700, 200 FROM DUAL UNION ALL SELECT 200, 0 FROM DUAL]]--> </querystring> <field class="java.math.BigDecimal" name="ABC"> <field class="java.math.BigDecimal" name="EFG"> <variable calculation="Sum" class="java.math.BigDecimal" name="ABC_SUM"> <variableexpression><!--[CDATA[$F{ABC}]]--></variableexpression> </variable> <variable calculation="Sum" class="java.math.BigDecimal" name="EFG_SUM"> <variableexpression><!--[CDATA[$F{EFG}]]--></variableexpression> </variable> <variable class="java.math.BigDecimal" name="DIFFERENCE"> <variableexpression><!--[CDATA[$F{ABC}-$F{EFG}]]--></variableexpression> </variable> <variable calculation="Sum" class="java.math.BigDecimal" name="SUM_DIFF"> <variableexpression><!--[CDATA[$V{DIFFERENCE}]]--></variableexpression> </variable> <background> <band splittype="Stretch"> </band></background><title></title> <pageheader> <band height="35" splittype="Stretch"> </band></pageheader> <columnheader> <band height="20" splittype="Stretch"> <statictext> <reportelement height="20" uuid="d37003ae-1daf-4f89-9f9d-3994b54481e7" width="100" x="0" y="0"> <textelement textalignment="Center"> <text><!--[CDATA[ABC]]--></text> </textelement></reportelement></statictext> <statictext> <reportelement height="20" uuid="57377aba-d959-41d9-89bb-b96d3c034edc" width="100" x="100" y="0"> <textelement textalignment="Center"> <text><!--[CDATA[EFG]]--></text> </textelement></reportelement></statictext> <statictext> <reportelement height="20" uuid="cc515a93-d4e3-482a-acbf-f2c20921682a" width="100" x="200" y="0"> <textelement textalignment="Center"> <text><!--[CDATA[ROW_SUM]]--></text> </textelement></reportelement></statictext> <statictext> <reportelement height="20" uuid="cc515a93-d4e3-482a-acbf-f2c20921682a" width="100" x="300" y="0"> <textelement textalignment="Center"> <text><!--[CDATA[DIFFERENCE]]--></text> </textelement></reportelement></statictext> </band> </columnheader> <detail> <band height="20" splittype="Stretch"> <textfield> <reportelement height="20" uuid="b3e3e203-7476-40b2-a6c4-fd6c29e941bd" width="100" x="0" y="0"> <box> <pen linewidth="0.25"> <toppen linewidth="0.25"> <leftpen linewidth="0.25"> <bottompen linewidth="0.25"> <rightpen linewidth="0.25"> </rightpen></bottompen></leftpen></toppen></pen></box> <textelement textalignment="Right"> <textfieldexpression><!--[CDATA[$F{ABC}]]--></textfieldexpression> </textelement></reportelement></textfield> <textfield> <reportelement height="20" uuid="a9cb86f7-be0e-4dc0-9cd7-6b7e454e8988" width="100" x="100" y="0"> <box> <pen linewidth="0.25"> <toppen linewidth="0.25"> <leftpen linewidth="0.25"> <bottompen linewidth="0.25"> <rightpen linewidth="0.25"> </rightpen></bottompen></leftpen></toppen></pen></box> <textelement textalignment="Right"> <textfieldexpression><!--[CDATA[$F{EFG}]]--></textfieldexpression> </textelement></reportelement></textfield> <textfield> <reportelement height="20" uuid="afe65731-24a4-4fa4-a6e3-b2308f386d70" width="100" x="300" y="0"> <box> <pen linewidth="0.25"> <toppen linewidth="0.25"> <leftpen linewidth="0.25"> <bottompen linewidth="0.25"> <rightpen linewidth="0.25"> </rightpen></bottompen></leftpen></toppen></pen></box> <textelement textalignment="Right"> <textfieldexpression><!--[CDATA[$V{DIFFERENCE}]]--></textfieldexpression> </textelement></reportelement></textfield> <textfield> <reportelement height="20" uuid="00399405-011d-4349-8156-2aafcaae6de2" width="100" x="200" y="0"> <box> <pen linewidth="0.25"> <toppen linewidth="0.25"> <leftpen linewidth="0.25"> <bottompen linewidth="0.25"> <rightpen linewidth="0.25"> </rightpen></bottompen></leftpen></toppen></pen></box> <textelement textalignment="Right"> <textfieldexpression><!--[CDATA[$V{SUM_DIFF}]]--></textfieldexpression> </textelement></reportelement></textfield> </band> </detail> <columnfooter> <band height="45" splittype="Stretch"> </band></columnfooter> <lastpagefooter> <band height="50"> </band></lastpagefooter> <summary> <band height="42" splittype="Stretch"> <textfield> <reportelement height="20" uuid="197aeae3-94d5-4251-8ee2-6f26150cc417" width="100" x="0" y="20"> <box> <pen linewidth="0.25"> <toppen linewidth="0.25"> <leftpen linewidth="0.25"> <bottompen linewidth="0.25"> <rightpen linewidth="0.25"> </rightpen></bottompen></leftpen></toppen></pen></box> <textelement textalignment="Right"> <textfieldexpression><!--[CDATA[$V{ABC_SUM}]]--></textfieldexpression> </textelement></reportelement></textfield> <textfield> <reportelement height="20" uuid="5b877ef3-887d-4c29-bc29-d77cba0106e0" width="100" x="100" y="20"> <box> <pen linewidth="0.25"> <toppen linewidth="0.25"> <leftpen linewidth="0.25"> <bottompen linewidth="0.25"> <rightpen linewidth="0.25"> </rightpen></bottompen></leftpen></toppen></pen></box> <textelement textalignment="Right"> <textfieldexpression><!--[CDATA[$V{EFG_SUM}]]--></textfieldexpression> </textelement></reportelement></textfield> </band> </summary> </field></field></property></property></property></jasperreport>
thanx for help.. now it's working...
hi ,
i tried the same calculation but it is showing me null.
my need is i have 3 cols a and b,c which are big decimal.
i want to add a+b into variable v1.
v2=v1/c
please guide me whee i am doing wrong
thanx for reply
i already try that but it will return row wise total but i want cumulative sum of tow column.....
My test report calculate sum for row (total column) and total sum for all columns int report. In your table total value in second row = 400. Why this result? Write more precisely what are you want.
for eg : in first row i got Total=200 in next line
(500+(total)-300)=400)
then next((0+(Total=400))-100)=300
then ((700+(total=300))-200)=800
then ((200+(total=800))-0)=1000