Pivot table on a separate excel sheet

[shuhovivan]

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?

[reportdev]

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 unionselect 2 as ID, 'blue' as color, 'none' as column_name from dual unionselect 3 as ID, 'orange' as color, 'none' as column_name from dual unionselect 4 as ID, 'red' as color, 'none' as column_name from dual unionselect 5 as ID, 'blue' as color, 'none' as column_name from dual unionselect 6 as ID, 'orange' as color, 'none' as column_name from dual unionselect 7 as ID, 'red' as color, 'none' as column_name from dual unionselect 8 as ID, 'blue' as color, 'none' as column_name from dual unionselect 9 as ID, 'green' as color, 'none' as column_name from dual unionselect 10 as ID, 'red' as color, 'none' as column_name from dual unionselect 11 as ID, 'blue' as color, 'none' as column_name from dual unionselect 12 as ID, 'orange' as color, 'none' as column_name from dual unionselect 13 as ID, 'yellow' as color, 'none' as column_name from dual unionselect 14 as ID, 'blue' as color, 'none' as column_name from dual unionselect 15 as ID, 'orange' as color, 'none' as column_name from dual unionselect 16 as ID, 'red' as color, 'none' as column_name from dual unionselect 17 as ID, 'blue' as color, 'none' as column_name from dual unionselect 18 as ID, 'orange' as color, 'none' as column_name from dual unionselect 19 as ID, 'red' as color, 'none' as column_name from dual unionselect 20 as ID, 'blue' as color, 'none' as column_name from dual unionselect 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>[/code]

 

 

 

[selene.platt_1]

Hi, I'm new to Jaspersoft and can't answer your question, but I am wondering if you created a true pivot table or a crosstab? I need to create a real Excel pivot table and crosstab is not that.