Crosstab export to excel has table missing column values

0

Hey guys,

I've created a report with multiple crosstabs. When I run the report for PDF, it works just fine and all the data shows up. However, when I run the report for Excel, the first column appears blank. I've actually removed everything but the crosstab to see if something else is affecting the report. Unfortunately, I cannot understand why only the last column shows up. Any help here would be appreciated. 

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  -->
<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="02020" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="translations" isIgnorePagination="true" uuid="9193b9c8-f6b8-4fa9-adb4-41fa87a008c2">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Mitsubishi Hitachi Test"/>
    <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="net.sf.jasperreports.print.keep.full.text" value="true"/>
    <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
    <property name="net.sf.jasperreports.export.xls.wrap.text" value="false"/>
    <property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>
    <property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>
    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
    <property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>
    <template><![CDATA["style.jrtx"]]></template>
    <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>
    <style name="Crosstab 1_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 1_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 1_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 1_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>
    <style name="Crosstab 2_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 2_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 2_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 2_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>
    <subDataset name="32030H" uuid="02636db3-1870-4148-8766-fbb49d921c6e">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Core Reports Adapter MHPSA"/>
        <parameter name="CheckListInstanceId" class="java.lang.String">
            <property name="format" value="uuid"/>
            <property name="autoSet" value="objectId"/>
        </parameter>
        <queryString language="CoreSQL">
            <![CDATA[query = select clie.tableColumnIndex, clie.tableRowIndex, clie.value, clie.title
from ChecklistInstanceElement clie
JOIN ChecklistInstance cli on cli.id = clie.checklistInstance
AND clie.tableId =  'M501G1_33103212_TraShiRec'
AND clie.type='HEADER'
AND clie.tableColumnIndex IS NOT NULL
AND cli.id=$P{CheckListInstanceId}
 & dtos = ChecklistInstanceElement.10;ChecklistInstance.17]]>
        </queryString>
        <field name="clie.tableColumnIndex" class="java.lang.Object"/>
        <field name="clie.tableRowIndex" class="java.lang.Object"/>
        <field name="clie.title" class="java.lang.Object"/>
        <field name="clie.value" class="java.lang.Object"/>
    </subDataset>
    <subDataset name="32030" uuid="aecfb9da-e55c-408b-938e-4358939de30e">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Core Reports Adapter MHPSA"/>
        <parameter name="CheckListInstanceId" class="java.lang.String">
            <property name="format" value="uuid"/>
            <property name="autoSet" value="objectId"/>
        </parameter>
        <queryString language="CoreSQL">
            <![CDATA[query = select clie.tableColumnIndex, clie.tableRowIndex, COALESCE(clie.value,'') as 'clie.value'
from ChecklistInstanceElement clie
JOIN ChecklistInstance cli on cli.id = clie.checklistInstance
AND clie.tableId =   'M501G1_33103212_TraShiRec'
AND clie.parentElementId like 'TableBodyRow%'
AND cli.id=$P{CheckListInstanceId} & dtos = ChecklistInstanceElement.10;ChecklistInstance.17]]>
        </queryString>
        <field name="clie.tableColumnIndex" class="java.lang.Object"/>
        <field name="clie.tableRowIndex" class="java.lang.Object"/>
        <field name="clie.value" class="java.lang.Object"/>
    </subDataset>
    <parameter name="ServiceCallId" class="java.lang.String">
        <property name="format" value="uuid"/>
        <property name="autoSet" value="objectId"/>
    </parameter>
    <queryString language="CoreSQL">
        <![CDATA[query = select ci.id
FROM Activity ac 
INNER JOIN ChecklistInstance ci 
ON ac.id = ci.object.objectId
where ac.object.objectId =$P{ServiceCallId}
AND ac.subject like '3.3.10.3.2.12%'
AND ci.closed=TRUE
ORDER BY ci.lastChanged DESC LIMIT 1 & dtos = Activity.21;ChecklistInstance.17]]>
    </queryString>
    <field name="ci.id" class="java.lang.Object"/>
    <detail>
        <band height="36">
            <printWhenExpression><![CDATA[new Boolean($V{REPORT_COUNT}.intValue()==1)]]></printWhenExpression>
            <crosstab>
                <reportElement x="0" y="0" width="312" height="20" uuid="2d43b7bb-0c88-4949-b061-e8f88988aa63">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab_CH"/>
                    <property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab_CG"/>
                    <property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab_CT"/>
                    <property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab_CD"/>
                </reportElement>
                <box>
                    <bottomPen lineStyle="Solid"/>
                </box>
                <crosstabDataset>
                    <dataset>
                        <datasetRun subDataset="32030H" uuid="48aab187-28c4-4767-9f1b-7cc04c062a12">
                            <datasetParameter name="CheckListInstanceId">
                                <datasetParameterExpression><![CDATA[$F{ci.id}]]></datasetParameterExpression>
                            </datasetParameter>
                            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                        </datasetRun>
                    </dataset>
                </crosstabDataset>
                <rowGroup name="clie.tableRowIndex" width="0">
                    <bucket class="java.lang.Object">
                        <bucketExpression><![CDATA[$F{clie.tableRowIndex}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents/>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="d58d5232-af8d-4922-a532-787d8cf42f20"/>
                                <text><![CDATA[Total clie.tableRowIndex]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="clie.tableColumnIndex" height="0">
                    <bucket class="java.lang.Object">
                        <bucketExpression><![CDATA[$F{clie.tableColumnIndex}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents/>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="60" height="20" forecolor="#FFFFFF" uuid="498ee4bd-adc2-4713-af0a-241366897bd3"/>
                                <text><![CDATA[Total clie.tableColumnIndex]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="clie.title_MEASURE" class="java.lang.Object">
                    <measureExpression><![CDATA[$F{clie.title}]]></measureExpression>
                </measure>
                <crosstabCell width="150" height="20">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField>
                            <reportElement x="0" y="0" width="150" height="20" uuid="4b5d5c71-4b8b-4869-bd31-802b88552336"/>
                            <box>
                                <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                                <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                                <bottomPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                                <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
                            </box>
                            <textElement textAlignment="Center" verticalAlignment="Middle">
                                <font isBold="true"/>
                            </textElement>
                            <textFieldExpression><![CDATA[$V{clie.title_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="80" height="15" columnTotalGroup="clie.tableColumnIndex">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="80" height="15" forecolor="#FFFFFF" uuid="6434b7dc-9ff3-4b71-8988-70307163849f"/>
                            <textFieldExpression><![CDATA[$V{clie.title_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="80" height="15" rowTotalGroup="clie.tableRowIndex">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="80" height="15" forecolor="#FFFFFF" uuid="929c6ff1-b5de-49a8-9c31-7b03d0634371"/>
                            <textFieldExpression><![CDATA[$V{clie.title_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="80" height="15" rowTotalGroup="clie.tableRowIndex" columnTotalGroup="clie.tableColumnIndex">
                    <cellContents mode="Opaque" style="Crosstab_CT">
                        <textField>
                            <reportElement x="0" y="0" width="80" height="15" forecolor="#FFFFFF" uuid="755ab7bd-40ab-4d8c-b27a-8a5eb115b151"/>
                            <textFieldExpression><![CDATA[$V{clie.title_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
            <crosstab>
                <reportElement x="0" y="21" width="312" height="15" uuid="90aa32b8-adda-4229-bcb5-979f2ce599c2">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <property name="com.jaspersoft.studio.crosstab.style.header" value="Crosstab 1_CH"/>
                    <property name="com.jaspersoft.studio.crosstab.style.group" value="Crosstab 1_CG"/>
                    <property name="com.jaspersoft.studio.crosstab.style.total" value="Crosstab 1_CT"/>
                    <property name="com.jaspersoft.studio.crosstab.style.detail" value="Crosstab 1_CD"/>
                </reportElement>
                <crosstabDataset>
                    <dataset>
                        <datasetRun subDataset="32030" uuid="180845d9-ba76-49b4-862d-eeaacbc81d51">
                            <datasetParameter name="CheckListInstanceId">
                                <datasetParameterExpression><![CDATA[$F{ci.id}]]></datasetParameterExpression>
                            </datasetParameter>
                            <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                        </datasetRun>
                    </dataset>
                </crosstabDataset>
                <rowGroup name="clie.tableRowIndex" width="0">
                    <bucket class="java.lang.Object">
                        <bucketExpression><![CDATA[$F{clie.tableRowIndex}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents/>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents mode="Opaque" style="Crosstab 1_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="80" height="15" forecolor="#FFFFFF" uuid="231c7fd8-33c0-4d4e-8b7e-87dc47214084"/>
                                <text><![CDATA[Total clie.tableRowIndex]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="clie.tableColumnIndex" height="0">
                    <bucket class="java.lang.Object">
                        <bucketExpression><![CDATA[$F{clie.tableColumnIndex}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents/>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab 1_CT">
                            <staticText>
                                <reportElement x="0" y="0" width="80" height="15" forecolor="#FFFFFF" uuid="9ffb5c9d-e765-4dcc-a069-8abf736b786a"/>
                                <text><![CDATA[Total clie.tableColumnIndex]]></text>
                            </staticText>
                        </cellContents>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="clie.value_MEASURE" class="java.lang.Object">
                    <measureExpression><![CDATA[$F{clie.value}]]></measureExpression>
                </measure>
                <crosstabCell width="150" height="15">
                    <cellContents mode="Opaque" style="Crosstab 1_CD">
                        <textField>
                            <reportElement x="0" y="0" width="150" height="15" uuid="5419bcc0-e88c-4e69-9cdd-2b617705d7c7"/>
                            <textElement textAlignment="Center" verticalAlignment="Middle"/>
                            <textFieldExpression><![CDATA[$V{clie.value_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="80" height="15" columnTotalGroup="clie.tableColumnIndex">
                    <cellContents mode="Opaque" style="Crosstab 1_CT">
                        <textField>
                            <reportElement x="0" y="0" width="80" height="15" forecolor="#FFFFFF" uuid="8fdc2b99-7a8b-4575-b2c9-763a99864c3f"/>
                            <textFieldExpression><![CDATA[$V{clie.value_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="80" height="15" rowTotalGroup="clie.tableRowIndex">
                    <cellContents mode="Opaque" style="Crosstab 1_CT">
                        <textField>
                            <reportElement x="0" y="0" width="80" height="15" forecolor="#FFFFFF" uuid="61526085-f415-45c3-bae9-bd5070b294d2"/>
                            <textFieldExpression><![CDATA[$V{clie.value_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell width="80" height="15" rowTotalGroup="clie.tableRowIndex" columnTotalGroup="clie.tableColumnIndex">
                    <cellContents mode="Opaque" style="Crosstab 1_CT">
                        <textField>
                            <reportElement x="0" y="0" width="80" height="15" forecolor="#FFFFFF" uuid="8b8afe6d-0053-44df-9f08-764f02e05a6f"/>
                            <textFieldExpression><![CDATA[$V{clie.value_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
            </crosstab>
        </band>
    </detail>
</jasperReport>

fsuarjun03's picture
Joined: Apr 29 2015 - 12:16pm
Last seen: 1 week 1 day ago

0 Answers:

No answers yet
Feedback
randomness