Creating and Opening WRF Files

About WRF files

To generate a report with WDReportGen, you must create a WRF file with a .wrf extension. The WRF file contains information such as the name of the report template file, the name of the report file, the log file name, data sources, parameters and functions. The WRF file tells WDReportGen how to get data from data sources and how to put data into a report.

Create a new WRF file

On the File menu, click New.

Open a WRF file

1. On the File menu, click Open.

2. In the Look in list, click the drive, folder, or Internet location that contains the file you want to open.

3. In the folder list, locate and open the folder that contains the file.

4. Click the file, and then press Open button.

Save a WRF file

On the File menu, click Save. If you're saving the file for the first time, you'll be asked to give it a name.

If you want save a file to another name, do as follows:

1. On the File menu, click Save As.

2. In the File name box, enter a new name for the file.

3. Press Save button.

Configuring Files

About files

You should specify the report template file, report file, report file type and log file. The report template file defines layouts, formats and styles of the report. The report file is the report you want to generate. The type of the report file can be different from the template file. The log file records the log information in the report generating.

Configuring file information

1. On the Report menu, click Configuration. The Configuration dialog box appears.

2. Click the File tab.

3. Input the path and name of the template file, the report file and the log file into their text box.

4. In the File Type box, click the file type you want. If the file type of the report is same as the template file, click the (Default) in the File Type box. WDReportGen will display the converter type.

5. If you want to protect the report, select the Protect Report check box. If the check box is selected, the Word report generated is protected, and can not be modified. If you select Random Password option button, a random password will be created to protect the report. If you select Input Password option button, you can input a password to protect the report.

6. Press OK button to confirm the changes, press Cancel button to discard the changes.

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 WDReportGen supports can be one of these. What file format WDReportGen 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 WDReportGen directory contains the information of file formats. You can expand it if your Microsoft Word supports more file formats.

Note: For an external converter, the file format name is the class name of the file converter, and the format value may not be the same on another computer. So do not assign a number to the format value.

Configuring Data Sources

About data sources

A data source identifies a database computer you want to access. Because of accessing data through ODBC, WDReportGen can access a wide range of data sources, such as Oracle, DB2, Sybase, Informix, Microsoft SQL Server, Teradata, MySQL, Microsoft Access, dBase. WDReportGen supports more than one data sources in one report. You can get data from some different databases such as Oracle, DB2 and Microsoft SQL Server, and put them into one report.

Adding, modifying and deleting a data source

1. On the Report menu, click Configuration. The Configuration dialog box appears.

2. Click the Data Source tab.

3. If you want to add a data source, press New button, the New Data Source dialog box appears. Input data source name, user name and password, press OK button.

• To define a connection using an ODBC data source name, click Using ODBC data source name option, input data source name, user name and password, press OK button.
• To define a connection using a connection string, click Using connection string option, input data source name, and connection string, press OK button.

4. If you want to modify a data source, click the data source name in the Data Source list box, and press Edit button, the Edit Data Source dialog box appears. Change data source name, user name and password, press OK button.

• To define a connection using an ODBC data source name, click Using ODBC data source name option, change data source name, user name and password, press OK button.
• To define a connection using a connection string, click Using connection string option, change data source name, and connection string, press OK button.

5. If you want to delete a data source, click the data source name in the Data Source list box, and press Delete button, the confirmation dialog box appears. Press Yes button to delete the data source.

6. You can test a data source. Click the data source name in the Data Source list box, and Press Test button to display the information of connection to the data source.

7. Select or clear the Encrypt Password check box. If the check box is selected, passwords will be saved in an encrypted format. Or passwords will be saved in plain text.

8. Press OK button to confirm the changes, press Cancel button to discard the changes.

Configuring Parameters

About parameters

You can use parameters in SQL statements. These values need to be provided to WDReportGen before it executes these SQL statements. To use a parameter, you must declare it first. When WDReportGen generate a report, it will prompt you to input the value of the parameter. WDReportGen will replace the parameter name in the SQL statements with the actual value before it submits the SQL statements to data sources.

A parameter has a name, a title and a default value. The name of a parameter identifies the parameter. You can use the names in SQL statements. The titles will be displayed in the prompt dialog box when WDReportGen is run.

Note: WDReportGen 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. For example, you give the name "$ReportDate" for a parameter. Parameters are case-sensitive.

Adding, modifying and deleting a parameter

1. On the Report menu, click Configuration. The Configuration dialog box appears.

2. Click the Parameter tab.

3. If you want to add a parameter, press New button, the New Parameter dialog box appears. Input parameter name, parameter title and default value, press OK button.

4. If you want to modify a parameter, click the parameter name in the Parameter list box, and press Edit button, the Edit Parameter dialog box appears. Change the name, title and default value of the parameter, press OK button.

5. If you want to delete a parameter, click the parameter name in the Parameter list box, and press Delete button, the confirmation dialog box appears. Press Yes button to delete the parameter.

6. Press OK button to confirm the changes, press Cancel button to discard the changes.

Inputting Functions

You should input functions in the editor window. A function includes a SQL statement and some arguments. WDReportGen executes the SQL statement, and determines whether or how to add data into the report. WDReportGen sequentially executes functions.

Each function is begin with the "@" character. Syntax:

@functionno=functionname(arguments)
sqlstatement

The functionno is a label of the report function.

The functionname represents a report function.

The arguments for a function define various properties for the function. For example, the "table" argument identifies a table in the Microsoft Word document. An argument takes the form Name="Value". The argument value can be delimited by single or double quotes.

The sqlstatement is a SQL statement.

Running WRF Files

You can run a WRF file to generate a report in Microsoft Word document format. WDReportGen supports Windows mode and command line mode.

Windows mode

1. On the Report menu, click Run, the Run Report dialog box appears.

2. If you want to display the generated report, select the Display Report with Microsoft Word check box.

3. Press Start button to run the WRF file.

4. If parameters are defined in the WRF file, WDReportGen will pop up a prompt dialog box. Input the values of the parameters, and press OK button.

5. While WDReportGen is being run, it will display some information such as status, SQL count, error count, function No., records count and log information.

6. You can interrupt the running. Click End button to interrupt it. WDReportGen will immediately save and close the report.

7. Click Close button after completion.

8. If you want to open the report, click Open Report File on the File menu.

9. If you want to check the log, click Open Log File on the File menu.

Command line mode

You can run a WRF file in command line. You have defined two parameters in the WRF file "myreport.wrf". The first parameter is sales date "$SalesDate", and the second is the category of the products "$Category". You can run WDReportGen in command line mode as follows:

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

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

Using Formula Fields

In Microsoft Word, you can use (Formula) field to calculates a number. To insert an (Formula) field in a table or in regular text, you can use the Formula command (Table menu) or press CTRL+F9. For example, the following formula multiplies 2 by 3 and then adds 5 to the result.

{ = 5+2*3 \# "#,##0.00"}

In an (Formula) field, an expression that can contain any combination of numbers, bookmarks that refer to numbers, fields resulting in numbers, and the available operators and functions. The expression can refer to values in a table and values returned by functions. For more detail information about (Formula) field, refer to Microsoft Word Help.

In a report template file, you can use all kind of (Formula) field. And they will be brought to the final report file.

Example

Add totals such as Total Quantity, Total Amunt.

You can use (Formula) field of Microsoft Word.

1. Create a template file as follows, define the formula of total quantity as "=SUM(ABOVE)" in cell B3, and the formula of total amount as "=SUM(ABOVE)" in cell C3.

2. Write report function as follow.

@F2=REPORT(table=Report2 type=var cell=A2)
SELECT c.CategoryName
	, SUM(d.Quantity)
	, 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) = YEAR('$ReportMonth-01')
AND MONTH(o.OrderDate) = MONTH('$ReportMonth-01')
GROUP BY c.CategoryName
ORDER BY c.CategoryName
;

3. The report generated by WDReportGen is as follows.

Sorting, Grouping and Totaling

Sorting data

Sorting means placing data in some kind of order to help you find and evaluate it. For example, you may want to have a customer list sorted alphabetically by name or by country.

To sort your data, you can use SQL. Use the ORDER BY clause to have your results displayed in a sorted order.

SELECT EmployeeID
,LastName
,FirstName
,HireDate
FROM Employees
ORDER BY HireDate;	/* ascending sort */

In the example above, results will come back in ascending order by hire date. To explicitly specify ascending or descending order, add ASC or DESC, to the end of your ORDER BY clause. The following is an example of a descending order sort.

ORDER BY HireDate DESC;	/* descending sort */

Totaling

You can sum the values, count all the values or only those values that are distinct from one another, and determine the maximum, minimum, average. To add totals, there are two ways.

Grouping data and subreports

Grouped data is data that is sorted and broken up into meaningful groups. In a customer list, for example, a group might consist of all those customers living in the same Region.

Subtotaling

A subtotal is a summary that totals or sums numeric values in a group. You can sum the values in each group, count all the values in each group, and determine the maximum, minimum, average in each group. For example, determine the total sales per sales representative in a sales reports.

To add subtotals, you can use aggregate function in SQL statement.

1. Use aggregate function and GROUP BY clause, get summary data for each group, and insert results into a temporary table.
2. If you have different kinds of summaries, repeat the step 1, and insert results into another temporary table.
3. Use group report function, and join the detail data and the summary data using JOIN. The summary fields must be included in the group list.
4. Except for sub-totals, you can add total too using aggregate function in SQL statement. You must add total first using a Fixed Table report function before you use the Variable Table report function. Because the cell address of the total field will change after you use Variable Table report function.

For more information, refer to the samples invoice.wrf and sales_detail.wrf within WDReportGen.

Pictures

Inserting pictures into a report template

To make eye-catching reports, you can add pictures to your reports. You can insert pictures into the report template directly in Microsoft Word. For example, you want to display a logo in your report. You can insert the logo graphics file into the report template. For more information about adding pictures to documents, refer to Microsoft Word Help.

Inserting pictures into a report

Except for inserting the static pictures during report design, you want to insert pictures during report buliding process. You hope a reporting tool to pull pictures from database into Word report. WDReportGen can insert pictures from the graphics files, and support all graphics file format that Microsoft Word support.

To insert pictures into a report using WDReportGen, you should do as follows:

1. Store the path and name of the graphics files in the database

You stored the path and file name of the pictures in database, did not store the pictures. The file path can be a relative path, an absolute path or a URL. For example, you store "images\emp1.jpg" in Photo field.

2. Identify the image fields in the report function

Write a report function in the WRF file, and identify the image fields using IMAGE argument. For example,

@F1=Report(table=1 ... image=photo)

3. Specify the inserted way, text wrapping style and size in the report template

To specify the inserted way, text wrapping style and size, you should write a formatting expression in the report template file. For a table report, you write a formatting expression in the cell. For a form report, write a formatting expression in the field switch "\#". WDReportGen will get the formatting expression, and insert a picture into the report according to the instruction in the format expression.

4. Run WDReportGen to generate report with pictures

During report generating process, WDReportGen will read the graphics files, and insert them into the report according to your instruction. If the path and file name of the picture is "", WDReportGen will return "". WdReportGen will return "#Error" if it does not find the file of the picture.

For more detail information about pictures, refer to the samples employee_profile.wrf, product_catalog.wrf within WDReportGen.

Using Parameters

To use a parameter, you must define it first. If you have defined a parameter name, you can use it in SQL statements. When WDReportGen is run, it will replace the parameter name in the SQL statements with the actual value before it submits the SQL statements to data sources. Besides in SQL statements, you can use parameters in the paths and names of report file and log file.

In fact, WDReportGen 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 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 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

Programming

Using add-ins, macros

In Microsoft Word, you can automate a task with a macro. A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task.

You can write macros in the report template file, and can use automatic macros, such as AutoOpen, AutoClose to automate a task. For examples, you can use AutoOpen macro to make the template, or use AutoClose to change the report after WDReportGen puts data into the report.

Sometimes you want to make a WRF file programmatically. You can do this because the WRF file is a text file. You can write a program to make a WRF file using C, perl or DOS shell, and then run WDReportGen 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 WDReportGen in command line mode.

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 WDReportGen 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" -C %1