Populating subreport with json datasource

Posted on November 28, 2018 at 4:02am

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>

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>

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"}]}]
}

Jaspersoft® Studio

Tom John

Joined: Nov 28 2018 - 3:22am

Last seen: 4 years 5 months ago

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

narcism - 4 years 6 months ago

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

Tom John - 4 years 6 months ago

add comment

1 Answer:

Posted on November 28, 2018 at 6:24am

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:

narcism

6000

Joined: Nov 22 2010 - 12:39am

Last seen: 3 days 12 hours ago

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

Tom John - 4 years 6 months ago

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.

narcism - 4 years 6 months ago

Please could you help me?

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

My code

Thanks

gustavo.lewitan - 2 years 3 months ago

add comment

Main menu

Populating subreport with json datasource

1 Answer:

Tags

Main menu

You are here

Populating subreport with json datasource

1 Answer:

Tags