Report Desk: Subreports
Version 8.6
Performance Considerations
You will notice that the subreport has a template with a SELECT statement in it. This means that, for each detail line in the main report, a separate SELECT statement will be performed from the subreport. So a report that contains 100 detail rows will execute 101 SELECT statements, even if only one of the detail row generates subreport information. Obviously, this can cause a performance issue if there are many detail rows in the main report and/or multiple subreport levels.
When to Use Subreports
Sometimes there is information related to a detail line in a report that can be obtained from a previously-defined separate report -- a subreport that is intended to be embedded in other reports. For example, in the report below, the distribution information (outlined in red) relating to each vendor follows the vendor detail line. You can see that some vendors have distributions and others do not. If the subreport contains no data for a detail line, it will not be included in the output.A report design may be designated a subreport (rather than a main report) so that it can be included in other reports. Any main report can have one or more subreports associated with each detail line. Subreports can have their own subreports. A subreport may have optional column headings that repeat for each detail line in the main report. A subreport can be designated as optional, allowing the end user to decide whether or not to include it in the report.
How to Add a Subreport to a Report
Using the Report Desk Designer, click in the Subreport tab, then click on the Click here to add a new row label. Note that when there is not already a subreport in the list, click on the blank ReportID cell instead to add a new report:
You will see a new dialog where you can select an already-defined subreport or create a new one:
You can select from a list of all available subreports, or, if you select <New Report>, you will be taken to a series of dialogs where you can create a new report. (Note that the SELECT statement in the subreport should not define any linkage between this report and the subreport -- that is done from the master report.) After you design a new report or select an existing one, you will next need to fill out the rest of this dialog: First, fill out the Master Value and Detail Field columns. This is where you specify the mapping between the current detail line in the report to the available fields in the subreport:
Here, the VEND_NO column from the main report's APVENFIL table is mapped to the VEND_DIST_VEND_NO column from the subreport's APVENDST table. In some cases, you may need to map multiple column combinations to specify the correct records in the subreport. Also, you may type into these fields instead of selecting a listed column name. So, for example, you would map 'APVENFIL' to NOTE.NOTE_FILE_NAME and APVENFIL.VEND_NO to NOTE.NOTE_FILE_REF_NO if you were mapping this report to a Notes subreport.
Next, if you want to narrow the subreport horizontally instead of using the whole width of the report, you can use the Begin Report Under and/or End Report Under comboboxes for that purpose:
The columns are listed in the order they appear on the main report.
Next, you can decide whether or not you want titles above each occurrence of the subreport data. Use the Include Titles checkbox for that.
Your subreport can be made optional, leaving the choice to include the subreport to the user. To do that check the Make Optional checkbox, specify whether the Default value for the checkbox will be checked or not and, optionally enter a User Prompt:
Finally save the Update Subreport dialog, and you will see the list of subreports for this main report on the Subreport tab.
Performance Considerations
You will notice that the subreport has a template with a SELECT statement in it. This means that, for each detail line in the main report, a separate SELECT statement will be performed from the subreport. So a report that contains 100 detail rows will execute 101 SELECT statements, even if only one of the detail row generates subreport information. Obviously, this can cause a performance issue if there are many detail rows in the main report and/or multiple subreport levels.We make the following suggestions to help minimize any performance issue:
- Make subreport optional. That way, users can skip the subreports entirely if they want to.
- Keep the subreport SELECT statement simple. The less work the subreport SELECT statement does, the better the performance.
- Avoid multiple and/or nested subreports, unless they are optional.
- Make sure the key mappings between the main report and the subreport use the SQL indices if possible.
Rendering to CSV or XLSX Format
The subreport portion(s) of a Report Desk report are ignored when the selected output format is CSV or XLSX. That means that only the detail (including group headers and body lines) will appear in the output. When you try to generate a report to CSV or XLSX that has a subreport, the software will ask you to confirm that you want to run the report anyway.
If you want CSV or XLSX output to include the data in the subreport as well, you will need to create a new report that has the original report's subreport as the body of the new report and the original report's body as a new group in the new report. As an example compare the PRTAXLST reports which use subreports to the PRTXCITY and PRTXSTAT reports which have a group instead of a subreport.
JEG
Program: EL860RD