Ranges' placement in XL Report

As we have said previously, the number of ranges (i.e. number of datasets) is not limited, and there is no restriction on the number of datasets that place the only record in a report - NoRange-Datasets. Once again, there are no constraints on quantity. There are constraints on range placement. Why?

XL Report sees a range as a single whole, so rows are inserted into range and not into worksheets. It leads to the simple conclusion - ranges cannot intersect. And what's more, they cannot have common columns if the upper range is narrower than the lower one or ones. The illustration shows you the result of inserting rows in the blue range. Because this rule was broken, the gray range has lost one of a range's main requirements - continuity.

The next illustration shows you several variants of a ranges' placement in a report. So the conclusion: any underlying range can have common columns with an overlying range only when the columns of the underlying one are a subset of the overlaying range's columns.

Nested ranges - designing master-detail reports

Where? QDemo.dpr form: frmMD1; unit: fMD1; template: tMD1.xls

XL Report allows you to create complex reports by making use of nested ranges. This way you simulate the subordinate relations of your data.

We have created a new form, added three instances of TTable and bound them to DBDEMOS (Customer, Orders, and Items). Then we have bound them with each other through the MasterSource property. In such a way we have created a data structure that allowed us to look through orders of every customer. To view the data we have added three DataSources, several edit fields for Customer and two grids (TDBGrid) for Orders and Items. To get the data in a report we have added an instance of TxlReport. Now the template. The Items table resides in the lowest level of subordination structure. So its range (we have decided that it should be a list range) must be the innermost one and the first range to start with. We have added field formulas and headers, and gave a name for the range - ItemsRange. Note that this range is a list range.

To include orders' data we have added an empty row above the header row for ItemsRange and specified the field formulas for Orders datasource, selected the range, which included this row, the header row for ItemsRange, ItemsRange itself, and one more empty row below it (it would serve as an option row for Orders' range), and named the selected range - OrdersRange. Then we added the header row above the OrdersRange. The question is: what sort of range is OrdersRange? When answering this question you should exclude all inner ranges (the ItemsRange in this case) out of consideration. The answer is: the OrdersRange is an arbitrary range because of presence of the header row of the ItemsRange.

Then we added another two rows above the header row of the OrdersRange: one of them became the header row of the range to be, and field formulas for customer's data (CustNo and Company fields) were specified in the other one. Then we selected the last one, the header row of the OrdersRange, OrdersRange itself (recall that it included ItemsRange at this moment), and an empty row - it's another option row - and gave the name for the selected range - CustRange (see the picture at the following page). Note that the CustRange is an arbitrary range because it contains more than one row per record; namely, it contains the header row of the OrdersRange. To say truth, we had no special need in so many empty cells at the left of the template. It's just a design solution. But you should remember that a) leftmost columns of all nested ranges must coincide with each other thus forming the common option column for all nested ranges and b) rightmost columns must coincide too.

The template was saved as tMd1.xls and bound to the xlReport component through the use of the XLSTemplate property.

Then three items were created in the DataSource collection of the xlReport and were bound to corresponding datasets (Dataset property) and ranges (Range property). To reflect the subordinate structure of our data we used the MasterSource property: because the OrdersRange was nested in the CustRange, the item bound to Orders must have a reference to the CustRange in its MasterSourceName property, and again, because the ItemsRange was nested in the OrdersRange, the item bound to Items must have a reference to the OrdersRange in its MasterSourceName property.

The following is the full set of rules you will use designing master-detail reports:

• Every range must stick to the above given continuity rules
• Every range must have an option row of its own
• Every range in a nest of ranges must be of the same width and their left (and right) borders must be coincident
• The leftmost column is an option column for every nest of ranges
• There is no restriction on the number of ranges in a nest
• The size of a generated report cannot exceed the size of a worksheet <g>
• An inner range can be placed between any rows of an outer range
• Every range (minus inner ranges) is considered to be a single whole and is either a list range or an arbitrary range whose restrictions were given in corresponding chapters

Totals in a column in master-detail reports

Where? QDemo.dpr form: frmMD2; unit: fMD2; template: tMD2.xls

Now we show you the use of the option row in nested range. We derived this template out of the previous one by making small changes to the option row of the ItemsRange range. Both the template and the report are shown below.

We remind you that summary options can work in a list range only (the ItemsRange is a list range - it contains only one data row, the option row and option column plus it has the header row above). Can you get totals in a column of a range which is torn apart by included ranges? Yes. Look at OrdersRange range again. It isn't a list range. But we put the Sum option into the option cell of the AmountPaid column and get the report.

How does it work? Well, after excluding the ItemsRange (the row with field formulas and the option row) out of consideration, you see that the OrdersRange contains three rows: the one with field formulas, the header row for now excluded ItemsRange, and the option row.

In this case, summary options take only the first row of such a range into account. It contains the field formula for the AmountPaid field thus allowing calculating the sum.

Sorting nested ranges

Where? QDemo.dpr form: frmMD3; unit: fMD3; template: tMD3.xls

You can use the Sort option at the lowest level of nesting. We remind you that this option work in a list range only. The following template is designed to view the list of items in every order sorted ascending.

Multiple-sheet master-detail reports

Where? QDemo.dpr form: frmMD4; unit: fMD4; template: tMD4.xls

To create a multiple-sheet report you should specify the name of a master dataset in the MultisheetAlias property and the name of one of the dataset's fields in the MultisheetField property. XL Report, being activated via the Report method, will create the report workbook with as many worksheets as there are records in the dataset; each one is named after the corresponding value of the MultisheetField.

We have taken the template of the previous example and cleared the Range property of the item in the DataSources collection that is bound to the Customer table. Then we have specified "tblCustomers" in the MultisheetAlias and "Company" in the MultisheetField. The most significant part of the resulting report follows:

When creating a multiple-sheet report, XL Report loops through records of the MultisheetAlias and creates worksheets named after the value of the supplied field (MultisheetField). Each record triggers the full set of events for datasources (including macro calls). You are allowed to think about this process as about building several different reports. This mechanism is transparent for all VBA macros and event procedures. Naturally, this transparency has required bringing in some restrictions on design of templates. Here they are:

• A template for a multiple-sheet report must contain only one worksheet;
• If the MultisheetField contains identical values, then the names of resulting worksheets has additional numeric identifier added;
• The OnlyValues option will be applied to every report worksheet;
• For every report worksheet, every Name object referencing the report worksheet will be destroyed
• The developer is responsible for uniqueness of the names created in macros and event procedures (names of pivots and charts, for instance).

Time to look back - complex reports

Now you know that:

• XL Report imposes some limitations on placement of ranges in a template.
• It allows creating reports based on data linked with one-to-many relation through nesting of ranges and the MasterSourceName property.
• You can get totals in a column of a nested range using summary options.
• You can use the Sort option in the list ranges only at the lowest level of nesting
• XL Report allows creating multiple-sheet reports. Every record of a master dataset supplied in the MultisheetAlias property causes creation of a new (separate) worksheet named after the value of the field supplied in the MultisheetField property. Design of such reports has some limitations cited in the "Multiple-sheet master-detail reports" chapter.