Report with subreport in subreport by JSON

[zaleskid1]

I try to connect reports by subreports.

I created 3 separated reports from one *.json file:

{    "dateRange": {        "begin": "2016-09-01",        "end": "2016-09-01"    },    "customerReportDtos": [        {            "customerName": "Customer to find",            "projectReportDtos": [                            ],            "customerWorkedMinutes": 0        },        {            "customerName": "Test customer",            "projectReportDtos": [                {                    "projectName": "Test project2",                    "projectWorkedMinutes": 15,                    "userReportDtos": [                        {                            "userName": "Jan Kowalski",                            "userWorkedMinutes": 7                        },                        {                            "userName": "Jan Nowak",                            "userWorkedMinutes": 8                        },                        {                            "userName": "Damian Zaleski",                            "userWorkedMinutes": 0                        }                    ]                },                {                    "projectName": "Test project3",                    "projectWorkedMinutes": 12,                    "userReportDtos": [                        {                            "userName": "Jan Kowalski",                            "userWorkedMinutes": 5                        },                        {                            "userName": "Jan Nowak",                            "userWorkedMinutes": 6                        },                        {                            "userName": "Damian Zaleski",                            "userWorkedMinutes": 1                        }                    ]                }            ],            "customerWorkedMinutes": 27        }    ],    "sumOfcustomerWorkedMinutes": 27}[/code]

First one was CUSTOMER.jrxml (main report)) with chosen node as `customerReportDtos` My CUSTOMER.jrxml

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 7.2.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="MAIN_REPORT" pageWidth="595" pageHeight="842"              columnWidth="555" leftMargin="20" rightMargin="20"              topMargin="20" bottomMargin="20"              uuid="bfabbdd4-774c-4dda-8fff-1b5f8e6d1a47">    <property name="com.jaspersoft.studio.data.defaultdataadapter"              value="NOWY_JSON"/>    <parameter name="CHILD_LOCATION" class="java.lang.String"/>    <queryString language="JSON">        <![CDATA[customerReportDtos]]>    </queryString>    <field name="customerName" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression"                  value="customerName"/>        <fieldDescription><![CDATA[customerName]]></fieldDescription>    </field>    <field name="projectReportDtos" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression"                  value="projectReportDtos"/>        <fieldDescription><![CDATA[projectReportDtos]]></fieldDescription>    </field>    <field name="customerWorkedMinutes" class="java.lang.Integer">        <property name="net.sf.jasperreports.json.field.expression"                  value="customerWorkedMinutes"/>        <fieldDescription><![CDATA[customerWorkedMinutes]]></fieldDescription>    </field>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band height="79" splitType="Stretch"/>    </title>    <pageHeader>        <band height="35" splitType="Stretch"/>    </pageHeader>    <columnHeader>        <band height="61" splitType="Stretch">            <staticText>                <reportElement x="0" y="0" width="278" height="30"                               uuid="db13493f-5468-4e9a-a52e-83d4c4034770">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="667fa1ca-acfd-4a4b-bdee-fb462d853ce4"/>                </reportElement>                <text><![CDATA[customerName]]></text>            </staticText>            <staticText>                <reportElement x="278" y="0" width="277" height="30"                               uuid="59d59bdf-8ef0-47b9-b0ef-7d918ec2ba6e">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="d3a2710e-e350-4311-ac40-3670997716e9"/>                </reportElement>                <text><![CDATA[customerWorkedMinutes]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="222" splitType="Stretch">            <textField>                <reportElement x="0" y="0" width="278" height="30"                               uuid="14c49156-ccc9-4afa-afa3-8d1de0e36f2d">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="667fa1ca-acfd-4a4b-bdee-fb462d853ce4"/>                </reportElement>                <textFieldExpression><![CDATA[$F{customerName}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="278" y="0" width="277" height="30"                               uuid="e13a6ecf-50ee-4634-830c-ace9c282c0d4">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="d3a2710e-e350-4311-ac40-3670997716e9"/>                </reportElement>                <textFieldExpression><![CDATA[$F{customerWorkedMinutes}]]></textFieldExpression>            </textField>            <subreport>                <reportElement x="0" y="20" width="570" height="59"                               uuid="ac54a12b-8022-49a1-bc90-90cff664e3a1"/>                <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>                <subreportExpression><![CDATA["PROJECT.jrxml"]]></subreportExpression>            </subreport>        </band>    </detail>    <columnFooter>        <band height="45" splitType="Stretch"/>    </columnFooter>    <pageFooter>        <band height="54" splitType="Stretch"/>    </pageFooter>    <summary>        <band height="42" splitType="Stretch"/>    </summary></jasperReport>[/code]

Second one was PROJECT.jrxml (first subreport) with chosen node as  `customerReportDtos.projectReportDtos` My PROJECT.jrxml

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 7.2.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="new" pageWidth="595" pageHeight="842" columnWidth="555"              leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"              uuid="55e0a9d7-656e-4b3e-98b7-da762f908266">    <property name="com.jaspersoft.studio.data.defaultdataadapter"              value="NOWY_JSON"/>    <queryString language="JSON">        <![CDATA[customerReportDtos.projectReportDtos]]>    </queryString>    <field name="projectName" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression"                  value="projectName"/>        <fieldDescription><![CDATA[projectName]]></fieldDescription>    </field>    <field name="projectWorkedMinutes" class="java.lang.Integer">        <property name="net.sf.jasperreports.json.field.expression"                  value="projectWorkedMinutes"/>        <fieldDescription><![CDATA[projectWorkedMinutes]]></fieldDescription>    </field>    <field name="userReportDtos" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression"                  value="userReportDtos"/>        <fieldDescription><![CDATA[userReportDtos]]></fieldDescription>    </field>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band height="79" splitType="Stretch"/>    </title>    <pageHeader>        <band height="56" splitType="Stretch"/>    </pageHeader>    <columnHeader>        <band height="61" splitType="Stretch">            <staticText>                <reportElement x="-20" y="30" width="300" height="30"                               uuid="b9421a66-52f4-4834-a505-84e9bc95a943">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="d7b95de3-c8af-4278-9735-3f0a01bde44f"/>                </reportElement>                <text><![CDATA[projectName]]></text>            </staticText>            <staticText>                <reportElement x="280" y="30" width="295" height="30"                               uuid="90ee9932-81f6-40d8-8e4c-828c82c9ee92">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="9cd2499f-724b-4155-8e73-a33c6728cefa"/>                </reportElement>                <text><![CDATA[projectWorkedMinutes]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="233" splitType="Stretch">            <textField>                <reportElement x="-20" y="0" width="300" height="30"                               uuid="9d0dd2a5-526f-4184-b6f2-54f69c2915d6">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="d7b95de3-c8af-4278-9735-3f0a01bde44f"/>                </reportElement>                <textFieldExpression><![CDATA[$F{projectName}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="280" y="0" width="295" height="30"                               uuid="3f2d7322-1f8f-4d40-ba5c-0290fcc3cfae">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="9cd2499f-724b-4155-8e73-a33c6728cefa"/>                </reportElement>                <textFieldExpression>                    <![CDATA[$F{projectWorkedMinutes}]]>                </textFieldExpression>            </textField>            <subreport>                <reportElement x="-11" y="33" width="561" height="97"                               uuid="4dd99e2f-e47e-4579-8f97-b3ddbd15cbb2"/>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA[in properties>subreport>Expression "USER.jrxml"]]>                </subreportExpression>            </subreport>        </band>    </detail>    <columnFooter>        <band height="45" splitType="Stretch"/>    </columnFooter>    <pageFooter>        <band height="54" splitType="Stretch"/>    </pageFooter>    <summary>        <band height="42" splitType="Stretch"/>    </summary></jasperReport>[/code]

and third one was USER.jrxml (subreport for first subreport) with chosen node as `customerReportDtos.projectReportDtos.userReportDtos` and My USER.jrxml

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 7.2.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="USER" pageWidth="595" pageHeight="842" columnWidth="555"              leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"              uuid="a89f1b2e-859f-4ffc-86a7-c5a15fef4f2b">    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="NOWY_JSON"/>    <queryString language="JSON">        <![CDATA[customerReportDtos.projectReportDtos.userReportDtos]]>    </queryString>    <field name="userName" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression"                  value="userName"/>        <fieldDescription><![CDATA[userName]]></fieldDescription>    </field>    <field name="userWorkedMinutes" class="java.lang.Integer">        <property name="net.sf.jasperreports.json.field.expression"                  value="userWorkedMinutes"/>        <fieldDescription>            <![CDATA[userWorkedMinutes]]>        </fieldDescription>    </field>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band height="79" splitType="Stretch"/>    </title>    <pageHeader>        <band height="51" splitType="Stretch"/>    </pageHeader>    <columnHeader>        <band height="61" splitType="Stretch">            <staticText>                <reportElement x="180" y="30" width="100" height="30"                               uuid="95e1d9f3-8547-424a-9fe5-00481657be90">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="4f1d70b5-0282-43db-9e40-5cf0b4adaf20"/>                </reportElement>                <text><![CDATA[userName]]></text>            </staticText>            <staticText>                <reportElement x="290" y="30" width="100" height="30"                               uuid="f6a5fd03-bf20-4a22-a2e5-36e8ac59c17e">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="0f1b47b1-770b-481f-a8de-49241233f4fe"/>                </reportElement>                <text><![CDATA[userWorkedMinutes]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="30" splitType="Stretch">            <textField>                <reportElement x="185" y="0" width="100" height="30"                               uuid="c9e90893-ac2d-43ee-9d40-04f6f74a2088">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="4f1d70b5-0282-43db-9e40-5cf0b4adaf20"/>                </reportElement>                <textFieldExpression><![CDATA[$F{userName}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="290" y="0" width="100" height="30"                               uuid="f78c1b1d-9627-449f-a957-ca961e48cabf">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID"                              value="0f1b47b1-770b-481f-a8de-49241233f4fe"/>                </reportElement>                <textFieldExpression>                    <![CDATA[$F{userWorkedMinutes}]]>                </textFieldExpression>            </textField>        </band>    </detail>    <columnFooter>        <band height="45" splitType="Stretch"/>    </columnFooter>    <pageFooter>        <band height="54" splitType="Stretch"/>    </pageFooter>    <summary>        <band height="42" splitType="Stretch"/>    </summary></jasperReport>[/code]

Separatly they are work fine on preview without adding subreports, but when I wanted to connect them together by subreport.

I moved to the main report (CUSTOMER.jrxml) 
Created subreport and linked to PROJECT by adding in properties>subreport>Expression "PROJECT.jrxml".
I moved to subreport (PROJECT.jrxml), and linked to USER by adding in properties>subreport>Expression "USER.jrxml".

When I'm trying to check it on preview I get an error called:
Exception, if you want to see more information look into details
Reason: net.sf.jasperreports.engine.JRException: Resource not found at PROJECT.jrxml

(I also tried to change Expression from *.jrxml files to *.jasper files, nothing changed), and I have connection expression as $P{REPORT_CONNECTION}

[narcism]

You get that message because your PROJECT.jrxml does not compile. The one that you linked contains an invalid subreport expression:

<subreportExpression><![CDATA[in properties>subreport>Expression "USER.jrxml"]]></subreportExpression>[/code]

The expression should be just "USER.jrxml". Make sure you recompile each modified subreport or choose Project > Build Automatically to have Studio do it for you after saving each modified file.

Once you get past the above issue:

1. Remove the connectionExpression that passes $P{REPORT_CONNECTION}. It has effect only for JDBC connections, which you don't have.

2. In CUSTOMER.jrxml set this dataSourceExpression:

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

3. In PROJECT.jrxml set this dataSourceExpression:

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

4. Optionally, you may want to delete the unused bands and/or shrink the detail bands in order to reduce the white space between records.

[caryanie03]

Hi there! Why can't I get it working when the language is groovy? and when I choose "java", I always get this error  "The method subDataSource(String) is undefined for the type JRDataSource"