crosstab in jasper displays only the first column from input json

Question

my json is as follows

  "values": [
   {
            "purchase": {
                "name":"bags"
                },
                  "weekSpend": [
                    { 
                        "weekStartDate": 20181105,
                        "spend":100

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

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

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

                    }
                ]
    },
   ]

I want to displayed the grid where rows are productname and (columns by weekstartdate)

I followed the technique provided in How to show column at Crosstab even the data is absent

However the productname are listed in row but the column are showing only first column.

my data set is as follows

<queryString language="jsonql">
    <![CDATA[]]>
</queryString>
<field name="productName" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="product.name"/>
    <fieldDescription><![CDATA[product.name]]></fieldDescription>
</field>
<field name="weekStartDate" class="java.lang.String[]">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="weekSpend.weekStartDate"/>
</field>
<group name="activityDateGroup">
    <groupExpression><![CDATA[$F{weekStartDate}]]></groupExpression>
</group>

my crossdata datasource is as follows

        <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).*")]]></dataSourceExpression>
                </datasetRun>
            </dataset>
        </crosstabDataset>

and bucketexpression is on weekstartdate.

With these using JsonQL still only the first column is displayed

How do i get list of column based on weekstardate from iterating from rootnode.

i am using jasper studio 6.5.

Thanks Anjana.

score 0 · Accepted Answer · answered Nov 12 '18 at 10:47

There is an invalid field mapping: weekStartDate to java.lang.String[], that is not supported in JSONQL.

Instead you could have this subDataset expression:

<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>

with these field mappings:

<field name="productName" class="java.lang.String">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="^{2}.purchase.name"/>
</field>
<field name="weekStartDate" class="java.lang.Integer">
    <property name="net.sf.jasperreports.jsonql.field.expression" value="weekStartDate"/>
</field>

Thanks Narcis, This worked and i am able to get the columns displays — Anjana, Nov 12 '18 at 14:52
however encountered another with nested subdataset in crosstab. posted details at https://stackoverflow.com/questions/53264657/jasper-map-crosstab-crosstabheadercell-from-subdataset-different-from-crosstabda. — Anjana, Nov 12 '18 at 14:53

crosstab in jasper displays only the first column from input json

1 Answers1