Ranges' placement in Active 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-Datasources. Once again, there are no constraints on quantity. There are constraints on range placement. Why?

Active 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? form: frmMasterDetail1; template: MasterDetail1.xls

Active 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 and added a DataList to display the Company field from the Customer table and two DataGrids to view Orders and Items respectively. Then the Active XL Report was added and named ocxXLReport. Bearing in mind the subordination of tables (Customer - Orders - Items) and names of respective datasources (Customer - Orders - Items) we went to 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 - CustomersRange (see the picture at the following page). Note that the CustomersRange 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 MasterDetail1.xls and bound to the ocxXLReport component through the use of the Template 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
• 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

Then three datasource were added to the ocxXLReport and were bound to corresponding ranges through their Range properties. To reflect the subordinate structure of data we used the MasterSource property: because the OrdersRange was nested in the CustomersRange, the Orders datasource must have a reference to the CustomersRange in its MasterSourceName property, and again, because the ItemsRange was nested in the OrdersRange, the Items datasource must have a reference to the OrdersRange in its MasterSourceName property.

Master-detail report generation goes through two stages. The first one is recognizing your datasets' structure; the second one is supplying Active XL Report with actual data. The first stage is completed when your call any of reporting methods (Report or ReportTo): you pass your datasets to the method chosen but Active XL Report takes only their structures and not data. At the second stage, the AfterMasterMoveNext event occurs that allows you to specify concrete data for all datasets. In this event Active XL Report pass you the current Master-dataset and gets the correspondent Detail-dataset from you. Here follows the example of this event handler implementation in case of ADO recordset:

Totals in master-detail reports

Where? form: frmMasterDetail2; template: MasterDetail2.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 the 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? form: frmMasterDetail3; template: MasterDetail3.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 reports

Where? form: frmMD4; template: MasterDetail4.xls

To create a multiple-sheet report you should supply Active XL Report with MultisheetSourceName and MultisheetFieldName in corresponding properties.

We have taken the template of the "Nested ranges - designing master-detail reports" example and cleared the Range property of the Customers datasource. Then we have put Customers in MultisheetSourceName and Company in MultisheetFieldName. The ocxXLReport.Report call was left unchanged.

Then we have compiled and run the project. The most significant part of the resulting report follows:

When the Report method is called, XL Report loops through records of the MultisheetSourceName's dataset and creates worksheets named after the value of the field. Each record triggers the AfterMasterMoveNext event for every top-level Range-datasource in datasources' subordination hierarchy. Also every record MultisheetSourceName's dataset triggers "before" and "after" macros for every datasource thus way allowing you to think about this process as about building several different reports. This mechanism is transparent for all VBA macros in datasources. 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 MultisheetFieldName field 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:

• Active 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
• Active XL Report allows creating multiple-sheet reports. Every record of a MultisheetSourceName dataset causes creation of a new (separate) worksheet named after the value of the MultisheetFieldName field. Design of such reports has some limitations cited in the "Multiple-sheet reports" chapter.