User's Guide

Excel Report Builder is an easy to use Microsoft Excel-based reporting tool for your database applications. It enables the use of MS Excel for the creation and editing of various styles of sophisticated reports (standard, columnar, master-detail).

NOTE:  You must have MS Excel 7.0, 8.0, 2000 or 2003.  If you use "Symantec AV", "McAfee", AVG, Kaspersky, or other  antivirus program for security you have adjust the settings allowing Excel Report to function.

Installation and start of Excel Report Builder.

To install Report Builder:

a) Extract the files in excelrep.zip to their own directory
b) Run the file, setup.exe from this directory

Starting Excel Report Builder

·  Click the Excel Report Builder from the Windows Start Menu.

After few seconds the splash screen will depart and the main dialog of the program - fig. 2 will be presented..

Fig. 2. Excel Report Builder - Main Form.

Excel Report Builder Functions

The main form consists of the Main Menu and the Button Panel which provide access to all of Report Builder�s functions.
 

File Menu

The �File" Menu is comprised of the following submenu items:
 

New	Create a NEW report
Open�	OPEN a previously created report
Close	CLOSE the current report
Save	SAVE the current report
Save as�	SAVE the current report under another name
Exit	EXIT Excel Report Builder

Several function on the File submenu, New, Open and Save are also conveniently located on the button panel

Report Menu

The "Report" Menu contains the following submenu items:
 

Edit Reprot	Edit the layout of the report
Run Report	Create a report using the parameters specified in the layout

Options Menu

The �Options� Menu contains the following submenu item:
 

Display report

Displays the newly-generated report

Windows Menu

The menu item Windows contains these submenu items:
 

Cascade
Tile Horizontal
Tile Vertical
Arrange
	Name of open windows

The menu item"About" presents the following information

About
Information about Excel Report Builder
 

About

Information about Excel Report Builder

---

Excel Report Builder facilitates the creation of the following report types:

 1. Form Report (for example an agreement, notice, reference).

 2. Columnar Report.

 3. Master-Detail Report.
 

---

Report  Examples

---

The Form Report

Name	~Name
Company	~Company
Income	~Income
Sum	~Sum

Here, the names �~Name� , �~Company�, and �~Income� � - are placeholders for fields whose data will be inserted during report generation.

---

The Columnar Report

Name	Company	Income	Sum
~Name	~Company	~Income	~Sum

Here ~Name , ~Company, ~Income � - are placeholders for fields whose data will be inserted during report generation, and Name, Company, Income are file names

---

 

Setting up a new report. Defining the report parameters.

Start Excel Report Builder.

From the �Report� menu, select �New� and then the type of report you wish: �Form Report�, �Columnar Report� or "Master-Detail Report"

The Form Report

The first field, Report name contains the name of the report.

The checkbox  Report type specifies whether to create Form Report, Columnar or Master-Detail Report.

First, specify the name of the table. Select the checkbox �Open table using...�

You can provide either an Alias or a File name for the Table.

The checkbox  Number of pages allows to display each form of the report on a separate page (Several), or multiple forms on a page. (Single).

If the table is specified as an Alias, select the desired table from the appropriate list.

Fig. 3. Selecting the table name by database alias.

If you want to open file by database name set up database alias in BDE Administrator:

BDE Alias Configuration

If the table is specified by filename, double click the "Table Name" column and select the desired file from the standard file selection windows.

Additionally you can configure following parameters:

• Records range
• SQL query

Fig. 4. Selecting the table name by path.

To create a report the form template file must exist. Therefore, after specifying the form name, press the button �Edit Reprot�.

MS Excel is then started and the specified file is either opened, or created with the specified name. For convenience, the �Insert� window appears. Place the cursor in the desired location of the form, select the desired Table field from the list and press the �Insert field� button. To terminate editing, press the �Close� button.

NOTE: Do NOT forget to save the changes you made to the form before pressing �Close�!

The definition of the report parameters of the type �The Form Report� is now complete. Don�t forget to save your changes by pressing the �Save� button and then move to the section �Operations after the definition of parameters�.
 

The Columnar Report (columnar report)

This type of the report allows you to fill in several tables at once and they can be at several locations in the form of the document.

The value and the majority of parameters are described in detail in the section �Defining report parameters. Report Format: Form�.

It is possible to select database tables using either an ALIAS or a file specification. If neither ALIAS nor the filespec are indicated, the current directory will be searched.

All report parameters are saved to a file having the extension *.pr. This file can later be retrieved for automatic creation of the report.

While report parameters file is a standard text file, editing it manually is not recommended.

We recommend that you create the template using Microsoft Excel, however it is possible to create and edit the form using the Excel Report Builder (button �Edit Report�). When Excel is started, the document form loaded (or created) and an �Insert� window appears on the screen. It enables the insertion of the desired field placeholders into the form of the document athough this task can also be done manually.

To edit the report form, press the �Edit Report� button to start Excel and open a previous file or to create a new one.

For your convenience, the �Insert� window appears. Move the cursor to where you would like the table to appear, select the table from the list and press the �Insert table� button.

Note that when inserting a table using this button, table�s template is simply created as indicated below:
 

~Field1

~Field2

~Field3

~Field4

~Field5

The fields can appear in no particular order, some fields may even be absent. Take for example, the following template of the table:
 
 

~Field4

~Field1

AAAA

~Field5

~Field5

To terminate the editing of the report form press the �Close� button. Do not forget to save any changes made to the form! You may then exit Excel if you wish.

It is also possible to make temporary changes to a form, generate an �ad hoc� report, and then exit the form without saving the changes. If you do desire to save the changes, use the MS Excel �Save� feature prior pressing the �Run report� button.
 

Procedures following the definition of parameters

After specifying your parameter values, save them by pressing the �Save� button. If you have made temporary changes you do not wish to preserve, just exit without saving after generating the report. To create a report based on specified parameters press the �Run report� button.  To terminate editing or creation of report parameters, press the �Exit� button.

You will now be returned to the Report Builder�s main window. After saving the report parameters you may now begin creation of the report�s Excel-based form by selecting �Edit Report� from the Report menu.

MS EXCEL is started and the specified file is either opened or created depending on whether it exists.

For your convenience, the �Insert� window appears. figure 5  Place the cursor at the position in the form where you would like the table to appear, select the table from the list and press the �Insert  field� button.

Using MS Excel functions, create the form�s placeholders, into which the data will be placed.

Fig. 5 Insert Field Dialog Box.

To finish template creation, press the �Insert� dialog box�s Close button, after which the following dialog will be displayed.

To close the dialog, and return to the Main program window, press the �Close� button.
 

Editing an existing report

Start Excel Report Builder.

From the �Report� Menu select �Open� and then the desired file with the extension *.PR (for example NAME.PR). That can be made by pressing the button:

After changing any parameters you must save the changes by pressing the �Save� button. You may also generate an �ad hoc� report using the changed parameters without saving them.

To edit the report form, press the �Edit Report� button. To create a report based on the current parameters press the �Run report� button. To terminate editing, press the �Close� button to return to Excel Report Builder�s main window.

Generating reports

Interactive mode

To operate the program in interactive mode, start Excel Report Builder. From the �File� menu select �Open� and then select the desired file with the extension PR (For Example NAME.PR).

After loading the parameter file, select �Run report� from the �Report� menu or press the Run report button The report will be generated according to parameters indicated in the PR file. (The put_rep1.exe program will be run automatically). After termination, a message indicating completion will be displayed. You may then run a report using a different parameter file or exit the program.

Command line mode

To create a report  type the command:

put_rep1.exe  C:\BVISTA\NAME.PR

Where C:\BVISTA\NAME.PR is the full path name to the parameter file previously created by Excel Report Builder.

The report will be generated according to parameters specified in the file NAME.PR.

If the output file for the report already exists, the new report will be appended to the end of the existing one. After termination, a message indicating completion will be displayed.