Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Wednesday, December 03, 2014

Tip : Importance of ORDER BY clause of sql query for grouped bands in Jasper iReport


Copy paste below XML code in your iReport/Studio and observe how 2 groups are added and and how the variables created for summing up a field(StoreSales) value. 

<?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="report2" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="35b2d5f2-2cd9-4c77-8617-4c7e643bb063">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <queryString>
        <![CDATA[SELECT     DISTINCT c.country, c.state_province,c.city,
SUM(sf7.store_sales) AS StoreSales
FROM customer c INNER JOIN sales_fact_1997 sf7 ON sf7.customer_id=c.customer_id
GROUP BY c.country,c.state_province, c.city
ORDER BY c.state_province]]>
    </queryString>
    <field name="country" class="java.lang.String"/>
    <field name="state_province" class="java.lang.String"/>
    <field name="city" class="java.lang.String"/>
    <field name="storesales" class="java.math.BigDecimal"/>
    <variable name="storesales_1" class="java.math.BigDecimal" resetType="Group" resetGroup="Country" calculation="Sum">
        <variableExpression><![CDATA[$F{storesales}]]></variableExpression>
    </variable>
    <variable name="storesales_2" class="java.math.BigDecimal" resetType="Group" resetGroup="State" calculation="Sum">
        <variableExpression><![CDATA[$F{storesales}]]></variableExpression>
    </variable>
    <group name="Country">
        <groupExpression><![CDATA[$F{country}]]></groupExpression>
        <groupHeader>
            <band height="21">
                <textField>
                    <reportElement x="0" y="1" width="106" height="20" uuid="2db194d8-11f2-45b5-aca0-59b2eb8a33ac"/>
                    <textElement>
                        <font size="14" isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{country}]]></textFieldExpression>
                </textField>
                <textField evaluationTime="Group" evaluationGroup="Country">
                    <reportElement x="104" y="1" width="100" height="20" uuid="8f2c109e-3f20-4cd6-bd6d-47612999d0dc"/>
                    <textFieldExpression><![CDATA[$V{storesales_1}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
    </group>
    <group name="State">
        <groupExpression><![CDATA[$F{state_province}]]></groupExpression>
        <groupHeader>
            <band height="21">
                <textField>
                    <reportElement x="0" y="1" width="48" height="20" uuid="4b6cc68b-843a-465f-9690-017e1a9a975b"/>
                    <textElement>
                        <font size="12" isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{state_province}]]></textFieldExpression>
                </textField>
                <textField evaluationTime="Group" evaluationGroup="State">
                    <reportElement x="48" y="1" width="100" height="20" uuid="e5f8bc83-c7fa-498c-ab32-9be5671746ab"/>
                    <textFieldExpression><![CDATA[$V{storesales_2}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
    </group>
    <title>
        <band height="34" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="4" y="0" width="100" height="20" uuid="6b6244a4-876b-4590-bb17-62e22c601c24"/>
                <text><![CDATA[country]]></text>
            </staticText>
            <staticText>
                <reportElement x="104" y="0" width="100" height="20" uuid="075e9518-1677-4852-a366-65525755d240"/>
                <text><![CDATA[state_province]]></text>
            </staticText>
            <staticText>
                <reportElement x="204" y="0" width="100" height="20" uuid="d5542f7e-cf62-4278-883c-f2dbd8b5de4f"/>
                <text><![CDATA[city]]></text>
            </staticText>
            <staticText>
                <reportElement x="304" y="0" width="100" height="20" uuid="de08318f-91ad-415e-b91c-b2c98ef59ccf"/>
                <text><![CDATA[storesales]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="4" y="0" width="100" height="20" uuid="ca601a6c-c26a-4618-9068-1807aad3e660"/>
                <textFieldExpression><![CDATA[$F{country}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="104" y="0" width="100" height="20" uuid="ac18ba4e-205f-4ed8-ac74-0cc169a3c07d"/>
                <textFieldExpression><![CDATA[$F{state_province}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="204" y="0" width="100" height="20" uuid="d5216c78-2e4c-432a-b9b6-b9178b7d8548"/>
                <textFieldExpression><![CDATA[$F{city}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="304" y="0" width="100" height="20" uuid="5cb1bb39-1728-4fb8-b041-4f8a167146b3"/>
                <textFieldExpression><![CDATA[$F{storesales}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

NOTES : 
1) Make sure of adding order by clause with the filed you want to give in group by expression if you are getting only one value or repeating values.
2) One of making non reputation of values is to use sub queries.

Reference : 

http://stackoverflow.com/questions/14450634/grouping-records-in-jasperreports
:-)