Sub Reports are used to bring in more information if the Form you use has missing data points. Essentially this creates a "join" between the reports and, when the report runs, it shows the data from the Sub Report as well.
Keep the following information in mind before you use Sub Reports.
- Sub Reports are most efficient on single-record reports. If your report lists many rows of data and you connect one or more Sub Reports to it, you drastically decrease the performance of the report because Sub Reports also must call the database for each row returned. With several Sub Reports attached, you can triple or quadruple the number of calls to the database. If your report has 1000 lines and three Sub Reports, the result is 4000 calls to the database.
- Seek different report Forms that would better suit your needs before adding numerous Sub Reports. Using Sub Reports risks the report timing out.
- If you have multiple columns needed from Sub Reports, plan how to best achieve this. For example, if columns from the Work Order Form are in a Sub Report, a better plan is to have all those columns in the main report and return a single Sub Report of the columns rather than each column having its own Sub Report.
- Try to choose a less data-intensive report Form. For example: You have a Trade Code but need a Trade Description. You can join the Work Order Form to get that column, but you can also join the Trade Form by Trade Code. Joining the Form as a Sub Report, even if you aren’t using the data, still loads all the data related to that Form. When connected to the work order, you pull in all work order transactional data. Whereas, if you use the Trade Form, you have a smaller amount of data.
Tips
Ideally, the most unique field available to link the Sub Report to the Main Report is the best way to go.
Try to match the number of columns in your Sub Report to the same number of columns on your Main Report even if you are only bringing in one field from the Sub Report (it seems to align better on the output).
When linking a Sub Report to a main report, try to use a "unique" identifier (tag number, building code, WO #) something that has a one-to-one relationship.