I am using Jaspersoft studio to create reports. I am having a sub report in my Jasper main report. The problem is I am unable to make this work, because if I add the sub report into the Detail band
of the main report, the sub report get generated number of times, sometimes row by row, sometimes entire sub report repeated in number of pages. I can't put this in Summery Band
due to the same reason.
I am unable to put this in Column Footer Band
or other footers because it displays the below error
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: Subreport overflowed on a band that does not support overflow.
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:467)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:442)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:334)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
Caused by: net.sf.jasperreports.engine.JRRuntimeException: Subreport overflowed on a band that does not support overflow.
at net.sf.jasperreports.engine.fill.FillerSubreportParent.addPage(FillerSubreportParent.java:113)
at net.sf.jasperreports.engine.fill.JRBaseFiller.addPageToParent(JRBaseFiller.java:1213)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.addPage(JRVerticalFiller.java:1836)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillPageBreak(JRVerticalFiller.java:1919)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBreak(JRVerticalFiller.java:1948)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:730)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:264)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:124)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:540)
at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:393)
at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:704)
at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:203)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
I am unable to put this in page header due to below error, or else it just keep on executing.
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: Infinite loop creating new page due to page header overflow.
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:467)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:442)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:334)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
Caused by: net.sf.jasperreports.engine.JRRuntimeException: Infinite loop creating new page due to page header overflow.
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillPageHeader(JRVerticalFiller.java:415)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:258)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:124)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:540)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:120)
at java.lang.Thread.run(Unknown Source)
Below is the code of my main report
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.final using JasperReports Library version 6.0.0 -->
<!-- 2014-12-16T11:26:41 -->
<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="Portfolio_View_Report" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="961f2f5f-8ed1-422c-9a1a-6b9dcdf1624f">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="xxx"/>
<parameter name="idPortfolio" class="java.lang.Integer"/>
<queryString>
<![CDATA[SELECT * FROM Client]]>
</queryString>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="119" splitType="Stretch">
<property name="local_mesure_unitheight" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<image>
<reportElement x="210" y="1" width="140" height="76" uuid="f5d606fc-24d8-4536-9c35-56ce81c863c2"/>
<imageExpression><![CDATA["C:/Users/xxx/JaspersoftWorkspace/xxx/xxx.png"]]></imageExpression>
</image>
<staticText>
<reportElement x="0" y="80" width="210" height="30" uuid="8c8eb2f8-9d3d-46a0-9d4c-0d049e8c544d"/>
<textElement>
<font fontName="Times New Roman" size="14" isBold="true" isUnderline="true"/>
</textElement>
<text><![CDATA[Portfolio View Report]]></text>
</staticText>
</band>
</title>
<pageHeader>
<band height="159" splitType="Stretch">
<staticText>
<reportElement x="-1" y="0" width="163" height="20" uuid="8ffdf975-7965-4402-b9af-ef7785872f24"/>
<textElement verticalAlignment="Middle">
<font fontName="Times New Roman"/>
</textElement>
<text><![CDATA[Generated By: xxx]]></text>
</staticText>
<textField pattern="dd-MM-yyyy">
<reportElement x="450" y="0" width="100" height="20" uuid="cfd44de8-bc3d-4a2d-9f53-b9fdb969b061"/>
<textElement verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
</textField>
<subreport runToBottom="true">
<reportElement stretchType="RelativeToTallestObject" x="0" y="55" width="555" height="60" uuid="a99b1643-200b-4d7c-ade2-26e30b4fcc82">
<property name="local_mesure_unitheight" value="pixel"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</reportElement>
<subreportParameter name="idPortfolio">
<subreportParameterExpression><![CDATA[$P{idPortfolio}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA["Transactions_Report.jasper"]]></subreportExpression>
</subreport>
</band>
</pageHeader>
<detail>
<band height="36" splitType="Stretch">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/>
</band>
</detail>
<columnFooter>
<band height="285" splitType="Stretch">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/>
</band>
</columnFooter>
<pageFooter>
<band height="120" splitType="Stretch"/>
</pageFooter>
<summary>
<band height="100" splitType="Stretch"/>
</summary>
</jasperReport>
Below is the code of my sub report
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.final using JasperReports Library version 6.0.0 -->
<!-- 2014-12-16T11:28:26 -->
<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="Transactions_Report" pageWidth="500" pageHeight="842" columnWidth="500" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="4204d16c-e22f-46a2-b4c5-8be93fa35bc4">
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="xxx"/>
<parameter name="idPortfolio" class="java.lang.Integer"/>
<queryString language="SQL">
<![CDATA[SELECT Transactions.`idTransactions`,
Transactions.`Transaction_TimeStamp`,
Transactions.`idPortfolio`,
CASE when Transactions.`Transaction_Amount` <= 0
THEN NULL
else Transaction_Amount
END AS Encashments,
CASE when Transactions.`Transaction_Amount` > 0
THEN NULL
else (Transactions.`Transaction_Amount`*-1)
END AS Additions,
Portfolio.Activation
FROM Transactions
INNER JOIN Portfolio ON Transactions.idPortfolio = Portfolio.idPortfolio
WHERE Transactions.idPortfolio= $P{idPortfolio}
ORDER BY `Transaction_TimeStamp` DESC]]>
</queryString>
<field name="Transaction_TimeStamp" class="java.sql.Date">
<fieldDescription><![CDATA[]]></fieldDescription>
</field>
<field name="Additions" class="java.lang.Double"/>
<field name="Encashments" class="java.lang.Double"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="47" splitType="Stretch">
<staticText>
<reportElement x="5" y="10" width="180" height="30" uuid="30e059fd-caed-4e67-a4f6-d06f2897ac59"/>
<textElement>
<font fontName="Times New Roman" size="14" isBold="true" isUnderline="true"/>
</textElement>
<text><![CDATA[Transations]]></text>
</staticText>
</band>
</title>
<columnHeader>
<band height="44" splitType="Stretch">
<staticText>
<reportElement x="0" y="0" width="160" height="30" uuid="344e0833-c92a-47bf-bece-162b5ac7e52b">
<property name="local_mesure_unitwidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<text><![CDATA[Date]]></text>
</staticText>
<staticText>
<reportElement x="166" y="0" width="160" height="30" uuid="9bf82277-53b7-4244-aae9-84cf310e6d94">
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<text><![CDATA[Additions]]></text>
</staticText>
<rectangle>
<reportElement x="0" y="30" width="490" height="1" uuid="45244617-f10e-4867-bc84-71aa0c42649b"/>
<graphicElement>
<pen lineWidth="0.5"/>
</graphicElement>
</rectangle>
<staticText>
<reportElement x="330" y="0" width="160" height="30" uuid="68c554b4-0d79-4236-9e3f-db1b5dd19f7f">
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<text><![CDATA[Encashments]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="40" splitType="Stretch">
<textField pattern="dd-MM-yyyy">
<reportElement x="2" y="10" width="158" height="30" uuid="e2ab3765-dd44-4f8d-aedb-d411cce30384">
<property name="local_mesure_unitwidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textFieldExpression><![CDATA[$F{Transaction_TimeStamp}]]></textFieldExpression>
</textField>
<textField isBlankWhenNull="true">
<reportElement x="166" y="10" width="160" height="30" uuid="bb3baa95-bf15-4462-bb59-3461b1f4e1c7">
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textFieldExpression><![CDATA[$F{Additions}]]></textFieldExpression>
</textField>
<textField isBlankWhenNull="true">
<reportElement x="330" y="10" width="160" height="30" uuid="c625f008-1390-4245-8035-9bdc98c21d79">
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textFieldExpression><![CDATA[$F{Encashments}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>
What is wrong with this?
I found out that for my situation, it is best to use Tables instead of subreports. This is what I did.
Detail Band
.Now, eventhough I don't need a query for the main report, I must provide one in order the jasper to continue. The query is tricky and problamatic, because if it returned more than one result, the items in the Detail Band
will repeat. So, since I need the tables in the Detail Band
to be printed only "once", I must restrict the result returned by the main report's query only to one. I did something like below.
SELECT Name FROM Client WHERE idClient=1
Now the items in the detail band will act only once. Since the tables in the detail band contains their own queries, the entire table will be filled with the expected results.
The same method may work for sub reports as well.