Search code examples
jasper-reportsjaspersoft-studio

How to get sum per page of column in table component and return to main report to display value?

Recently I started working with jasper report and faced some problem. I have table with records and also a have label with text field where I should put sum of column per page. (I added table footer for debugging). Here is a picture: enter image description here

I did that in following way: in the table I add variable "amount" with following properties: enter image description here enter image description here

Notice: reset type is page, because I should display sum per page.

In the main report I added variable "sumPerList" according to How to return value from table's DataSource to main report in iReport? and tried to play with evaluation time but it didn't help (it doesn't matter what evaluation time I set, the value is always wrong), I couldn't display in field "total sum Main" the same value like in field "total sum" (table scope). The reason why I decided display total sum separately from table, because I have empty space between them, but I cann't add empty row in table. Also I tried to set summ function here: enter image description here enter image description here

But it doesn't help either. May be there is another way to do it? (I use TIBCO Jaspersoft® Studio 6.8.0 - Visual Designer for JasperReports 6.8.0.)

Solution

  • A dataset return value will not help because the value is returned only when the table is complete, not on every page break.

    One (hacky) way to do it is to pass a value holder object to the table subdataset, and write an expression in the table that sets the value in the object as a side effect.

    You can use for instance an AtomicInteger as a value holder, note that we don't need atomicity and we're only using it as a mutable integer.

    The solution would look something like this:

    • The report has a variable TableTotal initialized to an AtomicInteger
    • TableTotal is passed to the table subdataset as a parameter called PageTotalHolder
    • The table subdataset has a variable called PageTotal (whose value we want to show in the page footer)
    • In the table column footer we use a print when expression that sets the current PageTotal value to PageTotalHolder (and always returns true): $P{PageTotalHolder}.getAndSet($V{PageTotal}) >= Integer.MIN_VALUE
    • The report page footer shows $V{TableTotal}.get()

    Pasting here the full JRXML for convenience. You can run it in Jaspersoft Studio with One Empty Record.

    <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.8.0.final using JasperReports Library version 6.8.0-2ed8dfabb690ff337a5797129f2cd92902b0c87b  -->
<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="Blank_A4_10" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="f750af4c-d61b-4bdb-b537-6d71b6754832">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <style name="Table_TH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <subDataset name="Dataset1" uuid="8668793a-f800-4489-9589-c9b6d2ee97a0">
        <parameter name="PageTotalHolder" class="java.util.concurrent.atomic.AtomicInteger"/>
        <variable name="Value" class="java.lang.Integer">
            <variableExpression><![CDATA[(int) (10 + 10 * Math.sin($V{REPORT_COUNT}))]]></variableExpression>
        </variable>
        <variable name="PageTotal" class="java.lang.Integer" resetType="Page" calculation="Sum">
            <variableExpression><![CDATA[$V{Value}]]></variableExpression>
        </variable>
    </subDataset>
    <variable name="TableTotal" class="java.util.concurrent.atomic.AtomicInteger" calculation="System">
        <initialValueExpression><![CDATA[new java.util.concurrent.atomic.AtomicInteger()]]></initialValueExpression>
    </variable>
    <detail>
        <band height="203" splitType="Stretch">
            <componentElement>
                <reportElement x="0" y="0" width="200" height="200" uuid="657ddbb8-e98b-43de-be5e-10645069dc44"/>
                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                    <datasetRun subDataset="Dataset1" uuid="2eb1dde6-8cf9-45c1-94ce-0bdcb26e120a">
                        <datasetParameter name="PageTotalHolder">
                            <datasetParameterExpression><![CDATA[$V{TableTotal}]]></datasetParameterExpression>
                        </datasetParameter>
                        <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.JREmptyDataSource(200)]]></dataSourceExpression>
                    </datasetRun>
                    <jr:column width="120" uuid="51f2b0c5-0655-4cc3-8d57-75149a7514f6">
                        <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column1"/>
                        <jr:columnHeader style="Table_CH" height="30"/>
                        <jr:columnFooter style="Table_CH" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="120" height="30" uuid="95a0ff6d-cab6-44df-9aa0-3e41774194e2">
                                    <printWhenExpression><![CDATA[$P{PageTotalHolder}.getAndSet($V{PageTotal}) >= Integer.MIN_VALUE]]></printWhenExpression>
                                </reportElement>
                                <textElement textAlignment="Right"/>
                                <textFieldExpression><![CDATA[$V{PageTotal}]]></textFieldExpression>
                            </textField>
                        </jr:columnFooter>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="120" height="30" uuid="c3ee39f0-59fd-47a3-8cba-90f009dc42ca"/>
                                <textElement textAlignment="Right"/>
                                <textFieldExpression><![CDATA[$V{Value}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </detail>
    <pageFooter>
        <band height="54" splitType="Stretch">
            <textField>
                <reportElement x="200" y="0" width="100" height="30" uuid="eb1905ee-50d5-4601-b8ed-62864e6caea5"/>
                <textFieldExpression><![CDATA[$V{TableTotal}.get()]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
</jasperReport>