| Chapter 4 Report Templates |
The report generated by WDReportCom is a Microsoft Word document. The layouts, formats and styles of the report are defined by a report template, and the data of the report are got from databases such as Oracle, DB2.
To make a report using WDReportCom, you should create a report template first. The report template is a Microsoft Word document that defines the layouts, formats and styles of the report. In the Microsoft Word report template, you can input static content such as titles, descriptions, comments, a cover, a company logo, format the static content, and define the format of the data you will get from databases.
WDReportCom will generate the report based on the report template file. All static contents and the layouts, formats and styles defined in the report template file will be brought to the final report file.
If you have known these concepts of Microsoft Word, please skip this section. For more detail information about Microsoft Word, refer to Microsoft Word Help.
A document is a Microsoft Word file with extension .doc. You can open and save it using Microsoft Word. Microsoft Word documents may contain a combination of text, formatting and graphics.
Headers and footers are areas in the top and bottom margins of each page in a document. You can insert text or graphics in headers and footers - for example, page numbers, the date, a company logo, the document's title or file name, or the author's name - that are printed at the top or bottom of each page in a document.
A table is made up of rows and columns of cells that you can fill with text and graphics. Tables are often used to organize and present information. You can set borders, shading, alignment and fonts in tables.
A bookmark identifies a location or selection of text that you name and identify for future reference. For example, you might use a bookmark to identify text that you want to revise at a later time.
When you fill a page with text or graphics, Microsoft Word inserts an automatic page break and starts a new page. To force a page break at a specific location, you can insert a manual page break.
There are two basic types of graphics that you can use to enhance your Microsoft Word documents: drawing objects and pictures.
Drawing objects include AutoShapes, diagrams, curves, lines, and WordArt drawing objects. These objects are part of your Word document. Use the Drawing toolbar to change and enhance these objects with colors, patterns, borders, and other effects.
Pictures are graphics that were created from another file. They include bitmaps, scanned pictures and photographs, and clip art. You can change and enhance pictures by using the options on the Picture toolbar and a limited number of options on the Drawing toolbar. In some cases, you must ungroup and convert a picture to a drawing object before you can use the Drawing toolbar options.
Inline picture: A graphic or other object that is positioned directly in the text of a Microsoft Word document at the insertion point.
Floating picture: A graphic or other object that is inserted in the drawing layer so that you can position it precisely on the page or in front of or behind text or other objects.
Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. You can create a chart in a Microsoft Word document using Microsoft Graph or Microsoft Excel. When you create a new chart in Word, Microsoft Graph or Microsoft Excel opens and a chart is displayed with its associated data in a data sheet or worksheet.
You can use these formatting features of Microsoft Word to effectively display your data.
To make text stand out, you can format the text in selected characters. You can set font, color, size of text, bold and italic formats, animate or highlight the text.
You can set text alignment, tab stops, line spacing, spacing before or after paragraphs, and borders.
Bulleted and numbered lists in Microsoft Word are easy to create. You can quickly add bullets or numbers to existing lines of text, or Microsoft Word can automatically create lists as you type.
Borders, shading, and graphic fills can add interest and emphasis to various parts of your document. You can add borders to pages, text, tables and table cells, graphic objects, pictures, and Web frames. You can shade paragraphs and text. You can apply colored or textured fills to your graphic objects.
By using AutoFormat, you can quickly apply formatting such as headings, bulleted and numbered lists, borders, numbers, symbols, and fractions to your text. You can automatically format a document either as you type or after you've written it. In both cases, you can control which automatic changes Microsoft Word makes. You can also turn off automatic formatting.
A style is a set of formatting characteristics that you can apply to text, tables, and lists in your document to quickly change their appearance. When you apply a style, you apply a whole group of formats in one simple task. For example, instead of taking three separate steps to format your title as 16 pt, Arial, and center-aligned, you can achieve the same result in one step by applying the Title style.
Fields are used as placeholders for data that might change in a document and for creating form letters and labels in mail-merge documents. Microsoft Word inserts fields when you use particular commands, such as the Date and Time command on the Insert menu. You can also manually insert your own fields by using the Field command on the Insert menu. Field codes appear between curly brackets, or braces ( { } ). Fields are somewhat like formulas in Microsoft Excel - the field code is like the formula, and the field result is like the value that the formula produces. You can switch between displaying field codes and results in your document.
(Formula) field calculates a number by using a mathematical formula. You can insert an (Formula) field in a table or in regular text. Computation in tables can be completed using (Formula) fields, such as add, subtract, multiply, divide, sum. Syntax:
{ = Formula [Bookmark ] [\# Numeric Picture ] }
DocVariable field defines a document variable. Each document has a collection of variables, which can be added and referenced by the Microsoft Visual Basic for Applications programming language. This field provides a way to display the contents of the document variables in the document. Syntax:
{ DOCVARIABLE "Name" }
Inserts the string assigned to a document variable. "Name" is the name of the document variable.
A table is made up of rows and columns of cells that you can fill with text and graphics. Tables are often used to make reports, and organize and present information.
WDReportCom supports two types of table reports: fixed table report, variable table report.
Fixed table report: The number of rows and columns in the table is fixed. When WDReportCom executes a SQL statement, directly puts the result data into cells in the table.
Variable table report: The number of rows or columns in the table is unfixed, and it is variable as the number of result records. When WDReportCom executes a SQL statement, it repeats the table rows or columns for each record or group, and then puts data into cells of the table.
For a fixed table report, you need to create a table in the report template file according to the report. The format of the table is the same as the format in the report, but cells that should be filled data into are blank. When WDReportCom executes a SQL statement, the data values from data source will be filled into these cells.
One record from data source can be put into two or more rows/columns. To do this, you need to create a repeat range that includes two or more rows/columns.
The format of the last row/column border can be different from the others. For example, the outside borders used double lines, and the inside borders used single lines. To do this, you should reserve the blank rows/columns for 2 records. When WDReportCom inserts some blank rows/columns, the new rows/columns will inherit the format of the first row/column in the reserved rows/columns.
WDReportCom will repeat the range for each record. Ranges can be nested. The inside range is the detail range for detail record, and the external range is the group range for group. WDReportCom will repeat the inside range for each record, and repeat the group range for each group.
To format cells that contain static contents, use "Format" menu in Microsoft Word. To format cells that contain (Formula) fields, use the switches of fields in Microsoft Word. For more detail information, refer to Microsoft Word Help.
For cells in which data are got from database, you can set font, color, alignment using Microsoft Word. But to display values in formatting string, you should use other way.
You should write formatting expressions into data cells in the report template file. WDReportCom will get the text of the cell as a format expression before it puts a value into a cell, and output the value using the format expression. In fact, WDReportCom calls the format function in Visual Basic. The text got from a cell is used as the format expression in format function. For a variable table report, WDReportCom will use the format expressions in the reserved rows/columns. For more information about format expression, refer to "Format Expression in Data Cells".
A format expression for numbers can have from one to four sections separated by semicolons. You can define the different formats and colors for positive values, negative values and zeros.
For example, the format "$#,##0;($#,##0)" has two sections: the first defines the format and color (black) for positive values and zeros; the second section defines the format and color (red) for negative values. It displays "2345.12" as "$2,345", displays "-5432" as "($5,432)".
The format "#,##0.00;;" has three sections: the first defines the format and color (black) for positive values, the second defines the format and color (red) for negative values, the third section defines the format and color (blue) for zeros. Note, the first semicolon ";" is red, the second semicolon ";" is blue. The negative values and zeros are printed using the format of the positive value. But the color for negative values is red, the color for zeros is blue. It displays "8.9" as "8.90", displays "-123" as "-123.00", and displays "0" as "0.00".
Tables don't have to consist of simple grids. Not every row has to have the same number of columns. You can merge and split cells to create irregular tables. An irregular table is the table that contains split cells or merge cells, and it does not have the same number of cells for each row or column. While an irregular table provides for an attractive way to display data, but it does make it harder to process the document. You have some difficulty to reference a cell in an irregular table. For example in the following table, for most Office version, cell1 is in column 3 and row 2, cell2 is in column 3 and row 3. But for some lower Office version, cell2 is in column 2 and row 3. Moreover, an error may occur when you try to work with some rows or columns in an irregular table.
To simplify your work and ensure that report function can be executed correctly, you should regularize the irregular tables. Split the merge cells, and remove the border in these cells. For example, the following table is a regularized table, cell1 is in column 3 and row 2, and cell2 is in column 3 and row 3.
You can reference table cells as A1, A2, B1, B2, and so on, with the letter representing a column and the number representing a row. Cell references in Microsoft Word are always absolute references and are not shown with dollar signs. You can reference an entire row or column in a calculation in the following ways:
Unfortunately, there is no inherent capability of Microsoft Word to inform you of the reference of a cell you have selected. You can get around this problem by using a macro. In the sample file "monthly_sales.doc", there is a macro called "CellRef". The macro can tell you the table number and the cell reference. When you position the insertion pointer in a table cell and then run the macro, it displays a message box that shows the table number and the cell reference of the current cell. You can copy the macro "CellRef" into your documents. It is easy for you to reference a cell.
You can reference a table by an index number or a bookmark. The index number represents the position of the table in a document. The index number starts at 1. So table 1 is the first table in a document, table 2 is the second table, and so on. You can reference a nested table inside a table by an index number like 2-1-2. 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. In the sample file "monthly_sales.doc", there is a macro called "CellRef". The macro can tell you the index number of a table. When you position the insertion pointer in a table cell and then run the macro, it displays a message box that shows the table number and the cell reference of the current cell.
You can reference a table by the bookmark too. If you add a bookmark in a table, you can reference the table using the bookmark. If you want to reference a nested table inside a table, you must add the bookmark in the nested table. It is very useful if you do not know the number of tables. To add a bookmark in a table, do as follows:
1. Click in the upper-left cell in the first row of the table, and place the insertion point before the text.
2. On the Insert menu, click Bookmark.
3. Under Bookmark name, type or select a name.
Bookmark names must begin with a letter and can contain numbers. You can't include spaces in a bookmark name.
4. Click Add.
Note: Microsoft Word 97 or lower does not support the nested tables.
To enhance the visual impact of your report, you can insert pictures into your report. WDReportCom supports many popular graphics file formats: bitmap, JPG, GIF, PNG, TIFF and so on. For the graphics file formats WDReportCom supports, refer to Microsoft Word Help.
You should store the path and name of the graphics files in the database, and identify the image fields in the report function. WDReportCom will read the graphics files, and insert them into the cells in the report file. You can adjust the position of the pictures by changing the cell margins.
To specify the inserted way, text wrapping style and size, you should write a formatting expression into the cell in the report template file. WDReportCom will get the text of the cell, and insert a picture into the cell according to the instruction in the format expression. The format expression for pictures as follows:
[wrapstyle] [size]
The wrapstyle specifies the inserted way and text wrapping style, and can be one of the following values. The default value is NONE. "NONE" means floating pictures.
| Values | Description |
| INLINE | Inline picture |
| NONE | Floating picture, none text-wrapping style |
| SQUARE | Square text-wrapping style |
| THROUGH | Through text-wrapping style |
| TIGHT | Tight text-wrapping style |
| TOPBOTTOM | TopBottom text-wrapping style |
The size specifies the size of a picture. Possible values are STRETCH, Wnnn or / and Hnnn. "STRETCH" means that the picture is resized to fit within the cell. "W100" means that the width of the picture is set to 100 points. "H50" means that the height of the picture is set to 50 points. The default means the original size. If you just specify the width or height of the picture, not both, WDReportCom will retain the original proportions of the picture when WDReportCom resize it.
Example
inline w120 h90
Remarks
WDReportCom will insert an inline picture, and set the width of the picture to 120 points, the height to 90 points. Note: On Microsoft Word 97 or lower version, it may not work correctly if you insert a floating picture into a table. And it will split the table under some text wrapping style. You should insert inline pictures into a table.
Beside table reports, WDReportCom supports form reports too. For a form report, you can get data from data sources, and put data as text, list, title and table in the report file. So you can make a form report as follows:
The report template file of a form report must contain merge fields or quote fields where data values will be inserted. A field can be created in text, list, title, table or other. When WDReportCom is run, it will replace the merge fields or quote fields with values from data source.
To create a merge field or quote field:
1. Click where you want to insert a field.
2. On the Insert menu, click Field.
3. From the Field names list, select MergeField or Quote.
4. In the Field name text box, enter a name for the merge field or quote field. For example, enter ProductName. Remember the field name, you will use it in the report function.
You can show field codes, and edit the field codes. To switch between field codes and results, do one of the following:
If the data value is a number, date or time, and you want to display it in a custom format, use the switches of fields like "\#" or "\@". For more detail information, refer to Microsoft Word Help.
A range represents a contiguous area in a document, and contains text, lists, tables, table rows or paragraphs. When WDReportCom generates a report, it will repeat the range for each record or group.
A range can be defined by a Word bookmark. Or the entire document will be defined as the default range.
To define a range with a bookmark:
1. Create a document as your report template. For example, you create a template as follows:
2. Select the range that you want to define as a repeat block. For example, you select a table row.
3. On the Insert menu, click Bookmark.
4. Under Bookmark name, enter a bookmark name and click Add. For example, enter Product.
In Microsoft Word, bookmarks are hidden by default. To show bookmarks:
1. On the Tools menu, click Options, and then click the View tab.
2. Select the Bookmarks check box.
3. The bookmark appears in brackets ([...]) on the screen.
Ranges can be nested. The inside range is for detail record, and the external range is for group. For example, you defines a bookmark Category for the group of product category, and a bookmark Product for the detail record of product.
To enhance the visual impact of your report, you can insert pictures into your report. WDReportCom supports many popular graphics file formats: bitmap, JPG, GIF, PNG, TIFF and so on. For the graphics file formats WDReportCom supports, refer to Microsoft Word Help.
You should store the path and name of the graphics files in the database, and identify the image fields in the report function. WDReportCom will read the graphics files, and insert them into the fields in the report file.
To specify the inserted way, text wrapping style and the size, you should write a formatting expression in the field switch "\#" in the report template file. WDReportCom will get the format string in the field switch "\#", and insert a picture according to the instruction in the format expression. The format expression for pictures as follows:
[wrapstyle] [size]
The wrapstyle specifies the inserted way and text wrapping style, and can be one of the following values. The default value is INLINE. "INLINE" means inline pictures.
| Values | Description |
| INLINE | Inline picture |
| NONE | Floating picture, none text-wrapping style |
| SQUARE | Square text-wrapping style |
| THROUGH | Through text-wrapping style |
| TIGHT | Tight text-wrapping style |
| TOPBOTTOM | TopBottom text-wrapping style |
The size specifies the size of a picture. Possible values are Wnnn or / and Hnnn. "W100" means that the width of the picture is set to 100 points. "H50" means that the height of the picture is set to 50 points. The default means the original size. If you just specify the width or height of the picture, not both, WDReportCom will retain the original proportions of the picture when WDReportCom resize it.
Example
/# "square w84"
Remarks
On the supposition that the original picture is size 144 x 168 points. WDReportCom will insert a floating picture, set the text wrapping type to the square type, set the height of the picture to 72 points, and the width to 84 points.
You can create many different types of charts in Microsoft Word. The chart software may be Microsoft Graph or Microsoft Excel. It is depended on the version of your Microsoft Word. For Microsoft Word 2003 or earlier, the default chart software is Microsoft Graph. For Microsoft Word 2007 or later, the default chart software is Microsoft Excel.
WDReportCom supports two kinds of charts created by Microsoft Graph or Excel. It executes a SQL statement, and puts the result data into the datasheet or worksheet of the chart. To work with charts created in Graph or Excel, you must have Graph or Excel installed.
To create a Graph chart in the report using WDReportCom, you need to add a Graph chart in the report template file first. The chart will be brought into the report file with the same chart type, display option, data format, label format and other chart item
If your Microsoft Office is earlier than Office 2007, or Microsoft Excel 2007 is not installed, when you create a new chart in Microsoft Word, Microsoft Graph opens.
To add a Graph chart in the template file:
1. Open the report template file using Microsoft Word.
2. On the Insert menu, click Chart.
3. Change the sample data on the datasheet as you need.
4. Modify the chart. For example, you want to change the chart type, make the text larger, or change colors, patterns, lines, fills, and borders in charts.
5. After you have finished the modification, delete data from the chart. You should keep a blank chart in the report template file. WDReportCom will put data into the datasheet of the chart.
6. Change the chart to an inline shape if it is a floating shape. On the Format menu, click Object, click the Layout tab, and then click In Line text-wrapping style.
For more detail information, refer to Microsoft Word Help and Microsoft Graph Help.
To create an Excel chart in the report using WDReportCom, you need to add an Excel chart in the report template file first. The chart will be brought into the report file with the same chart type, display option, data format, label format and other chart item.
In Microsoft Word 2007, when you have Microsoft Excel installed, you can create Excel charts by clicking the Chart button, and then by using the chart tools to modify or format the chart. For Microsoft Word 2003 or earlier, you can import Excel charts.
To add an Excel chart in the template file:
1. Open the report template file using Microsoft Word.
2. Insert a chart with a chart sheet and a worksheet.
3. Change the sample data on the worksheet as you need.
4. Modify the chart. For example, you want to change the chart type, make the text larger, or change colors, patterns, lines, fills, and borders in charts.
5. After you have finished the modification, delete data from the chart. You should keep a blank chart in the report template file, and make the chart sheet active. WDReportCom will put data into the worksheet of the chart.
6. Change the chart to an inline shape if it is a floating shape. On the Format menu, click Object, click the Layout tab, and then click In Line text-wrapping style.
For more detail information, refer to Microsoft Word Help and Microsoft Excel Help.
You can reference a chart by an index number or a bookmark. The index number represents the position of the chart in a document. The index number starts at 1. So chart 1 is the first chart in a document, chart 2 is the second chart, and so on.
You can reference a chart by the bookmark too. You might use a bookmark to identify a chart if you assigned the bookmark to the chart. To add a bookmark, do as follows:
1. Create a chart in the report template.
2. Select the chart you want a bookmark assigned to.
3. On the Insert menu, click Bookmark.
4. Under Bookmark name, enter a bookmark name and click Add. For example, enter Chart1.
In Microsoft Word, bookmarks are hidden by default. To show bookmarks:
1. On the Tools menu, click Options, and then click the View tab.
2. Select the Bookmarks check box.
3. The bookmark appears in brackets ([...]) on the screen.