Range-datasets - data in ranges

We have addressed how XL Report handles datasets in the previous pages and introduced the term - NoRange-dataset. This term is derived from the empty value of the Range property of a DataSources item. Such datasets 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 in a template workbook and it becomes a Range-dataset. It's that easy. These datasets are used when you want to output all records of a dataset. The records are placed in the range supplied by you. Thus 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. 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 the 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 XL Report range definition meets the following standards:

• it has a name.
• it is a continuous rectangle.
• its cells can contain any allowable 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 (option column and option row) of a range are treated in a special way. Having built a report, XL Report clears the option column values and destroys the option row (except for summary options case - see below).

List range

Where? QDemo.dpr form: frmRange1; unit: fRange1; template: tRange1.xls

You can use all Excel formatting features in list ranges except for cell merging. In this example, we 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 fields SaleDate and ShipDate, 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 report in the figure below. Note the difference between the OrdersRange 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) 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 any cell to the right of the range).
• Transfers all visible records (active filters and SetRange boundary conditions are taken into account) into the rows added.
• Applies all formats of the template cells to the range.
• Deletes the template cells.
• After processing all options, deletes the option row, except for the case of summary options.

Range options and column options

Where? QDemo.dpr form: frmRange2; unit: fRange2; template: tRange2.xls

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

To use these additional features you need only change a template workbook specifying range and column options in the heretofore unused option row and option column respectively. An option itself is a string recognizable by XL Report analyzer.

Let's take the previous example, open the template and write down Sum (it's a column option) just below the field with the field formula for the Amount Paid field. We checked Use 1000 separator ( ) checkbox in the Properties of this cell. To complete the task we wrote down Total (it's just a string) 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.

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 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 lets you 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 sheet, or a report. So you see that sheets and reports have their options.

Formatting an arbitrary range

Where? QDemo.dpr form: frmQuickStart; unit: fQuickStart; template: tQuickStart.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 a 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.

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

Quick answer: VBA.

Range-dataset data in Excel formulas

Where? QDemo.dpr form: frmRange3; unit: fRange3; template: tRange3.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. 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 range 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? QDemo.dpr form: frmRange4; unit: fRange4; template: tRange4.xls

All through the past several chapters, you prepared to begin to use the range options. Do you remember that these options (range 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 exception.
• 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 can 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 cols 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. Does it work?

Time to look back - Range-datasets

You already know that:

• XL Report datasets whose corresponding DataSources item Range property are not left empty are called Range-datasets.
• An XL Report range, which serves as a data receiver, must have a special format. Minimal range size is two columns in two rows. The leftmost column and bottom row of a range are treated in a special way as they are used in specifying additional actions over a column or a range that XL Report performs.
• There are two types of ranges in an XL Report: list range and arbitrary range.
• A list range must meet all the requirements of an Excel list (see Appendix A)
• The leftmost column and bottom row of a range are reserved for range options and column options. 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 recognized by 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-dataset cells. However, you cannot merge cells in list ranges.
• 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 visible records (active filters and SetRange boundary conditions are taken into account) 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.