NoRange-datasets

We distinguish two dataset types according to the Range property value of the corresponding DataSources item. If this property is empty then the dataset is a NoRange-dataset. Such a dataset type is used to transfer one record - namely, the current one - to a report. In the previous example the tblCustomers dataset is a NoRange?dataset. You can use NoRange?dataset field formulas in any cell of any sheet of the template workbook and Excel will find their values at run-time. How? XL Report adds a hidden worksheet in a report workbook and transfers values of all fields for the current record. Then it names all these data cells. The names are given in accordance to the Alias_FieldName pattern (the XL Report term for this is field formula). This allows the NoRange?datasets to work correctly. Currently supported field types are:

• String - ftString, ftFixedChar, ftWideString, ftMemo, ftFmtMemo (without formatting);
• Integer - ftSmallint, ftWord, ftInteger, ftLargeint, ftAutoInc;
• Real - ftFloat, ftCurrency;
• Date/time - ftDate, ftTime, ftDateTime;
• Logical - ftBoolean.

For other field types, the Null value is transferred.

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

Data transfer methods

XL Report 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 TxlReport instance but you should be aware of restrictions of data transfer methods:

• xdmVariant. Data portions are transferred in a variant array with direct assignng 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 1500 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.
• xdmCSV. 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 result of division or with a date. Use this method with long strings (the limit is 8 Kb).
• xdmDDE. 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.

Once more on conditional formatting

Where? QDemo.dpr form: frmNoRange1; unit: fNoRange1; template: tNoRange1.xls

For this example the frmNoRange1 form has been added to QDemo.dpr. Just one dataset - TTable - has been added referencing the Customer.db table, and it is named tblCustomers. To show the current record of this dataset, an instance of TDataSource has been added, along with several labels, edit fields, and navigator. They are bound to CustNo, Company, State, and Country fields. A TxlReport component was added, and an item was added in its DataSources collection and bound to tblCustomers dataset. Two buttons were also added. Their names - Report and Template - reflect the method of TxlReport they call. The TxlReport.XLSTemplate property was set to the name of the template - tNoRange1.xls.

The previous report template was saved as a tNoRange1.xls workbook in the project folder. Every sign of tblOrders was erased and the tblCustomers_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 used conditional formatting with the =$I$9="US" formula and chose to highlight the cells in red. Save the template and quit Excel. Then start the project 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 - tblCustomers_Country. This is the preferable approach because of the possibility that the end user might change the meaning of the I9 cell.

NoRange-datasets data in Excel formulas

Where? QDemo.dpr form: frmNoRange2; unit: fNoRange2; template: tNoRange2.xls

You have seen how 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 Alias_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 the example we invented a fictitious term Next Sale and decided that it must be equal to last sale date from tblCustomers_LastInvoiceDate plus some additional days. We also determined that the additional days should be equal to tblCustomers_CustNo divided by 25. So the formula would appear as =tblCustomers_LastInvoiceDate+tblCustomers_CustNo/25. Another template was created, using the previous template as a sample, and it was saved under a new name tNoRange2.xls. Your instance of TxlReport must be informed of this change - TxlReport.XLSTemplate="tNoRange2.xls". Put this 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 - just click Report in the context menu of the TxlReport instance to see the results.

Note
We have shown here the use of conditional formatting with a formula using the data of our application. Some users of XL Report use conditional formatting to hide some values in a report by defining conditions that set a cell's background color equal to font color.

NoRange-datasets and Memo-fields

Where? QDemo.dpr form: frmNoRange3; unit: fNoRange3; template: tNoRange3.xls

You can use Memo-fields as any other fields but working with them you should be aware of data transfer method used.

Another form was added to our project and TTable instance was placed onto it. This TTable instance is named tblCustoly after Custoly.db from DBDEMOS. To display the current record of this dataset we added to the form: TDataSource instance, some labels, some edit fields (CustNo, First Name, Last Name, and Phone), and navigator. Then the instance of TDBMemo was added and bound to Remarks field. Finally, the TxlReport component was placed onto the form, and an item (of TxlDataSource type) added to its DataSources, and we bound the item to tblCustoly. Then the two standard buttons Report and Template that call corresponding TxlReport methods were placed on the form. The final stroke - TxlReport.XLSTemplate = "tNoRange3.xls".

Field formulas refer to the above-mentioned fields and the name of the template workbook is tNoRange3.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 DDE Table Format. Any other transfer method will produce the full string.

NoRange-datasets - only necessary fields

Where? QDemo.dpr form: frmNoRange4; unit: fNoRange4; template: tNoRange4.xls

If you plan to use only some fields of a NoRange-dataset, 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 (tNoRange4.xls) by separating them with a semicolon. In order for XL Report to find the cell you must give it the same name as the dataset.

During report generation, XL Report will find this cell, recognize the fields, transfer their values, and clear the cell. We named the cell tblCustoly and set its value to Remarks;First_Name;Last_Name in the above example.

Create XL Report component dynamically

We showed you how to create the component dynamically in Quick Start. Here we extend and improve on that example. The code that builds the report on a single NoRange-dataset is as follows:

Note that the AddDataSet call adds a NoRange-dataset to the DataSources collection of the Report component.

But there is an even better technique - using the global TxlReport instance. The sequence is as follows:

• Create an instance of TxlReport in the initialization section of an arbitrary unit;
• Destroy the instance in the finalization section of the same unit;
• Before every call of the Report method clear the DataSources collection and only then add the needed datasets.

Example:

Time to look back - NoRange-datasets

We have focused on these themes:

• XL Report operates on two types of datasets recognized by the empty-nonempty state of the Range property of DataSources items.
• If this property is empty then the dataset is a NoRange-dataset.
• XL Report creates a hidden worksheet to keep data for all NoRange-datasets.
• XL Report transfers only string, integer, real and logical values.
• All other field types produce Null (the Excel interpretation of Null) values in a report.
• Field formulas for NoRange-datasets 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.
• There are three data transfer methods: xdmVariant, xdmCSV, and xdmDDE.
• There is a way to limit the number of fields being transferred.
• If you plan to use fields longer than 255 characters, you will need to use the xdmCSV data transfer method.