How to do running totals?

[incirlikhodja]

I need the ability to do a running total on a column in both the header and the footer of a page.  The header total must show the total of the column prior to the current page's transactions have been summed and the footer must show the total after the current page's transactions have been summed.

For example,  the first page should display zeroes in the header total because nothing has been added yet; while the first page footer should display the totals of the first page's transactions. 

In the same example, the second page's header should display the same number as the first page's footer because the second page's transactions have not been added yet.  However, the second page's footer should display a total that reflect's the first page's footer total PLUS the transactions on the second page.

It's a running total.  How do I perform that in iReports?

[lacito]

Hi,

Read this first.

http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=76369

 

1) Create a "total" variable!

2) Variable class (for example) : java.math.BigDecimal

3) Calculation: Sum.

4) Reset type: Report

5) Increment type: None

6) Variable expression: $F{unit_price}.multiply(new java.math.BigDecimal($F{quantity}.intValue()))

7) Initial Value Expression: java.math.BigDecimal.ZERO

 

I hope it helps,

 

Laszlo

Code:

<?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="sales" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" whenResourceMissingType="Key">	<style name="style1" isDefault="true" forecolor="#333333" fill="Solid" hAlign="Center" vAlign="Middle" markup="none" fontName="DejaVu Sans" fontSize="10" pdfFontName="DejaVuSans.ttf" pdfEncoding="Identity-H" isPdfEmbedded="true"/>	<queryString>		<![CDATA[select o.ord_date, o.ord_id, c.company_name, c.title, c.last_name, c.first_name,p.prod_name, oi.unit_price, oi.quantity, oi.currency,cast(to_char(o.ship_date, 'YYYY') as varchar(4)) as _year,cast(to_char(o.ship_date, 'YYYYMM') as varchar(6)) as _monthfrom orders as o inner join customers as c on c.cust_id=o.cust_idinner join orderitems as oi on oi.ord_id=o.ord_idinner join products as p on p.prod_id=oi.prod_idorder by 1, 2, 3, 5, 6, 7]]>	</queryString>	<field name="ord_date" class="java.sql.Date"/>	<field name="ord_id" class="java.lang.Integer"/>	<field name="company_name" class="java.lang.String"/>	<field name="title" class="java.lang.String"/>	<field name="last_name" class="java.lang.String"/>	<field name="first_name" class="java.lang.String"/>	<field name="prod_name" class="java.lang.String"/>	<field name="unit_price" class="java.math.BigDecimal"/>	<field name="quantity" class="java.lang.Integer"/>	<field name="currency" class="java.lang.String"/>	<field name="_year" class="java.lang.String"/>	<field name="_month" class="java.lang.String"/>	<variable name="total" class="java.math.BigDecimal" calculation="Sum">		<variableExpression><![CDATA[$F{unit_price}.multiply(new java.math.BigDecimal($F{quantity}.intValue()))]]></variableExpression>		<initialValueExpression><![CDATA[java.math.BigDecimal.ZERO]]></initialValueExpression>	</variable>	<pageHeader>		<band height="20">			<textField>				<reportElement x="455" y="0" width="100" height="20"/>				<textElement/>				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{total}]]></textFieldExpression>			</textField>		</band>	</pageHeader>	<detail>		<band height="21" splitType="Prevent">			<line>				<reportElement x="0" y="20" width="555" height="1"/>			</line>			<textField isStretchWithOverflow="true" isBlankWhenNull="true">				<reportElement mode="Transparent" x="0" y="0" width="55" height="20"/>				<textElement textAlignment="Left">					<font size="8"/>				</textElement>				<textFieldExpression class="java.util.Date"><![CDATA[$F{ord_date}]]></textFieldExpression>			</textField>			<textField isStretchWithOverflow="true" isBlankWhenNull="true">				<reportElement mode="Transparent" x="56" y="0" width="30" height="20"/>				<textElement textAlignment="Right">					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.Integer"><![CDATA[$F{ord_id}]]></textFieldExpression>			</textField>			<textField isStretchWithOverflow="true" isBlankWhenNull="true">				<reportElement mode="Transparent" x="87" y="0" width="180" height="20"/>				<textElement textAlignment="Left">					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{company_name}!=null ? $F{company_name} : $F{last_name} + ", " + $F{first_name}+ ($F{title}!=null ? ", " + $F{title} : "")]]></textFieldExpression>			</textField>			<textField isStretchWithOverflow="true" isBlankWhenNull="true">				<reportElement mode="Transparent" x="268" y="0" width="180" height="20"/>				<textElement textAlignment="Left">					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{prod_name}]]></textFieldExpression>			</textField>			<textField isStretchWithOverflow="true" isBlankWhenNull="true">				<reportElement mode="Transparent" x="450" y="0" width="20" height="20"/>				<textElement textAlignment="Right">					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.Integer"><![CDATA[$F{quantity}]]></textFieldExpression>			</textField>			<textField isStretchWithOverflow="true" isBlankWhenNull="true">				<reportElement mode="Transparent" x="471" y="0" width="50" height="20"/>				<textElement textAlignment="Right">					<font size="8"/>				</textElement>				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{unit_price}]]></textFieldExpression>			</textField>			<textField isStretchWithOverflow="true" isBlankWhenNull="true">				<reportElement mode="Transparent" x="525" y="0" width="30" height="20"/>				<textElement textAlignment="Left">					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{currency}]]></textFieldExpression>			</textField>		</band>	</detail>	<pageFooter>		<band height="20">			<textField>				<reportElement x="455" y="0" width="100" height="20"/>				<textElement/>				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{total}]]></textFieldExpression>			</textField>		</band>	</pageFooter></jasperReport>