jasper map crosstab crosstabHeaderCell from subdataset different from crosstabdataset

Question

i had need to show crosstabHeaderCell columns dynamically from string array. this is a json

{
    "header": {
        "columnName": [
            "Product Store1",
            "location"
        ],
        "values": [{
                "purchase": {
                    "name": "bags",
                    "location": "MainMarket "
                },
                "weekSpend": [{
                        "weekStartDate": 20181105,
                        "spend": 100

                    },
                    {
                        "weekStartDate": 20181112,
                        "spend": 200

                    }
                ]
            },
            {
                "purchase": {
                    "name": "shoes",
                    "location": "MainMarket "
                },
                "weekSpend": [{
                        "weekStartDate": 20181105,
                        "spend": 100

                    },
                    {
                        "weekStartDate": 20181112,
                        "spend": 200

                    }
                ]
            }
        ]
    }

I want to show crosstabHeaderCell from list of values of array columnName in cross tab. from the above json I am able to iterate and show product name and location as rows and weekSpend as column with data set of crosstab as

<crosstabDataset isDataPreSorted="true">
            <dataset>
                <datasetRun subDataset="crossTabDataSet" uuid="e7b27508-8a48-4785-a48e-c646249df9a9">
                    <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("..values(@size > 0)..weekSpend.*")]]></dataSourceExpression>
                </datasetRun>
            </dataset>
        </crosstabDataset>

when i specify list component for crosstabHeaderCell as below i am getting error parameter REPORT_DATA_SOURCE not found.

<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="Horizontal" ignoreWidth="true">
                <datasetRun subDataset="listcol" uuid="a13d0691-5b9e-4d9f-aaa0-9d0ff0b67b78">
<dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("$.columnName.*")]]></dataSourceExpression>
                </datasetRun>
                <jr:listContents height="30" width="100">
                    <textField>
                        <reportElement x="0" y="0" width="100" height="30" uuid="99c3d5fe-9f90-4efc-bdab-4371eee17d54"/>
                        <textFieldExpression><![CDATA[$F{namecol}]]></textFieldExpression>
                    </textField>
                </jr:listContents>
            </jr:list>

the dataset listcol is defined as

<subDataset name="listcol" uuid="2630f2ff-b912-4f05-8189-7a5d47eada9e">
        <field name="namecol" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="[0]"/>
        </field>
    </subDataset>

how can a i refer to list of values from crosstab data set. why does list fail with parameter not found exception for REPORT_DATA_SOURCE. can we set the REPORT_DATA_SOURCE value from crosstab dataset to be used by inner dataset.

if i remove REPORT_DATA_SOURCE jr:list and use querystring listcol as $.columnName.*. i get blank in crosstabHeaderCell

Expected output is

Thanks Anjana,

can i use field of type string array in crosstab. in my example above i want to iterate through field columnName however the crosstab data set is from array of weekSpend. I want list within crosstab. currently i get compile error parameter REPORT_DATA_SOURCE not found for list. — Anjana, Nov 13 '18 at 00:35
It would be best to exemplify the desired output with actual data from the JSON you provided. — Narcis, Nov 13 '18 at 11:21
added expected output to show the row header name are mapped from json field columnName row are mapped from values array prurchase.name and coulumn are mapped weekspend array. Thanks — Anjana, Nov 13 '18 at 16:35

score 0 · Accepted Answer · answered Nov 14 '18 at 12:49

You need to make use of crosstabParameters in order to pass data from the enclosing report, like so:

<?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="XTabReport" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="33c43d53-f9b3-4cc9-92b2-bedd805d66a9">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="json_crosstab/DataAdapter.xml"/>
    <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>
    <subDataset name="XTabDS" uuid="9911fa6c-61b7-4952-81bf-4666e3e17fcf">
        <field name="weekStartDate" class="java.lang.Integer">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="weekStartDate"/>
        </field>
        <field name="spend" class="java.lang.Integer">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="spend"/>
        </field>
        <field name="name" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="^{2}.purchase.name"/>
        </field>
        <field name="location" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="^{2}.purchase.location"/>
        </field>
    </subDataset>
    <subDataset name="listSubDataset" uuid="01e76955-f29e-4d52-991b-aa0149bfdb37">
        <field name="colName" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="[0]"/>
        </field>
    </subDataset>
    <queryString language="jsonql">
        <![CDATA[]]>
    </queryString>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="58">
            <staticText>
                <reportElement x="140" y="10" width="280" height="30" uuid="c8e3d7ad-eeb0-4cd1-8bef-88874e6921d4"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14"/>
                </textElement>
                <text><![CDATA[List inside Crosstab]]></text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="30" splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="70" splitType="Stretch">
            <crosstab>
                <reportElement x="0" y="0" width="560" height="70" uuid="79f2afae-ac8b-4442-ac8e-ae1b1f68109f">
                    <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>
                <crosstabParameter name="listDS" class="net.sf.jasperreports.engine.data.JsonQLDataSource">
                    <parameterValueExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("header.columnName")]]></parameterValueExpression>
                </crosstabParameter>
                <crosstabDataset>
                    <dataset>
                        <datasetRun subDataset="XTabDS" uuid="73b2e155-5015-451a-8813-7e2ed2f731fb">
                            <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("..values..weekSpend.*")]]></dataSourceExpression>
                        </datasetRun>
                    </dataset>
                </crosstabDataset>
                <crosstabHeaderCell>
                    <cellContents>
                        <componentElement>
                            <reportElement x="0" y="0" width="180" height="30" isPrintWhenDetailOverflows="true" uuid="412c6db7-0cfa-4025-80cd-8c450f9eab96">
                                <property name="net.sf.jasperreports.export.headertoolbar.table.name" value=""/>
                            </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="Horizontal">
                                <datasetRun subDataset="listSubDataset" uuid="a911c80f-a0b3-4395-bbd7-2c52c67585d3">
                                    <dataSourceExpression><![CDATA[$P{listDS}]]></dataSourceExpression>
                                </datasetRun>
                                <jr:listContents height="30" width="90">
                                    <textField>
                                        <reportElement x="0" y="0" width="90" height="30" isPrintWhenDetailOverflows="true" uuid="168fd030-81cf-4b80-9766-6f4637c779ec"/>
                                        <box>
                                            <topPen lineWidth="0.5"/>
                                            <leftPen lineWidth="0.5"/>
                                        </box>
                                        <textElement>
                                            <paragraph leftIndent="3" spacingBefore="3"/>
                                        </textElement>
                                        <textFieldExpression><![CDATA[$F{colName}]]></textFieldExpression>
                                    </textField>
                                </jr:listContents>
                            </jr:list>
                        </componentElement>
                    </cellContents>
                </crosstabHeaderCell>
                <rowGroup name="name" width="90" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{name}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="90" height="20" uuid="e8aa37d7-0921-40a6-8aca-1cccae61d26d"/>
                                <textElement>
                                    <paragraph leftIndent="3" spacingBefore="3"/>
                                </textElement>
                                <textFieldExpression><![CDATA[$V{name}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents/>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <rowGroup name="location" width="90" totalPosition="End">
                    <bucket class="java.lang.String">
                        <bucketExpression><![CDATA[$F{location}]]></bucketExpression>
                    </bucket>
                    <crosstabRowHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="90" height="20" uuid="86107a55-79be-4170-a2ee-b2a0ae5315e2"/>
                                <textElement>
                                    <paragraph leftIndent="3" spacingBefore="3"/>
                                </textElement>
                                <textFieldExpression><![CDATA[$V{location}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabRowHeader>
                    <crosstabTotalRowHeader>
                        <cellContents/>
                    </crosstabTotalRowHeader>
                </rowGroup>
                <columnGroup name="weekStartDate" height="30" totalPosition="End">
                    <bucket class="java.lang.Integer">
                        <bucketExpression><![CDATA[$F{weekStartDate}]]></bucketExpression>
                    </bucket>
                    <crosstabColumnHeader>
                        <cellContents mode="Opaque" style="Crosstab_CH">
                            <textField>
                                <reportElement x="0" y="0" width="90" height="30" uuid="c65fb64a-d89b-4cc3-be28-d76624cb3d93"/>
                                <textElement>
                                    <paragraph leftIndent="3" spacingBefore="3"/>
                                </textElement>
                                <textFieldExpression><![CDATA[$V{weekStartDate}]]></textFieldExpression>
                            </textField>
                        </cellContents>
                    </crosstabColumnHeader>
                    <crosstabTotalColumnHeader>
                        <cellContents/>
                    </crosstabTotalColumnHeader>
                </columnGroup>
                <measure name="spend_MEASURE" class="java.lang.Integer">
                    <measureExpression><![CDATA[$F{spend}]]></measureExpression>
                </measure>
                <crosstabCell width="90" height="20">
                    <cellContents mode="Opaque" style="Crosstab_CD">
                        <textField>
                            <reportElement x="0" y="0" width="90" height="20" uuid="efb4b6dd-d9d3-4520-a94e-c0010a572b29"/>
                            <textElement>
                                <paragraph leftIndent="3" spacingBefore="3"/>
                            </textElement>
                            <textFieldExpression><![CDATA[$V{spend_MEASURE}]]></textFieldExpression>
                        </textField>
                    </cellContents>
                </crosstabCell>
                <crosstabCell height="20" columnTotalGroup="weekStartDate">
                    <cellContents/>
                </crosstabCell>
                <crosstabCell width="90" height="0" rowTotalGroup="location">
                    <cellContents/>
                </crosstabCell>
                <crosstabCell height="0" rowTotalGroup="location" columnTotalGroup="weekStartDate">
                    <cellContents/>
                </crosstabCell>
                <crosstabCell width="90" height="20" rowTotalGroup="name">
                    <cellContents/>
                </crosstabCell>
                <crosstabCell height="20" rowTotalGroup="name" columnTotalGroup="weekStartDate">
                    <cellContents/>
                </crosstabCell>
            </crosstab>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

with the following output:

Thanks Narcis this is useful i have to do some more test to mark to this accepted. however wanted to understand is it possible to change the width or any format of text field within list dynamically. — Anjana, Nov 14 '18 at 19:17
I applied the fix it work to show the dynamic values from list however the width of the header does not stretch as per the list of values in the array.can you pls guide on same. — Anjana, Nov 15 '18 at 03:57
also what if i want to use list in the row can i use the same paramter expression. — Anjana, Nov 15 '18 at 03:58
this option works however i am also looking at setting dynamic width for text field created through list. Thanks Narcis for your prompt help. — Anjana, Nov 15 '18 at 16:59

jasper map crosstab crosstabHeaderCell from subdataset different from crosstabdataset

1 Answers1

Linked