Jump to content
Changes to the Jaspersoft community edition download ×

Studio 6.6 using subreports creates hundreds of pages/copies


hondaman900

Recommended Posts

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_idJOIN   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>[/code]

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[ZHN0ZXN0Lm1vbmV0YXJ5X2Fzc2V0cyBBUyAsMTE4LDI1NCxiM2FhNzY2OS1lY2E0LTQwNTEtYTQ2Mi1mZTU5ZGNkOTUzZGI7]]>    </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_valueFROM 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>[/code]

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_idJOIN   dstest.real_property_assets as r    ON  r.created_by_id = p.created_by_id[/code]

Any help/suggestions is very welcome as I'm completely stuck.

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

I get

net.sf.jasperreports.engine.JRRuntimeException: Subreport overflowed on a band that does not support overflow[/code]

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)[/code]


 

Link to comment
Share on other sites

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



​​​​​​
 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...