NoRange-datasources

We distinguish two types of datasource according to the Range property value of the XLRDatasource object. If this property contains an empty string then the Datasource is a NoRange-datasource. Such a datasource type is used to transfer one record - namely, the current one - to a report. In the previous example the Customers datasource is a NoRange-datasource. NoRange-datasource field formulas can be used in any cell of any worksheet of the template workbook.

How does Excel find the values for these formulas? Active XL Report adds a hidden worksheet to a report workbook and transfers values of all fields from the current record into this worksheet. Then it names all the data cells on this worksheet. The names are given in accordance to the Datasourceame_FieldName pattern (the Active XL Report term for the pattern is field formula). This allows the NoRange-datasources work correctly. Currently supported field types are listed in Appendix D. In case of other field types Null is transferred.

Note
You can access the hidden worksheet mentioned above via setting the Debug property to True and selecting the Format|Sheet|Unhide menu in a report workbook.

Data transfer methods

XLReport provides three methods of data transfer: variant array, comma-separated values (CSV) via the clipboard, and Fast DDE Table Format using DDE calls. Your choice must be reflected in the value of the DataExportMode property of the XLReport instance but you should be aware of restrictions of data transfer methods:

• xlrExportVariant. Data portions are transferred in a variant array with direct assigning of its values to cells. Usually this method is the slowest one and used by XL Report core in some situations. Because of an error in Excel algorithms in all Excel versions, strings longer than about 1800 characters are truncated. There also are some problems with data type recognition, e.g. the "00033" string can be recognized as a number. This method is left for compatibility with previous versions of XL Report.
• xlrExportCSV. Data portions are transferred through the Clipboard in CSV format. Middle speed of data transfer. There are some problems with data type recognition, e.g. the "3/7" string can be replaced with either a result of division or with a date. Use this method with long strings (the limit is 8 Kb).
• xlrExportDDE. The default method. Data portions are transferred using Fast DDE Table Format. The fastest method because of use of Excel's native data types. Data type recognition problems were not observed. Strings longer than 255 characters are truncated.

Formatting cells

Where? form: frmNoRange1; template: NoRange1.xls

The previous report template was saved as a NoRange1.xls workbook in the /Templates folder of the demo projects. Every sign of Orders was erased and the Customers_FieldName cells were left undisturbed. In this example we wanted to single out the Country cells when the Coutry field would be equal to US. To achieve this we have used conditional formatting with the =$I$9="US" formula and chose to highlight the cells in red. Open the form and build the report for several customers including those that have US in Coutry field. You will see the difference. You can replace the reference of the I9 cell in the conditional formatting formula with the direct reference to the Country field - Customers_Country. This is the preferable approach because of the possibility that the end user might change the meaning of the I9 cell.

NoRange-datasources data in Excel formulas

Where? form: frmNoRange2; template: NoRange2.xls

You have seen how Active XL Report adds a hidden worksheet to a template workbook and transfers the values of all the fields of supported types. Then data cells are named according to the DatasourceName_FieldName pattern (field formula). In a generated report, you can check this fact by just using a field formula for any of your field names in any cell of the workbook.

Field formulas (in fact they are just cell names) can be used in any Excel formulas the same way as any other Excel names. You can also use them in VBA. In this example we invented a fictitious term Next Sale and decided that it must be equal to last sale date from Customers_LastInvoiceDate plus some additional days. We have also determined that the additional days should be equal to Customers_CustNo divided by 25. So the formula would appear as =Customers_LastInvoiceDate+Customers_CustNo/25. Another template was created, using the previous one as a sample, and it was saved under a new name NoRange2.xls. (Your XLReport component must be informed of this change - ocxXLReport.Template=".\Templates\NoRange1.xls"). Put the formula in an arbitrary cell of the template. To control the accuracy of computations put the field formula for the LastInvoiceDate field in another cell. Then you can test the result - open the form and click the Report button.

NoRange-datasource and Memo-fields

Where? form: frmNoRange3; template: NoRange3.xls

Field formulas refer to the fields of the Custoly table and the name of the template workbook is NoRange3.xls. To output the Remarks field we merged several cells and specified the appropriate field formula. Now try to build the report. If the string length for the Remarks field is greater than 255 then the string is trimmed because of the default data transfer method - Fast Table Format. It is really fast but if you want to produce the full string you should use xlrExportCSV data transfer method. The choice is up to you.

Limiting the number of fields in a NoRange-datasource

Where? form: frmNoRange4; template: NoRange4.xls

If you plan to use only some fields of a NoRange-datasource, why you should have to transfer all the fields when you need only some of them? This is the default behavior and is correct as far as it goes. Do you understand this? To allow you to transfer only some of the fields there were only two possibilities - either to scan all the cells in a template for field formulas or to allow you to define the fields. We chose the last approach because the first one leads to a time wasting operation.

You can specify all the fields needed in any free cell of the template by separating them with a semicolon. In order for Active XL Report to find the cell you must give it the same name as the datasource.

During report generation, Active XL Report will find this cell, recognize the fields, transfer their values, and clear the cell. We named the cell Custoly and set its value to Remarks;First_Name;Last_Name in the above example. Test your NoRange4.xls to see how this works.

Time to look back - NoRange-datasources

We have focused on these themes:

• Active XL Report operates on two types of datasources recognized by the empty-nonempty state of the Range property of XLReport.Datasources collection items (XLRDatasource objects, see Programmer's Reference).
• It creates a hidden worksheet to keep data for all NoRange-datasources.
• By default, all fields of supported types are transferred from the current record of a NoRange-datasource into report worksheets.
• There is a way to limit the number of fields being transferred.
• Active XL Report supported field types are listed in Appendix D.
• All other field types produce Null (the Excel interpretation of Null) values in a report.
• Field formulas can be used in any cell of any worksheet of a template workbook.
• Arbitrary formatting (including conditional formatting) can be applied to cells containing field formulas.
• Field formulas can be used in standard Excel formulas as well as in VBA programs.
• If you plan to use fields longer than 255 characters, you will need to use the xlrExportCSV data transfer method.