| Chapter 4 Report Templates |
The report generated by XLReportCom is a Microsoft Excel workbook that contains one or more worksheets. 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 XLReportCom, you should create a report template first. This report template is a Microsoft Excel workbook that defines the layouts, formats and styles of the report. In the Microsoft Excel report template, you can input static contents such as titles, descriptions, comments, a cover, a company logo, format the static content, and define the format of the cells you will fill data.
XLReportCom 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 Excel, please skip this section. For more detail information about Microsoft Excel, refer to Microsoft Excel Help.
A Microsoft Excel workbook is a file that contains one or more worksheets, which you can use to organize various kinds of related information. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from more than one worksheet. When you create a chart, you can place the chart on the same worksheet as its related data or on a separate chart sheet.
Worksheet is the primary document that you use in Microsoft Excel to store and work with data. It also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.
Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). A formula can contain any or all of the following: functions, references, operators, and constants. You can perform calculations using formulas.
A name is a word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy to understand names, such as Products to refer to hard to understand ranges, such as Sales!C20:C30.
Headers and footers are areas in the top and bottom margins of a worksheet. You can add a header and footer on each worksheet. You can insert a page number, date and time, graphic, file name in a header and footer, and change the font in header and footer text. You can have only one custom header and one custom footer on each worksheet. If you create a new custom header or footer, it replaces any other custom header or footer on the worksheet.
Microsoft Excel will break pages automatically. You can manually insert horizontal or vertical page breaks.
You can add graphics to your worksheets and charts to make them more visually appealing, to create eye-catching reports, or to add emphasis. For example, you can display a logo on your worksheet, create a flowchart, and use graphics in chart data markers. You can make your worksheet interactive by using graphic objects as hyperlinks or by assigning buttons to macros.
Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. To create a chart, you must first enter the data for the chart on the worksheet. Then select that data and create a chart. A chart is linked to the worksheet data it's created from and is updated automatically when you change the worksheet data.
You can use these formatting features of Microsoft Excel to effectively display your data.
To make text stand out, you can format all of the text in a cell or selected characters. You can set font, color, alignment of the text.
The data in a column is often very narrow while the label for the column is much wider. Instead of creating unnecessarily wide columns or abbreviated labels, you can rotate text and apply borders that are rotated to the same degree as the text.
To distinguish between different types of information in a worksheet, you can apply borders to cells, shade cells with a background color, or shade cells with a color pattern.
You can use number formats to change the appearance of numbers, including dates and times, without changing the number behind the appearance. The number format does not affect the actual cell value that Microsoft Excel uses to perform calculations.
The conditional format is a format, such as cell shading or font color, that Microsoft Excel automatically applies to cells if a specified condition is true.
The style is a combination of formatting characteristics, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.
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.
XLReportCom 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 XLReportCom 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 XLReportCom 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 worksheet in the report template file according to the report. The format of the worksheet is the same as the format in the report, but the cells that should be filled data into are blank. When XLReportCom executes a SQL statement, the data values from data source will be filled into these cells.
For a variable table report, you also need to create a worksheet in the report template file according to the report. But you just need to reserve some rows/columns in the worksheet for one or two records. XLReportCom will add some rows/columns according to the number of the records returned from data source.
You should reserve the blank rows/columns for 2 records if you want the format of the last row/column border is different from the others. For example, the outside borders used double lines, and the inside borders used single lines. When XLReportCom inserts some blank rows/columns, the new rows/columns will inherit the format of the first row/column in the reserved rows/columns.
One record from data source can be put into two or more rows/columns. You need to create a repeat range that includes two or more rows/columns. XLReportCom 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. XLReportCom will repeat the inside range for each record, and repeat the group range for each group.
To enhance the visual impact of your report, you can insert pictures into your report. XLReportCom supports many popular graphics file formats: bitmap, JPG, GIF, PNG, TIFF and so on. For the graphics file formats XLReportCom supports, refer to Microsoft Excel Help.
You should store the path and name of the graphics files in the database, and identify the image fields in the report function. XLReportCom will read the graphics files, and insert them into the cells in the report file.
To specify the positioning option and size, you should write a formatting expression into the cell in the report template file. XLReportCom 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:
[placement] [size]
The placement specifies the positioning option, and can be one of the following values. The default value is MNS.
| Values | Description |
| MAS | Move and size with cells. |
| MNS | Move but don't size with cells. |
| NMS | Don't move or size with cells. |
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, XLReportCom will retain the original proportions of the picture when XLReportCom resize it.
Example
w84
Remarks
On the supposition that the original picture is size 144 x 168 points. XLReportCom will insert a picture, set the positioning option to Move but don't size with cells, set the height of the picture to 72 points, and the width to 84 points.
Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. You can use Microsoft Excel to add sophisticated, colorful charts in your reports. For example, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales. You can create a chart on its own sheet or as an embedded object on a worksheet.
To create a chart in the report using XLReportCom, you need to add a chart in the report template file. The chart will be brought into the report file with the same chart type, display option, number format, titles, data labels and legends.
To add a chart in the template file:
1. Open the report template file using Microsoft Excel.
2. Enter the sample data for the chart on the worksheet.
3. Select that data and use the Chart Wizard to step through the process of choosing the chart type and the various chart options, or use the Chart toolbar to create a basic chart that you can format later.
4. Customize the chart. For example, change the chart type, colors, lines, fills, number formats, titles, data labels and legends in charts.
5. After you have finished the customization, delete data from the chart. You should keep a blank chart in the report template file. You can put data using Report function in XLReportCom.
For more detail information, refer to Microsoft Excel Help.