Report Generator for Microsoft Word

Chapter 6 Function Reference

Report Function

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

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

Fixed Table Report Function

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

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "fix"
TABLE = table
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 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 argument specifies the range in the table to be used for the records. WDReportGen will skip 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 records.

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 WDReportGen 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 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 makes the 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. First, it fetches the first record, puts the value of the first field into cell B2, the value of the second field into cell C2, the value of the third field into D2, and the value of the fourth field into E2. And then it fetches the next record, skips one row, and puts data into cells B3, C3, D3 and E3......

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. WDReportGen 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 table in the report file.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "var"
TABLE = table
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 RANGE or COPYRANGE argument specifies the range in the table 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 "B:D". The default range is the area that includes all cells for the records. 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.

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 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 sqlstatement is a SQL statement such as a SELECT statement.

Example

The following function will makes the 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 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. 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 fetches the first record, copy the range, and fill 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. WDReportGen 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 table.

Syntax

Report(...)
sqlstatement

Arguments

TYPE = "var"
TABLE = table
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 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 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 sqlstatement is a SQL statement such as a SELECT statement.

Example

The following function will makes the 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 group varibale table report defined in the report template:

The group variable 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 TABLE argument and GROUP argument, so it is a group 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 the 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. The default grouprange is "2:5".

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

9. WDReportGen executes the SQL statement, gets data from data source, and puts data into the table in the report file. Because the range is same as the group range, WDReportGen 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 5 records.

Form Report Function

For a form report, you can put data from data source as text, list, title and table in the report file. WDReportGen 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 report.

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 the report. The grouplist 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 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 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 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 the details and the area that includes all merge fields or quote fields for this group. For example, there are two groups, the range of the group one contains all merge fields or quote fields for the group one and the range of the group two, and the range of the group two contains all merge fields or quote fields for the group two and the range of the details. The default range is the range of the upper level group or the entire document.

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

Example

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

The form report generated in the report:

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 record. 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, and gets data from data source. It fetches a record, copy the group range for each group, copy the range for each record, and inserts data into the merge fields or quote fields.

Chart Function

The CHART function executes a SELECT statement to get data from data source, and put data into the datasheet of a chart in the report file. The CHART function supports two types of charts:

MSGraph chart
Excel chart

MSGraph Chart Function

If you use Microsoft Graph to create a chart, you can use MSGraph Chart function.

Syntax

Chart(...)
sqlstatement

Arguments

CHART = chart
FILLORDER = fillorder
CELL= celllist
RANGE = range
CONNECT = datasource

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 a chart.

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. The celllist identifies the cells in a data sheet. 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 argument specifies the range in the datasheet of the chart to be used for the records. WDReportGen will skip the rows/columns of the range for each record. A range is composed of some rows or columns. You can reference a range of cells like "2:4" or "B:D". The default range is the area that includes all cells for details.

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

Example

The following function makes the chart: Sales by Categories.

@F3_2=CHART(chart=Chart3 cell=A0)
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 datasheet 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 datasheet of the chart generated in the report:

Word Report Sample - Sales by Categories

The chart generated in the report:

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 a chart.

3. The default fillorder is col. WDReportGen will fill data by columns.

4. cell=A0. The cells corresponding to the first record are "A0,A1".

5. The default range is "A:A".

6. WDReportGen 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 A0, the value of the second field into cell A1. And then it fetches the next record, skips one cplumn, and puts data into cells B0, B1......

Excel Chart Function

If you use Microsoft Excel to create a chart, you can use Excel Chart function.

Syntax

Chart(...)
sqlstatement

Arguments

CHART = chart
TYPE = type
FILLORDER = fillorder
CELL= celllist
RANGE = range
CONNECT = datasource

The TYPE argument specifies the report type. Possible values are fix or var. "fix" means that WDReportGen will directly fill data vales into the worksheet of the chart. "var" means that WDReportGen will add some blank rows/columns before filling data values into the worksheet of the chart. Default is var. When the report type is "var", you should reserve two rows/columns in the worksheet in the report template, and set the data range of the chart to 2 rows/columns. The RESERVE must be 2.

The RANGE argument specifies the range in the worksheet of the chart to be used for the records. WDReportGen will skip the rows/columns of the range for each record. A range is composed of some rows or columns. You can reference a range of cells like "2:4" or "B:D". The default range is the area that includes all cells for details. For a variable table report, WDReportGen will insert the blank rows/columns of the range for each record.

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

Example

The following function makes the chart: Sales by Categories.

@F3_2=CHART(chart=Chart3 cell=A2)
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:

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

The worksheet of the chart generated in the report:

The chart generated in the report:

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 a chart.

3. The default type is var. You should reserve 2 blank rows in the worksheet, and select the 2 rows as the data range of the chart.

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 ��2:2��.

7. WDReportGen executes the SQL statement, and gets data from data source. First, it will add some blank rows in the worksheet according to the number of the records. And then it will put data into the worksheet.

DocVariable Function

The DOCVARIABLE function executes a SQL statement, and assigns the results to the document variables defined in the Microsoft Word document. The document variable is defined using DocVariable field.

Syntax

DocVariable(...)
sqlstatement

Arguments

NAME= namelist
CONNECT= datasource

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

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

WDReportGen will just fetch the first record, no matter how many records are returned from data source. DocVariable function supports headers and footers. You can use it to put data into headers or footers.

Example

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

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

Remarks

WDReportGen executes the SQL statement, get data from data source. It assigns the value of field "min_date" to the document variable "BeginDate" and the value of field "max_date" to the document variable "EndDate".

You should define the document variables in the report template first. The document variables are defined as follows:

{ DOCVARIABLE BeginDate \@ "YYYY-MM-DD" \* MERGEFORMAT}
{ DOCVARIABLE EndDate \@ "YYYY-MM-DD" \* MERGEFORMAT}

To define a document variable, do as follows:

1. Run Microsoft Word program, and open the report template file.

2. Click where you want to insert the information.

3. On the Insert menu, click Field.

4. Click DocVariable, and then input a field name.

5. Press OK button.

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 Word report file.

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