Hitachi Vantara Pentaho Community Wiki
Child pages
  • 7. Sub Reports
Skip to end of metadata
Go to start of metadata

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:

  1. 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.
     
  2. 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:

  1. On the Structure toolwindow, select the dataset currently being used.
  2. Click the 'Configure' button at the bottom right side of the application.
  3. The 'Configure' window shows up.
  4. Click on 'Add'.
  5. A 'new' query is added to the list.  Rename it accordingly.  In my case, I renamed it to  'sub1'.
  6. Click on 'Query Designer' and select the schema/tables accordingly.
  7. Click the 'OK' button.
  8. 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:
        • In the "Data" menu, click on "Edit Sub-report Parameters...".
    • 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

  • No labels

12 Comments

  1. 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

  2. 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

  3. Anonymous

    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.

  4. Anonymous

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

    Thanks,

    Madhu

  5. 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.





  6. Anonymous

    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
    ...

  7. 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...

  8. Anonymous

    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

  9. 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.

  10. Anonymous

    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

  11. Anonymous

    I have the same problem: 

    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
    ...

    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.


  12. Hi ,

    I am new to pentaho. Can any1 tell me how to position a sub report horizontally beside another, I specified the position in the properties tab but it always is displayed at the bottom....Am I misssing something ??

    I am using Report Designer 2.0.