Other features

In addition to range options and column options, Active XL Report processes several sheet options and report options. The first ones have an influence upon a worksheet, and the second, accordingly, on a report. Both are used to automate getting the optimal height and width for cells, scaling to the installed printer, hiding some worksheets, and protecting them from changes.

Sheet options

Where? form: frmOther1; template: Other1.xls

You need to specify the sheet options in the A2 cell of a given worksheet. When processing every worksheet of the template, Active XL Report checks that cell's value. When Active XL Report recognizes any options, it takes some additional actions. Here they are:

• OnlyValues - replaces all formulas on a worksheet with their values.
• Hide or SheetHide - hides a worksheet in a generated report workbook.
• RowsFit - makes the range rows fit the contents. It can be done manually - select the rows needed and double-click on the boundary between any of the selected rows.
• ColsFit - makes the range columns fit the contents. It can be done manually - select the columns needed and double-click on the boundary between any of the selected columns.
• AutoScale - sets up the page option Fit to 1 page wide by (empty) tall (this can be found in File|Page Setup on the Page tab).
• AutoSafe - protects a worksheet with a random password. This corresponds to Tools|Protection|Protect Sheet menu command call and Contents box checking.

Note
Using the AutoSafe option together with clearing (checking) the "Locked" checkbox in a cell's properties, you can implement a flexible approach to user's modifications of a report. For instance, if you clear the "Locked" checkbox of a given cell in a template and use the AutoSafe sheet or report option, then the user still will be able to change the contents of this cell in a report.

The A2 cell will be cleared only if it contains any Active XL Report options. So you can use this cell for your own needs. This rule also refers to the A1 cell that contains report options. Some Active XL Report users, knowing that report options can be specified on any worksheet, create a hidden worksheet with report options.

Report options

Where? form: frmOther2; template: Other2.xls

Report options should be placed in the A1 cell of any worksheet (or worksheets - in this case all the options supplied will count) of a template workbook. While processing every worksheet of a template, Active XL Report checks this cell. When Active XL Report recognizes any options it takes some additional actions. Here is the options list:

• OnlyValues - replaces every formula in the report workbook with its calculated value.
• ShowPivotBar - turns on the Pivot table bar. This bar is hidden by default even if the report contains a pivot table. This panel allows the user to edit the Pivot table. See Excel Help for additional information.
• AutoSafe - protects a report workbook with a random password. This corresponds to the Tools|Protection|Protect Workbook menu command with the Structure check box on. The use of AutoSafe on report level completely protects a report workbook because the Tools|Protection|Protect sheet menu command is applied to every worksheet in the workbook.

Add a report to an existing workbook

Where? form: frmOther3; template: Other3.xls+ExistingBook.xls

Active XL Report allows you to add a report to an existing workbook with the ReportTo method. The syntax is:

Where WorkbookName is the path and filename of an existing workbook.

Active XL Report creates a report going through usual chain of methods, and adds all non-hidden worksheets of the report workbook to the workbook specified. The OnlyValues option will be applied to every worksheet added. And more, all names referencing the worksheet added will be destroyed. The report worksheets are added after the last existing worksheet.

xlrDemo example adds the Sheet1 worksheet to the ExistingWorkbook.xls. If the Sheet1 worksheet already exists in the target workbook the added worksheet will be renamed according to Excel naming convention. Thus Sheet1 will become Sheet1(1).

Report parameters

Where? form: frmUnboundData1; template: UnboundData1.xls

It is easy: 1) use the Add method of the Params property of XLReport object to create a parameter, name it, and define its value; 2) use the parameter in a template as if it is a field of a NoRange-datasource whose name is fixed - XLRParams. For instance, if you create the StartDate parameter and use the XLRParams_StartDate field formula in a cell of a template then the formula will be substituted with the parameter's value.

Unbound data

Where? form: frmUnboundData2; template: UnboundData2.xls

The use of parameters will not help you to transfer a lot of data to a report. In order to transfer large volumes of data Active XL Report uses the similarity between 2D arrays and data sets: rows in array are similar to records in a data set, and columns are similar to fields. This idea is implemented in two mechanisms of unbound data transfer. The first one allows you transferring your data all at once, while the second one allows transferring portions of data - "records". In both cases, data are transferred in a variant array: in the first case - in a 2D array including two additional rows with "field" names and types; in the second case - in a 1D array containing the current "record". In the second case "field" names and types become known to Active XL Report through the use of GetDatasetInfo event, while "records" are formed in the GetRecord event.

Just pass the array of the above specified contents to the Report (ReportTo) method Active XL Report in order to use the first method. Be sure to set the DatasetType to xlrUnboundDataset. In order to use the second mechanism of unbound data transfer, leave the Dataset property and/or the corresponding item in the array - the parameter of the Report (ReportTo) method - empty. Active XL Report, having found that DatasetType is xlrUnboundDataset and the Dataset property is empty, will generate the GetDatasetInfo event and, having understood the dataset structure, will start to generate GetRecord events informing you about the required record number in the RecNo input parameter (it starts from 1). When you transfer all data, pass True (-1) in the Eof output parameter.

The "field" types for xlrUnboundDataset are defined in the DataTypes enumeration that can found in the Object Browser. The types are: xlrInteger, xlrFloat, xlrString, xlrBoolean, xlrDateTime, and xlrNotSupported. And these types are Excel types. Except for xlrNotSupported: fields and "fields" of type xlrNotSupported are not transferred to a report.

Time to look back - other features

Please remember that:

• Along with column and range options, there are sheet and report options.
• Sheet options must be placed in the A2 cell of a given sheet.
• Report options must be placed in the A1 cell of any sheet or sheets of a workbook.
• Using these options, you can hide some worksheets, protect worksheets or the whole workbook, and substitute formulas with their values.
• You can add report worksheets to an existing workbook.
• You can create and use any number of report parameters.
• You can transfer custom data into a report in a 2D variant array.
• You also can transfer unbound data using "record by record" approach, describing your dataset structure in the GetDatasetInfo event and transferring the current "record" in the GetRecord event.