Hello Javier
adding My main.jrxml code here:
in that currently i'm doing is:
1. added xls.one.page.per.sheet= true property
2. there was break element but i removed it cause it wasn't working with isIgnorePagination=true methode + isStartOnNewPage=true group methode. there is now simply isStartOnNewPage=true for second group cause now finally after so much effort and search i'm concentrating to divide 1st report on one sheet and other all in second sheet.
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version last-->
<property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
<property name="ireport.jasperserver.user" value="jasperadmin"/>
<property name="ireport.jasperserver.report.resource" value="/reports/SMARTORDER_REPORTS/SMARTORDER_BILLING_DSR/POS_Billing_DailySalesReport_Type2_files/main_jrxml"/>
<property name="ireport.jasperserver.reportUnit" value="/reports/SMARTORDER_REPORTS/SMARTORDER_BILLING_DSR/POS_Billing_DailySalesReport_Type2"/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="POSConnection"/>
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/>
<property name="net.sf.jasperreports.export.xls.exclude.origin.band.1" value="groupHeader"/> //
<property name="net.sf.jasperreports.export.xls.exclude.origin.group.1" value="Cancel_Bill"/> // you can exclude these 2 steps cause this is just to try export only one report so im trying to exclude all reports
<parameter name="fromDate" class="java.lang.String">
<parameterDescription><![CDATA[]]></parameterDescription>
</parameter>
<parameter name="toDate" class="java.lang.String"/>
<parameter name="hotelName" class="java.lang.String"/>
<parameter name="foodCategory" class="java.lang.String">
<defaultValueExpression><![CDATA["18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,38,39,40,41"]]></defaultValueExpression>
</parameter>
<parameter name="wineFoodCategory" class="java.lang.String">
<defaultValueExpression><![CDATA["14"]]></defaultValueExpression>
</parameter>
<parameter name="coldDrinkFoodCategory" class="java.lang.String">
<defaultValueExpression><![CDATA["17,37"]]></defaultValueExpression>
</parameter>
<parameter name="liquorFoodCategory" class="java.lang.String">
<defaultValueExpression><![CDATA["1,2,3,4,5,6,7,8,9,10,11,12,13,15,16"]]></defaultValueExpression>
</parameter>
<parameter name="companyId" class="java.lang.String"/>
<queryString>
<![CDATA[Select 1 from Dual]]>
</queryString>
<field name="1" class="java.lang.Long"/>
<group name="DSR_Group">
<groupHeader>
<band height="60">
<subreport>
<reportElement stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" mode="Transparent" x="-20" y="0" width="842" height="59" isRemoveLineWhenBlank="true" isPrintInFirstWholeBand="true" printWhenGroupChanges="DSR_Group" uuid="1d4602df-49db-47be-84ad-ad22c861f068"/>
<subreportParameter name="hotelName">
<subreportParameterExpression><![CDATA[$P{hotelName}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="coldDrinkFoodCategory">
<subreportParameterExpression><![CDATA[$P{coldDrinkFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="wineFoodCategory">
<subreportParameterExpression><![CDATA[$P{wineFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="liquorFoodCategory">
<subreportParameterExpression><![CDATA[$P{liquorFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="foodCategory">
<subreportParameterExpression><![CDATA[$P{foodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_DSR_Subreport_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="Cancel_Bill" isStartNewPage="true">
<groupHeader>
<band height="50">
<subreport>
<reportElement x="-20" y="0" width="842" height="50" isRemoveLineWhenBlank="true" uuid="718a14a1-286e-4c06-9f2b-9381b0c2a0e7"/>
<subreportParameter name="hotelName">
<subreportParameterExpression><![CDATA[$P{hotelName}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="coldDrinkFoodCategory">
<subreportParameterExpression><![CDATA[$P{coldDrinkFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="wineFoodCategory">
<subreportParameterExpression><![CDATA[$P{wineFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="liquorFoodCategory">
<subreportParameterExpression><![CDATA[$P{liquorFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="foodCategory">
<subreportParameterExpression><![CDATA[$P{foodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_Cancelled_Bill_Subreport_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="NC_Group">
<groupHeader>
<band height="50">
<subreport>
<reportElement x="-20" y="0" width="842" height="50" isRemoveLineWhenBlank="true" uuid="498c51ae-a361-4c0f-98e4-e05d973da8ff"/>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="coldDrinkFoodCategory">
<subreportParameterExpression><![CDATA[$P{coldDrinkFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="wineFoodCategory">
<subreportParameterExpression><![CDATA[$P{wineFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="liquorFoodCategory">
<subreportParameterExpression><![CDATA[$P{liquorFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="foodCategory">
<subreportParameterExpression><![CDATA[$P{foodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_DailyNCReport_Subreport_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="Cancel_KOT_Group">
<groupHeader>
<band height="50">
<subreport>
<reportElement x="-20" y="0" width="841" height="50" uuid="67c9d02f-cf0e-46f7-a072-31e94e5d0a60"/>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="foodcategoryId">
<subreportParameterExpression><![CDATA[$P{foodCategory}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_Cancelkot_Subreport_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="Cancel_BOT_Group">
<groupHeader>
<band height="50">
<subreport>
<reportElement x="-20" y="0" width="841" height="50" uuid="be1c0cf4-a1c7-4cd7-b0cd-cff0b06fa124"/>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="foodCategory">
<subreportParameterExpression><![CDATA[$P{foodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_Cancelbot_Subreport_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="Reprint_Bills">
<groupHeader>
<band height="50">
<subreport>
<reportElement x="-20" y="0" width="841" height="50" uuid="556e3c56-0683-41ed-98aa-01a9d888fdb0"/>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_reprintbill_Subreport_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="Summary_Group">
<groupHeader>
<band height="50">
<subreport>
<reportElement x="-20" y="0" width="841" height="50" uuid="6cd3ce43-0edc-40ee-87e3-e499a178af60"/>
<subreportParameter name="coldDrinkFoodCategory">
<subreportParameterExpression><![CDATA[$P{coldDrinkFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="wineFoodCategory">
<subreportParameterExpression><![CDATA[$P{wineFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="liquorFoodCategory">
<subreportParameterExpression><![CDATA[$P{liquorFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="foodCategory">
<subreportParameterExpression><![CDATA[$P{foodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_DSR_Summary_Subreport1_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="summarycancel_Group">
<groupHeader>
<band height="57">
<subreport>
<reportElement x="-20" y="0" width="842" height="57" isRemoveLineWhenBlank="true" uuid="599a967c-7970-4285-bada-db370e285b5d"/>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="coldDrinkFoodCategory">
<subreportParameterExpression><![CDATA[$P{coldDrinkFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="wineFoodCategory">
<subreportParameterExpression><![CDATA[$P{wineFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="liquorFoodCategory">
<subreportParameterExpression><![CDATA[$P{liquorFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="foodCategory">
<subreportParameterExpression><![CDATA[$P{foodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_DSR_Summary_Subreport3_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="SummaryNC_group">
<groupHeader>
<band height="51">
<subreport>
<reportElement x="-20" y="0" width="842" height="51" isRemoveLineWhenBlank="true" uuid="8779a930-536d-4215-a99d-96ed55b9303e"/>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="coldDrinkFoodCategory">
<subreportParameterExpression><![CDATA[$P{coldDrinkFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="wineFoodCategory">
<subreportParameterExpression><![CDATA[$P{wineFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="liquorFoodCategory">
<subreportParameterExpression><![CDATA[$P{liquorFoodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="foodCategory">
<subreportParameterExpression><![CDATA[$P{foodCategory}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_DSR_Summary_Subreport2_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="companyaccount_group">
<groupHeader>
<band height="60">
<subreport>
<reportElement x="-20" y="0" width="841" height="60" isRemoveLineWhenBlank="true" uuid="8d2cde54-2c95-4912-8498-5edd50111ffd"/>
<subreportParameter name="fromDate">
<subreportParameterExpression><![CDATA[$P{fromDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="toDate">
<subreportParameterExpression><![CDATA[$P{toDate}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="companyId">
<subreportParameterExpression><![CDATA[$P{companyId}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["POS_Billing_CompanyNamewise_Subreport_Type2.jasper"]]></subreportExpression>
</subreport>
</band>
</groupHeader>
</group>
<group name="Group1"/>
<title>
<band height="41" splitType="Stretch">
<line direction="BottomUp">
<reportElement x="-20" y="40" width="830" height="1" uuid="4338177c-bab1-4305-b8ee-495c03aa9e22">
<property name="local_mesure_unitheight" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</reportElement>
</line>
<textField>
<reportElement x="170" y="1" width="450" height="19" uuid="017a4739-0ade-4f16-9ddd-630f0fb0e075"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Times New Roman" size="16" isBold="true" pdfFontName="Helvetica"/>
</textElement>
<textFieldExpression><![CDATA[$P{hotelName}+" "+" Daily Sales Report"]]></textFieldExpression>
</textField>
<textField pattern="MMMMM dd, yyyy">
<reportElement x="170" y="19" width="450" height="17" uuid="f318ec8d-2fad-40d7-8f79-ee7fd4a1b7f5"/>
<box rightPadding="5"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="12"/>
</textElement>
<textFieldExpression><![CDATA["From :"+" "+$P{fromDate}+" "+"To :"+" "+$P{toDate}]]></textFieldExpression>
</textField>
</band>
</title>
<pageHeader>
<band height="3" splitType="Stretch"/>
</pageHeader>
<columnHeader>
<band height="2" splitType="Stretch"/>
</columnHeader>
<columnFooter>
<band splitType="Stretch"/>
</columnFooter>
<pageFooter>
<band height="33">
<textField evaluationTime="Report">
<reportElement x="733" y="7" width="67" height="14" uuid="6ee40ae8-4a38-4c44-8e29-20d584fd1a8d">
<property name="local_mesure_unitwidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box leftPadding="3" rightPadding="0"/>
<textElement textAlignment="Left"/>
<textFieldExpression><![CDATA[" of " + $V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
<textField pattern="MMM d, yyyy h:mm:ss a">
<reportElement x="-20" y="6" width="196" height="17" uuid="44854d7f-1c47-48c3-ae6d-8012bee7f985">
<property name="local_mesure_unitwidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box leftPadding="5"/>
<textElement>
<font fontName="Times New Roman" size="11" isItalic="true"/>
</textElement>
<textFieldExpression><![CDATA["Printed On"+" "+new java.util.Date()]]></textFieldExpression>
</textField>
<textField>
<reportElement x="280" y="8" width="250" height="13" uuid="da6d6f7d-ad04-458d-aeec-a55ca5f5dae9"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Eras Medium ITC" size="10" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA["HotelName :"+$P{hotelName}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="280" y="21" width="250" height="12" uuid="3ed7fd48-15c6-4af7-a7de-e59e2dcf554e">
<property name="local_mesure_unitwidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
<property name="local_mesure_unitheight" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</reportElement>
<textElement>
<font fontName="Eras Medium ITC"/>
</textElement>
<textFieldExpression><![CDATA["From Date :"+$P{fromDate}+" "+"ToDate :"+$P{toDate}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="663" y="7" width="70" height="14" uuid="62487b63-096f-4278-8068-efcc5dcf9f7c"/>
<box rightPadding="3"/>
<textElement textAlignment="Right"/>
<textFieldExpression><![CDATA["Page " + $V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
</band>
</pageFooter>
</jasperReport>
Thank You