Login/Register
Jasper subreport showing only one entry from the JSON data source when embedded in the Title band
Asked Answered
Solved javajsonjasper-reportssubreport
F

1

2

I am having issues with populating data in subreport in the title band of the main report. The data is populated correctly in subreport in the detail band of the main report. I am trying to find what could be wrong in the JSON query. Any help is appreciated.

enter image description here

JSON Sample Data Source (expenses.json)

{
  "expenses": {
    "date": "8 Sep 2016",
    "accounts": [
      {
        "title": "XYZ Corp (111)",
        "accountName": "XYZ Corp",
        "accountNumber": "111",
        "transactions": [
          {
            "date": "21 Jun 2016",
            "name": "gas",
            "price": "17.50"
          },
          {
            "date": "12 May 2016",
            "name": "shopping",
            "price": "111.99"
          },
          {
            "date": "30 Apr 2016",
            "name": "movies",
            "price": "7.50"
          }
        ]
      },
      {
        "title": "ABC Corp (222)",
        "accountName": "ABC Corp",
        "accountNumber": "222",
        "transactions": [
          {
            "date": "2 Jun 2016",
            "name": "coffee",
            "price": "3.25"
          },
          {
            "date": "21 May 2016",
            "name": "tea",
            "price": "1.90"
          },
          {
            "date": "20 Apr 2016",
            "name": "juice",
            "price": "4.50"
          }
        ]
      }
    ]
  }
}

Main report (main.jrxml)

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0  -->
<!-- 2016-09-08T14:41:09 -->
<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="expenses_main" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="8cf0f1cb-ccad-41ba-aa8a-44abd597de54">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="expenses json data"/>
    <queryString language="json">
        <![CDATA[expenses.accounts]]>
    </queryString>
    <field name="title" class="java.lang.String">
        <fieldDescription><![CDATA[title]]></fieldDescription>
    </field>
    <field name="date" class="java.lang.String">
        <fieldDescription><![CDATA[expenses.date]]></fieldDescription>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="71" splitType="Stretch">
            <staticText>
                <reportElement x="2" y="0" width="201" height="16" uuid="2e947a01-8b6b-4169-beaa-6b2dac0d4141"/>
                <text><![CDATA[Expense Report]]></text>
            </staticText>
            <subreport>
                <reportElement x="2" y="44" width="550" height="22" uuid="1d364987-9047-4660-bd58-8a2bcfc8493d"/>
                <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource()]]></dataSourceExpression>
                <subreportExpression><![CDATA["/Users/arul/JaspersoftWorkspaceV2/reports/sub1.jasper"]]></subreportExpression>
            </subreport>
            <textField>
                <reportElement x="442" y="0" width="100" height="16" uuid="1cfbe11b-4f85-4bad-9280-d8712b80a65c"/>
                <textFieldExpression><![CDATA[$F{date}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="410" y="1" width="31" height="15" uuid="ee159651-5d7b-4e0e-a52a-cb71338891cc"/>
                <text><![CDATA[Date: ]]></text>
            </staticText>
        </band>
    </title>
    <detail>
        <band height="54" splitType="Stretch">
            <textField>
                <reportElement x="2" y="10" width="550" height="20" uuid="3b6e1d4b-2a58-4684-b334-396807db1169"/>
                <textFieldExpression><![CDATA["Transactions for " + $F{title}]]></textFieldExpression>
            </textField>
            <subreport>
                <reportElement x="2" y="30" width="545" height="20" uuid="10fde2aa-2b0b-4c1c-90d5-410f2fb9369b"/>
                <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("transactions")]]></dataSourceExpression>
                <subreportExpression><![CDATA["/Users/arul/JaspersoftWorkspaceV2/reports/sub2.jasper"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
</jasperReport>

Subreport1 (sub1.jrxml)

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0  -->
<!-- 2016-09-08T14:43:35 -->
<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="expenses_summary" pageWidth="555" pageHeight="802" columnWidth="555" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="d8ef4ad1-3a01-4394-a78c-17f9d95f98de">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="expenses json data"/>
    <queryString language="JSON">
        <![CDATA[expenses.accounts]]>
    </queryString>
    <field name="accountName" class="java.lang.String">
        <fieldDescription><![CDATA[accountName]]></fieldDescription>
    </field>
    <field name="accountNumber" class="java.lang.String">
        <fieldDescription><![CDATA[accountNumber]]></fieldDescription>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="21">
            <staticText>
                <reportElement x="0" y="0" width="100" height="20" uuid="258b2395-7fd1-4df0-b000-e2bfd2620fab"/>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Account Summary]]></text>
            </staticText>
        </band>
    </title>
    <columnHeader>
        <band height="21" splitType="Stretch">
            <staticText>
                <reportElement mode="Opaque" x="0" y="0" width="100" height="20" backcolor="#DBD7D7" uuid="b04ff630-de8e-4a9b-9ea0-d402d4734ae7"/>
                <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>
                <text><![CDATA[Account Name]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="100" y="0" width="100" height="20" backcolor="#DBD7D7" uuid="6f0d406e-8d99-4364-888c-38bc6c0fa8c6"/>
                <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>
                <text><![CDATA[Account Number]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="21" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="20" uuid="c140795a-02a5-4334-9c5f-dc50e8a8ff6f"/>
                <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>
                <textFieldExpression><![CDATA[$F{accountName}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="100" y="0" width="100" height="20" uuid="ac7a5340-4c86-45d2-a932-b7b0e347a421"/>
                <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>
                <textFieldExpression><![CDATA[$F{accountNumber}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

Subreport2 (sub2.jrxml)

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0  -->
<!-- 2016-09-08T14:43:55 -->
<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="expenses_subreport" pageWidth="555" pageHeight="802" columnWidth="555" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="2e39bfaf-e964-4b5d-847e-6517de5718d4">
    <field name="date" class="java.lang.String"/>
    <field name="name" class="java.lang.String"/>
    <field name="price" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <columnHeader>
        <band height="20" splitType="Stretch">
            <staticText>
                <reportElement mode="Opaque" x="0" y="0" width="100" height="20" backcolor="#DBD7D7" uuid="8e073b78-91de-4819-886a-2527d5634067"/>
                <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>
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Date]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="100" y="0" width="100" height="20" backcolor="#DBD7D7" uuid="e7e7d858-05eb-4fda-9ae6-d998b4a51fd8"/>
                <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>
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Name]]></text>
            </staticText>
            <staticText>
                <reportElement mode="Opaque" x="200" y="0" width="100" height="20" backcolor="#DBD7D7" uuid="fef220d6-4f85-4fd7-8017-5b64aff64666"/>
                <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>
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Price]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="20" uuid="fd021427-d26c-4c9c-8c71-c9f136f11a66"/>
                <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>
                <textFieldExpression><![CDATA[$F{date}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="100" y="0" width="100" height="20" uuid="b6cd9815-ba45-49dd-be46-60a6a835d3fd"/>
                <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>
                <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="200" y="0" width="100" height="20" uuid="cf3e397e-5546-4490-a88c-7211f124475e"/>
                <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>
                <textFieldExpression><![CDATA[$F{price}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

PDF is generating using Jasper Reports 6.3.0 Java API.

import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.query.JsonQueryExecuterFactory;
import net.sf.jasperreports.engine.util.JRLoader;

import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Map;

public class JasperTest {
  public static void main(String[] args) throws Exception {
    JasperReport jasperReport = (JasperReport) JRLoader.loadObjectFromFile("/Users/arul/JaspersoftWorkspaceV2/reports/main.jasper");
    Map<String, Object> jasperParameters = new HashMap<String, Object>();
    jasperParameters.put(JsonQueryExecuterFactory.JSON_INPUT_STREAM, new FileInputStream("/Users/arul/JaspersoftWorkspaceV2/data/expenses.json"));
    JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, jasperParameters);
    JasperExportManager.exportReportToPdfFile(jasperPrint, "report.pdf");
  }
}
Fend answered 8/9, 2016 at 19:59 Comment(0)
B
1

Your are going to deep in your initial json query

<queryString language="json">
        <![CDATA[expenses.accounts]]>
</queryString>

Date is out of scope.

The solution is to stay on expenses scope

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

The subreport 1 (sub1.jasper) you will now call with subDataSource("accounts") expression

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

However you will now have only 1 detail band record (expenses), to solve this issue you can introduce another subreport.

Instead of directly calling sub report 2 introduce a subreport 1_2 (sub1_2.jasper) to iterate again on accounts subDataSource("accounts"), this will display the title and include your subreport 2 (sub2.jasper) with the datasource on transaction .subDataSource("transactions")

Main report (main.jrxml)

<?xml version="1.0" encoding="UTF-8"?>
<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="expenses_main" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="8cf0f1cb-ccad-41ba-aa8a-44abd597de54">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Json"/>
    <queryString language="json">
        <![CDATA[expenses]]>
    </queryString>
    <field name="date" class="java.lang.String">
        <fieldDescription><![CDATA[date]]></fieldDescription>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="71" splitType="Stretch">
            <staticText>
                <reportElement x="2" y="0" width="201" height="16" uuid="2e947a01-8b6b-4169-beaa-6b2dac0d4141"/>
                <text><![CDATA[Expense Report]]></text>
            </staticText>
            <subreport>
                <reportElement x="2" y="44" width="550" height="22" uuid="1d364987-9047-4660-bd58-8a2bcfc8493d"/>
                <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("accounts")]]></dataSourceExpression>
                <subreportExpression><![CDATA["sub1.jasper"]]></subreportExpression>
            </subreport>
            <textField>
                <reportElement x="442" y="0" width="100" height="16" uuid="1cfbe11b-4f85-4bad-9280-d8712b80a65c"/>
                <textFieldExpression><![CDATA[$F{date}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="410" y="1" width="31" height="15" uuid="ee159651-5d7b-4e0e-a52a-cb71338891cc"/>
                <text><![CDATA[Date: ]]></text>
            </staticText>
        </band>
    </title>
    <detail>
        <band height="36" splitType="Stretch">
            <subreport isUsingCache="false">
                <reportElement x="2" y="10" width="545" height="20" uuid="10fde2aa-2b0b-4c1c-90d5-410f2fb9369b"/>
                <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("accounts")]]></dataSourceExpression>
                <subreportExpression><![CDATA["sub1_2.jasper"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
</jasperReport>

Sub report 1_2 (sub1_2.jasper)

<?xml version="1.0" encoding="UTF-8"?>
<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="expenses_subreport" pageWidth="555" pageHeight="802" columnWidth="555" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="2e39bfaf-e964-4b5d-847e-6517de5718d4">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Json"/>
    <field name="title" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <detail>
        <band height="40" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <textField>
                <reportElement x="2" y="1" width="550" height="20" uuid="934d546e-aeeb-4760-b100-cbefed73c420"/>
                <textFieldExpression><![CDATA["Transactions for " + $F{title}]]></textFieldExpression>
            </textField>
            <subreport isUsingCache="false">
                <reportElement x="5" y="16" width="545" height="20" uuid="1b8b0487-9dfa-4b82-bfb1-13009cb74ece"/>
                <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("transactions")]]></dataSourceExpression>
                <subreportExpression><![CDATA["sub2.jasper"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
</jasperReport>

Sub report 1 and 2 (sub1.jrxml and sub2.jrxml)

No changes

Result

output

Bryon answered 8/9, 2016 at 23:18 Comment(2)
Thank you Peter, you nailed it. – Fend
@ArulDhesiaseelan as a note I would have used correct class in jasper report example price as BigDecimal or Double (you need to see formatter to json), but this way you can apply pattern and get good result when exporting to different formats – Bryon

© 2022 - 2024 — McMap. All rights reserved.