Quick Start

Where? form: frmQuickStart; template: QuickStart.xls

As stated earlier, to create a report you must first create a report template. Let's open QuickStart.xls.

The first thing you will notice is that there are a lot of #NAME? errors (if there isn't enough place for this text Excel replaces it with # signs). This is normal. Pick any such cell and look at the Formula bar. Almost all the formulas include the pattern =DatasourceName_FieldName (the underscore is required) - we call such a pattern a field formula - where DatasourceName is equal to Customers or Orders and FieldName - one of field names of the datasource to be. Since Excel cannot find these cells in the workbook immediately it displays the #NAME? error.

Note
A field formula must conform to Excel naming convention. See Appendix B that implies some restrictions on field names used in report generation.

You can apply any formatting to any workbook cells, insert pictures, and modify any of the parameters of the workbook itself. In this example, we have turned off the zero values display and hidden the gridlines (Tools|Options). Active XL Report will preserve all changes to the template.

To produce a report of all orders for the current customer we have created a specially formatted range - a set of cells in Excel terminology - B11:L15. Looking at the design of the template you can see that certain cells have been merged and some columns have been hidden. The hidden cells D15 and E15 contain the formulas - =Orders_ShipToAddr1 and =Orders_ShipToAddr2 respectively. The values of these cells are used in the formula of cell G15 - = "" & D15 & " " & E15 - to create the full shipping address. As a reminder - it is permissible to use all of the standard Excel formulas as well as functions, references, etc. This report includes a Special tax that is less than the "standard" Tax Rate by the value in cell L4. The formula for this Special tax rate - =I13-L$4 - can be seen in cell J14 (Note the use of $ character in referencing the L4 cell - this is an example of an absolute reference. See Excel Help for details).

We have frozen the top horizontal pane just under the report head (Window|Freeze Pane). Then we have added colors to the template. To distinguish the orders honored by Visa cards, we have applied conditional formatting to the G13 cell that contains the payment method - =$G13="Visa". To see this, select this cell and choose Format|Conditional formatting. So, for Visa orders, the field value will be displayed in a different color. The same trick was used for the Special tax cell. As the result, all negative values in this cell are shown in red.

As the final step in our work on the template, the A13:L16 range was named OrdersRange. You can find it in the Name box at the left of the Formula bar. Select this name from the Name box and you will see this range selected in the worksheet. Note the empty column at range's left and the empty row below. These cells are used in Active XL Report to describe additional actions that used to manipulate the data. They have been left empty in this example.

Why A13:L16?
An XL Report range consists of three regions: the first one describes the output format for a single record of a dataset bound to the range, the second one is an additional row just below the first region (option row), and the third is an additional column at the left of the first region (option column). While processing the dataset, XL Report multiplies the first region as many times as needed to output the dataset. This description implies that header row(s) containing column labels must not be included in the range. Nevertheless, the header row must be placed above some of XL Report ranges. See the "List range" chapter below.

The Active XL Report setup procedure adds XLReport to the list of available ActiveX controls. We have added this control to the frmQuickStart form and called it ocxXLReport. To set its properties, we have opened its property pages either by selecting Properties in its pop-up menu (VB) or through the Custom property (Access). There must be a link between the XLReport component and the report template. To create the link, we have set the Template property of ocxXLReport to the path and file name of the template. At the property page we have also defined two datasources for the template. The first one is Customers and the second one is Orders. The OrdersRange range defined in the template was assigned to the Orders datasource. That was all with the component.

Note
The correct setting of the Template property ensures the right workbook opening. If this property is empty then Active XL Report launches Excel and opens a new empty workbook. If you set it to the file name only, the program attempts to locate the template in the current folder. You can also use relative paths, e.g. "..\Templates" or ".\Templates".

Two buttons - cmdTemplate and cmdReport - were added to the form with the code intended for opening the template and creating the report accordingly.

Note
All code samples in this document are taken from the xlrDemo4VB demo project that uses ADO. For examples of Active XL Report usage with DAO see the xlrDemo4Access demo project.

The Report method gets an array of ADO Recordsets as a parameter. You can pass also DAO Recordsets, RDO Resultsets and unbound data, all of which will be referred as datasets further on.

Test the form by building reports for several customers. Note the report workbook name - it is created by adding serial numbers to the name of a report template.

Creating Active XL Report datasources dynamically

Where? form: frmQuickStart2; template: QuickStart.xls

To demonstrate the use of dynamically created datasources we took the previous form and emptied the XLRDatasources collection of the ocxXLReport component by deleting existing datasources. Then we rewrote the Click event as follows:

First, we link the xlr object variable to the ocxXLReport control and set its properties. Second, the Datasources collection is cleared (just a precaution). Then two datasources are added - Customers and Orders. The last one is bound to the OrdersRange range from the template. Then the current record in the rstCustomers recordset is set to reflect user's choice. Finally, the Report method assigns the rstCustomers recordset to the first datasource in the Datasources collection (it's the Customers datasource), the Me.adoOrders.Recordset to the Orders datasource (the second one), and builds a report.

Time to look back - quick start

To summarize the topics covered in this section:

• Active XL Report setup procedure adds the XLReport ActiveX control to the list of available controls.
• Creating a report with Active XL Report means mostly "to create a template in MS Excel".
• Special field formulas on the =DatasourceName_FieldName pattern define data transfer.
• To bind the XLReport component to the template you have to fill its Template property.
• To transfer one record (namely the current one) of a dataset in a report, you have to add a datasource (XLRDatasource object) to the Datasources collection of Active XL Report component (either through its property pages or at run-time) and pass the dataset as a parameter to the Report method of the component.
• To transfer all records of a dataset in a report, you have to add a datasource (XLRDatasource object) to the Datasources collection of XLReport (either through its property pages or at run-time), specify the template's range to be filled with the records, and pass the dataset as a parameter to the Report method of the component.
• To pass several datasets to a report you can use the following syntax component_name.Report Array(Dataset1, Dataset2,:,DatasetN). You can also set XLRDatasource.Dataset property of some datasources at run-time through the use of the XLRDatasource.Dataset property. In this case you can omit any of the datasets in the sample above (all commas are required) and this will inform the component that it can find the dataset in an appropriate XLRDatasource object.
• Active XL Report preserves all the template formats in a report as well as the parameters of the worksheets and the workbook.
• To edit a template at run time, you have to place the XLReport component onto the form, set the properties needed, and call the Edit method.