Valter Henrique Valter Henrique - 17 days ago 9
Java Question

How to create subgroups using JSON as datasource?

I would like to have a report where shows my

Orders
classified, grouped, by
Currency
, and by
Country
.

It should look like this:

EUR
- Germany
- (only orders with EUR as currency and order made in Germany)
- France
- (only orders with EUR as currency and order made in France)

US
- USA
- (only orders with US as currency and order made in USA)


So, I thought creating a
master
report, which will have 2
subreports
:

master
- subreport grouping orders by currency
- subreport grouping orders by country
- subreport listing the orders


So I did it like this:

master.jrxml

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.1.0.final using JasperReports Library version 6.1.0 -->
<!-- 2016-11-18T18:37:36 -->
<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="master" pageWidth="595" pageHeight="842" columnWidth="515" leftMargin="40" rightMargin="40" topMargin="50" bottomMargin="50" uuid="bbe115b5-a5a0-4b39-9b73-7092dc59ab6d">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="Demo JSON Data Adapter "/>
<property name="net.sf.jasperreports.json.source" value="/home/vasi/git/valter/reports/Demo/data/northwind.json"/>
<style name="Sans_Normal" isDefault="true" fontName="DejaVu Sans" fontSize="12" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
<style name="Sans_Normal_8" style="Sans_Normal" fontSize="8"/>
<style name="Sans_Bold" fontName="DejaVu Sans" fontSize="12" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
<style name="Sans_Bold_8" style="Sans_Bold" fontSize="8"/>
<queryString language="json">
<![CDATA[Northwind]]>
</queryString>
<field name="Customers" class="java.lang.String">
<fieldDescription><![CDATA[Customers]]></fieldDescription>
</field>
<field name="Orders" class="java.lang.String">
<fieldDescription><![CDATA[Orders]]></fieldDescription>
</field>
<title>
<band height="50">
<line>
<reportElement x="0" y="0" width="515" height="1" uuid="fc148b4e-50df-4a12-aa14-8505a4cfa6e1"/>
</line>
<staticText>
<reportElement style="Sans_Normal" x="0" y="10" width="515" height="30" uuid="5bf7651c-cd6b-4eaf-b65a-1413d60faab0"/>
<textElement textAlignment="Center">
<font size="22"/>
</textElement>
<text><![CDATA[Orders sorted by currency and country]]></text>
</staticText>
</band>
</title>
<detail>
<band height="70">
<subreport isUsingCache="true">
<reportElement x="0" y="0" width="515" height="70" uuid="ebd4e147-b294-4367-a072-dc1954980232"/>
<subreportParameter name="REPORT_DATA_SOURCE">
<subreportParameterExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("Orders")]]></subreportParameterExpression>
</subreportParameter>
<subreportExpression><![CDATA["reports/orders/subreport/currency.jasper"]]></subreportExpression>
</subreport>
</band>
</detail>
</jasperReport>


subreport/currency.jrxml

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.1.0.final using JasperReports Library version 6.1.0 -->
<!-- 2016-11-18T18:38:23 -->
<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="currency" pageWidth="515" pageHeight="742" columnWidth="515" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="bbe115b5-a5a0-4b39-9b73-7092dc59ab6d">
<style name="Sans_Normal" isDefault="true" fontName="DejaVu Sans" fontSize="12" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
<style name="Sans_Normal_8" style="Sans_Normal" fontSize="8"/>
<style name="Sans_Bold" fontName="DejaVu Sans" fontSize="12" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
<style name="Sans_Bold_8" style="Sans_Bold" fontSize="8"/>
<field name="Currency" class="java.lang.String">
<property name="net.sf.jasperreports.json.field.expression" value="Currency"/>
</field>
<sortField name="Currency"/>
<group name="CurrencyGroup" isReprintHeaderOnEachPage="true">
<groupExpression><![CDATA[$F{Currency}]]></groupExpression>
<groupHeader>
<band height="21">
<textField>
<reportElement style="Sans_Bold" x="5" y="5" width="100" height="15" isPrintWhenDetailOverflows="true" uuid="0aaeed6e-7ba1-4ab9-be59-d6ca702995fc"/>
<textFieldExpression><![CDATA[$F{Currency}]]></textFieldExpression>
</textField>
<line>
<reportElement x="0" y="20" width="515" height="1" isPrintWhenDetailOverflows="true" uuid="6d76c22c-329f-4e77-a886-8580d3cb6bc1"/>
</line>
</band>
</groupHeader>
<groupFooter>
<band height="31"/>
</groupFooter>
</group>
<pageHeader>
<band height="21">
<staticText>
<reportElement style="Sans_Bold" mode="Opaque" x="0" y="5" width="515" height="15" forecolor="#FFFFFF" backcolor="#333333" uuid="da0f1cad-f552-424b-bf19-b41cabbfa4ac"/>
<text><![CDATA[Currency]]></text>
</staticText>
</band>
</pageHeader>
<detail>
<band height="60">
<subreport>
<reportElement x="10" y="10" width="490" height="40" uuid="d181a0e1-1a31-4723-abe7-8f2f3dc09bce"/>
<subreportParameter name="REPORT_DATA_SOURCE">
<subreportParameterExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource()]]></subreportParameterExpression>
</subreportParameter>
<subreportExpression><![CDATA["reports/orders/subreport/subreport/countries.jasper"]]></subreportExpression>
</subreport>
</band>
</detail>
</jasperReport>


However, I don't know how to pass my datasource from my subreport to my subreport. From
master
report to my subreport it was okay, but I'm stuck with this exception:

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression for source text: ((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource()
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:506)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:481)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:362)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
Caused by: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression for source text: ((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource()
at net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:872)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:330)
at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:382)
at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:357)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2031)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:755)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:262)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:122)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:551)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:119)
at java.lang.Thread.run(Unknown Source)
Caused by: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression for source text: ((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource()
at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:264)
at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:610)
at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:578)
at net.sf.jasperreports.engine.fill.JRFillSubreport.getParameterValues(JRFillSubreport.java:680)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateParameterValues(JRFillSubreport.java:478)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:458)
at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluate(JRFillSubreport.java:345)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:258)
at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:454)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2029)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:755)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:262)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:122)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:551)
at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:736)
at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:58)
at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:216)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
... 1 more
Caused by: java.lang.ClassCastException: net.sf.jasperreports.engine.fill.SortedDataSource cannot be cast to net.sf.jasperreports.engine.data.JsonDataSource
at currency_1479490219429_385194.evaluate(currency_1479490219429_385194:195)
at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:251)
... 19 more


I've created a branch only for this question. This is my json data source.

I've also tried this suggestion, however, I don't know how to get my data source as parameter on my subreport, and how to re-send it again. :(

Any ideas, or sugesstions, would be very appreciated!

Answer

For those who might have the same issue. The solution was easier than I thought originally. It was necessary to create a second group only, based on the field that I wanted to group.

    <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1  -->
<!-- 2016-11-21T16:51:47 -->
<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="currency" pageWidth="515" pageHeight="742" columnWidth="515" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="bbe115b5-a5a0-4b39-9b73-7092dc59ab6d">
    <style name="Sans_Normal" isDefault="true" fontName="DejaVu Sans" fontSize="12" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
    <style name="Sans_Normal_8" style="Sans_Normal" fontSize="8"/>
    <style name="Sans_Bold" fontName="DejaVu Sans" fontSize="12" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
    <style name="Sans_Bold_8" style="Sans_Bold" fontSize="8"/>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <field name="Currency" class="java.lang.String">
        <property name="net.sf.jasperreports.json.field.expression" value="Currency"/>
    </field>
    <field name="ShipCountry" class="java.lang.String">
        <property name="net.sf.jasperreports.json.field.expression" value="ShipCountry"/>
    </field>
    <field name="Id" class="java.lang.String">
        <property name="net.sf.jasperreports.json.field.expression" value="OrderID"/>
    </field>
    <field name="OrderDate" class="java.lang.String">
        <property name="net.sf.jasperreports.json.field.expression" value="OrderDate"/>
    </field>
    <field name="ShipCity" class="java.lang.String">
        <property name="net.sf.jasperreports.json.field.expression" value="ShipCity"/>
    </field>
    <field name="Freight" class="java.lang.Float">
        <property name="net.sf.jasperreports.json.field.expression" value="Freight"/>
    </field>
    <group name="CurrencyGroup" isReprintHeaderOnEachPage="true">
        <groupExpression><![CDATA[$F{Currency}]]></groupExpression>
        <groupHeader>
            <band height="26">
                <textField>
                    <reportElement style="Sans_Bold" x="5" y="6" width="124" height="15" isPrintWhenDetailOverflows="true" uuid="0aaeed6e-7ba1-4ab9-be59-d6ca702995fc"/>
                    <textElement>
                        <font size="10"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{Currency}]]></textFieldExpression>
                </textField>
                <line>
                    <reportElement x="0" y="20" width="515" height="1" isPrintWhenDetailOverflows="true" uuid="6d76c22c-329f-4e77-a886-8580d3cb6bc1"/>
                </line>
                <line>
                    <reportElement x="1" y="6" width="515" height="1" isPrintWhenDetailOverflows="true" uuid="533d7e0a-969c-44c7-8114-eabe53fe2bde"/>
                </line>
            </band>
        </groupHeader>
    </group>
    <group name="ShipCountryGroup" isReprintHeaderOnEachPage="true">
        <groupExpression><![CDATA[$F{ShipCountry}]]></groupExpression>
        <groupHeader>
            <band height="20">
                <textField>
                    <reportElement style="Sans_Bold" x="22" y="3" width="124" height="15" isPrintWhenDetailOverflows="true" uuid="03b06cb9-2565-47fa-845a-9585c2dd2056"/>
                    <textElement>
                        <font size="10"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{ShipCountry}]]></textFieldExpression>
                </textField>
            </band>
        </groupHeader>
    </group>
    <pageHeader>
        <band height="21">
            <staticText>
                <reportElement style="Sans_Bold" mode="Opaque" x="0" y="5" width="515" height="15" forecolor="#FFFFFF" backcolor="#333333" uuid="da0f1cad-f552-424b-bf19-b41cabbfa4ac"/>
                <text><![CDATA[Currency]]></text>
            </staticText>
        </band>
    </pageHeader>
    <detail>
        <band height="50">
            <textField>
                <reportElement style="Sans_Normal_8" x="0" y="2" width="51" height="10" uuid="ec54687d-3c95-4647-9db5-fa71a6e81009"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[$F{Id}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" pattern="yyyy, MMM dd">
                <reportElement style="Sans_Normal_8" positionType="Float" x="54" y="2" width="87" height="10" uuid="a112ba7b-c321-467c-91ec-ffb513c23338"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$F{OrderDate}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true">
                <reportElement style="Sans_Normal_8" positionType="Float" x="146" y="2" width="108" height="10" uuid="6a61edb3-239e-4791-a046-a6459343ac07"/>
                <textFieldExpression><![CDATA[$F{ShipCity}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" pattern="ยค #,##0.00">
                <reportElement style="Sans_Normal_8" positionType="Float" x="259" y="2" width="92" height="10" uuid="61a8a117-6a43-46a7-9b96-10c5beb578ab"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[$F{Freight}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

I've created a branch for this answer. I hope it helps! :)