'Multiple Dataset ,Multiple Sheet excel report using Jasper is not generating with proper content

As part of our project, we wanted to implement multi dataset, multiple sheet Excel report using Jasper Reports. We could see that when the data is lower the report gets generated correctly.
When the data is huge, we could see that the report generating not as expected.
Attaching the JRXML and the report being generated from Jasper.
If we refer to the Report, we can see additional Sheet called Mainsheet, which has data from different data sets.
The version of Jasper being used is 5.5.0,6.17.0 and 6.19.1
Requesting your help in this regard.
<?xml version="1.0" encoding="UTF-8"?>

<!-- Created with Jaspersoft Studio version 7.1.0.final using JasperReports Library version 6.4.3  -->

<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="MainSheetTable" pageWidth="4981" pageHeight="3633" columnWidth="4941" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="501be692-98d0-4c85-a682-83829485b467">

       <property name="com.jaspersoft.studio.data.sql.tables" value=""/>

       <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Sample DB"/>

       <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>

       <property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>

       <property name="net.sf.jasperreports.export.xls.sheet.names.1" value="Deferral Details"/>

       <property name="net.sf.jasperreports.export.xls.sheet.names.2" value="Transaction List"/>

       <property name="net.sf.jasperreports.export.xls.sheet.names.3" value="Document List"/>

       <property name="net.sf.jasperreports.export.xls.sheet.names.4" value="Reason List"/>

       <property name="net.sf.jasperreports.export.xls.sheet.names.5" value="Event List"/>

       <property name="net.sf.jasperreports.export.xls.sheet.names.6" value="Followup Details List"/>

       <property name="com.jaspersoft.studio.unit." value="pixel"/>

       <property name="com.jaspersoft.studio.unit.pageWidth" value="inch"/>

       <property name="com.jaspersoft.studio.unit.pageHeight" value="inch"/>
       

       <subDataset name="TranncamtList" uuid="1ab2e25f-fbc3-42b2-bf46-2522bd41a5ad">

              <property name="com.jaspersoft.studio.data.sql.tables" value=""/>

              <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Sample DB"/>

              <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w1" value="130"/>

              <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w2" value="863"/>

              <parameter name="countryid" class="java.lang.String"/>

              <parameter name="businessunit" class="java.lang.String"/>

              <parameter name="productid" class="java.lang.String"/>

              <parameter name="departmentid" class="java.lang.String"/>

              <parameter name="customerbaseno" class="java.lang.String"/>

              <parameter name="customername" class="java.lang.String"/>

              <parameter name="userbranch" class="java.lang.String"/>

              <parameter name="fromDate" class="java.lang.String"/>

              <parameter name="toDate" class="java.lang.String"/>

              <parameter name="fromauthDate" class="java.lang.String"/>

              <parameter name="toauthDate" class="java.lang.String"/>

              <parameter name="frommakerDate" class="java.lang.String"/>

              <parameter name="tomakerDate" class="java.lang.String"/>

              <parameter name="recordstatus1" class="java.lang.String"/>

              <parameter name="recordstatus2" class="java.lang.String"/>

              <parameter name="recordstatus3" class="java.lang.String"/>

              <parameter name="recordstatus4" class="java.lang.String"/>

              <parameter name="recordstatus5" class="java.lang.String"/>

              <parameter name="recordstatus6" class="java.lang.String"/>

              <queryString language="SQL">

                     <![CDATA[SELECT * FROM TB_DTS_DEF_TRNACCAMT_DTL WHERE DEFERRAL_ID IN (SELECT DEFERRAL_ID FROM VW_DTS_DEFERRAL_DETAILS VDDD

WHERE (VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus1}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus2}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus3}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus4}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus5}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus6}

)

AND VDDD.COUNTRY_ID=$P{countryid}

AND VDDD.BUSINESS_UNIT=$P{businessunit}

AND VDDD.PRODUCT_ID=NVL(NULLIF($P{productid},''),VDDD.PRODUCT_ID)

AND VDDD.DEPARTMENT_ID=NVL(NULLIF($P{departmentid},''),VDDD.DEPARTMENT_ID)

AND VDDD.CUSTOMER_BASE_NO=NVL(NULLIF($P{customerbaseno},''),VDDD.CUSTOMER_BASE_NO)

AND VDDD.CUSTOMER_NAME LIKE $P{customername}

AND VDDD.USER_BRANCH=NVL(NULLIF($P{userbranch},''),VDDD.USER_BRANCH)

AND (TRUNC(VDDD.CREATED_TS) BETWEEN $P{fromDate} AND $P{toDate}

OR  TRUNC(VDDD.CLOSED_AUTHORIZED_DATE) BETWEEN $P{fromauthDate}  AND $P{toauthDate}

                                                  OR TRUNC(MAKER_TS) BETWEEN $P{frommakerDate} AND $P{tomakerDate}))]]>

              </queryString>

              
       </subDataset>

       <subDataset name="DocTrackList" uuid="e2d92cb5-121c-458c-b8d8-ed6406593685">

              <property name="com.jaspersoft.studio.data.sql.tables" value=""/>

              <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Sample DB"/>

              <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w1" value="201"/>

              <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w2" value="791"/>

              <parameter name="recordstatus1" class="java.lang.String"/>

              <parameter name="recordstatus2" class="java.lang.String"/>

              <parameter name="recordstatus3" class="java.lang.String"/>

              <parameter name="recordstatus4" class="java.lang.String"/>

              <parameter name="recordstatus5" class="java.lang.String"/>

              <parameter name="recordstatus6" class="java.lang.String"/>

              <parameter name="countryid" class="java.lang.String"/>

              <parameter name="businessunit" class="java.lang.String"/>

              <parameter name="productid" class="java.lang.String"/>

              <parameter name="departmentid" class="java.lang.String"/>

              <parameter name="customerbaseno" class="java.lang.String"/>

              <parameter name="customername" class="java.lang.String"/>

              <parameter name="userbranch" class="java.lang.String"/>

              <parameter name="fromDate" class="java.lang.String"/>

              <parameter name="toDate" class="java.lang.String"/>

              <parameter name="fromauthDate" class="java.lang.String"/>

              <parameter name="toauthDate" class="java.lang.String"/>

              <parameter name="frommakerDate" class="java.lang.String"/>

              <parameter name="tomakerDate" class="java.lang.String"/>

              <queryString language="SQL">

                     <![CDATA[SELECT * FROM VW_DTS_DEF_DOC_TRACK  TDDT WHERE TDDT.DEFERRAL_ID IN (SELECT DEFERRAL_ID FROM VW_DTS_DEFERRAL_DETAILS VDDD

WHERE (VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus1}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus2}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus3}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus4}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus5}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus6}

)

AND VDDD.COUNTRY_ID=$P{countryid}

AND VDDD.BUSINESS_UNIT=$P{businessunit}

AND VDDD.PRODUCT_ID=NVL(NULLIF($P{productid},''),VDDD.PRODUCT_ID)

AND VDDD.DEPARTMENT_ID=NVL(NULLIF($P{departmentid},''),VDDD.DEPARTMENT_ID)

AND VDDD.CUSTOMER_BASE_NO=NVL(NULLIF($P{customerbaseno},''),VDDD.CUSTOMER_BASE_NO)

AND VDDD.CUSTOMER_NAME LIKE $P{customername}

AND VDDD.USER_BRANCH=NVL(NULLIF($P{userbranch},''),VDDD.USER_BRANCH)

AND (TRUNC(VDDD.CREATED_TS) BETWEEN $P{fromDate} AND $P{toDate}

OR  TRUNC(VDDD.CLOSED_AUTHORIZED_DATE) BETWEEN $P{fromauthDate}  AND $P{toauthDate}

                                                  OR TRUNC(MAKER_TS) BETWEEN $P{frommakerDate} AND $P{tomakerDate})) --MAKER_TS]]>

              </queryString>

              

       </subDataset>

       <subDataset name="ReasonDetailList" uuid="0debec7d-39b8-4757-9107-50dd37c24cac">

              <property name="com.jaspersoft.studio.data.sql.tables" value=""/>

              <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Sample DB"/>

              <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w1" value="322"/>

              <property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w2" value="671"/>

              <parameter name="countryid" class="java.lang.String"/>

              <parameter name="businessunit" class="java.lang.String"/>

              <parameter name="productid" class="java.lang.String"/>

              <parameter name="departmentid" class="java.lang.String"/>

              <parameter name="customerbaseno" class="java.lang.String"/>

              <parameter name="customername" class="java.lang.String"/>

              <parameter name="userbranch" class="java.lang.String"/>

              <parameter name="fromDate" class="java.lang.String"/>

              <parameter name="toDate" class="java.lang.String"/>

              <parameter name="fromauthDate" class="java.lang.String"/>

              <parameter name="toauthDate" class="java.lang.String"/>

              <parameter name="frommakerDate" class="java.lang.String"/>

              <parameter name="tomakerDate" class="java.lang.String"/>

              <parameter name="recordstatus1" class="java.lang.String"/>

              <parameter name="recordstatus2" class="java.lang.String"/>

              <parameter name="recordstatus3" class="java.lang.String"/>

              <parameter name="recordstatus4" class="java.lang.String"/>

              <parameter name="recordstatus5" class="java.lang.String"/>

              <parameter name="recordstatus6" class="java.lang.String"/>

              <queryString language="SQL">

                     <![CDATA[SELECT * FROM VW_DTS_DEF_REASON_DTL  TDDR WHERE TDDR.DEFERRAL_ID IN (SELECT DEFERRAL_ID FROM VW_DTS_DEFERRAL_DETAILS VDDD

WHERE (VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus1}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus2}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus3}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus4}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus5}

      OR VDDD. DEFERRAL_RECORD_STATUS =  $P{recordstatus6}

)

AND VDDD.COUNTRY_ID=$P{countryid}

AND VDDD.BUSINESS_UNIT=$P{businessunit}

AND VDDD.PRODUCT_ID=NVL(NULLIF($P{productid},''),VDDD.PRODUCT_ID)

AND VDDD.DEPARTMENT_ID=NVL(NULLIF($P{departmentid},''),VDDD.DEPARTMENT_ID)

AND VDDD.CUSTOMER_BASE_NO=NVL(NULLIF($P{customerbaseno},''),VDDD.CUSTOMER_BASE_NO)

AND VDDD.CUSTOMER_NAME LIKE $P{customername}

AND VDDD.USER_BRANCH=NVL(NULLIF($P{userbranch},''),VDDD.USER_BRANCH)

AND (TRUNC(VDDD.CREATED_TS) BETWEEN $P{fromDate} AND $P{toDate}

OR  TRUNC(VDDD.CLOSED_AUTHORIZED_DATE) BETWEEN $P{fromauthDate} AND $P{toauthDate}

               OR TRUNC(MAKER_TS) BETWEEN $P{frommakerDate}AND $P{tomakerDate}))]]>

              </queryString>

              

       </subDataset>

    ```
jasper-reports export-to-excel
Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution	Source
'Multiple Dataset ,Multiple Sheet excel report using Jasper is not generating with proper content

Sources

Related Questions
