DEVHIDE
Login Or Sign up

Unable to see Jasper Report Sub Report table data populated with JSON

615 Views Asked by At

I've created a Jasper Report that only consists of a summary band with a table element. The data source is JSON and I am able to preview this report perfectly fine. (subJSONTest.jrxml) When I now try and use this report as a sub report in another jrxml file (JSONTest.jrxml), I cannot see any data at all when I attempt to preview the main report. Any help or insights would be greatly appreciated.

I expected the same table data to be displayed from main ==> sub report, just as I did when previewing the sub report on it's own.

subJSONTest.jrxml

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 8.1.0.final using JasperReports Library version 6.20.0-2bc7ab61c56f459e8176eb05c7705e145cd400ad  -->
<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="JSONTest" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="eeca104d-f460-4de0-8229-80d057e025a0">
    <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_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="Dataset1" uuid="f2dcbb4a-78c8-45b9-8086-0ab0924618fd">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="jsonTEST"/>
        <queryString language="jsonql">
            <![CDATA[report.samples_by_result_cfu]]>
        </queryString>
        <field name="suite" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="suite"/>
            <fieldDescription><![CDATA[suite]]></fieldDescription>
        </field>
        <field name="qtr" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="qtr"/>
            <fieldDescription><![CDATA[qtr]]></fieldDescription>
        </field>
        <field name="mth" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="mth"/>
            <fieldDescription><![CDATA[mth]]></fieldDescription>
        </field>
        <field name="product" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="product"/>
            <fieldDescription><![CDATA[product]]></fieldDescription>
        </field>
        <field name="analysis" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="analysis"/>
            <fieldDescription><![CDATA[analysis]]></fieldDescription>
        </field>
        <field name="component" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="component"/>
            <fieldDescription><![CDATA[component]]></fieldDescription>
        </field>
        <field name="sample_number" class="java.lang.Integer">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="sample_number"/>
            <fieldDescription><![CDATA[sample_number]]></fieldDescription>
        </field>
        <group name="suite">
            <groupExpression><![CDATA[$F{suite}]]></groupExpression>
        </group>
    </subDataset>
    <queryString language="jsonql">
        <![CDATA[report]]>
    </queryString>
    <background>
        <band splitType="Stretch"/>
    </background>
    <summary>
        <band height="213">
            <componentElement>
                <reportElement x="-10" y="6" width="550" height="200" uuid="eceddb4d-fca7-475f-b050-c22c25f5fcbf">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
                    <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
                    <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
                    <property name="com.jaspersoft.studio.components.autoresize.next" value="true"/>
                </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="Dataset1" uuid="ad76e183-2756-412b-a5c8-1d392d454155">
                        <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("samples_by_result_cfu")]]></dataSourceExpression>
                    </datasetRun>
                    <jr:column width="80" uuid="3a9a2cfb-62f3-44cf-9901-89d492f6296d">
                        <jr:tableHeader style="Table_TH" height="30"/>
                        <jr:tableFooter style="Table_TH" height="30"/>
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="80" height="30" uuid="245b781a-20a8-490b-b806-a6aa479f5844"/>
                                <textElement>
                                    <font fontName="Arial" size="12"/>
                                </textElement>
                                <text><![CDATA[Suite]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:columnFooter style="Table_CH" height="30"/>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="80" height="30" uuid="cd4a9ec3-1c43-4517-a1cb-e110acddc058"/>
                                <textFieldExpression><![CDATA[$F{suite}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="66" uuid="2ebad35a-8d61-482e-b0da-fdd29e41f3e6">
                        <jr:tableHeader style="Table_TH" height="30"/>
                        <jr:tableFooter style="Table_TH" height="30"/>
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30" uuid="2a96df8d-a7f4-42de-85b3-cd67732769e1"/>
                                <textElement>
                                    <font size="12"/>
                                </textElement>
                                <text><![CDATA[Quarter]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:columnFooter style="Table_CH" height="30"/>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30" uuid="4eb3e9b7-68c4-4349-bcbb-a72b6799e4fe"/>
                                <textFieldExpression><![CDATA[$F{qtr}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="66" uuid="019748dc-e41f-490d-8fdd-84f5dfd35f11">
                        <jr:tableHeader style="Table_TH" height="30"/>
                        <jr:tableFooter style="Table_TH" height="30"/>
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30" uuid="8328412e-24eb-48b3-9513-1a7309143aea"/>
                                <textElement>
                                    <font size="12"/>
                                </textElement>
                                <text><![CDATA[Product]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:columnFooter style="Table_CH" height="30"/>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30" uuid="e74b5e30-03d5-4974-acd0-3521b5b4be6e"/>
                                <textFieldExpression><![CDATA[$F{product}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="98" uuid="665a0eb1-2455-4be8-a9f6-8ba49603f7e8">
                        <jr:tableHeader style="Table_TH" height="30"/>
                        <jr:tableFooter style="Table_TH" height="30"/>
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="98" height="30" uuid="cfbea91e-839a-4a03-bdd6-4d9f535dfb95"/>
                                <textElement>
                                    <font size="12"/>
                                </textElement>
                                <text><![CDATA[Analysis]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:columnFooter style="Table_CH" height="30"/>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="98" height="30" uuid="8d1f34e1-3259-4574-8398-cef0ae985c47"/>
                                <textFieldExpression><![CDATA[$F{analysis}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="90" uuid="a91fc2ca-2fc2-4547-9680-785e6ff9b541">
                        <jr:tableHeader style="Table_TH" height="30"/>
                        <jr:tableFooter style="Table_TH" height="30"/>
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="90" height="30" uuid="36f35980-4b3b-4656-97b3-907a579fee47"/>
                                <textElement>
                                    <font size="12"/>
                                </textElement>
                                <text><![CDATA[Component]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:columnFooter style="Table_CH" height="30"/>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="90" height="30" uuid="234d2df6-0f99-48b5-84e3-f80225928ad7"/>
                                <textFieldExpression><![CDATA[$F{component}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="80" uuid="d661ab9f-9da5-40ea-b49f-67044cdb5b47">
                        <jr:tableHeader style="Table_TH" height="30"/>
                        <jr:tableFooter style="Table_TH" height="30"/>
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="80" height="30" uuid="b09d22ab-09ed-46b7-be85-a36ea6cd1fcb"/>
                                <textElement>
                                    <font size="12"/>
                                </textElement>
                                <text><![CDATA[Sample Number]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:columnFooter style="Table_CH" height="30"/>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="80" height="30" uuid="5ab7ca26-4cac-449b-983e-c257c8335229"/>
                                <textFieldExpression><![CDATA[$F{sample_number}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </summary>
</jasperReport>

JSONTest.jrxml

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 8.1.0.final using JasperReports Library version 6.20.0-2bc7ab61c56f459e8176eb05c7705e145cd400ad  -->
<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="JSONTest" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="eeca104d-f460-4de0-8229-80d057e025a0">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="jsonTEST"/>
    <queryString language="jsonql">
        <![CDATA[report]]>
    </queryString>
    <background>
        <band splitType="Stretch"/>
    </background>
    <detail>
        <band height="248" splitType="Stretch">
            <subreport>
                <reportElement x="-10" y="10" width="570" height="200" uuid="08b3b95b-e7c1-477d-8579-de67c5348404"/>
                <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("samples_by_result_cfu")]]></dataSourceExpression>
                <subreportExpression><![CDATA["subJSONTest.jasper"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
</jasperReport>

JSON Test Data

{
  "report": {
    "filter_parameters": {
      "suite": [
        "CELPHX-AA",
        "CELPHX-BB"
      ],
      "product": [
        "ROOM"
      ],
      "show_er": true,
      "end_date": "2022-02-02",
      "show_crr": true,
      "show_oos": false,
      "show_oot": false,
      "component": [],
      "test_name": [],
      "instrument": [],
      "start_date": "2022-01-31",
      "product_lot": [],
      "quarter_end": "2",
      "report_type": "Trending",
      "process_unit": [],
      "quarter_start": "1",
      "sample_status": [],
      "show_raw_data": true,
      "media_standard": [],
      "sampling_point": [],
      "date_range_type": "quarter",
      "quarter_end_year": 2022,
      "quarter_start_year": 2022
    },
    "samples_by_result_cfu": [
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Action >=1; TNTC (OOS)",
        "sample_number": 792328,
        "status": "A",
        "result": 0,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Action >=1; TNTC (OOS)",
        "sample_number": 792329,
        "status": "A",
        "result": 0,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Action >=1; TNTC (OOS)",
        "sample_number": 792330,
        "status": "A",
        "result": 0,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Action >=1; TNTC (OOS)",
        "sample_number": 792418,
        "status": "A",
        "result": 0,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Action >=1; TNTC (OOS)",
        "sample_number": 792431,
        "status": "A",
        "result": 0,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Action >=1; TNTC (OOS)",
        "sample_number": 792432,
        "status": "A",
        "result": 5,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "F",
        "number_of_samples": 10,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Action >=1; TNTC (OOS)",
        "sample_number": 792433,
        "status": "A",
        "result": 0,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Alert >=3; Action >=5; TNTC (OOS)",
        "sample_number": 792201,
        "status": "A",
        "result": 0,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Alert >=3; Action >=5; TNTC (OOS)",
        "sample_number": 792210,
        "status": "A",
        "result": 0,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Alert >=3; Action >=5; TNTC (OOS)",
        "sample_number": 792211,
        "status": "A",
        "result": 0,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      },
      {
        "suite": "CELPHX-AA",
        "qtr": "2 2022",
        "mth": "APR 2022",
        "sampled_date": "2022-04-12",
        "product": "ROOM",
        "analysis": "MICROBIAL_CT_EM_PHX",
        "component": "Total Count - Bacteria/Yeast/Mold",
        "product_spec": "Alert >=3; Action >=5; TNTC (OOS)",
        "sample_number": 792212,
        "status": "A",
        "result": 1,
        "units": "CFU",
        "in_control": "T",
        "in_spec": "T",
        "number_of_samples": 0,
        "number_of_oot": 0,
        "number_of_oos": 0,
        "number_of_tntc": 0
      }
    ]
  }
}

JAVA CODE

ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
parameters.put(JsonQueryExecuterFactory.JSON_INPUT_STREAM, bais);
print = JasperFillManager.fillReport(report, parameters);
json jasper-reports subreport
Original Q&A
1

There are 1 best solutions below

0
Narcis On

When you preview the subreport on its own, it works because you are constructing your main dataset from the root of the JSON tree.

But in your main report you send only the samples_by_result_cfu subtree to your subreport, so the mapping of JSON keys does not match there(in the subreport) anymore.

There are many ways to fix this, but the most simple ones are:

  1. Don't subDataSource() by key in the main report, like this:

    <subreport>
    <reportElement x="-10" y="10" width="570" height="200" uuid="08b3b95b-e7c1-477d-8579-de67c5348404"/>
    <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource()]]></dataSourceExpression>
    ...
</subreport>

Or

  1. Prepare your subreport to work directly from the samples_by_result_cfu JSON key by:

    2.1. Setting the main dataset's(not the subDataset's) queryString to:

     <queryString language="jsonql">
     <![CDATA[report.samples_by_result_cfu]]>
 </queryString>

    2.2. Set the dataSourceExpression for the table's datasetRun like so:

     <datasetRun subDataset="Dataset1" uuid="ad76e183-2756-412b-a5c8-1d392d454155">
     <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("^")]]></dataSourceExpression>
 </datasetRun>

    The "^" expression means "go up one level" so that it lands on the whole array at the samples_by_result_cfu key(from step 2.1).

Related Questions in JSON

Related Questions in JASPER-REPORTS

Related Questions in SUBREPORT

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs

Popular Questions

.

Copyright © 2021 Jogjafile Inc.