Tuesday, 29 March 2016

Tip : Excel Export Expand(+) and Collapse(-) for grouped data in Jasper Reports (or) The Outline Row Level Property for Excel Extract Reports in Jaspersoft

Hi,

In this post you can learn how to get Expand and Collapse options when exporting a grouped by data excel report.

 Steps : 
1) Design report as follows 
     
      Group Header  ($F{city})
             Detail (some fields)
      Group Footer (Dummy Text field with 0 height). 

(Click on image to get the best view)


2) Apply Body property to the fields that you keep in Detail Band
net.sf.jasperreports.export.xls.row.outline.level.1=Body

(click on Image to get the best of content)
 

3) Apply End property to the dummy field that you keep in Footer band.
net.sf.jasperreports.export.xls.row.outline.level.1=END

(Click on image to get the best view)
4) Sample Excel outputs
(Collapsed Stage)
A group expansion stage : 

 References : 
http://jasperreports.sourceforge.net/sample.reference/xlsfeatures/


Sample JRXML ( works on 6.2 or later Jasper Studio professional with foodmart database )
 <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-03-29T18:23:38 -->
<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="Expand and Collapse Excel" pageWidth="595" pageHeight="842" columnWidth="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="58e9ce0f-5fbd-48ec-8645-53f5a0b2ad01">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.unit." value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>
    <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
    <property name="ireport.jasperserver.url" value="http://localhost:8081/jasperserver-pro/"/>
    <property name="ireport.jasperserver.user" value="superuser"/>
    <property name="ireport.jasperserver.report.resource" value="/2_Training_Naresh/Reports/21_Expand_and_Collapse_Excel_Export_files/main_jrxml"/>
    <property name="ireport.jasperserver.reportUnit" value="/2_Training_Naresh/Reports/21_Expand_and_Collapse_Excel_Export"/>
    <queryString>
        <![CDATA[SELECT * FROM customer ORDER BY city]]>
    </queryString>
    <field name="customer_id" class="java.lang.Integer"/>
    <field name="account_num" class="java.lang.Long"/>
    <field name="lname" class="java.lang.String"/>
    <field name="fname" class="java.lang.String"/>
    <field name="mi" class="java.lang.String"/>
    <field name="address1" class="java.lang.String"/>
    <field name="address2" class="java.lang.String"/>
    <field name="address3" class="java.lang.String"/>
    <field name="address4" class="java.lang.String"/>
    <field name="city" class="java.lang.String"/>
    <field name="state_province" class="java.lang.String"/>
    <field name="postal_code" class="java.lang.String"/>
    <field name="country" class="java.lang.String"/>
    <field name="customer_region_id" class="java.lang.Integer"/>
    <field name="phone1" class="java.lang.String"/>
    <field name="phone2" class="java.lang.String"/>
    <field name="birthdate" class="java.sql.Date"/>
    <field name="marital_status" class="java.lang.String"/>
    <field name="yearly_income" class="java.lang.String"/>
    <field name="gender" class="java.lang.String"/>
    <field name="total_children" class="java.lang.Integer"/>
    <field name="num_children_at_home" class="java.lang.Integer"/>
    <field name="education" class="java.lang.String"/>
    <field name="date_accnt_opened" class="java.sql.Date"/>
    <field name="member_card" class="java.lang.String"/>
    <field name="occupation" class="java.lang.String"/>
    <field name="houseowner" class="java.lang.String"/>
    <field name="num_cars_owned" class="java.lang.Integer"/>
    <field name="fullname" class="java.lang.String"/>
    <group name="city">
        <groupExpression><![CDATA[$F{city}]]></groupExpression>
        <groupHeader>
            <band height="16">
                <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                <textField>
                    <reportElement x="0" y="0" width="100" height="16" uuid="752494bc-a3a7-447b-b24a-16dd57b4e203">
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <box leftPadding="5"/>
                    <textElement verticalAlignment="Middle">
                        <font isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{city}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="100" y="0" width="100" height="16" uuid="4edb4831-d823-471c-8cdf-9621627e0fc9">
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement verticalAlignment="Middle">
                        <font isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="200" y="0" width="100" height="16" uuid="925b91cc-0b9e-4c68-b43e-01546ae19316">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="300" y="0" width="120" height="16" uuid="607f71cb-a9c1-4417-bfa3-cc4bac1f81c2">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="420" y="0" width="175" height="16" uuid="1508d978-b950-4f89-a362-d944afcd326c">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
        <groupFooter>
            <band>
                <textField>
                    <reportElement x="0" y="0" width="100" height="0" uuid="24a95893-5789-4871-bca0-f6d7f8a9bcf4">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="END"/>
                    </reportElement>
                    <textFieldExpression><![CDATA["Text Field"]]></textFieldExpression>
                </textField>
            </band>
        </groupFooter>
    </group>
    <columnHeader>
        <band height="16" splitType="Stretch">
            <printWhenExpression><![CDATA[$V{PAGE_COUNT}==1]]></printWhenExpression>
            <staticText>
                <reportElement x="100" y="0" width="100" height="16" uuid="0d2dd0ef-ad82-40cb-b7af-6cdbedc6fa12"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Customer ID]]></text>
            </staticText>
            <staticText>
                <reportElement x="200" y="0" width="100" height="16" uuid="b1f7385d-2678-405c-bdc1-1ebf363d1f16"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Customer Name]]></text>
            </staticText>
            <staticText>
                <reportElement x="300" y="0" width="120" height="16" uuid="72efd2e8-b27c-49f5-bbf6-7f0cf7b65868"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Occupation]]></text>
            </staticText>
            <staticText>
                <reportElement x="420" y="0" width="175" height="16" uuid="71a0dffd-4d85-40f0-b04e-b966697b8a14">
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center"/>
                <text><![CDATA[Phone Number]]></text>
            </staticText>
            <textField>
                <reportElement x="0" y="0" width="100" height="16" uuid="126c9bef-1748-4333-b3c4-dd641268e523">
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement verticalAlignment="Middle">
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[""]]></textFieldExpression>
            </textField>
        </band>
    </columnHeader>
    <detail>
        <band height="16" splitType="Stretch">
            <textField>
                <reportElement x="100" y="0" width="100" height="16" uuid="262d3c0f-c9d4-4198-a6f1-4235e12967f6">
                    <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{customer_id}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="200" y="0" width="100" height="16" uuid="23a4613e-cbc2-49fa-8b85-c8c1c2b85538">
                    <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{fullname}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="300" y="0" width="120" height="16" uuid="9bdd418b-5ed5-4731-bb47-5f662f214693">
                    <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{occupation}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="420" y="0" width="175" height="16" uuid="1f727d26-1462-4c6a-8e6b-5dbb0e05668d">
                    <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[$F{phone1}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="0" width="100" height="16" uuid="282bb881-bc06-490f-83b2-a34764648547">
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                </reportElement>
                <textElement verticalAlignment="Middle">
                    <font isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA[""]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>
 

I hope it helps some one. .! & thank you to the friend who explained this to me today.  :-) :-)

References: 
http://jasperreports.sourceforge.net/sample.reference/xlsfeatures/ 
at

3 comments:

  1. UnknownThursday, 24 November 2016 at 03:32:00 GMT+5:30

    Sadakar: thanks for posting such a great article. I followed the instruction and get exactly what I want in the excel output. How can I get expand and collapse report when I preview in the jasper studio (Microsoft SSRS has this capability). Thank you.

    ReplyDelete
  2. UnknownFriday, 3 March 2017 at 18:33:00 GMT+5:30

    Hi Sadakar,
    I have a question on this, great that we can set a property to do this in Excel.
    Is there a property that we can set to see a collapse +/- button on jasperserver? I know I could do this by adding an image myself and setting up a hyperlink, but that's a bit of a hack way around it. Just wondering if there is anything out of the box so to speak that would give this view server side.

    Great work on all of your posts by the way. I like to think I am quite experienced with Jasper development but I always learn something for some of your posts. Keep it up please sir.

    ReplyDelete
  3. Sadakar PochampalliFriday, 3 March 2017 at 19:55:00 GMT+5:30

    I believe this feature is not available in jasperserver yet. I would say/talk about the traditional feature of jasper for expand/collapse. i.e., An analysis report (cube) has this feature.

    ReplyDelete

Subscribe to: Post Comments (Atom)