Report with subreport in subreport by JSON

0

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
}

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>

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>


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>

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}

zaleskid1's picture
Joined: Oct 10 2018 - 4:11am
Last seen: 8 months 3 weeks ago

1 Answer:

1

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>

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>

3. In PROJECT.jrxml set this dataSourceExpression:

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

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.

narcism's picture
1064
Joined: Nov 22 2010 - 12:39am
Last seen: 1 day 5 hours ago
Feedback
randomness