How to create rows with calculation/custom data for specific data set (group) in jasper reports?

I am a novice to jasper reports and I am stuck on report generating. I need to create an excel report according to the following schema:

(1st row)

Building 1
Amount (sum of all flat amount)

(2nd row)

Flat # 1 (belongs to building 1)
Amount (amount only for that flat)

(3rd row)

Flat # 2 (belongs to building 1)
Amount (amount only for that flat)

(4rd row)

Building 2
Amount (sum of all flat amount)

(5rd row)

Flat # 1 (belongs to building 2)
Amount (amount only for that flat)

(6rd row)

Flat # 2 (belongs to building 2)
Amount (amount only for that flat)

As far as I understand, I need to create a group for each building, but how makes a condition to differentiate the buildings in reports?

Solution

Assuming you have the following three fields: $F{building_number}, $F{flat_number}, $F{amount}

Create a Group on $F{building_number}, add Group Header Band, place $F{building_number} in Group Header. Place $F{flat_number} and $F{amount} to Detail1.

To get the '(sum of all flat amount)' into the Group Header, drag $F{amount} to the Group Header, Select 'Sum' when prompted. This will automatically create Variable set to Sum the amounts. Make sure to set the Evaluation Time on this Text Field to Auto.

Example:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.19.1.final using JasperReports Library version 6.1.1  -->
<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_A45" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="81b8aa8f-65d7-4eec-b9de-322d7c5d185d">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="technotrix_dev"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <queryString>
        <![CDATA[SELECT 1 as building_number, 101 as flat_number, 1234 as amount
UNION ALL
SELECT 1 as building_number, 102 as flat_number, 4551 as amount
UNION ALL
SELECT 2 as building_number, 201 as flat_number, 651 as amount
UNION ALL
SELECT 2 as building_number, 202 as flat_number, 986 as amount
]]>
    </queryString>
    <field name="building_number" class="java.lang.Long">
        <property name="com.jaspersoft.studio.field.name" value="building_number"/>
        <property name="com.jaspersoft.studio.field.label" value="building_number"/>
    </field>
    <field name="flat_number" class="java.lang.Long">
        <property name="com.jaspersoft.studio.field.name" value="flat_number"/>
        <property name="com.jaspersoft.studio.field.label" value="flat_number"/>
    </field>
    <field name="amount" class="java.lang.Long">
        <property name="com.jaspersoft.studio.field.name" value="amount"/>
        <property name="com.jaspersoft.studio.field.label" value="amount"/>
    </field>
    <variable name="amount1" class="java.lang.Long" resetType="Group" resetGroup="Buildings" calculation="Sum">
        <variableExpression><![CDATA[$F{amount}]]></variableExpression>
    </variable>
    <group name="Buildings">
        <groupExpression><![CDATA[$F{building_number}]]></groupExpression>
        <groupHeader>
            <band height="34">
                <textField>
                    <reportElement x="0" y="0" width="140" height="30" uuid="c74562ef-9318-4331-b29f-9d0c8ee4088e"/>
                    <textFieldExpression><![CDATA["Building: " + $F{building_number}]]></textFieldExpression>
                </textField>
                <textField evaluationTime="Auto">
                    <reportElement x="280" y="0" width="275" height="30" uuid="b39012ff-9590-4c10-8f77-0bb04cd5b207"/>
                    <textFieldExpression><![CDATA[$V{amount1}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
    </group>
    <columnHeader>
        <band height="37" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="278" height="30" uuid="77a47f2f-a339-4e81-b5f8-849f34538489">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="eea450ac-33ac-4083-a8a4-33de139f79cb"/>
                </reportElement>
                <text><![CDATA[flat_number]]></text>
            </staticText>
            <staticText>
                <reportElement x="278" y="0" width="277" height="30" uuid="fe9f5509-2cda-4a4f-986c-96128976c2fe">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="07ea5d14-30ad-450a-b86a-174abbb311ae"/>
                </reportElement>
                <text><![CDATA[amount]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <textField>
                <reportElement x="0" y="0" width="278" height="30" uuid="7fc24ca1-42cf-4e36-89a4-b123400c7e00">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="eea450ac-33ac-4083-a8a4-33de139f79cb"/>
                </reportElement>
                <textFieldExpression><![CDATA["Flat: " + $F{flat_number}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="278" y="0" width="277" height="30" uuid="c63698f6-298e-4e71-a1eb-cd34ca7158ee">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="07ea5d14-30ad-450a-b86a-174abbb311ae"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{amount}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>