Sample Report: Changing Report Grouping at Run Time Based on User's Input

The purpose of this sample report is to show user one of the way to change report grouping structure dynamically using available JR features. The techniques used in this report design are very basic features of JasperReports product. We encourage users to review TIBCO JASPERSOFT® STUDIO USER GUIDE RELEASE 6.2 document and get themselves familiar with the product in order to use it efficiently, effectively, and creatively to meet their report requirement.

Key Design Considerations:

- This report uses jasperserver repository "profile" database to list organizations, its users, and as associated roles.

- To simplify report design and avoid complex structure, this report utilizes simple tabular layout without grouping. Users with report design expertise who have mastered the JR features can utilize group function to create more complex report structures.

- The report data is grouped based on user's input, and report rows are displayed by grouping sequence.

- Report input selection is based on predefined key values and translated into "order by" syntax before being injected as a piece of report SQL query replacement via $P!{}.

It is imperative not to inject user's input directly into report SQL query using $P!{} to avoid hacking and malicious acts through SQL injection. Using a hidden parameter as a buffer zone instead, as demonstrated in this report design, to control and limit what can be used in the actual report SQL query.

Report Design:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.1.final using JasperReports Library version 6.2.1  -->
<!-- 2016-06-02T12:44:01 -->
<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_Letter" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="026e0562-4130-4d3e-8004-4285bde9118f">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/>
    <parameter name="GroupBy" class="java.lang.Integer">
        <defaultValueExpression><![CDATA[1]]></defaultValueExpression>
    </parameter>
    <parameter name="GroupItem" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[$P{GroupBy}.intValue()==3?"Role":$P{GroupBy}.intValue()==2?"User":"Organization"]]></defaultValueExpression>
    </parameter>
    <parameter name="OrderBy" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[" order by "+($P{GroupBy}.intValue()==3?"3, 1, 2":$P{GroupBy}.intValue()==2?"2, 1, 3":"1, 2, 3")]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT o.tenantname as org_name
, u.username as user_name
, r.rolename as role_name
FROM jitenant o
inner join jiuser u on u.tenantid = o.id
inner join jiuserrole j on j.userid = u.id
inner join jirole r on r.id = j.roleid
$P!{OrderBy} ]]>
    </queryString>
    <field name="org_name" class="java.lang.String"/>
    <field name="user_name" class="java.lang.String"/>
    <field name="role_name" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="35" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <textField>
                <reportElement x="0" y="0" width="570" height="30" uuid="51f0449a-2105-483e-926b-f3616fdd97d0"/>
                <textElement>
                    <font size="14" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["Demo Report on Dynamic Grouping of "+$P{GroupItem}]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band height="1" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="35" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <textField>
                <reportElement x="0" y="0" width="190" height="30" forecolor="#FC0905" uuid="01864a95-4b0d-4410-be9c-ca4f616c40d1">
                    <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{GroupBy}.intValue()==3?"role_name":$P{GroupBy}.intValue()==2?"user_name":"org_name"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="190" y="0" width="190" height="30" uuid="105b50cf-1e17-4aec-8aa1-83cac3dc0032">
                    <property name="com.jaspersoft.studio.unit.x" value="pixel"/>
                </reportElement>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{GroupBy}.intValue()==3?"org_name":$P{GroupBy}.intValue()==2?"org_name":"user_name"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="380" y="0" width="190" height="30" uuid="eb853cf4-82d1-4bc7-b6f7-4e694fb8c2d3">
                    <property name="com.jaspersoft.studio.unit.x" value="pixel"/>
                </reportElement>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{GroupBy}.intValue()==3?"user_name":"role_name"]]></textFieldExpression>
            </textField>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <printWhenExpression><![CDATA[!($P{GroupBy}.intValue()==2||$P{GroupBy}.intValue()==3)]]></printWhenExpression>
            <textField>
                <reportElement isPrintRepeatedValues="false" x="0" y="0" width="190" height="30" uuid="16978d74-66b3-49a1-b111-5b4c26a4768f">
                    <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{org_name}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="190" y="0" width="190" height="30" uuid="5c54b46f-088b-432b-87e1-2fe9f1ce36be">
                    <property name="com.jaspersoft.studio.unit.x" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{user_name}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="380" y="0" width="190" height="30" uuid="cdaf1f8c-dea1-43db-bf09-5bc4c7bfbc59">
                    <property name="com.jaspersoft.studio.unit.x" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{role_name}]]></textFieldExpression>
            </textField>
        </band>
        <band height="30">
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <printWhenExpression><![CDATA[$P{GroupBy}.intValue()==2]]></printWhenExpression>
            <textField>
                <reportElement isPrintRepeatedValues="false" x="0" y="0" width="190" height="30" uuid="05826092-c5e5-4059-b086-f8a1a15a0d99">
                    <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{user_name}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="190" y="0" width="190" height="30" uuid="cf5d9477-dde4-43e8-aec6-3f7e737dc8c6">
                    <property name="com.jaspersoft.studio.unit.x" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{org_name}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="380" y="0" width="190" height="30" uuid="353d8d6d-5857-4937-9125-58ee8e818e35">
                    <property name="com.jaspersoft.studio.unit.x" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{role_name}]]></textFieldExpression>
            </textField>
        </band>
        <band height="30">
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <printWhenExpression><![CDATA[$P{GroupBy}.intValue()==3]]></printWhenExpression>
            <textField>
                <reportElement isPrintRepeatedValues="false" x="0" y="0" width="190" height="30" uuid="82cbd8ee-c77b-4d7d-9a45-0662174428d2">
                    <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{role_name}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="190" y="0" width="190" height="30" uuid="ca4150c3-1ae4-42c5-8822-8ab3b5477bfd">
                    <property name="com.jaspersoft.studio.unit.x" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{org_name}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="380" y="0" width="190" height="30" uuid="01cf29d7-1390-481d-8510-0ff4757e3e4b">
                    <property name="com.jaspersoft.studio.unit.x" value="pixel"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{user_name}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter>
        <band splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="30" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <textField>
                <reportElement x="180" y="0" width="100" height="30" uuid="2261cff4-6169-41f8-8c43-189891acfef9"/>
                <textElement textAlignment="Right">
                    <font isItalic="true"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>
            </textField>
            <textField evaluationTime="Report">
                <reportElement x="280" y="0" width="100" height="30" uuid="b9502135-9343-400c-b843-416a6dad91fc"/>
                <textElement>
                    <font isItalic="true"/>
                </textElement>
                <textFieldExpression><![CDATA[" of "+$V{PAGE_NUMBER}]]></textFieldExpression>
            </textField>
        </band>
    </pageFooter>
    <summary>
        <band height="50" splitType="Stretch">
            <textField>
                <reportElement x="0" y="10" width="570" height="30" uuid="a65dd7b6-8e9f-41d7-b25f-e0b0725782c9">
                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>
                </reportElement>
                <box>
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textFieldExpression><![CDATA["          Total records: "+$V{REPORT_COUNT}+"."]]></textFieldExpression>
            </textField>
        </band>
    </summary>
</jasperReport>