Thursday 12 February 2015

Parameter in GROUP BY statement - Dynamic grouping

 Thanks Chandu Shekar... Keeping it open for community in my blog site.

Aim :  How to use parameter in GROUP BY statement ? Dynamically group the data in Jasper Studio 6.x

Example works on foodmart database.

JRXML Code :

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.final using JasperReports Library version 6.0.0  -->
<!-- 2015-02-12T15:41:46 -->
<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="case_56816" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="106066b5-8cc4-4e9d-92df-fa60c6cea9da">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="ireport.jasperserver.url" value="http://localhost:8082/jasperserver-pro/"/>
    <property name="ireport.jasperserver.report.resource" value="/public/Hummingbird/case_56816/case_56816_files/case_56816_jrxml"/>
    <property name="ireport.jasperserver.reportUnit" value="/public/Hummingbird/case_56816/case_56816"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
    <parameter name="criteria" class="java.lang.String">
        <defaultValueExpression><![CDATA["store"]]></defaultValueExpression>
    </parameter>
    <parameter name="groupBy" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[($P{criteria} == "store") ? "employee.store_id,employee.department_id":($P{criteria} == "department")?"employee.department_id,employee.store_id":"employee.store_id, employee.department_id"]]></defaultValueExpression>
    </parameter>
    <queryString language="SQL">
        <![CDATA[SELECT
     SUM(employee."salary") AS employee_salary,
     employee."store_id" AS employee_store_id,
     employee."department_id" AS employee_department_id
FROM
     "public"."employee" employee
GROUP BY $P!{groupBy}
LIMIT 5]]>
    </queryString>

    <field name="employee_salary" class="java.math.BigDecimal"/>
    <field name="employee_store_id" class="java.lang.Integer"/>
    <field name="employee_department_id" class="java.lang.Integer"/>
    <title>
        <band height="29" splitType="Stretch">
            <textField>
                <reportElement x="0" y="2" width="555" height="20" uuid="77dffc9a-1996-4f63-b704-fc1f213c4770"/>
                <textFieldExpression><![CDATA[$P{groupBy}]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="185" height="20" uuid="3d2abafe-8487-463f-b684-f309e94adb58"/>
                <text><![CDATA[employee_salary]]></text>
            </staticText>
            <staticText>
                <reportElement x="185" y="0" width="185" height="20" uuid="3f4f67b5-ec29-4185-a52a-4099492fdaff"/>
                <text><![CDATA[employee_store_id]]></text>
            </staticText>
            <staticText>
                <reportElement x="370" y="0" width="185" height="20" uuid="c7127794-4406-43e2-a057-acc5314fbf79"/>
                <text><![CDATA[employee_department_id]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="21" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="185" height="20" uuid="5aa29ed2-1730-49ce-a4e8-3f5d71862385"/>
                <textFieldExpression><![CDATA[$F{employee_salary}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="185" y="0" width="185" height="20" uuid="b189d5d9-a32c-4ad6-9ae9-47df69ed8da5"/>
                <textFieldExpression><![CDATA[$F{employee_store_id}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="370" y="0" width="185" height="20" uuid="8f691afe-d989-4d72-9c91-70f61a3d500a"/>
                <textFieldExpression><![CDATA[$F{employee_department_id}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

No comments:

Post a Comment