How to map subreport records to main records in a nested JSON?

[localhost]

Hello everybody,

I have a nested JSON structure like this:

{"tasks": [{        "settlementDate": "2018-06-05T10:00:00.000Z",        "type": "RESET_ACCOUNT_STATES",        "systemState": "ACTIVE",        "state": "SUCCESS",        "invocations": 1,        "logs": [{            "creationDate": "2018-06-05T12:55:36.973Z",            "subType": "SYSTEM_STATE_TASK_SUCCESS",            "userName": "SYSTEM",            "message": "Der Task wurde erfolgreich beendet."        }, {            "creationDate": "2018-06-05T12:55:36.935Z",            "subType": "SYSTEM_STATE_TASK_RUN",            "userName": "SYSTEM",            "message": "Der Task wurde gestartet."        }]}[/code]

Meaning that every task has some logs. My problem is that I can't make the logs appear unter the task they belong to, instead i get a list of tasks and a seperate list of logs.

For the task attributes (settlementDate etc.) I placed fields on the main report and for the logs I used a subreport. This is what I get:

As you can see there are 2 seperate lists. For the upper one (main report) I used a table and for the lower one (subreport) I only used fields.

How can I map these records together? The jrxml codes for both the main report and the subreport are provided at the end on this post.

Thanks in avdance.

 

Main Report:

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  --><!-- 2018-06-08T16:14:11 --><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="SystemStateReport"              pageWidth="595"              pageHeight="842"              columnWidth="555"              leftMargin="20"              rightMargin="20"              topMargin="20"              bottomMargin="20"              uuid="11b34e40-6d8b-4baf-8f4a-81a48fb4c5b8">    <property name="com.jaspersoft.studio.data.defaultdataadapter"              value="SystemStateAdapter"/>    <style name="Table_TH" 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="Table_CH" 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="Table 1_TH" 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="Table 1_CH" 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="Table 1_TD" 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="json" uuid="bb911da5-ae0e-481e-9c87-e05ac954114c">        <property name="com.jaspersoft.studio.data.defaultdataadapter"                  value="New Data Adapter (2)"/>        <queryString>            <![CDATA[]]>        </queryString>        <field name="invocations" class="java.lang.String">            <fieldDescription>                <![CDATA[invocations]]>            </fieldDescription>        </field>        <field name="state" class="java.lang.String">            <fieldDescription>                <![CDATA[state]]>            </fieldDescription>        </field>        <field name="systemState" class="java.lang.String">            <fieldDescription>                <![CDATA[systemState]]>            </fieldDescription>        </field>        <field name="type" class="java.lang.String">            <fieldDescription><![CDATA[type]]></fieldDescription>        </field>    </subDataset>    <subDataset name="Dataset1"                uuid="493bd3cf-3f61-4e67-b69d-e851932015ae">        <property name="com.jaspersoft.studio.data.defaultdataadapter"                  value="One Empty Record"/>        <parameter name="JSON_INPUT_STREAM"                   class="java.io.InputStream"                   isForPrompting="false"/>        <parameter name="net.sf.jasperreports.json.source"                   class="java.lang.String"                   isForPrompting="false"/>        <parameter name="net.sf.jasperreports.json.sources"                   class="java.util.List"                   isForPrompting="false"/>        <parameter name="net.sf.jasperreports.json.date.pattern"                   class="java.lang.String"                   isForPrompting="false"/>        <parameter name="net.sf.jasperreports.json.number.pattern"                   class="java.lang.String"                   isForPrompting="false"/>        <parameter name="JSON_LOCALE"                   class="java.util.Locale"                   isForPrompting="false"/>        <parameter name="net.sf.jasperreports.json.locale.code"                   class="java.lang.String"                   isForPrompting="false"/>        <parameter name="JSON_TIME_ZONE"                   class="java.util.TimeZone"                   isForPrompting="false"/>        <parameter name="net.sf.jasperreports.json.timezone.id"                   class="java.lang.String"                   isForPrompting="false"/>        <queryString language="JSON">            <![CDATA[]]>        </queryString>    </subDataset>    <parameter name="subReport" class="java.lang.Object">        <parameterDescription>            <![CDATA[Table as Subreport]]>        </parameterDescription>        <defaultValueExpression>            <![CDATA["logs.jasper"]]>        </defaultValueExpression>    </parameter>    <parameter name="JSON_INPUT_STREAM"               class="java.io.InputStream"               isForPrompting="false"/>    <parameter name="net.sf.jasperreports.json.source"               class="java.lang.String"               isForPrompting="false"/>    <parameter name="net.sf.jasperreports.json.sources"               class="java.util.List"               isForPrompting="false"/>    <parameter name="net.sf.jasperreports.json.date.pattern"               class="java.lang.String"               isForPrompting="false"/>    <parameter name="net.sf.jasperreports.json.number.pattern"               class="java.lang.String"               isForPrompting="false"/>    <parameter name="JSON_LOCALE"               class="java.util.Locale"               isForPrompting="false"/>    <parameter name="net.sf.jasperreports.json.locale.code"               class="java.lang.String"               isForPrompting="false"/>    <parameter name="JSON_TIME_ZONE"               class="java.util.TimeZone"               isForPrompting="false"/>    <parameter name="net.sf.jasperreports.json.timezone.id"               class="java.lang.String"               isForPrompting="false"/>    <queryString language="json">        <![CDATA[tasks]]>    </queryString>    <field name="state" class="java.lang.String">        <fieldDescription><![CDATA[tasks.state]]></fieldDescription>    </field>    <field name="systemState" class="java.lang.String">        <fieldDescription><![CDATA[tasks.systemState]]></fieldDescription>    </field>    <field name="invocations" class="java.lang.String">        <fieldDescription><![CDATA[tasks.invocations]]></fieldDescription>    </field>    <field name="type" class="java.lang.String">        <fieldDescription><![CDATA[tasks.type]]></fieldDescription>    </field>    <field name="time" class="java.lang.String">        <fieldDescription><![CDATA[logs.time]]></fieldDescription>    </field>    <field name="userName" class="java.lang.String">        <fieldDescription><![CDATA[logs.userName]]></fieldDescription>    </field>    <field name="description" class="java.lang.String">        <fieldDescription><![CDATA[logs.description]]></fieldDescription>    </field>    <group name="Group1">        <groupExpression><![CDATA[$F{type}]]></groupExpression>    </group>    <group name="Group2">        <groupExpression><![CDATA[$F{state}]]></groupExpression>    </group>    <background>        <band splitType="Stretch"/>    </background>    <pageHeader>        <band height="51">            <line>                <reportElement x="0" y="38" width="556" height="1"                               uuid="52da86d7-4dc7-4c66-aa8e-19cfd017525c">                    <property name="com.jaspersoft.studio.unit.x"                              value="pixel"/>                    <property name="com.jaspersoft.studio.unit.width"                              value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height"                              value="pixel"/>                </reportElement>            </line>            <textField>                <reportElement x="0" y="8" width="50" height="12"                               uuid="6967866f-e76f-446a-8086-1e0d1537cbbf">                    <property name="com.jaspersoft.studio.unit.height"                              value="pixel"/>                    <property name="com.jaspersoft.studio.unit.x"                              value="pixel"/>                </reportElement>                <textElement>                    <font fontName="Arial" size="10"/>                </textElement>                <textFieldExpression>                    <![CDATA["O e N B"]]>                </textFieldExpression>            </textField>            <textField>                <reportElement x="480" y="8" width="75" height="12"                               uuid="73ece9c7-8874-4a30-a541-75054a24d2c6">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                </reportElement>                <textElement>                    <font fontName="Arial" size="10"/>                </textElement>                <textFieldExpression><![CDATA["ASTI"]]></textFieldExpression>            </textField>            <textField>                <reportElement x="0" y="20" width="50" height="12"                               uuid="f35fc389-64e9-4ebd-b2ae-925574fe9c76">                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.x" value="pixel"/>                </reportElement>                <textElement>                    <font fontName="Arial" size="10"/>                </textElement>                <textFieldExpression><![CDATA["ZV"]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="50" y="8" width="430" height="24"                               uuid="9a4a10b8-6cc8-49ca-9c0d-290f92c38575"/>                <textElement textAlignment="Center" verticalAlignment="Middle">                    <font isBold="true"/>                </textElement>                <text><![CDATA[systemstatus-Report]]></text>            </staticText>            <textField>                <reportElement x="480" y="20" width="31" height="12"                               uuid="50876af8-1e45-400a-be05-dd88693837b3"/>                <textElement textAlignment="Left">                    <font fontName="Arial"/>                </textElement>                <textFieldExpression><![CDATA["Seite"]]></textFieldExpression>            </textField>            <textField evaluationTime="Report">                <reportElement x="540" y="20" width="15" height="12"                               uuid="b08f8ce9-7865-4607-a271-6e3400c493fe"/>                <textElement textAlignment="Right">                    <font fontName="Arial"/>                </textElement>                <textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>            </textField>            <textField evaluationTime="Report">                <reportElement x="519" y="20" width="21" height="12"                               uuid="3692b430-313d-49d3-8302-64133aa43dac"/>                <textElement textAlignment="Right">                    <font fontName="Arial"/>                </textElement>                <textFieldExpression><![CDATA["von"]]></textFieldExpression>            </textField>            <textField>                <reportElement x="510" y="20" width="15" height="12"                               uuid="f2ff5d94-7ace-4f8e-a1d5-ea73a49047b0"/>                <textElement textAlignment="Left">                    <font fontName="Arial"/>                </textElement>                <textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>            </textField>        </band>    </pageHeader>    <detail>        <band height="85" splitType="Stretch">            <componentElement>                <reportElement x="40" y="12" width="448" height="60" uuid="927fcab5-1cc7-4551-ac6d-2b8376ebeba7">                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>                </reportElement>                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components"                          xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">                    <datasetRun subDataset="json" uuid="f44d6c3d-db9b-460d-9c65-f13d1d203577">                        <dataSourceExpression>                            <![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("tasks")]]>                        </dataSourceExpression>                    </datasetRun>                    <jr:column width="90" uuid="b3791250-9c12-4089-8ecc-6949f7f0f22b">                        <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column1"/>                        <jr:columnHeader style="Table 1_CH" height="30" rowSpan="1"/>                        <jr:detailCell style="Table 1_TD" height="30">                            <textField>                                <reportElement x="0" y="0" width="90" height="30" uuid="24c1ba25-e900-4710-9dc2-c52025e82f44"/>                                <textFieldExpression><![CDATA[$F{state}]]></textFieldExpression>                            </textField>                        </jr:detailCell>                    </jr:column>                    <jr:column width="110" uuid="3bbee991-1c50-475c-8973-85aec7578dc5">                        <jr:columnHeader style="Table 1_CH" height="30" rowSpan="1"/>                        <jr:detailCell style="Table 1_TD" height="30">                            <textField>                                <reportElement x="0" y="0" width="110" height="30" uuid="819c3601-d903-425c-9d1a-c4ad7f4f5f9a"/>                                <textFieldExpression><![CDATA[$F{systemState}]]></textFieldExpression>                            </textField>                        </jr:detailCell>                    </jr:column>                    <jr:column width="70" uuid="96d7fad2-e967-48ab-805c-6c7545931b0b">                        <jr:columnHeader style="Table 1_CH" height="30" rowSpan="1"/>                        <jr:detailCell style="Table 1_TD" height="30">                            <textField>                                <reportElement x="0" y="0" width="70" height="30" uuid="1f0c7131-a160-474a-a108-79993389fdf5"/>                                <textFieldExpression><![CDATA[$F{invocations}]]></textFieldExpression>                            </textField>                        </jr:detailCell>                    </jr:column>                    <jr:column width="220" uuid="0e56f26b-3c72-4d38-8e54-fc0c27b3d4df">                        <jr:columnHeader style="Table 1_CH" height="30" rowSpan="1"/>                        <jr:detailCell style="Table 1_TD" height="30">                            <textField>                                <reportElement x="0" y="0" width="220" height="30" uuid="1d8c0323-cd4c-415d-b9a9-594c5ead3d84"/>                                <textFieldExpression><![CDATA[$F{type}]]></textFieldExpression>                            </textField>                        </jr:detailCell>                    </jr:column>                </jr:table>            </componentElement>        </band>        <band height="50">            <rectangle>                <reportElement positionType="Float" mode="Opaque" x="0" y="0" width="555" height="28" printWhenGroupChanges="Group1" forecolor="#FFFFFF" backcolor="#EAEAEA" uuid="00a13868-6267-48ff-a0c5-128c5f6f8191"/>            </rectangle>            <subreport>                <reportElement x="-10" y="10" width="565" height="18" uuid="a6d001c1-4b57-4db7-9263-31f558156b61"/>                <subreportParameter name="group">                    <subreportParameterExpression><![CDATA[$F{type}]]></subreportParameterExpression>                </subreportParameter>                <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("tasks.logs")]]></dataSourceExpression>                <subreportExpression><![CDATA[$P{subReport}]]></subreportExpression>            </subreport>            <staticText>                <reportElement x="20" y="7" width="110" height="18" uuid="915b2885-6ea9-46ad-af28-c33a27bf14ab"/>                <textElement>                    <font isBold="true"/>                </textElement>                <text><![CDATA[Zeitpunkt]]></text>            </staticText>            <staticText>                <reportElement x="176" y="7" width="71" height="18" uuid="493b2cd2-54e5-4aae-b1a2-f6916a05901f"/>                <textElement>                    <font isBold="true"/>                </textElement>                <text><![CDATA[Quelle]]></text>            </staticText>            <staticText>                <reportElement x="265" y="7" width="230" height="18" uuid="ca2f1463-869a-45a3-9507-ab4bf40fe483"/>                <textElement>                    <font isBold="true"/>                </textElement>                <text><![CDATA[beschreibung]]></text>            </staticText>        </band>    </detail>    <pageFooter>        <band height="48">            <staticText>                <reportElement x="0" y="11" width="205" height="15" uuid="456d406f-b320-460b-a437-85dc102f0643">                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.width" value="pixel"/>                </reportElement>                <textElement textAlignment="Left">                    <font fontName="Arial" size="10"/>                </textElement>                <text><![CDATA[sTRENG VERTRAULICH]]></text>            </staticText>            <staticText>                <reportElement x="375" y="11" width="105" height="15" uuid="509f56a1-a52f-410f-88d3-752604635035">                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.width" value="pixel"/>                </reportElement>                <textElement textAlignment="Left">                    <font fontName="Arial" size="10"/>                </textElement>                <text><![CDATA[Erstellungszeitpunkt:]]></text>            </staticText>            <textField pattern="dd.MM.yyyy HH:mm">                <reportElement x="470" y="11" width="85" height="15" uuid="cf586a93-19b7-41d7-8641-0e40801d70af">                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                </reportElement>                <textElement textAlignment="Right">                    <font fontName="Arial" size="10"/>                </textElement>                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>            </textField>        </band>    </pageFooter></jasperReport>[/code]

Subreport:

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  --><!-- 2018-06-12T12:29: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="subReport" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="57acf617-9df0-4d4e-b749-a357de0f1766">    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>    <queryString language="json">        <![CDATA[tasks.logs]]>    </queryString>    <field name="taskName" class="java.lang.String">        <fieldDescription><![CDATA[logs.taskName]]></fieldDescription>    </field>    <field name="creationDate" class="java.lang.String">        <fieldDescription><![CDATA[creationDate]]></fieldDescription>    </field>    <field name="userName" class="java.lang.String">        <fieldDescription><![CDATA[userName]]></fieldDescription>    </field>    <field name="message" class="java.lang.String">        <fieldDescription><![CDATA[message]]></fieldDescription>    </field>    <background>        <band splitType="Stretch"/>    </background>    <detail>        <band height="35" splitType="Stretch">            <rectangle>                <reportElement positionType="FixRelativeToBottom" x="-10" y="2" width="556" height="31" forecolor="#FFFFFF" backcolor="#F8F8F8" uuid="644a9cf2-7a3f-4afd-9a65-bcb654fafd80"/>            </rectangle>            <textField>                <reportElement x="0" y="10" width="140" height="20" uuid="0a2fd55e-3350-4ec7-9d11-b39b8188fdd6"/>                <textFieldExpression><![CDATA[$F{creationDate}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="165" y="9" width="70" height="20" uuid="dfa19ec5-5819-4b22-8bcc-dd17570c7361"/>                <textFieldExpression><![CDATA[$F{userName}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="256" y="10" width="230" height="20" uuid="01afd00c-94af-47f7-a42d-a285c7af2ac8"/>                <textFieldExpression><![CDATA[$F{message}]]></textFieldExpression>            </textField>        </band>    </detail></jasperReport>[/code]

 

[darth_fader]

You can always pass that whole json to a java layer, and create lists of java objects that correspond to your reports hierarchy (parent object list for parent report, child object list for child report) - then use java objects as your datasource accordingly. 

Will require a little json parsing, and a little coding, but neither would be that complicated. Then you can get the output set the way you want it.

[narcism]

You should not use a table for the task attributes. Instead, in your main report, you could:

1. Remove the table and both subDatasets
2. Use "tasks" as query string instead of empty
3. Set the task attributes as fields
4. In detail band create textFields for the above fields
5. Keep the subreport in detail band but with .subDataSource("logs") instead of .subDataSource("tasks.logs")

This way you iterate directly over tasks in your main report, and wherever you have logs, the subreport will display them.

There is even a more compact solution for this based on JSONQL, but it involves upgrading JaspersoftStudio to at least 6.4.0