Subdata Tables

The purpose of the Subdata Table element is to create a table that shows the child records from a hierarchical result set, or a "table within a table". This is useful for creating sub-reports and drill-down reports. This topic discusses the techniques required.

• "Drilling Down" Using a Subdata Table
• Grouping Data with Subdata Layers

Many developers find it easier to use a subreport (see SubReports) that includes a regular data table, rather than a subdata table. One advantage to this approach is that a subreport, which is a separate report definition, can be independently developed and tested, and then embedded in a parent report using the SubReport element. This technique works well with the More Info Row element (described below) and does not require hierarchical data.

"Drill Down" Using a Subdata Table

When presenting summarized data in table, there's often a need to "drill-down"; to display the supporting data. Sometimes this means displaying an entirely different detail report, but frequently the detail data can be shown right within the original data table. Here's an example:

Shown above is a data table that presents customer information, including their order totals. If more detailed information about a customer's orders is desired, the Customer ID value has been configured as a drill-down link.

When the Customer ID link is clicked, the main table expands downward, as shown above, and a second or "sub" data table, with order information for the selected customer, is displayed within it. Clicking the link again will hide the subdata table. A More Info Row element provides the expansion capability. For more information, see Data Table Rows.

Group Data with Subdata Layers

The first step in creating drill-down functionality, like that shown above, is to include the More Info Row element and Sub-Data Table elements in your report.

The definition example shown above includes a main data table, the More Info Row element ("miOrders") and a Subdata Table element ("sdtOrders"). The configuration of the More Info Row's attributes os shown.

In v11.0.212, the More Info Row Column element was introduced. This element can be used to closely align More Info Row columns with the columns of its parent table. When this element is used, the More Info Row element's Span First Column and Span Last Column attributes are left blank, and the More Info Row Column element's Column Span attribute is used instead to control spanning of parent table columns.

Subdata Layer element ("sdlCustOrders") has been added beneath the Subdata Table; it provides a container for a regular datalayer element ("dlCustomerOrders").

The datasource used by the regular datalayer element beneath a Subdata Layer element has to be the same as the datasource used by the main data table. In other words, they both get their data from the same place. This generally means that the same type of regular datalayer element will be used for both, so, if a DataLayer.SQL element is used to retrieve data for the main table, then a DataLayer.SQL element will be used with the Subdata Layer element.

Finally, a Subdata Layer Relation Column element is added beneath the Subdata Layer element. Its attributes are set, as shown above, to create the relationship between the data in the main table's datalayer (Parent Column) and the data in the sub-datalayer (Child Column).

The practical effect of this is to create a single, hierarchical XML data object that contains child data records interspersed within their parent data records.

Depending on the data returned to the sub-datalayer, it's possible that some child rows will not have a matching parent row; these are "orphan rows." It may be possible to ensure that there will be no orphan rows through, for example, the right SQL query or external validation of the data. If that's the case, then disabling error-checking in the Subdata Layer, by setting its Orphan Row Existence attribute to False, may yield faster performance. Otherwise use the default, True, to prevent errors.

Data Table Column and Label elements that are typical for any data table, as shown above.