Sub Reports

Sub reports are used to query and retrieve alternate data from the database. Alternate data is best thought of as any data unavailable in the main report information category, or as data that is available in the main report category but there is a need to sort or select the data differently than it appears in the main report. Sub reports can be inserted into one or more existing regions of the main report.

Sub report queries, of which there can be several within a single sub report, with multiple sub reports defined within a sub report, are each based on their own independent information category, or alternatively based on written SQL, and may be sorted and selected independently of each other, sub reports and independent of the main report itself.

Examples of the uses of sub reports:

• You are creating a purchasing report based on the purchase order number and the items within a PO. The data relationship of the PO to the item is one to many, i.e., one PO and many items. This relationship is technically referred to as Master (PO) to Detail (Items). However, for each PO item you wish to display the actual descriptive text. Upon examination you discover that each line of description has a detail relationship to the unique PO item. To include both item details and description details in a single information category would create a double master to detail relationship (PO to Item(s) and Item to Description(s)), which can create a phenomenon known as data duplication. The effect of data duplication is to cause the appearance in the report of one PO item for each line of descriptive text. Therefore, it would be prudent to create a sub report based on item description information and nest that sub report so that it executes after each occurrence of a PO item. This way, you can uniquely manage multiple occurrences of detail with relative ease.
• You are creating a basic Budget to Actual report which displays in summary fashion the year-to-date values of budget, expenses, encumbrances and balance remaining for a particular Org. Key (accounting) and a series of objects (salaries, office supplies and hardware). However, you decide that directly after you list the year-to-date summary information for each object, you would also like to list the transactions which comprise the summary totals. You would simply create a transaction-based sub report and nest it after the occurrence of each object summary.