| Chapter 5 Reporting with XLReportGen |
To generate a report with XLReportGen, you must create an XRF file with an .xrf extension. The XRF file contains information such as the name of the report template file, the name of the report file, log file name, data sources, parameters and functions. The XRF file tells XLReportGen how to get data from data sources and how to put data into a report.
On the File menu, click New.
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.
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.
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.
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 XRF 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.
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.
5. If you want to protect the report, select the Protect Report check box. If the check box is selected, the Excel 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.
You can convert a file from Microsoft Excel to and from another file format. For example, the template file is a Lotus 1-2-3 file with a .wk3 extension, and the report file is a HTML file with a .htm extension. For most file formats, Microsoft Excel converts only the active sheet. To convert the other sheets, open the template file, switch to the sheet you want to save, and save it.
The file formats XLReportGen supports can be one of these. What file format XLReportGen supports is dependent on your Microsoft Excel. For example, Microsoft Excel 2003 supports XML, but Microsoft Excel 97/2000 does not support it. For more information about converting files, please refer to Microsoft Excel Help. The file "xconv.cfg" located in the XLReportGen directory contains the information of file formats. You can expand it if your Microsoft Excel supports more file formats.
| File Format Name | Value | Description | Extension | Converter |
| xlWorkbookNormal | -4143 | Microsoft Excel Workbook | xls | Office97 |
| xlTemplate | 17 | Template | xlt | Office97 |
| xlAddIn | 18 | Microsoft Excel Add-In | xla | Office97 |
| xlHtml | 44 | HTML | htm html | Office2000 |
| xlWebArchive | 45 | Web Archive | mht mhtml | Office2003 |
| xlXMLSpreadsheet | 46 | XML Spreadsheet | xml | Office2003 |
| xlCSV | 6 | CSV (comma delimited) | csv | Office97 |
| xlCSVMac | 22 | CSV (comma delimited) (Macintosh) | csv | Office97 |
| xlCSVMSDOS | 24 | CSV (comma delimited) (MS-DOS) | csv | Office97 |
| xlCSVWindows | 23 | CSV (comma delimited) (Windows) | csv | Office97 |
| xlCurrentPlatformText | -4158 | Text (Tab-delimited) | txt | Office97 |
| xlTextMac | 19 | Text (Tab-delimited) (Macintosh) | txt | Office97 |
| xlTextMSDOS | 21 | Text (Tab-delimited) (MS-DOS) | txt | Office97 |
| xlTextPrinter | 36 | Formatted Text (Space-delimited) | prn | Office97 |
| xlTextWindows | 20 | Text (Tab-delimited) (Windows) | txt | Office97 |
| xlUnicodeText | 42 | Unicode Text | txt | Office2000 |
| xlExcel2 | 16 | Microsoft Excel 2.0 Worksheet | xls | Office97 |
| xlExcel2FarEast | 27 | Microsoft Excel 2.0 Worksheet Far East | xls | Office97 |
| xlExcel3 | 29 | Microsoft Excel 3.0 Worksheet | xls | Office97 |
| xlExcel4 | 33 | Microsoft Excel 4.0 Worksheet | xls | Office97 |
| xlExcel4Workbook | 35 | Microsoft Excel 4.0 Workbook | xlw | Office97 |
| xlExcel5 | 39 | Microsoft Excel 5.0/95 Workbook | xlw | Office97 |
| xlExcel9795 | 43 | Microsoft Excel 97-2003 & 5.0/95 Workbook | xls | Office2000 |
| xlDBF2 | 7 | DBF 2 (dBASE II) | dbf | Office97 |
| xlDBF3 | 8 | DBF 3 (dBASE III) | dbf | Office97 |
| xlDBF4 | 11 | DBF 4 (dBASE IV) | dbf | Office97 |
| xlDIF | 9 | DIF (data interchange format) | dif | Office97 |
| xlSYLK | 2 | SYLK (symbolic link format) | slk | Office97 |
| xlWJ2WD1 | 14 | WD1 (1-2-3) | wd1 | Office97 |
| xlWK1 | 5 | WK1 (1-2-3) | wk1 | Office97 |
| xlWK1ALL | 31 | WK1, ALL (1-2-3) | wk1 | Office97 |
| xlWK1FMT | 30 | WK1, FMT (1-2-3) | wk1 | Office97 |
| xlWK3 | 15 | WK3 (1-2-3) | wk3 | Office97 |
| xlWK3FM3 | 32 | WK3, FM3 (1-2-3) | wk3 | Office97 |
| xlWK4 | 38 | WK4 (1-2-3) | wk4 | Office97 |
| xlWKS | 4 | WKS (Works) | wks | Office97 |
| xlWorks2FarEast | 28 | Works Far East | wks | Office97 |
| xlWQ1 | 34 | WQ1 (Quattro Pro/DOS) | wq1 | Office97 |
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.
A data source identifies a database computer you want to access. Because of accessing data through ODBC, XLReportGen can access a wide range of data sources, such as Oracle, DB2, Sybase, Informix, Microsoft SQL Server, Teradata, MySQL, Microsoft Access, dBase. XLReportGen 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.
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.
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.
You can use parameters in SQL statements. These values need to be provided to XLReportGen before it executes these SQL statements. To use a parameter, you must declare it first. When XLReportGen generate a report, it will prompt you to input the value of the parameter. XLReportGen 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 XLReportGen is run.
Note: XLReportGen 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.
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.
You should input functions in the editor window. A function includes a SQL statement and some arguments. XLReportGen executes the SQL statement, and determines whether or how to add data into the report. XLReportGen 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 "sheet" argument identifies a worksheet in the Excel workbook. 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.
You can run an XRF file to generate a report in Microsoft Excel spreadsheet format. XLReportGen supports Windows mode and command line 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 Excel check box.
3. Press Start button to run the XRF file.
4. If parameters are defined in the XRF file, XLReportGen will pop up a prompt dialog box. Input the values of the parameters, and press OK button.
5. While XLReportGen 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. XLReportGen 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.
You can run an XRF file in command line. You have defined two parameters in the XRF file "myreport.xrf". The first parameter is sales date "$SalesDate", and the second is the category of the products "$Category". You can run XLReportGen in command line mode as follows:
excelreport c:\excelreport\myreport.xrf -c 1996-05-01 "Dairy Products"
XLReportGen will replace "$SalesDate" in SQL statements with "1996-05-01", replace "$Category" with "Dairy Products", and then submit SQL statements to data sources.
Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
A formula can also contain any or all of the following: functions, references, operators, and constants. For more detail information about formulas, functions and references, refer to Microsoft Excel Help. In a report template file, you can use all kind of Microsoft Excel formulas. And then all formulas in the report template file will be brought to the final report file.
Example
Show Unit Price, Quantity, Discount and Amount. The Amount will be changed if an end user changes Unit Price, Quantity or Discount.
You can use a fomula to show Amount.
1. Create a template file as follows, and define the formula "=C2*D2*(1-E2)" in cell F2. You must use relative reference.
2. Write report function as follow, and use COPYRANGE to copy the formula to the next cell. For the first record, XLReportGen will directly put data into row 2. For the other record, it will put data after copy row 2. So the formula in cell F2 will copy to cell F3, F4... and Microsoft Excel will automatically change the formula to "=C3*D3*(1-E3)" ...
@F1=Report(sheet="Sheet1" cell=A2 copyrange=2:2)
SELECT c.CompanyName AS Customer
,p.ProductName
,d.Quantity
,d.UnitPrice
,d.Discount
FROM Orders o
, Customers c
, OrderDetails d
, Products p
WHERE o.CustomerID = c.CustomerID
AND o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND YEAR(o.OrderDate) = YEAR('$ReportMonth-01')
AND MONTH(o.OrderDate) = MONTH('$ReportMonth-01')
ORDER BY 1, 2
;3. The report file generated by XLReportGen is as follows.
Example
Add totals such as Total Quantity, Total Amunt.
You can use statistical functions of Microsoft Excel, such as SUM.
1. Create a template file as follows, define the formula of total quantity as "=SUM(C7:C8)" in cell C9, and the formula of total amount as "=SUM(D7:D8)" in cell D9. You must use relative reference.
2. Write report function as follow. When XLReportGen insert some rows according to the records, Microsoft Excel will automatically change the formulas.
@F2=REPORT(sheet="Report2" type=var cell=B7 reserve=2)
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 XLReportGen is as follows. The formula of total quantity is changed to "=SUM(C7:C14)", and the formula of total amount is changed to "=SUM(D7:D14)".
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 */
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.
1. You can add total using statistical functions of Microsoft Excel, such as SUM. For more detail information, refer to "Using Excel Formulas" in this document.
2. You can use aggregate functions in SQL statement, such as COUNT, SUM, AVG, MAX, MIN.
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 VARIABLE TABLE REPORT function. For more detail information, refer to "Group Variable Table Report" in this document.
Using GROUP REPORT function, you can make subreports within a report. A subreport would typically be used to perform one-to-many lookups such as Customer / Order / OrderDetails.
To make sub reports within the main report,
For more detail information, refer to the samples invoice.xrf, product_catalog.xrf and sales_detail.xrf within XLReportGen.
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 the functions of Microsoft Excel or aggregate functions in SQL statement.
1. You can add sub-totals using statistical functions of Microsoft Excel, such as SUM.
2. If you want to have total and sub-totals, you can add total using SUMIF function. For example, SUMIF(G:G,"Subtotal:",H1:H16).
3. You can add sub-totals using aggregate function in SQL statement too.
For more detail information, please refer to samples invoice.xrf, sales_detail.xrf within XLReportGen.
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.
To create a chart in a report, you should create the chart in the template file. You can create a chart on its own sheet or as an embedded object on a worksheet. For more detail information how to create chart, refer to Microsoft Excel Help.
To create a chart in the report template file, you can use some sample data. Using sample data, you can set the various chart options. After you have made the report template, you delete the sample data. When you generate the report, XLReportGen will put data into the report, and you get the chart. For more detail information about charting, refer to the sample monthly_sales.xrf within XLReportGen.
Example
The following function provides data for the chart: Sales by Categories.
@F2=REPORT(sheet="Report2" type=var cell=B7 reserve=2)
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.CategoryNameResult
The worksheet of the chart defined in the report template:
The chart defined in the report template is a blank chart.
The worksheet of the chart generated in the report:
The chart generated in the report:
To make eye-catching reports, you can add pictures to your reports. You can insert pictures into the report template directly in Microsoft Excel. 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 worksheets, refer to Microsoft Excel Help.
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 Excel report. XLReportGen can insert pictures from the graphics files, and support all graphics file format that Microsoft Excel support.
To insert pictures into a report using XLReportGen, 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 XRF file, and identify the image fields using IMAGE argument. For example,
@F1=Report(sheet="Employee Profile" ... image=photo)
3. Specify the positioning option and size in the report template
To specify the positioning option and size, you should write a formatting expression into the cell in the report template file. XLReportGen will get the text of the cell, and insert a picture into the cell according to the instruction in the format expression.
4. Run XLReportGen to generate report with pictures
During report generating process, XLReportGen 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 "", XLReportGen will return "". XLReportGen 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.xrf, product_catalog.xrf within XLReportGen.
To use a parameter, you must define it first. If you have defined a parameter name, you can use it in SQL statements. When XLReportGen 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, XLReportGen 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 MS 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.xls LogFileName=log\report_$CustomerID.log or ReportFileName=report\$CustomerID\report.xls LogFileName=log\$CustomerID\report.log
In Microsoft Excel, 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 Auto_Open, Auto_Close to automate a task. For examples, you can use Auto_Open macro to make the template, or use Auto_Close to change the report after XLReportGen puts data into the report.
Add-ins, files in the XLStart directory are not loaded when Microsoft Excel is called by a program, and Auto_Open macros won't be run when you open the file from a program. XLReportGen has an option to process it.
To use the option:
Sometimes you want to make an XRF file programmatically. You can do this because the XRF file is a text file. You can write a program to make an XRF file using C, perl or DOS shell, and then run XLReportGen to generate report. The two steps can be written into a batch file.
For example, you write a batch file runrpt.bat as follows. changexrf is an executable file that reads template.txt and output template.xrf. First runrpt.bat call changexrf to make the XRF file, and then call XLReportGen 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 changexrf %1 <"template.txt" >"template.xrf" ExcelReport "template.xrf" -C %1