Using XRF files

About XRF files

Like XLReportGen, XLReportCom also can read an XRF file to generate a report. The XRF file is a text file with an .xrf extension. It contains information such as the name of the report template file, the name of the report file, the name of the log file, data sources, parameters and functions. The XRF file tells XLReportCom how to get data from data sources and how to put data into a report. Using the XRF file, it will simplify your development.

Using an XRF file with XLReport Object

XLReport object provides the ExcelReport method to generate a report based on an XRF file. For example, you have created the XRF file "myreport.xrf" and the template file. In the XRF file, there are two parameters. The first parameter is the sales date "$SalesDate", and the second is the category of the products "$Category". You can call ExcelReport method to generate the report.

Set xlApp = New Excel.Application
Set xlrpt = New XLReport
Call xlrpt.ExcelReport(xlApp, "c:\excelreport\myreport.xrf", _
    "1996-05-01", "Dairy Products")

XLReportCom will replace "$SalesDate" in SQL statements with "1996-05-01", replace "$Category" with "Dairy Products", and then submit SQL statements to data sources.

Using an XRF file in command line

In the XLReportCom, there is an executable file ExcelReport.exe that can read an XRF file to generate a report. It is the same as XLReportGen command line. For example, you have created the XRF file "myreport.xrf" and the template file. In the XRF file, there are two parameters. The first parameter is the sales date "$SalesDate", and the second is the category of the products "$Category". You can run ExcelReport.exe in command line mode as follows:

excelreport c:\excelreport\myreport.xrf 1996-05-01 "Dairy Products"

XLReportCom will replace "$SalesDate" in SQL statements with "1996-05-01", replace "$Category" with "Dairy Products", and then submit SQL statements to data sources.

Creating an XRF file

The XRF file is a text file. You can create and modify an XRF file in XLReportGen or a text editor.

Sometimes you want to make an XRF file programmatically. You can write a program to create an XRF file using C, perl or DOS shell, and then run XLReportCom to generate report. The two steps can be written into a batch file.

1. Write a program to make the XRF file as you need.

2. Write a batch file to call the program and ExcelReport.exe.

For example, you write a batch file runrpt.bat as follows. changexrf is an executable file that reads template.txt and output template.xrf. First runrpt.bat call changexrf to make the XRF file, and then call ExcelReport.exe to generate the report.

@echo off
if "%1"=="" goto usage
goto process
:usage
echo Usage: runrpt ReportDate
echo ReportDate	Date format 'YYYY-MM-DD'
goto :EOF
:process
changexrf %1 <"template.txt" >"template.xrf"
ExcelReport "template.xrf" %1

Using parameters

You can use parameters in the XRF file. You can pass values to XLReportCom when it processes an XRF file. XLReportCom will replace the parameter names with the actual values. You can use the parameters in the SQL statements and the paths and names of the files.

To use a parameter, you must define it first. If you have defined a parameter name, you can use it in SQL statements. In fact, XLReportCom will replace all strings that are the same as the names of the parameters. You should be careful to define a unique name for each parameter. It is a good choice a name begins with the "$" character.

Example

Input an order id to get the order information. The field OrderID is numeric type.

1. Defining a parameter

Define a parameter as follows:

Name: $OrderID
Title: Order ID (>=10248)
Default: 10360

2. Using a parameter

You can use the parameter "$OrderID" in SQL statements. For example:

SELECT o.OrderID
,o.OrderDate
,SUM(d.UnitPrice * d.Quantity * (1-d.Discount)) AS Amount
FROM Orders o, OrderDetails d
WHERE o.OrderID = d.OrderID
AND o.OrderID = $OrderID
GROUP BY o.OrderID, o.OrderDate
;

Example

Define two parameters. The first parameter is the sales date, and the second is the category of the products. The field OrderDate is date type, and CategoryName is char type.

1. Defining parameters

Define parameters as follows:

Name1: $SalesDate
Title1: Sales Date
Default1: 1996-05-01
Name2: $Category
Title2: Category of Products
Default2: 

2. Using parameters

You can use the parameters "$SalesDate", "$Category" in SQL statements. For example:

SELECT ......
FROM Orders, OrderDetails, Products, Categories
WHERE ......
AND OrderDate = '$SalesDate'
AND CategoryName LIKE '$Category%'
;
/* For Microsoft Jet SQL, LIKE '$Category*' */

Example

Get the information from the database, table and column that you identify when the report is generated.

1. Defining parameters

Define parameters as follows:

Name1: $Database
Title1: Database Name
Default1:
Name2: $Table
Title2: Table Name
Default2:
Name3: $Column
Title3: Column Name
Default3:

2. Using parameters

You can use the parameters "$Database", "$Table" and "$Column" in SQL statements. For example:

USE $Database;
or
DATABASE $Database;
SELECT $Column
FROM $Table
;

Example

Use parameters in the path and name of the report file and the log file.

1. Defining a parameter

Define a parameter as follows:

Name: $CustomerID
Title: Customer ID
Default: C000001

2. Using a parameter

ReportFileName=report\report_$CustomerID.xls
LogFileName=log\report_$CustomerID.log
or
ReportFileName=report\$CustomerID\report.xls
LogFileName=log\$CustomerID\report.log

Converting files

You can convert a file from Microsoft Excel to another file format or from another file format to Microsoft Excel. For example, the template file is a Lotus 1-2-3 file with a .wk3 extension, and the report file is a HTML file with a .htm extension. For most file formats, Microsoft Excel converts only the active sheet. To convert the other sheets, open the template file, switch to the sheet you want to save, and save it.

The file formats XLReportCom supports can be one of these. What file format XLReportCom supports is dependent on your Microsoft Excel. For example, Microsoft Excel 2003 supports XML, but Microsoft Excel 97/2000 does not support it. For more information about converting files, please refer to Microsoft Excel Help. The file "xconv.cfg" is located in the XLReportCom directory contains the information of the file format. You can expand it if your Microsoft Excel supports more file formats.

For Microsoft Excel 2007, please copy "xconv2007.cfg" to "xconv.cfg". This file contains the information of file formats for Microsoft Excel 2007.

Note: Some of these file formats may not be available to you, depending on the language support (U.S. English, for example) that you've selected or installed.

XRF File Reference

XRF File Format

The layout of an XRF file is as the following:

ExcelReport Version 2.0
[Data Source]
......
[File]
......
[Parameter]
......
[SQL]
......

"ExcelReport" is the flag of XRF file. "Version 2.0" is the version of the XRF file.

An XRF file contains several sections. The sections of [Data Source], [File], and [Parameter] consist of a group of related settings. The sections and settings are listed in the XRF file in the following format:

[section name]
keyname=value

In this example, [section name] is the name of a section. The enclosing brackets ([]) are required, and the left bracket must be in the leftmost column on the screen.

The keyname=value statement defines the value of each setting. A keyname is the name of a setting. It can consist of any combination of letters and digits, and must be followed immediately by an equal sign (=). The value can be an integer, a string, or a quoted string, depending on the setting.

You can include comments in these sections. You must begin each line of a comment with a semicolon (;).

The [SQL] section consists of functions. Each function is begin with the "@" character. Syntax:

@functionno=functionname(arguments)
sqlstatement

The functionno is a label of the function.

The functionname represents a function.

The arguments for a function define various properties for the function. An argument takes the form Name="Value". The argument value can be delimited by single or double quotes.

The sqlstatement is a SQL statement.

You can use comments in [SQL] section. A comment is the "/*" characters, followed by any sequence of characters (including new lines), followed by the "*/" characters. You cannot nest comments.

[Data Source] Section

The [Data Source] section contains information how to connect to data sources.

Name1=< name1 >
Name2=< name2 >
......
Name10=< name10 >

These settings specify the names of data sources you want to connect to. Name1 specifies the name of the first data source. Name2 specifies the name of the second data source...... You can define up to 10 data sources in one XRF file. You can make a connection to a data source using an ODBC data source name or a connection string. Even if you use a connection string to make a connection, you should define a name that you can reference in functions.

User1=< username1 >
User2=< username2 >
......
User10=< username10 >

These settings specify the user names. If you use an ODBC data source name to make a connection, you should define user name and password. If you use a connection string to make a connection, XLReportCom will ignore the setting. User1 specifies the user name of the first data source. User2 specifies the user name of the second data source...... They are optional settings. If defined default user and password in ODBC data source, you may not define them.

Password1=< password1 >
Password2=< password2 >
......
Password10=< password10 >

These settings specify the user passwords. If you use an ODBC data source name to make a connection, you should define user name and password. If you use a connection string to make a connection, XLReportCom will ignore the setting. Password1 specifies the password of the first data source. Password2 specifies the password of the second data source...... They are optional settings. If defined default user and password in ODBC data source, you may not define them.

ConnectionString1=< connectionstring1 >
ConnectionString2=< connectionstring2 >
......
ConnectionString10=< connectionstring10 >

These settings specify the connection strings. If you defined a connection string, XLReportCom will make a connection to the data source using the connection string, and ignore the settings of the name, user and password. But you must define a name that you can reference in functions. ConnectionString1 specifies the connection string of the first data source. ConnectionString2 specifies the connection string of the second data source...... They are optional settings. If no connection string, XLReportCom will make a connection to data source using the ODBC data source name.

EncryptPassword =Y/N

This setting specifies how to save passwords of data sources. If the value is Y, passwords will be saved in an encrypted format. If the value is N, passwords will be saved in plain text.

[FILE] Section

[FILE] section contains information about files.

ReportTemplateFileName=< templatefilename >

This setting specifies the name of the report template file. < templatefilename > value is the name and path of the report template file. The file path can be a relative path or an absolute path. If it is a relative path, the base path is the path of the XRF file.

ReportFileName=< reportfilename >

This setting specifies the name of the report file. < reportfilename > value is the name and path of the report file. The file path can be a relative path or an absolute path. If it is a relative path, the base path is the path of the XRF file. In < reportfilename >, you can use parameters.

ReportFileType=< reportfiletype >

This setting specifies the type of the report file. < reportfiletype > value is the name or value of the file format. For example, xlCSV or 6. What file format XLReportCom supports is dependent on your Microsoft Excel.

ProtectReport=< Y/N >

This setting specifies whether the report generated is protected. If the value is Y, the report is protected, and can not be modified. If the value is N, the report is not protected. Default is N.

ProtectionPassword=< protectionpassword >

This setting specifies the password that is used to protect the report. < protectionpassword > value is the password. This setting is valid when ProtectReport is Y. If there is not this setting and ProtectReport is Y, a random password will be created.

LogFileName=< logfilename >

This setting specifies the name of the log file. < logfilename > value is the name and path of the log file. The file path can be a relative path or an absolute path. If it is a relative path, the base path is the path of the XRF file. In < logfilename >, you can use parameters.

[PARAMETER] Section

[PARAMETER] section contains information about parameters.

Name1=< name1 >
Name2=< name2 >
......
Name10=< name10 >

These settings specify the names of the parameters. Name1 specify the name of the first parameter, Name2 specifies the name of the second parameter...... You can define up to 10 parameters in one XRF file.

Title1=< title1 >
Title2=< title2 >
......
Title10=< title10 >

These settings specify the titles of the parameters. Title1 specifies the title of the first parameter. Title2 specifies the title of the second parameter......

Default1=< default1 >
Default2=< default2 >
......
Default10=< default10 >

These settings specify the default values of the parameters. Default1 specifies the default value of the first parameter. Default2 specifies the default value of the second parameter......

Function Reference

Fixed Table Report

Uses FixTableReport method to generate a fixed table report. In a fixed table report, the number of rows and columns is fixed. XLReportCom executes a SQL statement to get data from data source, and directly fills data into the cells of a worksheet.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "fix"
SHEET = sheet
FILLORDER = fillorder
CELL= celllist
RANGE = range
IMAGE = fieldlist
PAGEBREAK = pagelength
CONNECT = datasource

The TYPE argument specifies the report type. "fix" means a fixed table report.

The SHEET argument identifies a worksheet in the report template. The sheet is the name or index number of the worksheet. The index number starts at 1.

The FILLORDER argument specifies the order in which XLReportCom fills data. Possible values are row or col. "row" means to fill data by rows, and "col" means to fill data by columns. Default is row.

The CELL argument specifies the positions where data values will be inserted. The celllist is the list of cells separated by the "," character. For example, "A2,B2,B3,D2,D3". The cells in the celllist should correspond to the data source fields in the SQL statement. The value of the first field is put into the first cell, and the value of the second field is put into the second cell ...... XLReportCom will use the next cell if you omit a cell except the first cell. If FILLORDER="row", the next cell is the right cell. If FILLORDER="col", the next cell is the below cell.

The RANGE or COPYRANGE argument specifies the range in the worksheet to be used for the records. XLReportCom will skip or repeat the range for each record. You can reference a range of cells like "2:4" or "B2:D5". The default range is the area that includes all cells for the records. For Range argument, XLReportCom will skip the rows/columns of the range for each record. For COPYRANGE argument, it will copy the original range to the range where data will be filled for each record.

The IMAGE argument specifies the data source fields are picture files. The fieldlist is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In data source, you stored the path and file name of the picture, not the picture. The file path can be a relative path, an absolute path or a URL. If it is a relative path, the base path is the path of the report template file.

The PAGEBREAK argument specifies the page breaks. The unit of page length is r that means record. For example, "6r" or "6" means that XLReportCom will insert a page break per 6 records. Default is no page break.

The CONNECT argument specifies the connection to a data source. The CONNECT can takes a string that expresses a data source name or a number that expresses a data source index. The index number of data source is the sequential number defined in the XRF file, and starts at 1. The default implies the first data source.

The sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Fixed Table Report function to make the report "Top 5 Employees for Sales".

@F1=REPORT(sheet="Report6" type=fix cell=B7)
SELECT TOP 5 e.FirstName + ' ' + e.LastName
	, SUM(d.Quantity)
, Sum(d.UnitPrice * d.Quantity * (1-d.Discount)) AS SalesAmount
FROM Orders o
	,OrderDetails d
	,Products p
	,Employees e
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND o.EmployeeID = e.EmployeeID
AND YEAR(o.OrderDate) = 1996
AND MONTH(o.OrderDate) = 04
GROUP BY e.FirstName, e.LastName
ORDER BY 3 DESC

Variable Table Report

Uses VarTableReport method to generate a variable table report. In a variable table report, the number of rows or columns in the table is unfixed, and it is variable as the number of the result records. XLReportCom executes a SQL statement to get data from data source, inserts some blank rows/columns or copy a range for each record, then fills data into the cells of a worksheet.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "var"
SHEET = sheet
FILLORDER = fillorder
CELL= celllist
RANGE = range
IMAGE = fieldlist
RESERVE = reserverecords
PAGEBREAK = pagelength
NODATA = nodataoption
CONNECT = datasource

The TYPE argument specifies the report type. "var" means a variable table report. Default is var.

The SHEET argument identifies a worksheet in the report template. The sheet is the name or index number of the worksheet. The index number starts at 1.

The FILLORDER argument specifies the order in which XLReportGen fills data. Possible values are row, col, rowrange or colrange. "row" means to insert entire rows and fill data by rows. "col" means to insert entire columns and fill data by columns. "rowrange" means to insert range and fill data by rows. "colrange" means to insert range and fill data by columns. Default is row.

The CELL argument specifies the positions where data values will be inserted. The celllist is the list of cells separated by the "," character. For example, "A2,B2,B3,D2,D3". The cells in the celllist should correspond to the data source fields in the SQL statement. The value of the first field is put into the first cell, and the value of the second field is put into the second cell ...... XLReportCom will use the next cell if you omit a cell except the first cell. If FILLORDER="row", the next cell is the right cell. If FILLORDER="col", the next cell is the below cell.

The RANGE or COPYRANGE argument specifies the range in the worksheet to be used for the records. XLReportCom will skip or repeat the range for each record. You can reference a range of cells like "2:4" or "B2:D5". The default range is the area that includes all cells for the records. For Range argument, XLReportCom will insert the blank rows/columns of the range for each record. For COPYRANGE argument, it will copy the original range and insert the copied range for each record.

The IMAGE argument specifies the fields are picture files. The fieldlist is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In data source, you stored the path and file name of the picture, not the picture. The file path can be a relative path, an absolute path or a URL. If it is a relative path, the base path is the path of the report template file.

The RESERVE argument specifies the number of the records for which you reserved some rows/columns in the report template for the report. The reserverecords represents the number of the records you reserved in the report template. Possible values are 1 or 2. One means you reserved some rows/columns for one record, and two means some rows/columns for two records. Default is 1.

The PAGEBREAK argument specifies the page breaks. The unit of page length is r that means record. For example, "6r" or "6" means that XLReportCom will insert a page break per 6 records. Default is no page break.

The NODATA argument specifies an option when no data are returned from data source. If the value is "delrange", XLReportGen will delete the range when no data are returned. If the value is "delsheet", XLReportGen will delete the sheet when no data are returned. Default is to do nothing.

The CONNECT argument specifies the connection to a data source. The CONNECT can takes a string that expresses a data source name or a number that expresses a data source index. The index number of data source is the sequential number defined in the XRF file, and starts at 1. The default implies the first data source.

The sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses VarTableReport method to make the report "Mail Label".

@F1=Report(sheet="Mail Label" type=var 
cell=B7,B8,B9,B10 copyrange=1:11 pagebreak = 4r)
SELECT CompanyName
,Address
,CityName & ', ' & CountryName
,PostalCode
FROM Customers, Cities, Countries
WHERE Customers.CityCode = Cities.CityCode
AND Customers.CountryCode = Cities.CountryCode
AND Customers.CountryCode = Countries.CountryCode
ORDER BY CompanyName

Group Table Report

Uses GroupTableReport method to generate a variable table report and group data. In a variable table report, the number of rows or columns in the table is unfixed, and it is variable as the number of the result records. XLReportCom executes a SQL statement to get data from data source, copy the group range for each group, copy the detail range for each record, then fills data into the worksheet.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "var"
SHEET = sheet
FILLORDER = fillorder
CELL= celllist
RANGE = range
GROUP= grouplist
GROUPRANGE = grouprange
IMAGE = fieldlist
PAGEBREAK = pagelength
NODATA = nodataoption
CONNECT = datasource

The TYPE argument specifies the report type. "var" means a variable table report. Default is var.

The SHEET argument identifies a worksheet in the report template. The sheet is the name or index number of the worksheet. The index number starts at 1.

The FILLORDER argument specifies the order in which XLReportGen fills data. Possible values are row, col, rowrange or colrange. "row" means to insert entire rows and fill data by rows. "col" means to insert entire columns and fill data by columns. "rowrange" means to insert range and fill data by rows. "colrange" means to insert range and fill data by columns. Default is row.

The CELL argument specifies the positions where data values will be inserted. The celllist is the list of cells separated by the "," character. For example, "A2,B2,B3,D2,D3". The cells in the celllist should correspond to the data source fields in the SQL statement. The value of the first field is put into the first cell, and the value of the second field is put into the second cell ...... XLReportCom will use the next cell if you omit a cell except the first cell. If FILLORDER="row", the next cell is the right cell. If FILLORDER="col", the next cell is the below cell.

The RANGE or COPYRANGE argument specifies the range in the worksheet to be used for the details. XLReportCom will skip or repeat the range for each record. You can reference a range of cells like "2:4" or "B2:D5". The default range is the area that includes all cells for the details. For Range argument, XLReportCom will insert the blank rows/columns of the range for each record. For COPYRANGE argument, it will copy the original range and insert the copied range for each record. But if the range of any group is not same as the range of the details, RANGE is same as COPYRANGE.

The GROUP argument specifies the group of the report. The grouplist is the list of data source fields separated by the "," character. You can identify a field using the name or index number of the field, but not simultaneously. In one report, there may be up to 10 groups. Notes: the order of the groups should be in accordance with the order of the ORDER BY clause in the SQL statement.

The GROUPRANGE argument follows the GROUP argument, and specifies the range of the group in the worksheet. For example, the grouprange of level 1 must follow the group of level 1, and the grouprange of level 2 must follow the group of level 2. XLReportCom will repeat the group range for each group. The range of the group should contain the range of the details and the area that includes all cells for this group. You reference a group range like "2:4" or "B2:D5". For example, there are two groups, the range of the group one contains all cells for the group one and the range of the group two, and the range of the group two contains all cells for the group two and the range of the details. The default range is the area that includes all cells for this group and the range or group range for the lower level group.

The IMAGE argument specifies the fields are picture files. The fieldlist is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In data source, you stored the path and file name of the picture, not the picture. The file path can be a relative path, an absolute path or a URL. If it is a relative path, the base path is the path of the report template file.

The PAGEBREAK argument specifies the page breaks. The unit of page length is r or g. "r" means record, "g1" means group one, "g2" means group two...... For example, "6r" or "6" means that XLReportCom will insert a page break per 6 records, "1g1" or "1g" means a page break per group one, and "1g1,6r" means a page break per group one or 6 records. Default is "" that means no page break.

The NODATA argument specifies an option when no data are returned from data source. If the value is "delrange", XLReportGen will delete the range when no data are returned. If the value is "delsheet", XLReportGen will delete the sheet when no data are returned. Default is to do nothing.

The CONNECT argument specifies the connection to a data source. The CONNECT can takes a string that expresses a data source name or a number that expresses a data source index. The index number of data source is the sequential number defined in the XRF file, and starts at 1. The default implies the first data source.

The sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Group Table Report function to make the report "Customer Profile".

@F1=Report(sheet="Customer Profile" 
  cell=A6,B7,C7,D7,D8,E7,E8,E9
  range=6:9 group=1 pagebreak = 6r)
SELECT LEFT(CompanyName,1)
,CompanyName
,ContactName
,'Phone: ' & Phone
,'Fax: ' & Fax
,Address
,CityName & ', ' & CountryName
,PostalCode
FROM Customers, Cities, Countries
WHERE Customers.CityCode = Cities.CityCode
AND Customers.CountryCode = Cities.CountryCode
AND Customers.CountryCode = Countries.CountryCode
ORDER BY CompanyName

Name

Executes a SQL statement, and assigns the values to the names defined in the Excel workbook. XLReportCom will just fetch the first record, no matter how many records are returned from data source.

Syntax

Name(...)
sqlstatement

Arguments

NAME= namelist
CONNECT= datasource

The NAME argument specifies the names you want assign values to. The namelist is the list of names separated by the "," character. For example, "BeginDate, EndDate" means two names: BeginDate and EndDate that should be defined in the report template. The names in the list should correspond to the fields in the SQL statement. The value of the first field is put into the first name, and the value of the second field is put into the second name ...

The CONNECT argument specifies the connection to a data source. The CONNECT can takes a string that expresses a data source name or a number that expresses a data source index. The index number of data source is the sequential number defined in the XRF file, and starts at 1. The default implies the first data source.

The sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Name function to assign the values of min_date and max_date to the names: BeginDate and EndDate.

@F1=NAME(NAME=BeginDate,EndDate)
SELECT min_date, max_date
FROM tmp0

ExecSQL

Executes a SQL statement, but no data is returned to the report.

Syntax

ExecSQL(...)
sqlstatement

Arguments

CONNECT= datasource

The CONNECT argument specifies the connection to a data source. The CONNECT can takes a string that expresses a data source name or a number that expresses a data source index. The index number of data source is the sequential number defined in the XRF file, and starts at 1. The default implies the first data source.

The sqlstatement is a SQL statement that can be DDL (Data Definition Language), DML (Data Manipulation Language) and even DCL (Data Control Language).

Using EXECSQL function, you can open a database, create a temporary table, insert data into a temporary table, update data, execute a stored procedure, and drop a table. It is very useful to create a temporary table, and prepare data for REPORT function.

Example

This example uses ExecSQL functions to create a table tmp0, and add some records into the table. No result is returned to the report.

@F1=EXECSQL()
CREATE TABLE tmp0 (
min_date DATE,
max_date DATE)
;
@F2=EXECSQL()
INSERT INTO tmp0 
SELECT ...