Populating subreport with json datasource

[Tom John]

Hi there,
I hope one of you guys can help, because I am getting slightly mad..
Here's my goal: i need to produce a jrxml with multiple tables or subreports, using a json file as a datasource.
Problem: I can'find a way to properly populate tables/subreports in a reasonable way.
Maybe I am missing some very basic stuff here, but everything used to work fine when using a jdbc connection to an oracle db: i had two tables (each one composed by header, detail and footer) in the summary band of the main report and they used to print properly, like this:

     ...main report summary band:

     Something written in between
 
     Table 1 (header, fields, footer)
 
     Something written in between
 
     Table 2 (header, fields, footer)

My problem now using json is that tables and subreports populate in a totally different way when called in the main report.
For example, I have a subreport with its header, detail  and footer band. When I put the subreport in the detail band of the main report, all the three bands of the subreport get repeated for every record of the fields in its detail band.
If I put the subreport in the summary band of the main report, only the last record of the fields is printed, with header and footer.
Now, I understand that the subreport is repeated for each report passed to it and that if put in the summary band the last one overwrites the previous ones, but why wasn't this happening with the jdbc connection? It is a completely different way to gather data, but I can't figure out how this affects the internal passing of data in jaspersoft.  

 

What is happening in main report:

     ...detail band:

     subreport header
     subreport field record 1
     subreport footer

     subreport header
     subreport field record 2
     subreport footer

     subreport header
     subreport field record 3
     subreport footer

 

What I need to obtain:

     ...detail band (or summary band):

     subreport header
     subreport field record 1
     subreport field record 2
     subreport field record 3
     subreport footer

 

If I have two subreports it gets even crazier:

     ...detail band:

     subreport1 header
     subreport field record 1
     subreport footer

     subreport2 header
     subreport field record 2
     subreport footer

     subreport1 header
     subreport field record 3
    subreport footer

    subreport2 header
    subreport field record 1
    subreport footer

    subreport1 header
    subreport field record 2
    subreport footer

    subreport2 header
    subreport field record 3
    subreport footer

 

While what i need (and it was natural with jdbc connection) is two subsequent subreports:

    ...detail band (or better summary band):

    subreport1 header
    subreport1 field record 1
    subreport1 field record 2
    subreport1 field record 3
    subreport1 footer

    subreport2 header
    subreport2 field record 1
    subreport2 field record 2
    subreport2 field record 3
    subreport2 footer

I could use a table or a subreport, both in the datail or in the summary band, it's the same, but everything I tried didn't work.

Here is my code:

Main report

<?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="progetto" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20"    bottomMargin="20" uuid="e3d45459-802b-4e15-83b4-7f5cc307ace4">    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="sito2" />    <queryString language="json">        <![CDATA[sitojs]]>    </queryString>    <field name="name" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="name" />        <fieldDescription>            <![CDATA[name]]>        </fieldDescription>    </field>    <field name="age" class="java.lang.Integer">        <property name="net.sf.jasperreports.json.field.expression" value="age" />        <fieldDescription>            <![CDATA[age]]>        </fieldDescription>    </field>    <field name="address" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="address" />        <fieldDescription>            <![CDATA[address]]>        </fieldDescription>    </field>    <field name="taking_hostel" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="taking_hostel" />        <fieldDescription>            <![CDATA[taking_hostel]]>        </fieldDescription>    </field>    <background>        <band splitType="Stretch" />    </background>    <title>        <band height="60" splitType="Stretch">            <textField>                <reportElement x="228" y="30" width="100" height="30" uuid="3c3c93be-91f6-485e-92ec-c3e33b06d34f" />                <box>                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                </box>                <textElement textAlignment="Center" verticalAlignment="Middle" />                <textFieldExpression>                    <![CDATA["My_report_title"]]>                </textFieldExpression>            </textField>        </band>    </title>    <detail>        <band height="90" splitType="Stretch">            <textField>                <reportElement positionType="Float" x="40" y="0" width="134" height="30" uuid="c0e0ea5a-bc70-42cf-86f6-c321223e0c18" />                <textFieldExpression>                    <![CDATA["Something written here"]]>                </textFieldExpression>            </textField>            <textField>                <reportElement positionType="Float" x="40" y="60" width="134" height="30" uuid="0262b953-9a76-4209-9da0-9bfdfb4316cd" />                <textFieldExpression>                    <![CDATA["Something else written here"]]>                </textFieldExpression>            </textField>            <subreport>                <reportElement positionType="Float" x="-10" y="30" width="560" height="30" uuid="96f50784-0e81-479b-bc74-aa1210a05d1b" />                <dataSourceExpression>                    <![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("taking_hostel")]]>                </dataSourceExpression>                <subreportExpression>                    <![CDATA["C:/Users/TQuetri/Desktop/yamls/18_11_28_ask/subreport.jasper"]]>                </subreportExpression>            </subreport>        </band>    </detail>    <summary>        <band height="38" splitType="Stretch" />    </summary></jasperReport>[/code]

 

Subreport:

<?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="asggsr_tablecopy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20"    topMargin="20" bottomMargin="20" uuid="163757ea-2d63-46e9-9766-7886b2fe73d1">    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="sito" />    <queryString language="json">        <![CDATA[sitojs.taking_hostel]]>    </queryString>    <field name="type" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="type" />        <fieldDescription>            <![CDATA[type]]>        </fieldDescription>    </field>    <field name="condition" class="java.lang.Integer">        <property name="net.sf.jasperreports.json.field.expression" value="condition" />        <fieldDescription>            <![CDATA[condition]]>        </fieldDescription>    </field>    <sortField name="type" order="Descending" />    <variable name="Variable_1" class="java.lang.Integer" resetType="Group" resetGroup="Group1" calculation="Sum">        <variableExpression>            <![CDATA[$F{condition}]]>        </variableExpression>    </variable>    <group name="type">        <groupExpression>            <![CDATA[$F{type}]]>        </groupExpression>    </group>    <group name="Group1">        <groupExpression>            <![CDATA[$F{type}]]>        </groupExpression>        <groupFooter>            <band height="30">                <textField>                    <reportElement x="180" y="0" width="100" height="30" uuid="fdbb0183-1b5e-4b77-bcd0-68d04ee950e0" />                    <box>                        <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                        <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                        <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                        <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    </box>                    <textElement textAlignment="Center" verticalAlignment="Middle">                        <font isBold="true" />                    </textElement>                    <textFieldExpression>                        <![CDATA[$F{type}]]>                    </textFieldExpression>                </textField>                <textField>                    <reportElement x="280" y="0" width="100" height="30" uuid="22c71825-efaa-483b-945d-7952b793d0d4" />                    <box>                        <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                        <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                        <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                        <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    </box>                    <textElement textAlignment="Center" verticalAlignment="Middle">                        <font isBold="true" />                    </textElement>                    <textFieldExpression>                        <![CDATA[$V{Variable_1}]]>                    </textFieldExpression>                </textField>            </band>        </groupFooter>    </group>    <background>        <band splitType="Stretch" />    </background>    <columnHeader>        <band height="30" splitType="Stretch">            <staticText>                <reportElement x="180" y="0" width="100" height="30" uuid="60bb2295-53f1-4a45-90e4-96a8e5d010c4">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="e6a15ba1-645e-45b9-9fb4-899606919690" />                </reportElement>                <box>                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                </box>                <textElement textAlignment="Center" verticalAlignment="Middle" /> <text>                    <![CDATA[type]]></text>            </staticText>            <staticText>                <reportElement x="280" y="0" width="100" height="30" uuid="5370badd-f572-4a0c-8698-81c31f3b7105">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5c917f99-6d63-4e78-9fc8-c1ace373cf62" />                </reportElement>                <box>                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                </box>                <textElement textAlignment="Center" verticalAlignment="Middle" /> <text>                    <![CDATA[condition]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="30" splitType="Stretch">            <textField>                <reportElement x="280" y="0" width="100" height="30" uuid="a179fb0b-5133-474b-a964-6c913ef642c6">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="e6a15ba1-645e-45b9-9fb4-899606919690" />                </reportElement>                <box>                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                </box>                <textElement textAlignment="Center" verticalAlignment="Middle" />                <textFieldExpression>                    <![CDATA[$F{type} + "buh"]]>                </textFieldExpression>            </textField>            <textField>                <reportElement x="180" y="0" width="100" height="30" uuid="74ddb4f0-7f06-4d34-a2a6-610e04e2e81f">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5c917f99-6d63-4e78-9fc8-c1ace373cf62" />                </reportElement>                <box>                    <topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                    <rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000" />                </box>                <textElement textAlignment="Center" verticalAlignment="Middle" />                <textFieldExpression>                    <![CDATA[$F{condition}]]>                </textFieldExpression>            </textField>        </band>    </detail></jasperReport>[/code]

I'd apprecaite any help, hope to get some suggestion!

Cheers

Here is the json sample:

{
  "sitojs":[{"name" : "CBA", "age" : 23, "address" : "11 street", "taking_hostel" : [{"type":"yes", "condition":"5"}]},
            {"name" : "DEF", "age" : 22, "address" : "1 street", "taking_hostel" :  [{"type":"no", "condition":"7"}]},
            {"name" : "GHI", "age" : 25, "address" : "153 street", "taking_hostel" :  [{"type":"no", "condition":"12"}]},
            {"name" : "JKL", "age" : 23, "address" : "141 street", "taking_hostel" :  [{"type":"yes", "condition":"1"}]},
            {"name" : "MNO", "age" : 25, "address" : "121 street", "taking_hostel" :  [{"type":"no", "condition":"5"}]},
            {"name" : "QPR", "age" : 25, "address" : "21 street", "taking_hostel" :  [{"type":"yes", "condition":"4"}]},
            {"name" : "STU", "age" : 26, "address" : "31 street", "taking_hostel" :  [{"type":"yes", "condition":"7"}]},
            {"name" : "VWX", "age" : 23, "address" : "11 street", "taking_hostel" :  [{"type":"yes", "condition":"13"}]},
            {"name" : "YZA", "age" : 23, "address" : "10 street", "taking_hostel" :  [{"type":"no", "condition":"10"}]}]
}

 [/code]

[narcism]

Posting some sample JSON data might speed things up a little.

[Tom John]

Sure, you're right! Done, added at the bottom. Thank you

[narcism]

Things are not all that different between JSON/JSONQL and JDBC data sources in terms of data source processing.

With subreports, as you might already know, it is about how the main data source relates to the subreport's data source: is it the same, just a subset or is it completely different? And these things matter because when reusing the same data source inside a subreport, it might completly exhaust it and the main report would be left without any more records to process.

In your case the main dataset consists of 9 items from the "sitojs" array, produced by your query:

<queryString language="json">    <![CDATA[sitojs]]></queryString>

 which means that the detail band will be repeated 9 times with your subreport. To fix this, you could:

• leave the main report's query empty
• either leave your subreport inside the detail band or move it to the summary band, and
• set it's dataSourceExpression to:

<dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("sitojs.taking_hostel")]]></dataSourceExpression>

and you will get the expected result:

[Tom John]

That's it! Thanks man, you saved my day :)

Actually I'd prefer to be able to use some data from the query also in the main report, but if it's not feasible, I will adapt my report and use only subreports, hope to get the same results.. I will test it. I thought it was quite straight forward at the beginning, but I still have to adapt to jasper's way of thinking!

Thank you for your time

[narcism]

In this case you can actually use the data from your query in the main report. I had in mind a different scenario when I mentioned that.

[gustavo.lewitan]

Please could you help me?[/code]

I'm trying to use this subreport model and I always get the same error "Error evaluating expression" if I don't use dataSourceExpression, the report looks empty[/code]

My code

            <subreport>

                <reportElement x="280" y="40" width="200" height="200" uuid="eaa2ef4a-d2a9-4d1d-b118-103480b00135"/>

                <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("data.encabezado.cotizaciones")]]></dataSourceExpression>

                <subreportExpression><![CDATA["conrbim_cotizaciones_subr.jasper"]]></subreportExpression>

            </subreport>

 

Thanks