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.
The ”File" Menu is comprised of the following
|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
The "Report" Menu contains the following
|Edit Reprot||Edit the layout of the report|
|Run Report||Create a report using the parameters specified in the layout|
The “Options” Menu contains the following
|Display report||Displays the newly-generated report|
The menu item Windows contains these submenu
|Name of open windows|
The menu item"About" presents the following information
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.
The Form Report
The Columnar Report
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
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:
The fields can appear in no particular order, some fields
may even be absent. Take for example, the following template of the table:
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.
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:
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.