How can I show sum of columns in column header? [c

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

Is it possible to design report like this?

回答1:

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)

回答2:

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

回答3:

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