Search code examples
jasper-reportspivotcrosstab

How to avoid error incrementing crosstab dataset when trying to add pivot in jasper studio?

I want to add a pivot (crosstab) in jasper report. When I add the pivot, I get an error when I download the file. I tried to debug with running the query and I don't have an error and I also try to debug with showing the parameter on the view, the parameter is show up without an error, I also try to show the result of query, and the data is show up.

So what is my problem? and how to fix my code?.

detail band

<detail>
    <band height="230">
        <crosstab>
            <reportElement x="167" y="30" width="200" height="200" uuid="b1977adf-e860-43cf-8940-fe4862f16621"/>
            <rowGroup name="KOMPETENSI1" width="60">
                <bucket class="java.lang.String">
                    <bucketExpression><![CDATA[$F{KOMPETENSI}]]></bucketExpression>
                </bucket>
                <crosstabRowHeader>
                    <cellContents mode="Opaque" style="Crosstab_CH">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" uuid="72845a66-f743-4f35-8091-02df93de67d0"/>
                            <textFieldExpression><![CDATA[$V{KOMPETENSI1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabRowHeader>
                <crosstabTotalRowHeader>
                    <cellContents>
                        <staticText>
                            <reportElement x="0" y="0" width="-2147483648" height="-2147483648" uuid="5792f3fa-1bb5-4134-91e1-b51918c05ec0"/>
                            <text><![CDATA[Total KOMPETENSI1]]></text>
                        </staticText>
                    </cellContents>
                </crosstabTotalRowHeader>
            </rowGroup>
            <columnGroup name="TAHUN1" height="20">
                <bucket class="java.lang.String">
                    <bucketExpression><![CDATA[$F{TAHUN}]]></bucketExpression>
                </bucket>
                <crosstabColumnHeader>
                    <cellContents mode="Opaque" style="Crosstab_CH">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" uuid="fd89bbdf-eb6c-4c75-ab2c-9414ccd88458"/>
                            <textFieldExpression><![CDATA[$V{TAHUN1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabColumnHeader>
                <crosstabTotalColumnHeader>
                    <cellContents>
                        <staticText>
                            <reportElement x="0" y="0" width="-2147483648" height="-2147483648" uuid="2d91db64-a9d7-43d3-b1c5-4c61412de5a1"/>
                            <text><![CDATA[Total TAHUN1]]></text>
                        </staticText>
                    </cellContents>
                </crosstabTotalColumnHeader>
            </columnGroup>
            <columnGroup name="JOB1" height="20">
                <bucket class="java.lang.String">
                    <bucketExpression><![CDATA[$F{JOB}]]></bucketExpression>
                </bucket>
                <crosstabColumnHeader>
                    <cellContents mode="Opaque" style="Crosstab_CH">
                        <textField>
                            <reportElement x="0" y="0" width="60" height="20" uuid="6adc10a6-8c28-4d17-bc6d-32727f7a8805"/>
                            <textFieldExpression><![CDATA[$V{JOB1}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabColumnHeader>
                <crosstabTotalColumnHeader>
                    <cellContents>
                        <staticText>
                            <reportElement x="0" y="0" width="-2147483648" height="-2147483648" uuid="fe8d03a8-5308-4845-b868-2b4cec45c2b5"/>
                            <text><![CDATA[Total JOB1]]></text>
                        </staticText>
                    </cellContents>
                </crosstabTotalColumnHeader>
            </columnGroup>
            <measure name="LEVEL_JOB_MEASURE1" class="java.lang.String">
                <measureExpression><![CDATA[$F{LEVEL_JOB}]]></measureExpression>
            </measure>
            <measure name="LEVEL_KOMPETENSI_MEASURE1" class="java.lang.String">
                <measureExpression><![CDATA[$F{LEVEL_KOMPETENSI}]]></measureExpression>
            </measure>
            <crosstabCell width="60" height="20">
                <cellContents mode="Opaque" style="Crosstab_CD">
                    <textField>
                        <reportElement x="0" y="0" width="60" height="10" uuid="0e4ec703-4f2c-4389-bb9b-02a7f8df230e"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_JOB_MEASURE1}]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="0" y="10" width="60" height="10" uuid="8f49c8dc-e172-4407-ba54-6aa077591235"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_KOMPETENSI_MEASURE1}]]></textFieldExpression>
                    </textField>
                </cellContents>
            </crosstabCell>
            <crosstabCell width="60" height="20" columnTotalGroup="TAHUN1">
                <cellContents mode="Opaque" style="Crosstab_CT">
                    <textField>
                        <reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="78be64d3-2d78-411f-9d60-3b545311e376"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_JOB_MEASURE1}]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="47ac3c87-fee5-4e99-bade-9304180bdc2c"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_KOMPETENSI_MEASURE1}]]></textFieldExpression>
                    </textField>
                </cellContents>
            </crosstabCell>
            <crosstabCell width="60" height="20" columnTotalGroup="JOB1">
                <cellContents mode="Opaque" style="Crosstab_CG">
                    <textField>
                        <reportElement x="0" y="0" width="60" height="10" uuid="94604b9b-36f7-4572-8c24-c48a538503b8"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_JOB_MEASURE1}]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="0" y="10" width="60" height="10" uuid="8f2f6f73-049e-45ae-9d5b-fe55abad0c24"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_KOMPETENSI_MEASURE1}]]></textFieldExpression>
                    </textField>
                </cellContents>
            </crosstabCell>
            <crosstabCell width="60" height="20" rowTotalGroup="KOMPETENSI1">
                <cellContents mode="Opaque" style="Crosstab_CT">
                    <textField>
                        <reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="41785b14-fc8f-4c85-928b-0df8555ffcb5"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_JOB_MEASURE1}]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="0cfec42f-0f33-46ff-9367-2c0c4978744c"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_KOMPETENSI_MEASURE1}]]></textFieldExpression>
                    </textField>
                </cellContents>
            </crosstabCell>
            <crosstabCell width="60" height="20" rowTotalGroup="KOMPETENSI1" columnTotalGroup="TAHUN1">
                <cellContents mode="Opaque" style="Crosstab_CT">
                    <textField>
                        <reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="1081fcf5-f921-47a0-89c0-923b9be4fd73"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_JOB_MEASURE1}]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="0215fe30-2917-40a7-84d1-2cdac6216373"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_KOMPETENSI_MEASURE1}]]></textFieldExpression>
                    </textField>
                </cellContents>
            </crosstabCell>
            <crosstabCell width="60" height="20" rowTotalGroup="KOMPETENSI1" columnTotalGroup="JOB1">
                <cellContents mode="Opaque" style="Crosstab_CT">
                    <textField>
                        <reportElement x="0" y="0" width="60" height="10" forecolor="#FFFFFF" uuid="ba2855de-e2bf-4354-9c4f-67105912b4d7"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_JOB_MEASURE1}]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="0" y="10" width="60" height="10" forecolor="#FFFFFF" uuid="202c0fa6-308b-4a7e-9656-33eea6859d89"/>
                        <textFieldExpression><![CDATA[$V{LEVEL_KOMPETENSI_MEASURE1}]]></textFieldExpression>
                    </textField>
                </cellContents>
            </crosstabCell>
        </crosstab>
    </band>
</detail>

Parameter definition:

<parameter name="pPersonId" class="java.lang.String">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<parameter name="npp" class="java.lang.String"/>

Query:

<queryString>
    <![CDATA[select CASE WHEN (JENJAB like 'SEVP') THEN '4' 
    WHEN (JENJAB like 'EVP') THEN '4' WHEN (JENJAB like 'SVP') THEN '4' 
    WHEN (JENJAB like 'SVP') THEN '4' WHEN (JENJAB like 'AVP') THEN '3' 
    WHEN (JENJAB like 'AVP') THEN '2' END AS LEVEL_JOB, kompetensi, 
    level_kompetensi, tahun, job from OL_ASSESSMENT_RESULT 
    where tahun in (select tahun from ( select distinct tahun  
    from OL_ASSESSMENT_RESULT order by tahun desc)  
    where ROWNUM <=3 ) and npp = $P{npp}]]>
</queryString>

Field declaration:

<field name="KOMPETENSI" class="java.lang.String"/>
<field name="LEVEL_JOB" class="java.lang.String"/>
<field name="LEVEL_KOMPETENSI" class="java.lang.String"/>
<field name="TAHUN" class="java.lang.String"/>
<field name="JOB" class="java.lang.String"/>

Error:

JRRuntimeException: Error incrementing crosstab dataset
Caused by: JRException: Crosstab data has already been processed

enter image description here

Solution

  • Your problem is that you have put the pivot, crosstab in the detail band and are using the report datasource for the crosstab.

    Hence, on every record in the datasource the report iterates the detail band, creating new crosstab that are using same datasource. This does not work and gives you error

    JRException: Crosstab data has already been processed

    Solution

    1. Move the crosstab to the summary or title band, completely remove the detail band.

    2. Create a specific subDataset for the crosstab