Report Component for Microsoft Word

Chapter 6 WRF Files

Using WRF files

About WRF files

Like WDReportGen, WDReportCom also can read a WRF file to generate a report. The WRF file is a text file with a .wrf 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 WRF file tells WDReportCom how to get data from data sources and how to put data into a report. Using the WRF file, it will simplify your development.

Using a WRF file with WDReport Object

WDReport object provides the WordReport method to generate a report based on a WRF file. For example, you have created the WRF file "myreport.wrf" and the template file. In the WRF 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 WordReport method to generate the report.

Set wdApp = New Word.Application
Set wdrpt = New WDReport
Call wdrpt.WordReport(wdApp, "c:\wordreport\myreport.wrf", _
    "1996-05-01", "Dairy Products")

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

Using a WRF file in command line

In the WDReportCom, there is an executable file WordReport.exe that can read a WRF file to generate a report. It is the same as WDReportGen command line. For example, you have created the WRF file "myreport.wrf" and the template file. In the WRF 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 WordReport.exe in command line mode as follows:

wordreport c:\wordreport\myreport.wrf 1996-05-01 "Dairy Products"

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

Creating a WRF file

The WRF file is a text file. You can create and modify a WRF file in WDReportGen or a text editor.

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

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

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

For example, you write a batch file runrpt.bat as follows. changewrf is an executable file that reads template.txt and output template.wrf. First runrpt.bat call changewrf to make the WRF file, and then call WordReport.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
changewrf %1 <"template.txt" >"template.wrf"
WordReport "template.wrf" %1

Using parameters

You can use parameters in the WRF file. You can pass values to WDReportCom when it processes a WRF file. WDReportCom 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, WDReportCom 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.doc
LogFileName=log\report_$CustomerID.log
or
ReportFileName=report\$CustomerID\report.doc
LogFileName=log\$CustomerID\report.log

Converting files

You can convert a file from Microsoft Word document to and from another file format. For example, the template file is a RTF file with a .rtf extension, and the report file is a HTML file with a .htm extension. Microsoft Word uses file format converters to open and save documents in different formats. The most commonly used converters are installed with Microsoft Word by default. If you want to open or save in a format that's not installed by default, you may need to install additional converters.

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

File Format Name Value Description Extension Converter

wdFormatDocument 0 Word Document doc Office97

wdFormatTemplate 1 Word Template dot Office97

wdFormatRTF 6 Rich Text Format rtf Office97

wdFormatHTML 8 HTML htm html Office2000

wdFormatFilteredHTML 10 Filtered HTML htm html Office2003

wdFormatWebArchive 9 Web Archive mht mhtml Office2003

wdFormatXML 11 XML Document xml Office2003

wdFormatText 2 Text txt Office97

wdFormatTextLineBreaks 3 Text (Line Breaks) txt Office97

wdFormatDOSText 4 Text (DOS) txt Office97

wdFormatDOSTextLineBreaks 5 Text (DOS Line Breaks) txt Office97

wdFormatUnicodeText 7 Unicode Text txt Office97

MSWordWin2 Word 2.x for Windows doc External

MSWordWin5 Word 5.0 for Windows doc External

MSWord6RTFExp Word 97-2003 & 6.0/95 - RTF doc External

wks632 Works 6.0 & 7.0 wps External

MSWorksWin6 Works 6.0 & 7.0 wtf External

Works2001 Works 7.0 wps External

WrdPrfctDos WordPerfect 5.x doc External

WordPerfect6x WordPerfect 6.x wpd doc External

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.

WRF File Reference

WRF File Format

The layout of a WRF file is as the following:

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

"WordReport" is the flag of WRF file. "Version 2.0" is the version of the WRF file.

a WRF 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 WRF 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 WRF 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, WDReportCom 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, WDReportCom 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, WDReportCom 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, WDReportCom will make a connection to data source using the ODBC data source name.

EncryptPassword =Y/N

This setting specifies how to save the passwords of the data sources. If the value is Y, passwords will be saved in an encrypted format. If the value is N, the 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 WRF 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 WRF 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, wdFormatRTF or 6. What file format WDReportCom supports is dependent on your Microsoft Word and converters installed.

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 WRF 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 WRF 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. WDReportCom executes a SQL statement to get data from data source, and directly fills data into the cells of a table.

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 WDReportCom supports is 3.

The FILLORDER argument specifies the order in which WDReportCom 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 ...... WDReportCom 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. WDReportCom 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 WDReportCom 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

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

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

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. WDReportCom 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 table.

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 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 ...... WDReportCom 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 records. WDReportCom 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, WDReportCom 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", WDReportCom 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

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

@F1=Report(type=var table=1 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. WDReportCom 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. WDReportCom 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, WDReportCom 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. WDReportCom 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 WDReportCom 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

This example uses Group Table Report function to make 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

Form Report

Uses FormReport method to generate a form report and group data. For a form report, you can put data from data source as text, list, title and table in the report file. WDReportCom gets data from a recordset object, copy the group range for each group, and copy the detail range for each record.

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.

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

This example uses Form Report function to make 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

MSGraph Chart

Uses MSGraphChart method to generate a chart. WDReportCom gets data from a recordset object, and fills data into the datasheet of a chart in the report file.

Syntax

Report(...)
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 RANGE argument specifies the range in the datasheet of the chart to be used for the records. WDReportCom 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 sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Chart function to make 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

Excel Chart

Uses ExcelChart method to generate a chart. WDReportCom gets data from a recordset object, and fills data into the worksheet of a chart in the report file.

Syntax

Report(...)
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 WDReportCom will directly fill data vales into the worksheet of the chart. "var" means that WDReportCom 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 sqlstatement is a SQL statement such as a SELECT statement.

Example

This example uses Chart function to make 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

DocVariable

Uses SetDocVariable method to assigns the values to the docvariables defined in the Word document. WDReportCom will just fetch the first record, no matter how many records are returned from data source.

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.

Remarks

DocVariable function supports headers and footers. You can use it to put data into headers or footers.

Example

This example uses DocVariable function to assign the values of min_date and max_date to the document variables: BeginDate and EndDate.

@F1=DOCVARIABLE(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 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 ...

File Format Name	Value	Description	Extension	Converter
wdFormatDocument	0	Word Document	doc	Office97
wdFormatTemplate	1	Word Template	dot	Office97
wdFormatRTF	6	Rich Text Format	rtf	Office97
wdFormatHTML	8	HTML	htm html	Office2000
wdFormatFilteredHTML	10	Filtered HTML	htm html	Office2003
wdFormatWebArchive	9	Web Archive	mht mhtml	Office2003
wdFormatXML	11	XML Document	xml	Office2003
wdFormatText	2	Text	txt	Office97
wdFormatTextLineBreaks	3	Text (Line Breaks)	txt	Office97
wdFormatDOSText	4	Text (DOS)	txt	Office97
wdFormatDOSTextLineBreaks	5	Text (DOS Line Breaks)	txt	Office97
wdFormatUnicodeText	7	Unicode Text	txt	Office97
MSWordWin2		Word 2.x for Windows	doc	External
MSWordWin5		Word 5.0 for Windows	doc	External
MSWord6RTFExp		Word 97-2003 & 6.0/95 - RTF	doc	External
wks632		Works 6.0 & 7.0	wps	External
MSWorksWin6		Works 6.0 & 7.0	wtf	External
Works2001		Works 7.0	wps	External
WrdPrfctDos		WordPerfect 5.x	doc	External
WordPerfect6x		WordPerfect 6.x	wpd doc	External