Pivot table on a separate excel sheet

0

I created a report with a pivot table on a "Summary" band and set "Summary on a new page" in the report properties because i want to summary was on a separate excel sheet. Further i set "Ignore pagination" because i want to all my data was on one page.

And in the end I got the pivot table still places on the same sheet with the main data.

How could i place the pivot table on a new excel sheet, separate from data?

shuhovivan's picture
Joined: Feb 10 2017 - 1:56am
Last seen: 3 months 1 week ago

1 Answer:

1

you need to use these properties on a field which separates these two sections.

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

below is a sample jrxml, which will help you.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2017-03-17T09:11:04 -->
<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="symbols" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="f6ce5ff2-122e-4088-ae00-ca461a0feff4">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="PRODSUP"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <style name="Crosstab_CH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CG" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CT" mode="Opaque" backcolor="#005FB3">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Crosstab_CD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <queryString>
        <![CDATA[select 1 as ID, 'red' as color, 'none' as column_name from dual union
select 2 as ID, 'blue' as color, 'none' as column_name from dual union
select 3 as ID, 'orange' as color, 'none' as column_name from dual union
select 4 as ID, 'red' as color, 'none' as column_name from dual union
select 5 as ID, 'blue' as color, 'none' as column_name from dual union
select 6 as ID, 'orange' as color, 'none' as column_name from dual union
select 7 as ID, 'red' as color, 'none' as column_name from dual union
select 8 as ID, 'blue' as color, 'none' as column_name from dual union
select 9 as ID, 'green' as color, 'none' as column_name from dual union
select 10 as ID, 'red' as color, 'none' as column_name from dual union
select 11 as ID, 'blue' as color, 'none' as column_name from dual union
select 12 as ID, 'orange' as color, 'none' as column_name from dual union
select 13 as ID, 'yellow' as color, 'none' as column_name from dual union
select 14 as ID, 'blue' as color, 'none' as column_name from dual union
select 15 as ID, 'orange' as color, 'none' as column_name from dual union
select 16 as ID, 'red' as color, 'none' as column_name from dual union
select 17 as ID, 'blue' as color, 'none' as column_name from dual union
select 18 as ID, 'orange' as color, 'none' as column_name from dual union
select 19 as ID, 'red' as color, 'none' as column_name from dual union
select 20 as ID, 'blue' as color, 'none' as column_name from dual union
select 21 as ID, 'orange' as color, 'none' as column_name from dual ]]>
    </queryString>
    <field name="ID" class="java.math.BigDecimal"/>
    <field name="COLOR" class="java.lang.String"/>
    <field name="COLUMN_NAME" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <columnHeader>
        <band height="21" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="80" height="21" uuid="c0b2325b-132e-4c24-9e37-8db4bada59d4"/>
                <text><![CDATA[ID]]></text>
            </staticText>
            <staticText>
                <reportElement x="80" y="0" width="100" height="21" uuid="06bdcdc0-6e54-4b0c-8243-79a57ff1fa3f"/>
                <text><![CDATA[COLOR]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="21" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="80" height="21" uuid="8d4a6ce2-40d1-4630-961d-b00f9d1a716b"/>
                <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="80" y="0" width="100" height="21" uuid="ce1f369c-884f-46f8-a8d8-35f1cc015388"/>
                <textFieldExpression><![CDATA[$F{COLOR}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <summary>
        <band height="80">
            <crosstab>
                <reportElement x="0" y="20" width="555" height="60" uuid="1280ef10-599c-4071-aae6-55044ebe1372">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                </reportElement>
                <rowGroup name="COLOR1" width="80" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{COLOR}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                            <textField>
                                <reportElement x="0" y="0" width="80" height="20" uuid="aaed7cbb-221b-4cc4-b88e-4341df7ff6e1"/>
                                <textFieldExpression><![CDATA[$V{COLOR1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="80" height="20" forecolor="#FFFFFF" uuid="5672ab88-1ed3-4743-b00e-4cf8db6e6908"/>
                                <text><![CDATA[Total COLOR1]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="COLUMN_NAME1" height="20" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{COLUMN_NAME}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                            <textField>
                                <reportElement x="0" y="0" width="100" height="20" uuid="5606f0ac-09c6-48ac-8bc5-5d140652c311"/>
                                <textFieldExpression><![CDATA[$V{COLUMN_NAME1}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
                            <staticText>
                                <reportElement x="0" y="0" width="100" height="20" forecolor="#FFFFFF" uuid="eb39b777-dfd8-4c47-b74e-c5d0079042ab"/>
                                <text><![CDATA[Total COLUMN_NAME1]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="COLOR_MEASURE1" class="java.lang.Integer" calculation="Count">
                    <measureExpression><![CDATA[$F{COLOR}]]></measureExpression>
                </measure>
                <crosstabCell width="100" height="20">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField>
                            <reportElement x="0" y="0" width="100" height="20" uuid="08a2eb95-b685-4d24-8f2c-136b25819981"/>
                            <textFieldExpression><![CDATA[$V{COLOR_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="100" height="20" columnTotalGroup="COLUMN_NAME1">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="100" height="20" forecolor="#FFFFFF" uuid="1a043525-f7e9-45a4-bb50-352e72237a1d"/>
                            <textFieldExpression><![CDATA[$V{COLOR_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="100" height="20" rowTotalGroup="COLOR1">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="100" height="20" forecolor="#FFFFFF" uuid="1589e069-69b5-4ab9-ba47-dbb5b01878bb"/>
                            <textFieldExpression><![CDATA[$V{COLOR_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="100" height="20" rowTotalGroup="COLOR1" columnTotalGroup="COLUMN_NAME1">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="100" height="20" forecolor="#FFFFFF" uuid="078873ef-6f19-4672-85bd-5d7676ad7a1b"/>
                            <textFieldExpression><![CDATA[$V{COLOR_MEASURE1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
            <staticText>
                <reportElement x="0" y="0" width="555" height="20" uuid="69b7eaef-385f-45d9-bdf3-8768d8131ecb">
                    <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/>
                    <property name="net.sf.jasperreports.export.xls.sheet.name" value="Summary"/>
                </reportElement>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Summary Starts here]]></text>
            </staticText>
        </band>
    </summary>
</jasperReport>

reportdev's picture
686
Joined: Oct 12 2015 - 12:05pm
Last seen: 1 day 22 hours ago
Feedback