Chapter 5 Reporting with WDReportGen

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 report template file, the name of report file, 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 the layouts, formats and styles of report. The report file is the report you want to generate. The type of report file can be different from the template file. The log file records the log information in the report generating.

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 WRF file. In the paths and names of report file, template file and log file, you can use parameters. For detailed information about parameters, refer to "Configuring Parameters" in this document.

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 template file, report file and log file into their text box.

4. WDReportGen 5 can not convert a file to other file format. You cannot change the File Type box.

5. If you want to protect the report, select the Protect Report check box, and input a password in the Password box. If the check box is selected, the Word report generated is protected, and cannot be modified without the password.

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

Converting files

WDReportGen 5 can not convert a file to other file format.

Configuring Data Sources

About data sources

A data source identifies a database you want to access. WDReportGen can access to almost all of the databases such as Oracle, DB2, Sybase, Informix, Microsoft SQL Server, Teradata, MySQL, Microsoft Access and dBase through OLE DB and ODBC. It supports more than one data sources in one report. You can get data from the different databases such as Oracle, DB2 and Microsoft SQL Server, and put them into one report.

You can define a connection to a data source using an ODBC data source name or a connection string. If you use an ODBC data source name to make a connection, you should specify a user name and a password. If you use a connection string to make a connection, you also should specify a data source name that you can reference in functions.

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.

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.

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 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 the functions.

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

@functionno=functionname(arguments)
sqlstatement

The functionno is the label of 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.

For more detailed information about functions, see "Function Reference" in this document.

You can use comments in text. A comment is the "/*" characters, followed by any sequence of characters (including new lines), followed by the "*/" characters. You cannot nest comments.

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

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, you can use the aggregate functions in SQL statement, such as COUNT, SUM, AVG, MAX, and MIN.

  1. In the fixed table report, you can add total directly using a separate SQL.
  2. In the variable table report, you must add the 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 the variable table report function.

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.

To group data in a report, you should use GROUP argument in the REPORT function. For more detail information, refer to "Table Report Function" and "Form Report Function" in this document.

Using the feature of grouping data, you can make sub reports within a report. A sub report would typically be used to perform one-to-many lookups such as Customer / Order / OrderDetails.

To make sub reports within the main report,

1. Write a JOIN SQL statement to get data from two or more tables. For example, you can join Customers, Orders and OrderDetails tables.

2. Use GROUP argument in the REPORT function.

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

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 the different kinds of summaries, repeat the step 1, and insert results into another temporary table.
  3. Use the variable table report function, and join the detail data and the summary data using JOIN. The summary fields must be included in the group list.

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 building process. 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 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 picture is "", WDReportGen will return "".

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 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 products. The field OrderDate is the date type, and CategoryName is the 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 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.docx
LogFileName=log\report_$CustomerID.log
or
ReportFileName=report\$CustomerID\report.docx
LogFileName=log\$CustomerID\report.log

Programming

Making WRF files programmatically

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