CHAPTER 2: Introduction

The Empress Report Writer is a highly flexible tool for the production of custom-formatted reports. It can be used in conjunction with Empress databases or as a standalone report writer reading data from operating system files. Empress Report Writer allows you to design a wide range of reports, with proper headers, footers, and pagination. Data can be read from database tables via Empress Query Language SELECT statements, taken from operating system files, or received from Empress 4GL. Data records can be grouped according to the values of attributes or computed results. Sums, maxima, minima and averages are easily applied to groups. Control over the layout of information is provided through such features as centering, justification and wrapping in columns.

Commands to the Empress relational database management system to change or retrieve data are given in simplified English sentences via the Query Language. This manual assumes a reasonable familiarity with the Query Language as described in the Empress SQL: User's Guide and the Empress SQL: Reference manuals. Query Language commands are suitable for entering data into a database, removing or changing data already in the database, and retrieving data from the database. On retrieval, Empress allows some grouping of the data, some arithmetic calculations, and some flexibility in formatting the retrieved data.

The Empress Query Language does not, however, allow complex formatting of reports with multi-line titles, pagination, and sophisticated grouping. Those capabilities are provided by Empress Report Writer.

Empress Report Writer's commands provide flexibility in designing reports. The basic commands allow you to specify:

These are texts to be printed at the beginning and end of each page. This will typically be a date, the page number, the title of the report, the company name, etc. A single report may use several different styles of headers and footers.

Records may be retrieved from tables by Empress Query Language SELECT statements, using the full power of Empress features such as sorting. Data may also be read in from operating system files dumped from an Empress database, or files produced by another program or a text editor.

Character strings, and arithmetic operations on attribute values, can be assigned to variables for later use. For example, if gross and deductions are attributes in a payroll table, then the net pay for each record selected can be computed using:

LET netpay = gross - deductions;

This creates the variable netpay, which can be printed out or used in other computations. Similarly:

LET title = "Annual Report 1992";

stores the string "Annual Report 1992" in the variable title.

The data to be printed can be separated into groups by the values of attributes or computed values. The actions to be taken before and after each group, such as summing averaging group values, can be specified. Date attributes may also be grouped by year, month or week.

Sums, maxima, minima and averages of columns can be specified simply, as in:

PRINT SUM netpay, SUM gross, AVERAGE ATTR "hours";

A PICTURE string can be used to specify the exact format in which data is to be printed. This allows suppression of leading zeros, placement of decimal points, dollar signs, minus signs, etc. For example, the picture "$999.99" prints dollar amounts with three digits before the decimal point and two after, forcing leading zeros to be printed if none are present. The PICTURE "aaaaaaaaa dd, yyyy" prints dates in the format January 12, 1992, while "mm dd yy" prints "1 12 92".

Reports are produced from a script containing instructions for generating the report. These instructions are in the form of statements which specify the data to be retrieved, any manipulations to be performed on it, and how it is to be printed out. Report scripts are operating system files. They are created using a text editor such as ed or vi.

Report scripts are stored in operating system files. A command of the form:

emprepwr [-f datafile] script [argument ...]

is used to generate a report using Empress Report Writer commands stored in the file script.

Any parameters used in the report script are passed arguments which follow the name of the Empress Report Writer script. Arguments with spaces or tabs in them must be enclosed in quotes.

The -f option is used to have data automatically read from the specified datafile. To use this option, the data must be in the format produced by the Empress Query Language SELECT command using the REPORT DUMP option.

Data included in a report comes initially from one of several sources: an Empress database, an operating system file, Empress 4GL, or parameters passed to Empress Report Writer when the report script is run. Further data values may be derived from these by arithmetic calculations.

Note that Empress Report Writer may be used as an independent report generator, which does not require access to an Empress database.