XLReportGen Frequently Asked Questions

XLReportGen is a report generator for Microsoft ® Excel ® that uses Microsoft Excel as reporting tool and outputs reports in Microsoft Excel spreadsheet, HTML, XML, Lotus 1-2-3 or other format.

If you know how to use Microsoft Excel and write SQL statements, you can use XLReportGen to create all kinds of reports as you need. The report generated by XLReportGen is a Microsoft Excel workbook or other format file, such as HTML.

Yes. This is a main feature of XLReportGen. The complexity might come from reports formatting as well as reports content.

• The complex report formatting

You design reports like layouts, styles, formats directly using Microsoft Excel. You can take full advantage of Microsoft Excel including text formatting, number formats, text rotating, borders, colors, patterns, conditional formatting, style, drawing, pictures, and more.

• The complex report content

In one report, you can execute more than one SQL statement, and get data using different queries. Further more, you can create a temporary table, prepare data using many SQL statements including INSERT/UPDATE/DELETE/INSERT SELECT, and then make a report.

No. But XLReportGen can be run on your desktop or server. So you can use XLReportGen on the server to generate the reports. If you want to run it in the background on the server, you need to run it in command line mode.

Run the installation program, and follow the instructions to complete installation.

If you don't have installed Microsoft Office, please install it first.

If your environment is Windows 95/98 and Office 97, and you don't have installed VB6.0 run-time files, please install it. For Windows 2000, Windows XP, Windows 2003 and Office 2000 or later, you do not need to install VB6.0 run-time files because they are included in these OS and Office. To install VB6.0 run-time files, just download and run vbrun60sp5.exe.

If you don't have installed ODBC Driver for the database you want to access, please install it.

If your OS is Windows 95/98 and you don't have installed MS Data Access Components 2.0 (MDAC_TYP) or later, please install it. For Windows 2000, Windows XP and Windows 2003, you do not need to install MDAC_TYP because it is preinstalled in these OS. To install MDAC_TYP, just download and run mdac_typ.exe.

You can uninstall XLReportGen from [Control Panel].

1. Double-click the Add/Remove Programs icon in the Windows Control Panel.

2. Do one of the following:

• For Windows 2000, Windows XP and Windows 2003 Edition:

Click XLReportGen in the Currently installed programs box, and then click the Change/Remove button.

• For Windows 98 and Windows NT 4.0:

Click XLReportGen on the Install/Uninstall tab, and then click the Add/Remove button.

3. Follow the instructions on the screen to complete uninstall.

To create a report with XLReportGen, you should do as follows:

1. Create a report template file using Microsoft Excel, define the layouts, styles, formats of reports.

2. Create an XRF file with an .xrf extension using XLReportGen. The XRF file tell XLReportGen how to extract data from database, and where and how to put data.

3. Run the XRF file to generate a report file.

After XLReportGen is installed, some sample reports are installed too. The sample reports include a sample database, some report template files (.xls) and XRF files (.xrf). They are located in the ExcelReport directory under \Samples.

To use these samples, you must add a data source named "Report Sample", and specify the database "Sample.mdb". If you have not installed MS access ODBC driver, please install it first.

Creating a System DSN for a Microsoft Access Database

1. Run XLReportGen.
2. Open a XRF file. Click Open on the File menu.
3. Run the XRF file to generate a report. Click Run on the Report menu.
4. Open, view and check the report. Click Open Report File on the File menu.
5. Open, check the log file. Click Open Log File on the File menu.

The sample reports can be changed to adapt to your own needs.

This is a problem of multilanguage. You are trying to open a database or an object in a database that was created in another language edition of Microsoft Access. You can try as follow:

1. Open the database "sample.mdb" using Microsoft Access.
2. On the Tools menu, click Options, and then click the General tab.
3. Make sure that the New Database Sort Order is set to General and then click OK.

The report file is a Microsoft Excel workbook generated by XLReportGen.

To make a report using XLReportGen, you should create a report template file first. This report template is a Microsoft Excel workbook that defines the layouts, formats and styles of the report. In the report template, you can input static content such as titles, descriptions, comments, a cover, a company logo, and set the format for the static content and data got from data sources.

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.

A function includes a SQL statement and some arguments. A function can be used to execute a SQL statement, and tell XLReportGen whether or how to add data into a report.

You can test SQL statements using Microsoft Query that is a component of Microsoft Office. You can find it under the installation directory of Microsoft Office.

"C:\Program Files\Microsoft Office\OFFICE11\MSQRY32.EXE"

It probably is a wrong SQL statement. Such as a missing column, or syntax error.

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

There are two ways.

1. You can add total using math functions of Microsoft Excel, such as SUM.

(1) In the fixed table report, you write a SUM function in the total field in the report template file. The range of SUM function should contain cells for detail records.
(2) In the variable table report, except for the range for the detail records, the range of SUM function must contain at least one row/column that is not included in the range for the detail records. For example, the rows 2:3 is the range for the detail record, you should add blank row 4, and the function is written as SUM(H2:H4). If you do not want to show the blank row in the report, you can hide the row. You must use relative references. Microsoft Excel will change the function automatically as XLReportGen adds some rows in the report.

2. You can use aggregate function in SQL statement.

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

To group data in a report, you can use GROUP VARIABLE TABLE REPORT function. For more detail information, refer to XLReportGen Help and the samples customer_profile.xrf, invoice.xrf, product_catalog.xrf and sales_detail.xrf within XLReportGen.

A subreport is a report 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,

1. Write a JOIN SQL statement to acccess data from two or more tables. For example, you can join Customers, Orders and OrderDetails tables.
2. Use GROUP VARIABLE TABLE REPORT function.

For more detail information, refer to the samples invoice.xrf, product_catalog.xrf and sales_detail.xrf within XLReportGen.

1. You can add sub-totals using math functions of Microsoft Excel, such as SUM.

(1) The range of SUM function should contain cells for detail records in the report template file.
(2) The range of SUM function must contain at least one row/column that is not included in the range for the detail records. For example, the row 13 is for detail record, you should add blank row 14, and the function is written as SUM(H13:H14). If you do not want to show the blank row in the report, you can hide the row.
(3) You should use relative references. For example, SUM(H13:H14).

Microsoft Excel will change the function automatically as XLReportGen adds some rows in the report.

2. If you want to have total and sub-totals,

(1) You can add total using SUMIF function. The range of SUMIF function must contain one row/column that is not included in the range of the group. For example, the range of the group is rows 1:15, you should add blank row 16, and write the function as SUMIF(G:G,"Subtotal:",H1:H16). You can hide the blank row.
(2) You can add total 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.

3. You can add sub-totals using aggregate function in SQL statement too.

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

For more detail information, please refer to the samples invoice.xrf and sales_detail.xrf within XLReportGen.

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.

1. First enter the sample data for the chart in the template file.
2. Then select that data and use the Chart Wizard to step through the process of choosing the chart type and the various chart options.
3. Customize your chart.
4. Delete the sample data.
5. Write functions in an XRF file to input the data.

For more detail information, please refer to the sample monthly_sales.xrf within XLReportGen.

In SQL statements, you can use parameters.

1. To use a parameter, you must declare it first. A parameter has a name, a title and a default value. To define a parameter, click Configuration on the Report menu, and click the Parameter tab
2. In SQL statements, use the parameter name.

XLReportGen will replace the parameter name with the actual value before it submits the SQL statements to data sources.

Example

Define a parameter as follows:

Name: $CustomerName
Title: Customer Name

You can use the parameter name "$CustomerName" in SQL statements. The parameter name in SQL statements should be delimited by quotation mark. For example:

SELECT CompanyName
,CityName
,CountryName
,ContactName
FROM Customers, Cities, Countries
WHERE Customers.CityCode = Cities.CityCode
AND Customers.CountryCode = Cities.CountryCode
AND Customers.CountryCode = Countries.CountryCode
AND CompanyName LIKE '$CustomerName%'
;

You can use parameters in the path and name of the report file, template file and log file. For example, you define a parameter $CustomerID, you can name the report file as follows:

report\report_$CustomerID.xls
report\$CustomerID\report.xls

Of course, just write FILLORDER=col in REPORT function. For example, there are 1 field and 10 records, you can show them as 1 row and 10 columns on report.

Yes, XLReportGen 2.2 or later support text BLOB.

You can insert pictures into your report from using many popular graphics files XLReportGen.

1. Store the path and name of the graphics files in the database.
2. Identify the image fileds in the report function.
3. To specify the positioning option and size, you can write a formatting expression into the cell in the report template file.

XLReportGen will read the graphics files, insert them into the report, and position and size the pictures. For more detail information, refer to the samples employee_profile.xrf and product_catalog.xrf within XLReportGen.

In most of reports, you just need to execute one SQL statement. But sometimes, you want to execute more than one SQL statement in one report processing.

There is an option to control this. If you want to run Auto_Open macro automatically, you must enable the option first. To enable the option,

There is an option to control this. If you want to use addins during report generating, you must enable the option first. To enable the option,

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. These steps can be written into a batch file.

Yes. If you do not save a password in the XRF file, a login dialog box will appear when you run the XRF file in XLReportGen. You can input password interactively to log on to the data source. If you want to run XLReportGen in command line mode, you can input usernames and passwords in command line.

You can protect the generated report so that it can not be modified. To protect the report, select the Protect Report check box in the Configuration dialog box.

Once your order is accepted, you will receive an email confirmation of the order. And then, a full version download link will be emailed to you within 24 hours.

Yes, it's completely free for all.