How can I show sum of columns in column header?

Question

I have to design a Jasper report in iReport 5 tool with the following constraints:

I need to show the sum of each column in the header like in the image
I need to show the columns vertical like in the image

Desired output

expected output

Is it possible to design report like this?

Voting to re-open, the answer can be contained in a couple of lines (hence not to broad), furthermore it is a duplication target for other similar questions — Petter Friberg, Apr 21 '16 at 10:37

score 3 · Answer 1 · edited May 23 '17 at 11:45

Using the normal detail band and columnHeader band this is achieved by creating a variable with calculationType="sum" on the field you like to sum

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

Then display the variable using a textField in the columnHeader band, setting evaluationTime="Report" so that variable is calculated before displaying it.

To rotate a textElement vertical use the rotation attribute (rotation="Left")

Example:

<?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="ReportTest" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="30" bottomMargin="30" uuid="43c90ca5-f3c3-4dda-8423-9ff1442f90e3">
    <queryString>
        <![CDATA[select * from mytable]]>
    </queryString>
    <field name="descr" class="java.lang.String">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
    <field name="value" class="java.lang.Double">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
    <variable name="sumValue" class="java.lang.Double" calculation="Sum">
        <variableExpression><![CDATA[$F{value}]]></variableExpression>
    </variable>
    <columnHeader>
        <band height="70">
            <textField>
                <reportElement mode="Opaque" x="0" y="50" width="100" height="20" forecolor="#000000" backcolor="#CCCCCC" uuid="dfe13f55-12a6-4c33-b5ba-00dd61f37c96"/>
                <box leftPadding="2">
                    <topPen lineWidth="0.25"/>
                    <leftPen lineWidth="0.25"/>
                    <bottomPen lineWidth="0.25"/>
                    <rightPen lineWidth="0.25"/>
                </box>
                <textElement verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA["TOTALE"]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report" pattern="###0.00;-###0.00">
                <reportElement mode="Opaque" x="100" y="50" width="100" height="20" forecolor="#000000" backcolor="#CCCCCC" uuid="ed251db0-474e-4e20-8788-3c2f08bfd1e7"/>
                <box leftPadding="2" rightPadding="2">
                    <topPen lineWidth="0.25"/>
                    <leftPen lineWidth="0.25"/>
                    <bottomPen lineWidth="0.25"/>
                    <rightPen lineWidth="0.25"/>
                </box>
                <textElement textAlignment="Right" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$V{sumValue}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement mode="Opaque" x="100" y="0" width="100" height="50" forecolor="#000000" backcolor="#CCCCCC" uuid="62b62711-8cfb-4df2-8f9e-4a34249dcc66"/>
                <box leftPadding="2">
                    <topPen lineWidth="0.25"/>
                    <leftPen lineWidth="0.25"/>
                    <bottomPen lineWidth="0.25"/>
                    <rightPen lineWidth="0.25"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle" rotation="Left">
                    <font size="8"/>
                </textElement>
                <text><![CDATA[SESSIONS]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20" splitType="Immediate">
            <textField>
                <reportElement x="0" y="0" width="100" height="20" uuid="6a009a8c-16de-451c-a0f1-516a48f793d0"/>
                <box leftPadding="2">
                    <topPen lineWidth="0.25"/>
                    <leftPen lineWidth="0.25"/>
                    <bottomPen lineWidth="0.25"/>
                    <rightPen lineWidth="0.25"/>
                </box>
                <textElement verticalAlignment="Middle">
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{descr}]]></textFieldExpression>
            </textField>
            <textField pattern="###0.00;-###0.00">
                <reportElement x="100" y="0" width="100" height="20" uuid="1fccff95-408c-4364-b003-c691fefdde62"/>
                <box rightPadding="2">
                    <topPen lineWidth="0.25"/>
                    <leftPen lineWidth="0.25"/>
                    <bottomPen lineWidth="0.25"/>
                    <rightPen lineWidth="0.25"/>
                </box>
                <textElement textAlignment="Right" verticalAlignment="Middle">
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{value}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

Result (with some arbitrary data)

Fabio Fantoni · Answer 2 · 2016-02-08T11:33:39.130

if you're not familiar with crosstab, as previously suggested, you could create a subreport just to show the column totals and put it "before" the detail band (for example in the page header band).

obviously, following this way, you will access the datasource twice, and this could could be something you want to avoid, especially if time matters.

basically, a crosstab is the better solution overall, but if you need something simpler (maybe you're not familiar with iReport) or one-shot-like you could think about a subreport

score 0 · Answer 3 · edited Mar 07 '16 at 09:45

0

in this type of reports, you can use a crosstab, you put a field than name it monthly. Use in the columns and a field name it month in rows, and use the sum function in cells

<crosstab>
    <rowGroup name="month" width="128" totalPosition="End">
        ...
    </rowGroup>
    <columnGroup name="monthlyUse" height="66">
            ...
    </columnGroup>
    <measure name="nameMeasure" class="java.lang.Integer" calculation="Sum">
                <measureExpression><![CDATA[$F{number}]]></measureExpression>
    </measure>              
    ....                
</crosstab>

the crosstab will generate a table shwoing the monthly usage with a total row

edited Mar 07 '16 at 09:45

Raziel

444
6
22

answered Feb 08 '16 at 08:36

Bilal Dekar

3,200
4
28
53

You can use crosstab, but actually there is no need. It can also be done on normal report with columnHeader and detail band – Petter Friberg Feb 08 '16 at 08:47
in my sql query dataset i have two columns 1.Month name 2.total item in that month....for example first row is April with 24 items and May with 24 items then with crosstab i want to show sum of item columns .... but i am not able to do that... – Mithlesh Singh Feb 09 '16 at 09:27
1

mithlesh singh specify whats exactly the problem? you have two fields (month name and total item) when creating the crosstab you select month name in Row Group then total item in Column Group, then you have to use another field you name it number for example, it should be an Integer or Double, you select number in the Data and select function Sum – Bilal Dekar Feb 09 '16 at 10:32

How can I show sum of columns in column header?

3 Answers3

Linked

Related