Chapter 6 Function Reference

Table Report Function

The TABLE REPORT function executes a SQL statement to get data from data source, and puts data into a table in the report file.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = reporttype
TABLE = table
FILLORDER = fillorder
CELL= celllist
RANGE = range
GROUP= grouplist
GROUPRANGE = grouprange
IMAGE = fieldlist
RANGECOUNT = rangecount
PAGEBREAK = pagelength
NODATA = nodataoption
CONNECT = datasource

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

The TABLE argument identifies a table in the report template. The table is the index number of the table or the bookmark name in the table. The index number starts at 1. For examples, table 2 is the second table in the document. The index number of a nested table likes 2-1-2. For examples, table 2-1 is the first table inside table 2, and table 2-1-2 is the second table inside table 2-1. The max nested level WDReportGen supports is 3.

The FILLORDER argument specifies the order in which WDReportGen fill 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 ...... WDReportGen 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 table to be used for the details. WDReportGen will skip or repeat the range for each record. You can reference a range of cells like "2:4" or "B:D". The default range is the area that includes all cells for the details. For Range argument, WDReportGen 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 table. 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. WDReportGen 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 "B:D". 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 WDReportGen 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", WDReportGen will delete the range when no data are returned. "deltable" means to delete the table. 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 WRF file, and starts at 1. The default implies the first data source.

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

Example 1, Fixed Table Report

The following function makes the Word report: Top 5 Employees for Sales.

@F1=REPORT(table=6 type=fix cell=B2)
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:

Word Report Template - Top N Employees for Sales

The fixed table report generated in the report:

Word 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. table = 6. WDReportGen will put data into the sixth table in the report file.

4. cell=B2. The cells corresponding to the first record are "B2,C2,D2,E2".

5. The default range is "B2:E2".

6. WDReportGen executes the SQL statement, and gets data from data source. It puts the data into the report by records.

Example 2, Variable Table Report

The following function will makes the Word report: Mail Label.

@F1=Report(table=1 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 variable table report defined in the report template:

Word Report Template - Mail Label

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

Word Report Sample - Mail Label

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.

3. table=1. WDReportGen will put data into the first table 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. WDReportGen will copy the range for each record.

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

7. WDReportGen executes the SQL statement, and gets data from data source. First, it inserts some rows (11 rows per record) according to the number of records. Second, it copies the source range into the all added ranges. And then it adds page breaks per 4 records. Finally, it puts the data into the report by records.

Example 3, Variable Table Report with Group

The following function will makes the Word report: Customer Profile.

@F1= Report(table=1 cell=A2,B3,C3,D3,D4,E3,E4,E5 
copyrange=2:5 group=1 pagebreak = 5r)
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 variable table report defined in the report template:

Word Report Template - Customer Profile

The variable table report generated in the report:

Word Report Sample - Customer Profile

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. The default type is "var". It is a variable table report.

3. table=1. WDReportGen will put data into the first table in the report file.

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

5. cell= A2,B3,C3,D3,D4,E3,E4,E5. These cells correspond to the first record.

6. copyrange=2:5. Because the default range is "B3:E5", you must specify a range explicitly. WDReportGen will copy the range for each record.

7. There is no grouprange. WDReportGen will give a default value. The default grouprange is "2:5".

8. pagebreak = 5r. WDReportGen will add a page break per 5 records.

9. WDReportGen executes the SQL statement, and gets data from data source. First, it groups the data. Second, it inserts some ranges according to the number of records. Next, it copies the source range into the all added ranges. And then it adds page breaks per 5 records. Finally, it puts the data into the report by records.

Form Report Function

The FORM REPORT function executes a SQL statement to get data from data source, and puts data into a range in the report file. You can put data from data source as text, list, title and table in the report file.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "form"
CELL= celllist
RANGE = range
GROUP= grouplist
GROUPRANGE = grouprange
IMAGE = fieldlist
PAGEBREAK = pagelength
NODATA = nodataoption
CONNECT = datasource

The TYPE argument specifies the report type. "form" means a form report.

The CELL argument specifies the positions where data values will be inserted. The celllist is the list of merge fields or quote fields separated by the "," character. For example, "ProductName, ProductID, QuantityPerUnit, UnitPrice". The merge fields or quote fields in the celllist should correspond to the data source fields in the SQL statement. The value of the first data source field is put into the first merge field or quote field, and the value of the second data source field is put into the second merge field or quote field ......

The RANGE argument specifies the range to be used for the records. WDReportGen will repeat the range for each record. A range is defined by a bookmark. You reference a range using a bookmark name. The default range is the group range or the entire document.

The GROUP argument specifies the group of 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 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 groups should be in accordance with the order of ORDER BY clause in the SQL statement.

The GROUPRANGE argument follows the GROUP argument, and specifies the range of group. 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. WDReportGen will repeat the range for each group. A range is defined by a bookmark. You reference a range using a bookmark name. The range of the group should contain the range of details and the area that includes all merge fields or quote fields for this group. For example, there are two groups, the range of group one contains all merge fields or quote fields for the group one and the range of group two, and the range of group two contains all merge fields or quote fields for the group two and the range of details. The default range is the range of the upper level group or the entire document.

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 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 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 WDReportGen 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 no page break.

The NODATA argument specifies an option when no data are returned from data source. If the value is "delrange", WDReportGen will delete the range 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 WRF 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 will makes the Word report: Product Catalog.

@F1=Report(type=form cell=CategoryName,Description
,ProductName,ProductID,QuantityPerUnit,UnitPrice
range=Product group=1,2 grouprange=Category)
SELECT CategoryName
,Description
,ProductName
,ProductID
,QuantityPerUnit
,UnitPrice
FROM Products, Categories
WHERE Products.CategoryID = Categories.CategoryID
ORDER BY 1,3
;

Result

The form report defined in the report template:

Word Report Template - Product Catalog

The form report generated in the report:

Word Report Sample - Product Catalog

Remarks

1. The SQL statement will get the information of products including product category, category description, product name, product ID, quantity per unit, unit price.

2. type="form". It is a form report.

3. cell=CategoryName, Description, ProductName, ProductID, QuantityPerUnit, UnitPrice. These merge fields or quote fields correspond to data source fields in the SQL statement.

4. range= Product. The bookmark "Product" defines the range for detail data. WDReportGen will copy the range for each record.

5. group=1,2. WDReportGen will group data by CategoryName and Description.

6. grouprange= Category. The bookmark "Category" defines the group range. WDReportGen will copy the range for each group.

7. WDReportGen executes the SQL statement, gets data from data source, and groups the data. It will process the report by record. First, it fetches a record. Second, it copies the source range/group range and inserts the range/group range into the report. Next, it puts the data into the report. And then it processes the next record.

Chart Function

The CHART function is used to make the charts.

Syntax

Chart(...)
sqlstatement

Arguments

TYPE = reporttype
CHART = chart
FILLORDER = fillorder
CELL= celllist
RANGE = range
GROUP= grouplist
GROUPRANGE = grouprange
RANGECOUNT = rangecount
NODATA = nodataoption
CONNECT = datasource

The TYPE argument specifies the report type. Possible values are fix or var. "fix" means a fixed table report, and "var" means a variable table report. For Excel chart, the default is var. For Graph chart, the default is fix.

The CHART argument identifies a chart in the report template. The chart is the index number or the bookmark name of the chart. The index number starts at 1. For examples, chart 2 is the second chart in the document. You can reference a chart by a bookmark. For examples, chart="Chart1". "Chart1" is the bookmark of chart.

The FILLORDER argument specifies the order in which WDReportGen fills data. Possible values are row or col. "row" means to fill data by rows. "col" means to fill data by columns. For Excel chart, the default is row. For Graph chart, the default is col.

The CELL argument specifies the positions where data values will be inserted. The celllist is the list of cells separated by the "," character. It identifies the cells in a datasheet or 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 ...... WDReportGen 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. Note: On the datasheet of Graph chart, the leftmost column and the top row, which are commonly used for legend text or axis labels, are referred to as column 0 (zero) and row 0 (zero).

The RANGE or COPYRANGE argument specifies the range in the worksheet or datasheet to be used for the records. WDReportGen will skip or repeat the range for each record. You can reference a range of cells like "2:4" or "B2:D5". The range "2:4" means 3 entire rows. The range "A:B" means 2 entire columns. The default range is the area that includes all cells for the records. For the fixed table report, WDReportGen will skip the range for each record. For the variable table report, it will insert the blank range for each record. The COPYRANGE argument is similar to the Range. It will copy the original range to the range where data will be filled for each record. If there is no range or copyrange argument, the default for the fixed table report is range, and the default for the variable table report is copyrange.

The GROUP argument specifies the group of 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 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 groups should be in accordance with the order of ORDER BY clause in the SQL statement.

The GROUPRANGE argument follows the GROUP argument, and specifies the range of 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. WDReportGen will repeat the group range for each group. The range of group should contain the range of details and the area that includes all cells for this group. You reference a group range like "2:4" or "B2:D5". The ranges of groups must be same as the range of details.

The RANGECOUNT argument specifies the number of blank range which you defined in the report template. It is valid when the type is "var". Possible values are 1 or 2. One means one blank range you defined, and two means two ranges. If you hope that the format of the last row/column border can be different from the others, you can define two blank ranges. Default is 1.

The NODATA argument specifies an option when no data are returned from data source. It is valid when the type is "var". If the value is "delrange", WDReportGen will delete the range 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 WRF file, and starts at 1. The default implies the first data source.

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

Example, Microsoft Excel Chart

The following function makes the chart: Sales by Categories.

@F3_2=CHART(chart=Chart3 cell=A2 rangecount=2)
SELECT c.CategoryName
	, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
	,OrderDetails d
	,Products p
	,Categories c
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND p.CategoryID = c.CategoryID
AND YEAR(o.OrderDate) = 1996
AND MONTH(o.OrderDate) = 04
GROUP BY c.CategoryName
ORDER BY c.CategoryName

Result

The worksheet of the chart defined in the report template:

Word Report Template - Sales by Categories

The chart defined in the report template is a blank chart.

The worksheet of the chart generated in the report:

Word Report Sample - Sales by Categories

The chart generated in the report:

Word Chart Sample - Sales by Categories

Remarks

1. The SQL statement will get the information of sales by categories, including category name, and sales amount.

2. chart = Chart3. "Chart3" is the bookmark name of chart. It is an Excel chart. WDReportGen will put data into the worksheet of chart in the report file.

3. The default type is var. It is a variable table report.

4. The default fillorder is row. WDReportGen will fill data by rows.

5. cell=A2. The cells corresponding to the first record are "A2,B2".

6. The default range is "A2:B2". It is a copyrange.

7. rangecount=2. There are two blank ranges in the report template.

8. WDReportGen executes the SQL statement, and gets data from data source. First, it inserts some ranges according to the number of records. Second, it copies the source range into the all added ranges. And then it puts the data into the report by records. Finally, it refreshes the chart.

ExecSQL Function

The EXECSQL function executes a SQL statement, but does not return result to 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 WRF 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

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 ...
;