Learning how to use XLReportGen

You can teach yourself how to use XLReportGen by choosing from the methods available in this section:

Sample Database

XLReportGen comes with Sample.mdb, a sample database you can use when learning the program. Sample.mdb is a Microsoft Access database. Virtually all of the examples in this manual are based on Sample.mdb data.

The sample reports access the sample database through the ODBC data source name "Report Sample". When you install XLReportGen, you can choose to add the ODBC data source name. And you also can add the ODBC data source name manually.

To create the System DSN "Report Sample", do as follows:

1. Click the Windows Start button, choose Settings, and then click Control Panel.

2. On computers running Microsoft Windows 2000 or later, double-click Administrative Tools, and then double-click Data Sources (ODBC). The ODBC Data Source Administrator dialog box appears. On computers running previous versions of Microsoft Windows, double-click 32-bit ODBC or ODBC.

3. Select the System DSN tab, and then press Add button.

4. Choose Microsoft Access Driver (*.mdb), then press Finish button.

5. In the ODBC Microsoft Access Setup dialog box, type Report Sample in the Data Source Name box.

6. Press the Select button, and browse to select Sample.mdb.

7. Press OK button to close the ODBC Microsoft Access Setup dialog box.

8. Press OK button to close the ODBC Data Source Administrator dialog box.

Steps of Reporting

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

1. Prepare works

Before you create a report, you should determine the layout of the report, and know where and how to get the data.

You must know how to access the databases you are reporting from. So you need the data source name, user name and password. If you don't have added data sources, please add data sources first. Run ODBC Administrator, you can add a new data source. For detailed information about configuring ODBC, refer to ODBC Administrator Help.

2. Make a report template file

My First Report

The following tutorial has been designed to guide you to create your first report. In this tutorial, you will get an introduction to the program as you create a Customer List report. The Customer List is one of the most basic business reports and typically has information such as Customer Name, City, Country, and Contact Name.

Creating a report template

1. Run Microsoft Excel, a new workbook will open.

2. Select the cell A1, type "Customer Name". In the same way, you input "City", "Country" and "Contact Name" into the cells B1, C1 and D1.

3. Format the text of A1, B1, C1 and D1 as you like, including font, font size, font colour, bold, background, alignment and border.

4. You can change the width of these columns. The report template you have made is as follows:

5. Click Save on the File menu, chose a directory such as "C:\Report", type custlist.xls in the File name box and press Save button.

6. Click Close on the File menu.

Creating an XRF file

1. Run XLReportGen.

2. Click New on the File menu.

3. Click Save on the File menu, chose the directory to which you have saved the report template, type custlist.xrf in the File name box and press Save button.

Configuring the report

1. On the Report menu, click Configuration. The Configuration dialog box appears.

2. Click the File tab.

In the Template File box, type custlist.xls; In the Report File box, type Report\custlist.xls; In the Log File box, type Log\custlist.log.

3. Click the Data Source tab.

Press New button, the New Data Source dialog box appears. In the Name box, type Report Sample, press OK button.

4. On the Configuration dialog box, press OK button.

Inputting a function

In the editor windows, input a function as follows:

@F1=Report(sheet=1 cell=A2)
SELECT CompanyName
,CityName
,CountryName
,ContactName
FROM Customers, Cities, Countries
WHERE Customers.CityCode = Cities.CityCode
AND Customers.CountryCode = Cities.CountryCode
AND Customers.CountryCode = Countries.CountryCode
ORDER BY CompanyName,CityName,CountryName
;

You can test the SQL statement in a query tool such as Microsoft Access or Microsoft Query.

Understanding the function

Before going any further, let us understand this function.

1. The Report function will execute the SQL statement, get data from data source, and put data into the report.

2. The sheet argument identifies a worksheet, and the value 1 is the index number of the worksheet. So it is the first worksheet.

3. The cell argument specifies the cells that the first record will be filled into. The value is A2. So XLReportGen will fetch the first record, put the value of CompanyName field into A2, the value of CityName field into B2, the value of CountryName field into C2, and the value of ContactName field into D2. An then it fetch the next record, put them into A3,B3,C3 and D3......

Running an XRF file

1. On the Report menu, click Run, the Run Report dialog box appears.

2. Press Start button to run the XRF file.

3. XLReportGen will generate a report.

4. After the status is Done, click Close button.

Opening a report

1. On the File menu, click Open Report File to open the report you have generated.

You can view and check the report.

2. On the File menu, click Open Log File to open the log file that recorded the log information in the report generating..

You can check the log.

3. Close the report file and the log file.

Modifying the report template

1. On the File menu, click Open Template File to open the report template.

2. Change the width of columns. It is very useful to copy some sample data from the report file into the report template for formatting.

3. Insert a new row on the top, type Customer List as the report title.

4. Add borders for the range "A2:D4". The external border can be different from the internal border. The report template you have made is as follows:

4. Save and close the template file.

Modifying the function

In the editor windows, modify the function as follows:

@F1=Report(sheet=1 cell=A3 reserve=2)
SELECT CompanyName
,CityName
,CountryName
,ContactName
FROM Customers, Cities, Countries
WHERE Customers.CityCode = Cities.CityCode
AND Customers.CountryCode = Cities.CountryCode
AND Customers.CountryCode = Countries.CountryCode
ORDER BY CompanyName,CityName,CountryName
;

1. Change the value of cell argument because you insert a row.

2. The reserve argument specifies the number of records for that you reserve some rows. You have reserve two blank rows in the report template so that the format of the last row/column border may be different from the others.

Generating the report again

1. Save the XRF file.

2. Run the XRF file to generate the report.

3. Open the report, view and check the report.

The report should now look similar to the following:

Now you have created a report.

Samples

After XLReportGen is installed, some sample reports are installed too. Use these reports to learn XLReportGen. The sample reports can be changed to adapt to your own needs.

The sample reports include a sample database, some report template files (.xls) and XRF files (.xrf). They are located in the Application Data\LJZsoft under All Users or your profile folder.

{commonappdata} is the path to the Application Data folder under All Users. If you install XLReportGen without administrative privileges, {commonappdata} is the path to the Application Data folder under the current user. The Application Data folder is usually at:

Windows 95/98: C:\windows\All Users\Application Data\

Windows NT: C:\WinNT\Profiles\All Users\Application Data\

Windows 2000/XP: C:\Documents and Settings\All Users\Application Data\

Windows Vista: C:\ProgramData\