I'm creating a main report with multiple subreports in Studio 6.6. I place each subreport in it's own details band. The subreports use the same dataset as the main report.
However every time I try this I end up with multiple (hundreds of) pages of the same subreports (in Preview). In researching this issue I read elsewhere that if you put the subreports into the details section, then the main report repeats the subreports over for each record in the dataset, but that's not the results I see in the docs or tutorials on creating subreports. And when I do place the subreports in other sections of the main report, as the posts suggest, the other bands can't accommodate data run-on so it throws and error and that doesn't work. It seems that subreports should work in the details section of the main report. The individual subreports preview fine, but the main report renders hundreds of copies of the same pages.
Any and all help is very much appreciated.
Here's the source for my main report:
<?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0 --> <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="Master Your Assets" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="0bcccaeb-190d-436b-bcc1-49e5e712b56f"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="DS_SEP_PROP"/> <queryString language="SQL"> <![CDATA[select m.monetary_asset_name, m.monetary_asset_type, m.monetary_asset_value, p.personal_type, p.personal_name, p.personal_current_value, r.real_type, r.real_name, r.real_current_value from dstest.monetary_assets as m JOIN dstest.personal_property_assets as p ON m.created_by_id =p.created_by_id JOIN dstest.real_property_assets as r ON r.created_by_id = p.created_by_id]]> </queryString> <field name="monetary_asset_name" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="monetary_asset_name"/> <property name="com.jaspersoft.studio.field.tree.path" value="monetary_assets"/> </field> <field name="monetary_asset_type" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="monetary_asset_type"/> <property name="com.jaspersoft.studio.field.tree.path" value="monetary_assets"/> </field> <field name="monetary_asset_value" class="java.math.BigDecimal"> <property name="com.jaspersoft.studio.field.label" value="monetary_asset_value"/> <property name="com.jaspersoft.studio.field.tree.path" value="monetary_assets"/> </field> <field name="personal_type" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="personal_type"/> <property name="com.jaspersoft.studio.field.tree.path" value="personal_property_assets"/> </field> <field name="personal_name" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="personal_name"/> <property name="com.jaspersoft.studio.field.tree.path" value="personal_property_assets"/> </field> <field name="personal_current_value" class="java.math.BigDecimal"> <property name="com.jaspersoft.studio.field.label" value="personal_current_value"/> <property name="com.jaspersoft.studio.field.tree.path" value="personal_property_assets"/> </field> <field name="real_type" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="real_type"/> <property name="com.jaspersoft.studio.field.tree.path" value="real_property_assets"/> </field> <field name="real_name" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="real_name"/> <property name="com.jaspersoft.studio.field.tree.path" value="real_property_assets"/> </field> <field name="real_current_value" class="java.math.BigDecimal"> <property name="com.jaspersoft.studio.field.label" value="real_current_value"/> <property name="com.jaspersoft.studio.field.tree.path" value="real_property_assets"/> </field> <background> <band splitType="Stretch"/> </background> <title> <band height="108" splitType="Stretch"> <rectangle> <reportElement x="0" y="50" width="572" height="50" forecolor="rgba(0, 0, 0, 0.0)" backcolor="#36AAD1" uuid="04a4185b-cfdb-462c-aef2-43a0b1336c7b"/> </rectangle> <image> <reportElement x="0" y="40" width="572" height="50" uuid="ef2ac753-c9d2-48bd-a5ca-ca74d1ff5feb"/> <imageExpression><![CDATA["C:/Users/Stephen/Documents/Projects/DivSplit/largeLogoDropped2.png"]]></imageExpression> </image> <staticText> <reportElement x="0" y="0" width="572" height="50" uuid="bec23f9b-a42f-40f7-bb67-a7c583ecc834"/> <textElement verticalAlignment="Middle"> <font size="36" isBold="true"/> </textElement> <text><![CDATA[Your Assets Summary Statement]]></text> </staticText> </band> </title> <pageHeader> <band height="297"> <subreport overflowType="Stretch"> <reportElement stretchType="ElementGroupHeight" isPrintRepeatedValues="false" x="0" y="0" width="572" height="110" isPrintInFirstWholeBand="true" uuid="87e2a767-35e6-4ccb-aa43-e8bf5a0252a4"/> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> <subreportExpression><![CDATA["monetary_sub.jasper"]]></subreportExpression> </subreport> </band> </pageHeader> <pageFooter> <band height="233"> <subreport> <reportElement positionType="Float" stretchType="ElementGroupHeight" x="0" y="10" width="572" height="163" uuid="32bafcd9-de14-4d2e-97e7-2f9ac9b9f59f"/> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> <subreportExpression><![CDATA["personal_sub.jasper"]]></subreportExpression> </subreport> </band> </pageFooter> </jasperReport>
And here's the source for one of the subreports (the other is identical just diffreent table/records:
<?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0 --> <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="monetary_sub" pageWidth="572" pageHeight="792" columnWidth="532" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="bb290549-1052-4b30-b357-f1f91a01ee62"> <property name="com.jaspersoft.studio.data.sql.tables"> <![CDATA[ZHN0ZXN0Lm1vbmV0YXJ5X2Fzc2V0cyBBUyAsMTE4LDI1NCxiM2FhNzY2OS1lY2E0LTQwNTEtYTQ2 Mi1mZTU5ZGNkOTUzZGI7]]> </property> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="DS_SEP_PROP"/> <property name="com.jaspersoft.studio.unit." value="pixel"/> <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/> <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/> <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/> <queryString language="SQL"> <![CDATA[SELECT dstest.monetary_assets.monetary_asset_name, dstest.monetary_assets.monetary_asset_type, dstest.monetary_assets.monetary_asset_value FROM dstest.monetary_assets]]> </queryString> <field name="monetary_asset_name" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="monetary_asset_name"/> <property name="com.jaspersoft.studio.field.tree.path" value="monetary_assets"/> </field> <field name="monetary_asset_type" class="java.lang.String"> <property name="com.jaspersoft.studio.field.label" value="monetary_asset_type"/> <property name="com.jaspersoft.studio.field.tree.path" value="monetary_assets"/> </field> <field name="monetary_asset_value" class="java.math.BigDecimal"> <property name="com.jaspersoft.studio.field.label" value="monetary_asset_value"/> <property name="com.jaspersoft.studio.field.tree.path" value="monetary_assets"/> </field> <variable name="monetary_total" class="java.math.BigDecimal" calculation="Sum"> <variableExpression><![CDATA[$F{monetary_asset_value}]]></variableExpression> </variable> <columnHeader> <band height="23"> <staticText> <reportElement x="0" y="0" width="150" height="20" uuid="b86195e2-3969-4292-95f3-780559db3aab"/> <textElement verticalAlignment="Middle"> <font size="14" isBold="true"/> </textElement> <text><![CDATA[Asset Type]]></text> </staticText> <staticText> <reportElement x="150" y="0" width="190" height="20" uuid="4e45400a-7788-455c-a546-85595caafbea"/> <textElement verticalAlignment="Middle"> <font size="14" isBold="true"/> </textElement> <text><![CDATA[Asset Name]]></text> </staticText> <staticText> <reportElement x="340" y="0" width="192" height="20" uuid="72408582-ec57-44ff-8c55-7cef32d7acc9"/> <textElement textAlignment="Right" verticalAlignment="Middle"> <font size="14" isBold="true"/> </textElement> <text><![CDATA[Current Value]]></text> </staticText> </band> </columnHeader> <detail> <band height="24" splitType="Stretch"> <textField> <reportElement x="0" y="0" width="150" height="20" uuid="bcc94e21-e109-4cec-9f98-544a849f43ca"/> <textFieldExpression><![CDATA[$F{monetary_asset_type}]]></textFieldExpression> </textField> <textField pattern="¤#,##0.###;¤(-#,##0.###)"> <reportElement x="342" y="0" width="190" height="20" uuid="6eccc86e-9ab5-4185-a727-2a85b580afd9"/> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$F{monetary_asset_value}]]></textFieldExpression> </textField> <textField> <reportElement x="150" y="2" width="192" height="20" uuid="4da71914-8d7e-42e0-9407-9d3a0e18ef07"/> <textFieldExpression><![CDATA[$F{monetary_asset_name}]]></textFieldExpression> </textField> </band> </detail> <columnFooter> <band height="45" splitType="Stretch"> <textField pattern="¤#,##0.###;¤(-#,##0.###)"> <reportElement x="340" y="0" width="192" height="20" uuid="059f3cfa-0e32-4497-82d6-2d30cb16974f"/> <box> <topPen lineWidth="1.0" lineStyle="Double"/> <bottomPen lineWidth="1.0" lineStyle="Double"/> </box> <textElement textAlignment="Right"/> <textFieldExpression><![CDATA[$V{monetary_total}]]></textFieldExpression> </textField> </band> </columnFooter> </jasperReport>
Hree's the SQL statement I use in th emain report to support the embedded subreports (though other simpler SQL statements have the same mulriple pages results):
SELECT m.monetary_asset_name, m.monetary_asset_type, m.monetary_asset_value, p.personal_type, p.personal_name, p.personal_current_value, r.real_type, r.real_name, r.real_current_value FROM dstest.monetary_assets AS m JOIN dstest.personal_property_assets AS p ON m.created_by_id =p.created_by_id JOIN dstest.real_property_assets AS r ON r.created_by_id = p.created_by_id
Any help/suggestions is very welcome as I'm completely stuck.
3 Answers:
Posted on July 9, 2018 at 9:56am
I get
net.sf.jasperreports.engine.JRRuntimeException: Subreport overflowed on a band that does not support overflow
when I put one subreport in the header and one in the footer.
Full error details:
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.ReportController.fillReport(ReportController.java:536) at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$17(ReportController.java:511) at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:429) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:56) 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:140) at net.sf.jasperreports.engine.fill.JRBaseFiller.addPageToParent(JRBaseFiller.java:1426) at net.sf.jasperreports.engine.fill.JRVerticalFiller.addPage(JRVerticalFiller.java:2184) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillPageBreak(JRVerticalFiller.java:2330) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBreak(JRVerticalFiller.java:2420) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:778) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportContent(JRVerticalFiller.java:285) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:114) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:615) at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:413) at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:814) at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:61) at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)
Posted on July 12, 2018 at 10:46am
Try to add the subreport in the summary band, that will work.
Otherwise if you need it in the higher band sections. Just create a dummy group and then use the Group Header or Footer.
Also the above research you did is correct. If your main report returns 10 rows, then the subreport would be generated 10 times over if put in the detailed band.
So if your subreport query returns 10 rows, then you would have 10 * 10 data returned at end of day, most likely duplicates.
Same issues if you assign a fields to the group that increments based on field value, then you would get same result. There are situations where on wants this type of behavior.
In such situations you would most likely used the rows per group to pass over to subreport to be used as parameters in the subreports SQL query.
I believe if you do the above you will start to get the hang of it and then figure out how to produce what you are aiming for.
Best of Luck
