Added by Alex Silva, last edited by Nick Goodman on Apr 17, 2008 (view change)

This chapter shows how to incorporate "sub" reports into other reports when using the report designer.

Working with Sub Reports

A sub report is a fully functional report that can be added to other reports the same way other report elements are added.

There are two ways to add a sub report to a report:

Using the 'File' menu and selecting 'New Subreport'.

This will open a new window, identical in functionality and design to the main Report Designer window, where you will be able to design the new report, using the same rules and instructions outlined in the previous documents. After designing the sub report, it should be saved in a accessible location using a *.report extension. This is how this report will be added to the "main" report later on.
In the "Palette" toolwindow, select "SubReport" and drag it to the appropriate location on the design pane.

Sub Report Queries

If you are creating a sub report, chances are you will want to use a different query from your main report. In order to do this, you will need to configure the datasets properly. This procedure is very similar to the one explained in the quickstart guide. Here is also a quick walkthrough:

On the Structure toolwindow, select the dataset currently being used.
Click the 'Configure' button at the bottom right side of the application.
The 'Configure' window shows up.
Click on 'Add'.
A 'new' query is added to the list. Rename it accordingly. In my case, I renamed it to 'sub1'.
Click on 'Query Designer' and select the schema/tables accordingly.
Click the 'OK' button.
You can now use this query in your sub report by using the name selected.

Sub Report Properties

The following properties can be editied when working with sub reports:

Name
The name by which the sub report should be referenced.
Appearance
- File Path
  This is the path where the *.report file is saved.
- Query
  Refers to the name of the query, as defined in the datasets structure elements. (See Sub Report Queries above.)
  This property is a text-field and its value should match exactly one of the queries defined in the dataset.
- Parameters
  If the query used by the sub report contains parameters, this is where they should be specified. These parameters are "imported" by the sub report. Consider the query below:
  SELECT * FROM ORDERS WHERE "ORDERS"."CUSTOMERNUMBER" = ${CUSTOMERS_CUSTOMERNUMBER}
  The last element in the query, CUSTOMERS_CUSTOMERNUMBER, is a parameter that will have to be passed into the sub report in other for it to function properly.

In order to manage parameters, click on the '...' button next to the 'Parameters' properties field. Two types of parameters are available:

- - Import parameters:
    Click the 'Add' button on the 'Formula' dialog under the Import parameters section. The Outer name refers to the name this parameters is going to be exported as, where as Inner name refers to the name of the property in the "parent" report. For instance, in the above example where you have a query that will use the CUSTOMER_CUSTOMERNUMBER parameter in the query you need to IMPORT this into the subquery.
  - Export parameters: TBD.
    * Spatial
- Position
  Indicates where the sub report should be placed within its parent report. This is automatically updated by dragging the sub report element in the design pane.
- Minimum Size
  The smallest size for this sub report element.

Running Sub Reports

After designing your sub report, you can view the data by simply previewing either the sub report directly or the parent report.

Lastly, there are no limitations to how many sub reports can be attached to a report, and obviously, a sub report can also contain other sub reports.

Examples

Blog with working, simple Subreport

Comments (Hide)

Hi,

regarding the sub report queries, make sure that one of the queries is called "default" otherwise you run into trouble when trying to deploy the reprot to the BI server.

Regards,

Ingo

Posted by Ingo Klose at Nov 14, 2007 07:33

Hi,

I tried creating the subreport but it does not appear in report page. Any issue do you think may need to be done specially to get it viewed with the same report page.

Regards,

Adwaita

Posted by Adwaita Thakur at Nov 14, 2007 16:29; last updated at Nov 15, 2007 10:46

I also tried creating the sub-report as described in the article. But it went in vain. I am using report designer version of 1.5.6270 only. Please help me out.

Posted by Anonymous at Nov 23, 2007 02:18

Can someone help me to create sub reports? After trying repeated times my understanding is above procedure is incomplete.

Thanks,

Madhu

Posted by Anonymous at Jan 19, 2008 20:05

Well - it worked for me but it ook me some time to understand that the data source had to be defined in the "Master" report and not in the sub-report itself - I found this a bit counter-intuitive. I would suggest more amphasis on this in this tutorial.

However, I have been trying to do what many people are after - e.g. having the subreport in the Item Band section and parameters passed to the subreport for each row - I manage to get this working but only when the master query returns one row (so not very useful) - the error message I'm getting are either: "Unable to return a valid pageformat" or in my best case something like "unable to open the recordset"

Basically I followed the above example, but put the sub-report on the Item Band exporting one of the column name as parameter.

Posted by gege at Mar 05, 2008 13:26

To be more specific on the other error message - this is what I've got:

SEVERE: StatusBar.exceptionCaught
org.jfree.report.modules.misc.tablemodel.DataTableException: Accessing the result set failed:
...
ParentException:
java.sql.SQLException: Operation not allowed after ResultSet closed
...

Posted by Anonymous at Mar 05, 2008 17:03

OK - I'm replying to myself but I finally got this to work.

I was actually missing a 'crucial' option in the connection string:
?holdResultsOpenOverStatementClose=true (using the MySQL jdbc driver). I found this by looking at demo subreports. I'm on OSX 10.4.11, ReportDesigner 1.6.0 B348, using the jdbc mysql driver provided with the download (com.mysql.jdbc.driver).

This explains the "Accessing the result set failed".

So to be fair, it is not an issue with the Report Designer but rather a jdbc driver issue. Having said that, it is a bit annoying to have to rely on this rather obscure option. I'm using MS SQL at work and I couldn't find the equivalent option on any MS SQL jdbc driver (tried with both Microsoft and jTDS in vain).

If someone has an idea, please post...

Posted by gege at Mar 12, 2008 18:30

Anybody can explain to me how work the parameters? With more details please? I see an example on the pentaho-solutions and another example in... And I try to make exactly the same thing, but I have always this message error:

org.jfree.report.modules.misc.tablemodel.DataTableException: Accessing the result set failed:

Thank you for your help

Posted by Anonymous at Apr 08, 2008 06:43

Just to share an issue which took me some hours to discover:

if you wonder why your sub reports in the report designer never show up, it could be that you try to use a normal report as sub report.

Although you can put any report in the property "File Path", you MUST create that report with File -> Create SubReport. The xml in the .report file is not the same as for a normal report.

Posted by Ulrich at Jun 20, 2008 05:46

Hi
I wrote a seb query using a field from the default query as a parameter but an error appear to me

COULD NOT FETCH THE QUERY METADATA
REASON:
ERROR: SYNTAX ERROR AT OR NEAR "$"

waiting your response
thanks

Posted by Anonymous at Jul 03, 2008 04:59

I have the same problem:

because in the main report , i just created a "default" datasource, and used it in sub report.

my solution is create the second datasource "sub1", and use it for sub report.

Posted by Anonymous at Jul 28, 2008 22:46

I've been trying construct a multi-section report using sub reports to count different types of objects. Whenever I add a component in the designer, however, all the sub-reports move so that they're below anything I might add. I tried putting them in a sub-band, but then it can't generate the report (I can't even try to publish it).

It seems to be throwing a ClassCastException somewhere:

2008-07-31 16:35:26:213 WARNING org.pentaho.reportdesigner.crm.report.preview.PreviewComponent: PreviewComponent.updatePreview
org.pentaho.reportdesigner.crm.report.reportexporter.ReportCreationException: Unknown error occured
        at org.pentaho.reportdesigner.crm.report.reportexporter.jfreereport.JFreeReportVisitor.visit(JFreeReportVisitor.java:1413)
        at org.pentaho.reportdesigner.crm.report.model.SubReportElement.accept(SubReportElement.java:223)
        at org.pentaho.reportdesigner.crm.report.model.BandReportElement.accept(BandReportElement.java:233)
        at org.pentaho.reportdesigner.crm.report.model.BandToplevelReportElement.accept(BandToplevelReportElement.java:227)
        at org.pentaho.reportdesigner.crm.report.model.Report.accept(Report.java:561)
        at org.pentaho.reportdesigner.crm.report.preview.PreviewComponent.showReport(PreviewComponent.java:353)
        at org.pentaho.reportdesigner.crm.report.preview.PreviewComponent.updatePreview(PreviewComponent.java:305)
        at org.pentaho.reportdesigner.crm.report.ReportDialog$6.actionPerformed(ReportDialog.java:759)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.JToggleButton$ToggleButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at javax.swing.JComponent.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.ClassCastException: org.jfree.report.Band
        at org.pentaho.reportdesigner.crm.report.reportexporter.jfreereport.JFreeReportVisitor.visit(JFreeReportVisitor.java:1389)
        ... 31 more
Jul 31, 2008 4:35:26 PM org.pentaho.reportdesigner.crm.report.preview.PreviewComponent updatePreview
WARNING: PreviewComponent.updatePreview
org.pentaho.reportdesigner.crm.report.reportexporter.ReportCreationException: Unknown error occured
        at org.pentaho.reportdesigner.crm.report.reportexporter.jfreereport.JFreeReportVisitor.visit(JFreeReportVisitor.java:1413)
        at org.pentaho.reportdesigner.crm.report.model.SubReportElement.accept(SubReportElement.java:223)
        at org.pentaho.reportdesigner.crm.report.model.BandReportElement.accept(BandReportElement.java:233)
        at org.pentaho.reportdesigner.crm.report.model.BandToplevelReportElement.accept(BandToplevelReportElement.java:227)
        at org.pentaho.reportdesigner.crm.report.model.Report.accept(Report.java:561)
        at org.pentaho.reportdesigner.crm.report.preview.PreviewComponent.showReport(PreviewComponent.java:353)
        at org.pentaho.reportdesigner.crm.report.preview.PreviewComponent.updatePreview(PreviewComponent.java:305)
        at org.pentaho.reportdesigner.crm.report.ReportDialog$6.actionPerformed(ReportDialog.java:759)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.JToggleButton$ToggleButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at javax.swing.JComponent.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.ClassCastException: org.jfree.report.Band
        at org.pentaho.reportdesigner.crm.report.reportexporter.jfreereport.JFreeReportVisitor.visit(JFreeReportVisitor.java:1389)
        ... 31 more

Posted by Anonymous at Jul 31, 2008 15:37

Page Operations

Browse Space

Working with Sub Reports

Sub Report Queries

Sub Report Properties

Running Sub Reports

Examples