how to do sum in ireport

Posted on December 27, 2012 at 6:23am
0

 

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
     

 

singh758's picture
singh758
20
Joined: Oct 22 2012 - 7:20am
Last seen: 7 years 3 days ago

3 Answers:

1
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>
sanbez's picture
sanbez
880
Joined: Jan 11 2011 - 2:06am
Last seen: 5 months 6 hours ago

thanx for reply
i already try that but it will return row wise total but i want cumulative sum of tow column.....

singh758 - 8 years 2 months ago

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.

sanbez - 8 years 2 months ago

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

singh758 - 8 years 2 months ago
add comment
0
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.

sanbez's picture
sanbez
880
Joined: Jan 11 2011 - 2:06am
Last seen: 5 months 6 hours ago

thanx for reply
It Will return like that......

ABC EFG Result Required

500 0 0 500
15743 0 0 16243
2000 0 0 18243
2000 0 0 20243
150 0 0 20393
5772 0 0 26165
110 0 0 26275
0 110 0 26165
0 200 0 25965
0 5772 0 20193

0 20193 40386 0

singh758 - 8 years 1 month ago

I am don't understand. Why in column "Result" all values = 0 ? Are you solve your problem or not? My test report wоrks fine. II can put it. Are you want to see my test report?

sanbez - 8 years 1 month ago

yes i want to see ur test Report..

singh758 - 8 years 1 month ago
add comment
0
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>
sanbez's picture
sanbez
880
Joined: Jan 11 2011 - 2:06am
Last seen: 5 months 6 hours ago

thanx for help.. now it's working...

singh758 - 8 years 1 month ago

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

kcsekhar25 - 7 years 8 months ago

Nobody can't help you without information about problem. "but it is showing me null" - it is not enough to determine the error. Create new answer with description of problem. SHOW YOUR JRXML, define database (is it Oracle? Postgress, MSSQL? or is it JRBeanCollectionDataSource?...). Show version iReport (JasperServer?)
After that you get a chance to get answer

sanbez - 7 years 8 months ago
show 1 more...

sorry.
thoght as we are in same thread didn't give full information.
i got it.
i will post properly next time.

kcsekhar25 - 7 years 7 months ago
add comment
Feedback
randomness