Range-datasources - data in ranges

We have addressed how Active XL Report handles datasources in the previous pages and introduced the term - NoRange-datasource. This term is derived from the empty value of the Range property of a datasource. Such datasources are used when you need to report the data from one and only one record - namely the current record.

But what if you need all of the records? In that case, fill the Range property with the name of a range of cells in a template workbook and it becomes a Range-datasource. It's that easy. The records are placed in the range whose name is supplied by you. Thus Active XL Report takes great advantage of the Range object, a basic object of Microsoft Excel used in a large number of Excel Type Library methods and in VBA as well. Active XL Report further differentiates between two range types. The first one, a list range, is a range where each row represents the data of only one record. Such a range must have a header containing one row (note: the header isn't included in the named range but is an essential part of a list range). To put it another way - a list range must suit the definition of a table in a relational database, which is referred to as a list in Excel terminology. See Appendix A or Excel Help for Microsoft's recommendations on creating a list. The second range type is an arbitrary range, where every kind of formatting is allowed. In these ranges you can merge cells, skip rows and columns, omit the header, and so on. The OrdersRange range in Quick Start is an example of such an arbitrary range.

In summary, an Active XL Report range definition meets the following standards:

• it has a name.
• it is a contiguous rectangle.
• its cells can contain any allowed value (and can therefore be empty, softening the requirement for contiguity).
• it must contain at least two rows and two columns.

Note:
The leftmost column and bottom row (they are called option column and option row) of a range are treated in a special way. Having built a report, Active XL Report clears the option column values and destroys the option row (except for summary options case - see below).

List range

Where? form: frmRange1; template: Range1.xls

You can use all Excel formatting features in list ranges except for cell merging. In this example, we have converted the OrdersRange range from Quick Start into a list range. You can see that the range now contains only one row of the field formulas as well as the option row and column.

We applied a date format to the cells referencing SaleDate and ShipDate fields, then checked the Use 1000 Separator ( ) checkbox for Items Total and Amount Paid fields. We left unchanged the conditional format of the Payment Method field. You can see the template and the report in the figures below. Note the difference between the OrdersRange ranges selected in both figures. The range in the report now has no option row and contains the transferred data. In a report, you can refer to the data by the range name, and perform any operations needed with VBA.

While processing any type of range (including a list range) Active XL Report works in the following order:

• Inserts the required number of rows into the range (not rows into the sheet! This can be checked by inserting an arbitrary value into the cell to the right of the template range).
• Transfers all available records into the rows added.
• Applies all formats of the template cells to the range.
• Deletes the template cells.
• After processing all options (see below), deletes the option row, except for the case of summary options.

Range options and column options

Where? form: frmRange2; template: Range2.xls

In addition to data transfer, Active XL Report incorporates a number of other highly useful features. For instance, you can sort, total, and group your data, filter rows, and create pivot tables among other things.

To use these features you need only to change a template workbook using the heretofore-unused option row and column.

Let's take the previous example, open the template and write down Sum just below the field with the field formula for the Amount Paid field. We checked the Use 1000 separator ( ) checkbox in the Properties of this cell. To complete the task, write down Total in the adjoining cell. You can see the part of the report that was changed. Instead of the Sum option, you see the sum total for the Amount Paid column. The cell is formatted according to the formatting of the corresponding template cell. The formula in the report cell is =SUBTOTAL(9;:..

This was an example of column option use. Such options are applied only to the column they reside under. The Sum option is the most commonly used. Other options are described later.

Range options describe actions to be taken over the range as a whole. They are to be specified in the first cell of the option row. We have put down AutoFilter and OnlyValues strings, separated by a semicolon, into this cell. It's easy to deduce that the first one should apply the AutoFilter command to the current sheet. The result should look like:

OnlyValues allows you to substitute all formulas of the range with their values. Check the Amount Paid grand total cell to verify this statement. Using this option, you can "turn off" all formulas of a range, a worksheet, or a report workbook. So you see that worksheets and reports have their options.

Formatting an arbitrary range

Where? form: frmQuickStart; template: QuickStart.xls

You probably know by this time that an arbitrary range is a range that does not meet list range specifications (see Appendix A). As we said before, you can merge cells in an arbitrary range or its header, or omit the header. We refer you to Quick Start once again, as it shows almost every possible formatting feature. We also say again that arbitrary ranges are well suited for primary documents (such as invoices) especially if you want to pretty them up.

Q: If an arbitrary range is deprived of list range capabilities, then what will calculate the things needed?

A: VBA.

Range-datasource data in Excel formulas

Where? form: frmRange3; template: Range3.xls

When working in Excel you have probably come across the necessity of copying formulas across several cells. Then you probably know that the known effect works because of relative cell references in formulas. Active XL Report works the same way. Let's look more closely at the Quick Start example.

There is a formula =I13-L$4 in the J14 cell of the QuickStart.xls template. Look at the Special Tax cells in the generated report. Relative referencing is preserved and calculated values are right. So you can create almost any formula that is permissible in Excel.

We demonstrate the only restriction in the same example. Enter the formula =K13/10 into the L17 cell of the template. Save the template, close it and run the report. You might suppose that the appropriate cell residing under the OrdersRange must contain a value equal to Amount Paid from the first row of the range divided by 10. But instead of the value you see the #REF! error message. Why? The reason is simple - according to the schedule the template cells must be deleted after data transfer. In our case the K13 cell was deleted and so the formula referencing this cell became erroneous. Conclusion: you can't refer the range's cells outside of the range.

Simple range options

Where? form: frmRange4; template: Range4.xls

All through the past several chapters, you prepared to begin to use the range options. Do you remember that these options must be put in the lower left cell of the range and separated by semicolon? Here we list the simplest range options:

• OnlyValues - as it was said earlier, this option means substituting all formulas in the range for their values.
• AutoFilter - applies the AutoFilter command (Data|Filter|AutoFilter menu in Excel) to list ranges. The use of this option in arbitrary ranges leads to an error.
• RowsFit - this option automatically makes the range row height fit the range cell's contents. It can be done manually - select the rows needed and double-click on the boundary between any of the selected rows. This option is especially useful if you want to output strings containing the carriage return.
• ColsFit - The effect this option produces is obvious. So this option is especially useful if you want to output strings whose lengths are unpredictable.

Just for a test, let's shrink all range columns in the template of the previous example down to just one or two character width. Having this done, put ColsFit option in the lowest left cell of the range, save the template, close it, and run the report. The columns must be extended to their optimal width.

Time to look back - Range-datasources

You already know that:

• An Active XL Report datasource with non-empty Range property is called a Range-datasource.
• The range that serves as a data receiver must have a special format. Minimal range size is two columns in two rows.
• There are two types of ranges in an Active XL Report: a list range and an arbitrary range.
• A list range must meet all the requirements of an Excel list (see Appendix A)
• The leftmost column and the lowest row of an Active XL Report range are reserved for range options and column options. The range options should be put into the range's lower left cell, while column options should be put into the column's option cell.
• An option is a string value processed by Active XL Report. You can use several options at once if you separate them by a semicolon.
• Arbitrary formatting (including conditional formatting) can be applied to Range-datasource cells. However, you cannot merge cells in list ranges.
• Active XL Report processes a range according to the schedule:
• Inserts the required number of rows into the range (not rows into the sheet! Can be easily checked - just insert an arbitrary value in the cell to the right of the lower row of the range).
• Transfers all available records into the rows added.
• Applies all your template cells' formats to the range.
• Deletes the template cells.
• After processing of the options, deletes the option row, except for the case of options that allow you to insert totals - summary options.
• You can use common Excel formulas that can reference the field formulas. You cannot reference a range's cells in out-of-range formulas.
• You can insert a sum for a column automatically by using the Sum option in the column's option cell.
• Simple options can help you to fit your row height (RowsFit) and column width (ColsFit), to turn off all formulas (OnlyValues), and to turn on the AutoFilter (AutoFilter) in a range.