How to export Jasper Subreports in Different excel sheet?

Posted on April 16, 2016 at 2:22am

I am using UBUNTU 14.04 os also the version of my jasper studio is 5.6

I have one jasper report which containing 10 subReports. I want to export each report in different excel sheet. i'm trying to set proper alignment/segments in excel sheet so that i can do basic math operation on columns. my each subreport having multiple text field that causes segmentation problem in excel sheet. so i'm trying to export each subreport in each different sheet.

each subreport generate more than 1 pages(variable paging)

I have tried following ways to export xls in different sheet

  1. first i did ignorePagination to true in main report as well as in each subreport then added the page break element after each subreport group in main report but pagination isn't working.

  2. also tried: Set the property net.sf.jasperreports.page.break.no.pagination=apply net.sf.jasperreports.export.xls.one.page.per.sheet=true but in this jasper studio applies this property once ie. after each restart jasper studio then at second execution it won't work

  3. also tired setting property: net.sf.jasperreports.break.before.row=true and/or net.sf.jasperreports.break.after.row=true added line element or blank static text field element after each subreport in subreport group. this isn't working for whole report. after applying these properties segmentation problem remain as it is.

  4. even single net.sf.jasperreports.page.break.no.pagination=apply in main report is also not working

please provide some help! Thank You

Jaspersoft® Studio
pratik8552's picture
pratik8552
150
Joined: Mar 11 2016 - 9:52am
Last seen: 3 years 8 months ago

7 Answers:

Posted on April 16, 2016 at 7:09pm

There's not property to do this. You'll just have to make your subreports each into a report and then export those.

hozawa's picture
hozawa
125412
Joined: Apr 24 2010 - 4:31pm
Last seen: 2 years 11 months ago
Posted on April 18, 2016 at 2:06pm

I always do the following to achieve what to state:

  • Add the subreports in the same band (except detail or footer of any group)
  • Add the "break" element between all subreports set it up to break on page.
  • ignorePagination: default value (I don't remember if that is true or false).
  • net.sf.jasperreports.export.xls.one.page.per.sheet=true (just to be sure I add this property in the main report and every subreport)

As a bonus, there is a property that allows you to name each sheet as you want. If it will always have the same value you can use net.sf.jasperreports.export.xls.sheet.names.{arbitrary_name} changing 'arbitrary name' for the sheet number starting from 1, e.g. to name the third sheet you write the number 3 and the value you want. If the name of the sheet changes dynamically you have to use net.sf.jasperreports.export.xls.sheet.name on a report element. The first property does not accept variables/parameters/fields but the second property does.

Hope any of this helps,

 

Javier

javier.ggi90's picture
javier.ggi90
692
Joined: Dec 6 2015 - 1:46pm
Last seen: 5 years 1 month ago
Posted on April 18, 2016 at 11:46pm

Hello Javier

Thanks For Suggestions, I tried your way but it diden't workout. few reports are overlapping on each other and pagination also diden't workout. here is the image of report.

 

 

 

 

 

 

 

 

 

 

 

 

 

I found one way where in each group set property 'startOnNewPage=true' but after setting that property page break doesn't work. do you know how to set Force page break? or any more suggestion please feel free to tell.

Thank you.

 

 

 

pratik8552's picture
pratik8552
150
Joined: Mar 11 2016 - 9:52am
Last seen: 3 years 8 months ago
Posted on April 19, 2016 at 9:27am

Can you attach or paste your jrxml? Maybe I can find something there and help you better.

 

Javier

javier.ggi90's picture
javier.ggi90
692
Joined: Dec 6 2015 - 1:46pm
Last seen: 5 years 1 month ago
Posted on April 20, 2016 at 12:00am

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-->
<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="POS_Billing_DailySalesReport_Type2" language="groovy" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="0" isIgnorePagination="true" uuid="ddaeb9ca-aae7-4e61-9615-f25f60294adb">
<property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver/"/>
<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
 
pratik8552's picture
pratik8552
150
Joined: Mar 11 2016 - 9:52am
Last seen: 3 years 8 months ago
Posted on April 20, 2016 at 5:25am

 

Hello Javier
And here is the main.jrxml with one group which causes overlapping
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version last-->
<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="POS_Billing_DailySalesReport_Type2" language="groovy" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="0" isIgnorePagination="true" uuid="ddaeb9ca-aae7-4e61-9615-f25f60294adb">
<property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver/"/>
<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.3" value="groupHeader"/>
<property name="net.sf.jasperreports.export.xls.exclude.origin.group.3" value="Cancel_Bill"/>
<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="390">
<subreport>
<reportElement stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" mode="Transparent" x="-20" y="0" width="842" height="29" 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>
<break>
<reportElement x="0" y="29" width="800" height="1" uuid="1e895383-c447-4122-b64f-7fc77df97427"/>
</break>
<subreport>
<reportElement x="-20" y="40" width="842" height="29" 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>
<break>
<reportElement x="0" y="70" width="799" height="1" uuid="75c837b1-df5b-47fe-b907-9b92c560ad55"/>
</break>
<subreport>
<reportElement x="-20" y="80" width="842" height="30" 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>
<break>
<reportElement x="0" y="110" width="799" height="1" uuid="a14a28f5-118c-4d9a-9273-12b7dccdda88"/>
</break>
<subreport>
<reportElement x="-20" y="120" width="841" height="29" 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>
<break>
<reportElement x="0" y="149" width="799" height="1" uuid="74cf4d4f-4d52-48eb-98e7-31f0cf3cf2ae"/>
</break>
<subreport>
<reportElement x="-20" y="160" width="841" height="29" 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>
<break>
<reportElement x="0" y="190" width="799" height="1" uuid="59c2d5d1-08de-443c-996a-bf60ae162090"/>
</break>
<subreport>
<reportElement x="-20" y="200" width="841" height="30" 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>
<break>
<reportElement x="0" y="230" width="799" height="1" uuid="3d8d826a-401f-434a-816e-4bb3392ffb47"/>
</break>
<subreport>
<reportElement x="-20" y="240" width="841" height="29" 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>
<subreport>
<reportElement x="-20" y="280" width="842" height="29" 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>
<subreport>
<reportElement x="-21" y="320" width="842" height="29" 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>
<subreport>
<reportElement x="-20" y="360" width="841" height="30" 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="0" 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>
Thanks 
pratik8552's picture
pratik8552
150
Joined: Mar 11 2016 - 9:52am
Last seen: 3 years 8 months ago
Posted on April 8, 2017 at 2:48am

Worked for me, using following settings (JapserStudio Eclipse plugin):

The main report and all its subreports should have 'Ignore Pagination' checked (Pagination should be ignored)

On the main report set "net.sf.jasperreports.page.break.no.pagination=apply" and "net.sf.jasperreports.export.xls.one.page.per.sheet=true"

In between each subreport insert a "Break" element

 

ciukstar's picture
ciukstar
31
Joined: Sep 12 2014 - 11:25am
Last seen: 11 months 1 day ago
Feedback
randomness