Report Generator for Microsoft Excel

Chapter 6 Function Reference

Report Function

The REPORT function executes a SQL statement to get data from data source, and put data into a worksheet in the report file. The REPORT function can make three types of reports:

Fixed table report function
Non-group variable table report function
Group variable table report function

Fixed Table Report Function

In a fixed table report, the number of rows and columns is fixed. XLReportGen executes a SQL statement to get data from data source, and directly fills data vales into the cells of a worksheet in the report file.

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 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 or fields separated by the "," character. For a table report, the celllist identifies the cells in a worksheet. 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 ...... XLReportGen 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. XLReportGen 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, XLReportGen 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 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 XLReportGen 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

The following function makes 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
;

Result

The fixed table report defined in the report template:

Excel Report Template - Top N Employees for Sales

The fixed table report generated in the report:

Excel Report Sample - Top N Employees for Sales

Remarks

1. The SQL statement will get the information of top 5 employees for sales, including employee name, quantity of products, and sales amount.

2. type="fix". It is a fixed table report.

3. sheet = "Report6". XLReportGen will put data into the worksheet "Report6" in the report file.

4. cell=B7. The cells corresponding to the first record are "B7,C7,D7".

5. The default range is "B7:D7".

6. XLReportGen executes the SQL statement, and gets data from data source. First, it fetches the first record, puts the value of the first field into cell B7, the value of the second field into cell C7, and the value of the third field into D7. And then it fetches the next record, skips one row, and puts data into cells B8, C8, and D8......

Non-group Variable Table Report Function

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. XLReportGen 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 values into the cells of a worksheet in the report file.

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 RANGE or COPYRANGE argument specifies the range in the worksheet to be used for the records. XLReportGen 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, XLReportGen 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 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 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 sqlstatement is a SQL statement such as a SELECT statement.

Example

The following function will makes 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
;

Result

The non-group variable table report defined in the report template:

The non-group variable table report generated in the report:

Remarks

1. The SQL statement will get the information of customers including company name, address, city name, country name, and postal code.

2. type="var". It is a variable table report. And there is no GROUP argument, so it is a non-group variable table report.

3. sheet="Mail Label". XLReportGen will put data into the worksheet "Mail Label" in the report file.

4. cell=B7,B8,B9,B10. These cells correspond to the first record.

5. copyrange=1:11. Because the default range is "B7:B9", you must specify a range explicitly. XLReportGen will copy the range for each record.

6. pagebreak = 4r. XLReportGen will add a page break per 4 records.

7. XLReportGen executes the SQL statement, and gets data from data source. First, it fetches the first record, copys the range, and fills data. And then it fetches the next record...... One page contains 4 mail labels.

Group Variable Table Report Function

The Group Variable Table Report function generates 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. XLReportGen 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 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 or fields separated by the "," character. For a table report, the celllist identifies the cells in a worksheet. 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 ...... XLReportGen 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. XLReportGen 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, XLReportGen 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. The first GROUP is group one, the second is group two...... 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. XLReportGen 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 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 XLReportGen 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 sqlstatement is a SQL statement such as a SELECT statement.

Example

The following function will makes 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
;

Result

The group varibale table report defined in the report template:

The group varibale table report generated in the report:

Remarks

1. The SQL statement will get the information of customers including company name, contact name, phone, fax, address, city name, country name, and postal code.

2. There are GROUP argument, so it is a group variable table report.

3. sheet=" Customer Profile". XLReportGen will put data into the worksheet "Customer Profile" in the report file.

4. group=1. XLReportGen will group data by the first letter of the company name.

5. cell= A6,B7,C7,D7,D8,E7,E8,E9. These cells correspond to the first record.

6. range=6:9. Because the default range is "B7:E9", you must specify a range explicitly. XLReportGen will copy the range for each record.

7. There is no grouprange. XLReportGen will give a default. The default grouprange is "6:9".

8. pagebreak = 6r. XLReportGen will add a page break per 6 records.

9. XLReportGen executes the SQL statement, gets data from data source, and puts data into the worksheet "Customer Profile" in the report file. Because the range is same as the group range, XLReportGen will copy the range for each record, fill the value of the first field per group, and fill the values of other fields per record. One page contains the information of 6 records.

Name Function

The Name function executes a SQL statement, and assigns the values to the names defined in the Excel workbook. XLReportGen 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 namelist 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 sqlstatement is a SQL statement such as a SELECT statement.

XLReportGen will just fetch the first record, no matter how many records are returned from data source.

Example

The following function executes a SQL statement, assigns the values of fields to names.

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

Remarks

XLReportGen executes the SQL statement, get data from data source. It assigns the value of filed "min_date" to the name "BeginDate" and the value of field "max_date" to the name "EndDate".

You should define the names in the report template first. To define and use a name, do as follows:

1. Run Excel program, and open the report template file.

2. On the Insert menu, click Name, and click Define.

3. In the Name box, enter a name such as BeginDate. In the Reference box, enter "=1".

4. In a cell such as C4, enter "=BeginDate".

5. On the Format menu, click Cells, and then click the Number tab. Click Date type, and select format you want to use.

ExecSQL Function

The EXECSQL function executes a SQL statement, but does not return result to report.

Syntax

ExecSQL(...)
sqlstatement

Arguments

CONNECT= datasource

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

The following functions will create a table tmp0, and add some records into table. No result is returned to the report file.

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