Jump to content
We've recently updated our Privacy Statement, available here ×

  • Sample Report - Creating Multi-sheet xls Report Output with Subreports

    Tom C

    By Tom C

      • Features: Reports Version: v7.9 Product: Jaspersoft® Studio
     Share

    This is the forth of the mini series of sample reports using various techniques to deal with multi-sheet report xls outputs.

    In this sample report, we will break each sub report into separate xls sheets. Unlike in the previous samples where the sheets were simply separated based on the report page break using the one page per sheet xls property, we use the following property to place it at a strategic location to set up a sheet break when exporting the report into xls output.

    <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/>

    The reason for the choice of using this sheet break option is that sub reports most likely will have multiple pages of report content therefore we cannot reply on page break to start new sheet in such case or will have to face unintended sheet separation in the sub report.

    The "break before row" property should be set up at the first visible element in the report xls output file to start the new sheet. In this sample report, it is set in the sub report, on the first row of the report data feeding the first column in report detail band.

    Report templates:

    Main report

    <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928  -->
<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="sample report with multi-sheet" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/>
    <property name="net.sf.jasperreports.export.xls.exclude.origin.band.3" value="columnHeader"/>
    <property name="net.sf.jasperreports.export.xls.exclude.origin.report.3" value="*"/>
    <property name="net.sf.jasperreports.export.xls.exclude.origin.band.4" value="columnHeader"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>
    <subDataset name="ListDataset" uuid="07f1ee71-b1eb-4d3d-9bf2-cd080412ca60">
        <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo.xml"/>
        <queryString language="SQL">
            <![CDATA[select resourcetype
        , count(resourcetype) as resourcetypecount 
        from jiresource 
        group by resourcetype]]>
        </queryString>
        <field name="resourcetype" class="java.lang.String">
            <property name="com.jaspersoft.studio.field.name" value="resourcetype"/>
            <property name="com.jaspersoft.studio.field.label" value="resourcetype"/>
            <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/>
        </field>
        <field name="resourcetypecount" class="java.lang.Long">
            <property name="com.jaspersoft.studio.field.name" value="resourcetypecount"/>
            <property name="com.jaspersoft.studio.field.label" value="resourcetypecount"/>
        </field>
    </subDataset>
    <parameter name="Max per Sheet" class="java.lang.Integer" isForPrompting="false">
        <defaultValueExpression><![CDATA[50]]></defaultValueExpression>
    </parameter>
    <queryString language="SQL">
        <![CDATA[select * from (
        select resourcetype
        , count(resourcetype) as resourcetypecount 
        from jiresource 
        group by resourcetype
        ) t
        where resourcetypecount > $P{Max per Sheet}]]>
    </queryString>
    <field name="resourcetype" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.name" value="resourcetype"/>
        <property name="com.jaspersoft.studio.field.label" value="resourcetype"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/>
    </field>
    <field name="resourcetypecount" class="java.lang.Long">
        <property name="com.jaspersoft.studio.field.name" value="resourcetypecount"/>
        <property name="com.jaspersoft.studio.field.label" value="resourcetypecount"/>
    </field>
    <variable name="resourcetype" class="java.lang.String" resetType="None">
        <initialValueExpression><![CDATA[$F{resourcetype}.substring( $F{resourcetype}.lastIndexOf( "." )+1)]]></initialValueExpression>
    </variable>
    <columnHeader>
        <band height="30">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <printWhenExpression><![CDATA[$V{REPORT_COUNT}.intValue()==0]]></printWhenExpression>
            <staticText>
                <reportElement x="0" y="0" width="350" height="30" uuid="35d2384e-4628-4e1b-8b43-0a8944dcbf55">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbb25a9a-a33d-4d9c-8b12-3af376764728"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <textElement>
                    <font size="14" isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[resourcetype]]></text>
            </staticText>
            <staticText>
                <reportElement x="350" y="0" width="220" height="30" uuid="6ffff2b0-2fa0-464f-b054-914a12b9c60d">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbfc86c5-577f-49d0-970f-e6bcbacf421b"/>
                </reportElement>
                <textElement textAlignment="Right">
                    <font size="14" isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[resourcetypecount]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <printWhenExpression><![CDATA[$V{REPORT_COUNT}.intValue()==1]]></printWhenExpression>
            <componentElement>
                <reportElement x="0" y="0" width="570" height="30" uuid="50180da2-1bd9-4d4c-b934-3d5ce47b2856">
                    <property name="com.jaspersoft.studio.unit.x" value="px"/>
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                </reportElement>
                <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
                    <datasetRun subDataset="ListDataset" uuid="12da974d-be42-459e-9495-28dd9ef417fd">
                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    </datasetRun>
                    <jr:listContents height="30" width="570">
                        <textField>
                            <reportElement x="0" y="0" width="350" height="30" uuid="8706f920-24a9-404f-a7a9-500dbb8de522">
                                <property name="com.jaspersoft.studio.unit.width" value="px"/>
                                <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA["SUMMARY"]]></propertyExpression>
                            </reportElement>
                            <textElement>
                                <font size="8"/>
                            </textElement>
                            <textFieldExpression><![CDATA[$F{resourcetype}]]></textFieldExpression>
                        </textField>
                        <textField>
                            <reportElement x="350" y="0" width="150" height="30" forecolor="#FF0000" uuid="be0cfdef-cea6-4c69-96df-3974993dbf51">
                                <property name="com.jaspersoft.studio.unit.width" value="px"/>
                                <property name="com.jaspersoft.studio.unit.x" value="px"/>
                                <property name="com.jaspersoft.studio.unit.y" value="px"/>
                            </reportElement>
                            <textElement>
                                <font size="10"/>
                            </textElement>
                            <textFieldExpression><![CDATA[$F{resourcetype}.substring( $F{resourcetype}.lastIndexOf( "." )+1)]]></textFieldExpression>
                        </textField>
                        <textField>
                            <reportElement x="500" y="0" width="70" height="30" uuid="cf085817-b14d-442f-864f-0b9ec3e998e3">
                                <property name="com.jaspersoft.studio.unit.x" value="px"/>
                                <property name="com.jaspersoft.studio.unit.width" value="px"/>
                            </reportElement>
                            <textElement textAlignment="Right"/>
                            <textFieldExpression><![CDATA[$F{resourcetypecount}]]></textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
        </band>
        <band height="31">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <break>
                <reportElement x="0" y="0" width="100" height="1" uuid="87282198-6f09-43af-8625-d51ad00114bd">
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                </reportElement>
            </break>
            <subreport>
                <reportElement x="0" y="1" width="570" height="30" uuid="a4d1d961-fad1-4fd4-bbea-2552722841fe">
                    <property name="com.jaspersoft.studio.unit.height" value="px"/>
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                </reportElement>
                <subreportParameter name="resourcetypename">
                    <subreportParameterExpression><![CDATA[$F{resourcetype}.substring( $F{resourcetype}.lastIndexOf( "." )+1)]]></subreportParameterExpression>
                </subreportParameter>
                <subreportParameter name="Limit">
                    <subreportParameterExpression><![CDATA[$F{resourcetypecount}.intValue()%$P{Max per Sheet}.intValue()]]></subreportParameterExpression>
                </subreportParameter>
                <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                <subreportExpression><![CDATA["sample report with multi-sheet-sub.jasper"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
</jasperReport>

     

    Subreport

    <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 7.9.0.final using JasperReports Library version 6.16.0-48579d909b7943b64690c65c71e07e0b80981928  -->
<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="sample report with multi-sheet" pageWidth="572" pageHeight="752" columnWidth="572" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="ce1006b4-b668-45a7-b634-b8bce858cc00">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JRSrepo"/>
    <property name="net.sf.jasperreports.export.xls.exclude.origin.band.3" value="columnHeader"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>
    <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"/>
    <parameter name="resourcetypename" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["DashboardModelResource"]]></defaultValueExpression>
    </parameter>
    <parameter name="Limit" class="java.lang.Integer" isForPrompting="false">
        <defaultValueExpression><![CDATA[50]]></defaultValueExpression>
    </parameter>
    <parameter name="resourceTypeNameLike" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["'%"+$P{resourcetypename}+"'"]]></defaultValueExpression>
    </parameter>
    <queryString language="SQL">
        <![CDATA[select name
, label
, resourcetype
from jiresource
where resourcetype like $P!{resourceTypeNameLike}
limit $P!{Limit}]]>
    </queryString>
    <field name="name" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.name" value="name"/>
        <property name="com.jaspersoft.studio.field.label" value="name"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/>
    </field>
    <field name="label" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.name" value="label"/>
        <property name="com.jaspersoft.studio.field.label" value="label"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/>
    </field>
    <field name="resourcetype" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.name" value="resourcetype"/>
        <property name="com.jaspersoft.studio.field.label" value="resourcetype"/>
        <property name="com.jaspersoft.studio.field.tree.path" value="jiresource"/>
    </field>
    <variable name="resourcetype" class="java.lang.String" resetType="None">
        <initialValueExpression><![CDATA[$F{resourcetype}.substring( $F{resourcetype}.lastIndexOf( "." )+1)]]></initialValueExpression>
    </variable>
    <columnHeader>
        <band height="30">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <staticText>
                <reportElement x="0" y="0" width="350" height="30" uuid="35d2384e-4628-4e1b-8b43-0a8944dcbf55">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbb25a9a-a33d-4d9c-8b12-3af376764728"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <textElement>
                    <font size="14" isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[resourcetype]]></text>
            </staticText>
            <staticText>
                <reportElement x="350" y="0" width="220" height="30" uuid="52fdfa67-390d-457a-9613-3654afb1cc58">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="368cd102-03c6-4cd4-9761-f3bf209f6a21"/>
                </reportElement>
                <textElement>
                    <font size="14" isBold="true" isItalic="true"/>
                </textElement>
                <text><![CDATA[label]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="35" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <textField>
                <reportElement x="0" y="0" width="350" height="30" uuid="a04724ac-064b-46e3-aa03-8785540b974f">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbb25a9a-a33d-4d9c-8b12-3af376764728"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                    <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/>
                    <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$P{resourcetypename}]]></propertyExpression>
                    <printWhenExpression><![CDATA[!($V{REPORT_COUNT}.intValue()>1)]]></printWhenExpression>
                </reportElement>
                <textElement>
                    <font size="8"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{resourcetype}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="0" width="350" height="30" uuid="fa11098a-38c1-43af-951e-6e3e56120dc1">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fbb25a9a-a33d-4d9c-8b12-3af376764728"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                    <property name="com.jaspersoft.studio.unit.x" value="px"/>
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                    <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$P{resourcetypename}]]></propertyExpression>
                    <printWhenExpression><![CDATA[$V{REPORT_COUNT}.intValue()>1]]></printWhenExpression>
                </reportElement>
                <textElement>
                    <font size="8"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{resourcetype}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="350" y="0" width="220" height="30" uuid="a4698581-2936-4d45-aa9b-818c9e613f0e">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="368cd102-03c6-4cd4-9761-f3bf209f6a21"/>
                    <property name="com.jaspersoft.studio.unit.y" value="px"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{label}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

     

    Report output:

    Multisheet xls

    1_24.png.0a0ab5ba3331d2b8592eb840a0ac32f4.png

    PDF

    1_25.png.1f08e926358d111e2b0263a19f9198c2.png

     

    Note

    1) User can test run this report using JasperRerports Server repository data source;

    2) The sheet break is injected into the subreport using the "break before row" property (refero to http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.break.before.row)

    3) The sheet name is injected through report element sheet name property (refer to http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasper...).

    4) Report columns from main and sub report are suppressed in xls output using the method as discussed in this wiki FAQ posting.

    ===========================================================

    TTC-20210510

     Share
    Go to entries

    User Feedback

    Recommended Comments

    There are no comments to display.



    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

×
×
  • Create New...

© 2024 Cloud Software Group,
Inc. All rights reserved.

Products

Explore