Share via

Working with Related Tables using Multiple Detail Bands in Reports

  • Article

REPORT FORM and LABEL FORM are scoped commands. When you use any command with a scope clause, you can specify a record range and/or filtering conditions for records on which the command should act. If you do not specify a scope explicitly, each scoped command has a default scope. For reports and labels, the default scope is ALL.

Scope conditions and ranges are ordinarily evaluated with respect to, or driven by, the records in a single work area. In reports and labels, this means the Report Engine processes your report or label layout's detail band once for each record in your report scope. If your report includes group expressions, the detail band scope is the set of records belonging inside the innermost group break. When a group break occurs, the Report Engine pauses in its processing of the report scope, takes some other actions you specify, and then continues on through the scope. Otherwise, the detail band scope is equivalent to the report scope.

When you set up data for your report or label, you can relate other tables and cursors to the selected work area, or driving alias for the report. Along with standard Visual FoxPro scoping behavior, the Report Engine also provides the ability to make multiple passes through each detail band scope based on these relationships.

The Report Designer represents these multiple passes through the detail band scope by showing you multiple detail bands. You use different bands to display information for tables or cursors that are targets of different relationships.

Refer to the section on Working with Report Bands for information on how to adjust your report layout to indicate what related work areas are significant to report scope processing. This topic discusses how the Report Engine handles the multiple tables when you include these features in the report layout.

Using Target Aliases to represent Data Relationships

The target alias of a detail band refers to an open table or view in the current data session. In the Report Designer, target alias information may look like group break expressions, but it is quite different. When a group breaks, the record pointer moves forward. When the Report Engine moves to another detail band and re-evaluates the target alias, the record pointer moves back to the beginning of the detail band scope.

How the Report Engine validates Target Aliases at Run time

The Report Engine considers the driving alias of the report plus the Target alias attribute of each detail band to determine how to process each band. Before beginning the report run, it validates any target alias expressions contained in the report. To be valid, a target alias expression must evaluate to one of the following:

  • An empty string.

  • The alias of a table or view in relation to the driving alias for the report.

  • The driving alias for the report.

The Report Engine evaluates any expressions you have used as target aliases in your report layout, before it begins processing your report. If these expressions do not evaluate to aliases currently in use, the error "<alias> not found" occurs. If an alias is in use, it must either be the same as the driving alias or related to the report's driving alias. Otherwise, the error "<alias> is not related to the current work area" occurs.

Detail Bands with Target Aliases

The driving alias for a report with related tables is typically the parent in one or more data relationships. For example, a Customer table is related to an Orders table and a Payments table.

A report layout driven by this Customer table indicates these relationships by including one detail band with the target alias expression "Orders" and second detail band with the target alias expression "Payments".

Important

Include the quotation marks in each target alias expression, unless Orders or Payments is a variable holding the name of the actual alias.

For each detail band with a target alias, the Report Engine remains on the parent record and processes all the children in the appropriate target alias related to the current parent record. Then it moves to the next band, and processes the set of children in the next related table.

Detail Bands without Target Aliases

In reports with multiple tables and target aliases, you can use an empty target alias when you want a band to be processed only once for each driving alias record. This technique gives you a handy way to provide some summary information, similar to group headers and footers, but positioned more flexibly in your report output.

For example, in your Customer report, you might provide a data group on Customer ID, which enables you to provide information about the Customer in the group header or footer. By using a detail band with no alias, placed between the detail bands for Orders and Payments, you can provide additional information about the Customer, such as a string displaying current account status. Because this band has no target alias, its contents will appear only once before the Report Engine goes on to display Payments details.

Detail Bands with Target Aliases matching the Driving Alias

In most cases, setting a target alias to an expression matching the driving alias for a report results in the same behavior as an empty Target alias (the band is processed once for each record in the scope).

However, the Report Engine makes an important exception if you use this technique in the first detail band of the report: if the first band has a target alias exactly equal to the driving alias, it processes all the records in the detail scope, resets the record pointer in the driving alias, and then moves to the next detail band.

Using the driving alias as the target alias for the first band provides a way to process all the records in a current group or report scope multiple times. It also provides the ability to calculate some values for the current group before going on to display any content for the individual records. This technique is discussed further in the next section, Variables and Calculated Fields for Related Tables.

Automatic One-to-Many Behavior

Having validated any target alias expressions you have used for your detail bands, the Report Engine checks to see if you have explicitly set any OneToMany properties in your DataEnvironment or, if you are opening the tables yourself, whether the driving alias has any SET SKIP relationships set. If there are any already set, the Report Engine will not change your environment. However, if you have not set any one-to-many relationships explicitly, the Report Engine sets it up for your target aliases, and removes it at the conclusion of the report run. This automatic one-to-many behavior occurs only when you use at least one target alias in the report.

You can scope calculated fields and report variables to each of the various detail bands in a report layout, associating their dynamically-calculated results with the target aliases in each band.

Calculated fields in reports and report variables in Visual FoxPro versions previous to 9 could be Reset at the end of group(s), end of page, or end of report. The Reset at value indicated at what point in the report the Report Engine set these items back to their initial values, or on what basis their calculations were performed.

Another way of expressing this idea is to say that calculated fields and report variables are data-scoped based on their Reset values. In Visual FoxPro 9, the Reset at labels in Report Designer dialog boxes have been changed to Reset based on, to clarify this behavior.

In a report with multiple detail bands, you can scope report variables and calculated fields based on detail bands and their associated target aliases. For example, in your Customer report, you could scope a variable set to Count based on the detail band with the target alias Orders. This variable would provide a count of orders for each Customer. Because it is scoped to a band with correct target alias, this variable will provide a correct count. Without target alias expressions, the count would not be correct in cases where the Customer had more Payments than Orders.

Preprocessing report calculations

Because you can set up multiple detail bands with the same target alias, you can use one band to handle report calculations before displaying any content. In the Customer report example, you could have two bands with the target alias Orders. You could reset two report variables, OrderTotal and OrderCount, based on the first band but place all your report expressions displaying content from the Order table in the second band. The resulting calculations would allow you to provide a count and total of all orders in the detail header of the second band.

Using preprocessed report variables

When report variables are reset based on a detail band, their values remain correct after the Report Engine stops processing that band. Their values are not reset until the next time the Report Engine begins processing the same detail band (or its associated detail band header, if there is one) again. Any calculated results in these variables can be used in further calculations as you display information in additional report bands.

For example, you could use the OrderTotal variable to display a percent of total calculation, for each order in the second Orders band. After the Orders bands, you could also use the OrderTotal to calculate a running balance in the Payments band.

Preprocessing report variables based on the driving alias

Many people use a SQL-SELECT statement to produce a single driving cursor with fields sourced from multiple tables. Even with one cursor, you can still find it useful to set up multiple detail bands to process the same cursor multiple times. By using the driving alias as the target alias for the first detail band in a report, you have the opportunity to calculate values as if the values in the driving alias were repeated in a separate table.

See Also

Tasks

How to: Specify Target Aliases for Detail Bands

Reference

Scope Clauses
SET RELATION Command
TARGET( ) Function
SET SKIP Command
Relation Object
ChildAlias Property
OneToMany Property