# CHAPTER 8: Examples

## 8.1 Introduction

In this chapter we will go step-by-step from the most basic Empress Report Writer commands to producing complete reports. For the report data we shall use a database called `repairs`, with a number of different tables in it. The database contains employee records in a table called `personnel`, customer records in `customers`, records of automobile repair jobs in `jobs`, rates charged for the various job types in `rates`, and records of employees' work hours in `hours`.

Note

Please remember that `hours` is an Empress keyword; therefore, to create the table, enclose `hours` in quotes. Similarly, the attribute `hours` must be enclosed in quotes.

The structure of each of the tables and the data in them are given in Chapter 3 "Sample Database" of this manual. You will need this database in order to work through the following examples, so create the database using the information provided. For details on creating a database, consult the Empress SQL User's Guide manual.

## 8.1.1 How the Database Is Used

When a repair job comes in, it is assigned a job number and entered into the `jobs` table, along with the customer `number` for the customer who brought in the automobile. If the customer is a new one, an entry is made for him in the `customers` table, and a new number assigned to him. The job is then given to one of the mechanics, who usually does all the work on it.

The mechanic keeps track of the hours he spends on each aspect of the job, and when the job is complete he records it in the `hours` table, entering his personnel number, the date the job was completed, a brief description of the work, a code for the type of work, and the hours he spent on it. The code is used to determine both the amount billed to the customer and the amount received by the mechanic for the job, the rate of pay being taken from the rates table.

These records are then used to generate customer invoices, weekly time sheets for employees, and reports on the kinds of jobs the garage has been handling.

## 8.2 Example 1 - A Simple Report

The simplest report lists the different repair job classifications, sorted alphabetically, and the codes used for each classification. This report is normally thumbtacked above the mechanics' terminal, and the employees read the job code from it for entering into the hours table. The report takes information from the `rates` table, sorts it, and prints it out centered in the page.

The entire report looks like this:

`JOB CLASSIFICATIONS AND CODES`
                   Job Classification          Code

                      Axles                    5
                      Battery                  15
                      Body work                11
                      Brakes                   12
                      Carburetor               18
                      Distributor              20
                      Doors                    1
                      Exhaust                  14
                      Fenders                  4
                      Ignition/sparks          7
                      Lights                   3
                      Lubrication              6
                      Pistons                  17
                      Radiator                 19
                      Shocks                   16
                      Tires                    9
                      Transmission             10
                      Universal                13
                      Wheels                   8
                      Windshields              2

### 8.2.1 Printing the Report Title

As the first step in creating the report, we will start with the title, "`JOB CLASSIFICATIONS AND CODES`". This will illustrate the use of the `PRINT` statement, the means by which Empress Report Writer produces all its printed output.

Use a system editor to create a file called script containing the following line:

`PRINT "JOB CLASSIFICATIONS AND CODES";`

This is a typical Empress Report Writer output statement. It consists of a keyword, `PRINT`, followed by a character string argument enclosed in quotes, and ended by a semicolon.

All Empress Report Writer statements begin with one or more keywords. It is a good idea to end each statement with a semicolon so that it is clearly delimited. Some statements have arguments, and others do not. `PRINT` statements, like the one above, always have at least one argument, and may have as many as you wish, separated by commas. The arguments may be character strings, values to be printed, or keywords controlling where the other arguments are to be printed on the page.

The `PRINT` statement above is actually a complete Empress Report Writer report script.

It can be run by the command:

`emprepwr script`

Run this command at your terminal.

The first thing you will have noticed is that your output vanished off the screen before you had time to look at it properly. Empress Report Writer has a default page length of 66 lines, so the report you just produced consisted of one line of printing followed by 65 blank lines. To overcome this effect while we are building up the report script, edit your file script again and add the following line at the beginning:

`LET PAGELENGTH = 10;`

This `LET` statement reduces the page length to 10 lines; `LET` statements will be discussed in more detail later. Empress Report Writer has a number of special system variables such as `PAGELENGTH`, `PAGEWIDTH`, and `PAGENUMBER` which automatically keep track of things. These will also be discussed later.

Your entire file should now contain the following:

    LET PAGELENGTH = 10;
    PRINT "JOB CLASSIFICATIONS AND CODES";

Run the script again with the command:

`emprepwr script`

You should now see the heading "`JOB CLASSIFICATIONS AND CODES`" as the first line of output, followed by nine blank lines. Note that the heading is left-justified; this is the default for printed output.

Since we want a centered title for the report, we will have to add an appropriate instruction to the `PRINT` statement. As well as asking for a centered title, however, we must specify the width of the column it is to be centered in. If no width is given as an argument to a `PRINT` statement, Empress Report Writer assumes it is printing in a column just wide enough to fit the argument. In our example, for instance, Empress Report Writer is printing one column of output 29 characters wide.

Let us center the title in a column 65 characters wide. Edit script again and change the `PRINT` statement to the following:

    PRINT "JOB CLASSIFICATIONS AND CODES" WIDTH 65 CENTER;

Here we have two new keywords, `WIDTH` and `CENTER`, both associated with our original title argument. (`WIDTH` has an argument of its own 65, the width of the column.)

Run the report. You will now have a nicely centered title.

### 8.2.2 Right-Justified Titles

Just for interest, edit script again and change the `PRINT` statement to:

`PRINT "JOB CLASSIFICATIONS AND CODES" WIDTH 65 RIGHT;`

Run the report to see the effect of right-justifying an argument within its column, then change your script back to a centered title.

Remember that `CENTER` and `RIGHT` do not have any effect unless you specify a `WIDTH` for the column. If you want to see what happens without a width, edit your script to remove "WIDTH 65" while keeping `CENTER`, and run it. (Remember to restore the width specification to your script!)

### 8.2.3 Leaving Blank Lines

Let us now continue with the next part of the report the headings for the two columns of data. Edit script and change it to:

    LET PAGELENGTH = 10;

    PRINT "JOB CLASSIFICATIONS AND CODES"
       WIDTH 65 CENTER, NEWLINE;

    PRINT "Job Classification          Code" WIDTH 65 CENTER,
       NEWLINE;

The first `PRINT` statement now instructs Empress Report Writer to go to a new line after printing the first title. The new line request is necessary because Empress Report Writer does not automatically advance a line at the end of a `PRINT` statement. You may print several things on the same line from several different statements if you need to.

The second line is another straightforward `PRINT` statement, much like the first. We have requested a new line at the end of this statement because we will need one eventually, and ending `PRINT` statements with a new line request is a good habit to get into.

Run the report. Your output should look like the following (the lines across the page indicate beginning and end of the report on your screen):

`JOB CLASSIFICATIONS AND CODES
Job Classification Code`

For the report, we actually want two blank lines between the title lines. This could be accomplished either by adding two more `NEWLINE` statements after the first; or by giving the original `NEWLINE` statement an argument indicating how many line feeds Empress Report Writer should issue before printing (this would be 3); or by adding a `NEWLINE` statement with an argument to space down the additional lines.

Edit script and add the following line after the first `PRINT` statement:

`NEWLINE 2;`

This instructs Empress Report Writer to space two lines and begin printing on the third line, leaving two blank lines between the printed output. The `NEWLINE` keyword may appear as an argument to a `PRINT` statement, as well as being a single statement itself. It makes no difference to Empress Report Writer whether your script has:

    PRINT "JOB CLASSIFICATIONS AND CODES"
       WIDTH 65 CENTER, NEWLINE;

or

    PRINT "JOB CLASSIFICATIONS AND CODES"
       WIDTH 65 CENTER; NEWLINE;

The style you use is a matter of personal taste. However, the results of forgetting a new line command can be very peculiar (we will illustrate this shortly), so it is good practice to develop a script writing style which makes remembering the `NEWLINE` easier. To this end, we suggest you always include any new line requests at the end of a `PRINT` statement, rather than as statements by themselves or at the beginning of the next `PRINT` statement.

Run the report. You should now have the following:

`JOB CLASSIFICATIONS AND CODES
Job Classification Code`

### 8.2.4 Retrieving Data

Now that we have the titles set up, it is time to get some data for the report. This involves three steps:

1. Identify the database to be used.

2. Select the proper data.

3. Print the values retrieved.

### 8.2.5 Identifying the Database

Empress Report Writer must know the name of the database from which data is to be retrieved before it can do any retrievals. The database name is kept in the special Empress Report Writer system variable `DATABASE`. In this example, we will write all system variables in upper case, to distinguish them from attributes or other variables we may create. In your own scripts, however, they may be in upper case, lower case, or any combination of the two.

The simplest way to specify the name of the database (which is called `repairs`) is by a `LET` statement, as follows:

    LET DATABASE = "repairs";

Edit script and add the above line before any of the others, so that your entire script now consists of:

    LET DATABASE = "repairs";

    LET PAGELENGTH = 10;
    PRINT "JOB CLASSIFICATIONS AND CODES"
       WIDTH 65 CENTER, NEWLINE;
    NEWLINE 2;
    PRINT "Job Classification          Code"
        WIDTH 65CENTER, NEWLINE;

### 8.2.6 Selecting the Data

Data is retrieved by Empress Query Language `SELECT` statements. For the report, we want all the records from the `rates` table, sorted alphabetically by job type. `rates` has three attributes: `code`, `job_type`, and `rate`. We are only interested in `job_type` and `code`. Values for these can be retrieved in a single `SELECT` statement:

`SELECT job_type, code FROM rates SORT BY job_type;`

Edit script and add this line after the line specifying the database, with blank lines before and after it for readability.

The `SELECT` statement must be placed after the database is specified, or the database will be unknown and Empress Report Writer will print an error message. However, the `SELECT` statement must appear before any attempt to use the attribute values retrieved by it.

Your entire script is now:

    LET DATABASE = "repairs";
    SELECT job_type, code FROM rates SORT BY job_type;
    LET PAGELENGTH = 10;
    PRINT "JOB CLASSIFICATIONS AND CODES"
       WIDTH 65 CENTER, NEWLINE;
    NEWLINE 2;
    PRINT "Job Classification          Code"
       WIDTH 65CENTER, NEWLINE;

### 8.2.7 Printing the Retrieved Values

After the `SELECT` statement has been added to the report script, the values of the attributes retrieved are available and may be printed out or used in calculations.

For the report, we simply want to print the values of `job_type` and `code` for each record, in the proper columns for neatly laid-out output. This is done by the following lines:

    FOR EACH RECORD
       PRINT COLUMN 21, job_type, COLUMN 42, code,
         NEWLINE;
    END;

This set of statements illustrates a `RECORD` statement (`RECORD` statements are a class of `FOR` statements). It is identified by the keywords for each record, and any statements following these will be executed for each record retrieved, until the keyword `END` is encountered. In this example, there is only a single `PRINT` statement within the body of the `RECORD` statement. Empress Report Writer prints the current value of the two attributes `job_type` and `code` for each record processed.

The new keyword `COLUMN`, which takes one argument, appears in the `PRINT` statement. It instructs Empress Report Writer to move across the line to the given column, and start printing in that column.arguments

Thus, the `PRINT` statement above has five arguments separated by commas: three print commands (the `COLUMN` and `NEWLINE` commands) and two attributes (`job_type` and `code`).

How do you decide where each column should begin? If a report is to be printed on a special form, you will usually know how many columns fit across it, and can count off where fields start and end. For a free-form report like this example, take a rough guess and then correct the layout by trial and error.

Edit script and add the `RECORD` statement to its end, with a blank line before it for readability. You will also need to leave a blank line in the output between the column headings and the data in the output. Add an extra `NEWLINE` statement after the statement which prints the column headings. Your entire script now consists of:

    LET DATABASE = "repairs";
    SELECT job_type, code FROM rates SORT BY job_type;
    LET PAGELENGTH = 10;
    PRINT "JOB CLASSIFICATIONS AND CODES"
       WIDTH 65 CENTER, NEWLINE;
    NEWLINE 2;
    PRINT "Job Classification          Code" WIDTH 65 CENTER,
       NEWLINE;
    NEWLINE;
    FOR EACH RECORD
       PRINT COLUMN 21, job_type, COLUMN 42, code,
       NEWLINE;
    END;

Run the report. You should see the following:

`JOB CLASSIFICATIONS AND CODES`
                   Job Classification          Code

                      Axles                    5
                      Battery                  15
                      Body work                11
                      Brakes                   12
                      Carburetor               18
                      Distributor              20
                      Doors                    1
                      Exhaust                  14
                      Fenders                  4
                      Ignition/sparks          7
                      Lights                   3
                      Lubrication              6
                      Pistons                  17
                      Radiator                 19
                      Shocks                   16
                      Tires                    9
                      Transmission             10
                      Universal                13
                      Wheels                   8
                      Windshields              2

Your report now prints three 10-line report pages, with no white space at the page breaks, and once more runs off the screen. Note that the last page is padded up to its full length of 10 lines with five blank lines.

### 8.2.8 Forgetting New Line Requests

As an example of the sort of things that happen when you leave out new line requests, edit `script` and remove the `NEWLINE` from the final `PRINT` statement. Your script is now:

    LET DATABASE = "repairs";
    LET PAGELENGTH = 10;

    SELECT job_type, code FROM rates SORT BY job_type;

    LET PAGELENGTH = 20;
    PRINT "JOB CLASSIFICATIONS AND CODES"
       WIDTH 65 CENTER, NEWLINE;
    NEWLINE 2;
    PRINT "Job Classification           Code" WIDTH 65
       CENTER, NEWLINE;
    NEWLINE;

    FOR EACH RECORD
       PRINT COLUMN 21, job_type, COLUMN 42, code;
    END;

Run the report. On your screen you will see the titles printed, then lots of error messages complaining that you are attempting to overwrite the previous field, and finally all the lines of the report printed on top of each other. Empress Report Writer produces error messages when two printing requests overlap unless the special keyword `OVERPRINT` is used.

### 8.2.9 Overstriking Characters

`OVERPRINT` causes printing to go back to the beginning of the line very handy for underlining things. Edit `script` and put the `NEWLINE` back in, then change the line printing the first title as follows:

    PRINT "JOB CLASSIFICATIONS AND CODES"
       WIDTH 65 CENTER OVERPRINT,
       "_____________________________"
       WIDTH 65 CENTER, NEWLINE;

Run the report. On your screen you will see first the title printed, then the underscores printed over them, so that only the underscores will remain on the screen. When printed on paper this will underline the title neatly as expected. If you are designing a report with underlining which is to be viewed on a screen as well as printed, you should print the underscores first and then the words to be underlined. This leaves the words visible.

### 8.2.10 Final Details for the First Report

The report is almost complete, all we need now is a few extra lines at the top of the page, to place the report nicely in the page and to reset the page length for paper output, rather than viewing the report on the terminal screen.

Edit `script` and add a "NEWLINE 12;" statement before the report title. Also remove the page length specification.

### 8.2.11 Comments

As our last touch, we will add a comment at the beginning of the script to say what the script does. Any text between "`/*`" and "`*/`" in an Empress Report Writer script is treated as a comment by Empress Report Writer, and ignored. A comment need not be a single line; it may be several lines long.

Add the following line to the beginning of `script`:

`/* List of job classifications and codes */`

The script is now complete, and should look like this:

    /* List of job classifications and codes */

    LET DATABASE = "repairs";

    SELECT job_type, code FROM rates SORT BY job_type;

    NEWLINE 12;
    PRINT "JOB CLASSIFICATIONS AND CODES"
       WIDTH 65 CENTER, OVERPRINT;
       "_____________________________"
       WIDTH 65 CENTER, NEWLINE;
    NEWLINE 2;
    PRINT "Job Classification          Code" WIDTH 65
       CENTER, NEWLINE;
    NEWLINE;

    FOR EACH RECORD
       PRINT COLUMN 21, job_type, COLUMN 42, code,
    NEWLINE;
    END;

## 8.3 Example 2 - Grouping Records

The second report is a bit more complex than the first. It produces a summary of income grouped by job type over a given time period, taking information from the `hours` and `rates` tables. The rate of pay for employees, and hence the amount billed to the customer for each job, depends on the job type. Customers are billed at 1.5 times the rate paid to mechanics, so a separate table of charges to customers is not required. This report is used to check how business is doing, and see what kinds of repair jobs are most profitable. It is shown below:

### 8.3.1 Starting the Second Report

The report and column headings for the second report are very similar to those for the first, so we can get them set up very quickly. We will give this script an initial page length of 23 lines (one screenful), and add a comment at the beginning to say what the report is about.

Edit a new file, `script.2`, containing the following:

    /* Weekly summary of income by repair job type */

    LET PAGELENGTH = 23;
    LET DATABASE = "repairs";

    PRINT COLUMN 16, "SUMMARY OF INCOME BY JOB",
       "TYPE, FEB 28 - MARCH 5 1992", NEWLINE 3;
    PRINT COLUMN 25, "Job Type          Income", NEWLINE;
    PRINT COLUMN 25, ,"-------------------------"
       NEWLINE 2;

The first `PRINT` statement illustrates splitting a single statement over two lines in a script.

### 8.3.2 Selecting the Data

For this report we need to join the `hours` and `rates` tables, retrieving the hours spent on each job type from the `hours` table and the basic charging rate for each job type from the `rates` table, for the proper time period. For each repair job done, there is an entry in the `hours` table which includes the date, the job code, and the hours spent on it. The job types can be identified by the job codes from the `rates` table. We will be totalling the income for each job type, and therefore must sort the retrieved records by `job_type` to group the output into clusters of the same job types.

The `SELECT` statement is:

    SELECT job_type, ATTR "hours", rate
       FROM rates, "hours"
       WHERE "hours".code = rates.code
       AND date RANGE "28 Feb 92" TO "5 Mar 92"
       SORT BY job_type;

Edit `script.2` and add the `SELECT` statement after the database specification.

Note

Since hours is also an Empress Report Writer keyword it must be enclosed in quotes, and to distinguish it from an ordinary character string it must be prefaced with the keyword `ATTR`.

### 8.3.3 Defining a Derived Attribute

We bill customers at 1.5 times the `rate` in the `rates` table, therefore we need to calculate income as the hours times the rate times 1.5 for each job type. We are also going to need the sum of this for each job type, and the overall sum.

These goals are accomplished by defining a derived attribute called `income` as (`hours x rate x 1.5`), and then finding the sum of that.

Derived attributes can be treated exactly like real ones. A value exists for them for every record retrieved, and their values may be used to group the records for processing as well. The following statement defines `income`:

`DEFINE income = ATTR "hours" * rate * 1.5;`

The "`*`" is Empress Report Writer's multiplication symbol.

Edit `script.2` and add the definition of income right after the `SELECT` statement. Definitions must always be placed immediately after the `SELECT` statement yielding the attributes involved in the definition, before any other kinds of statements.

### 8.3.4 Looking at the Data Retrieved

At this point we will print out all the data retrieved, and the value of the derived attribute income, so that we can see what we are dealing with. This will also make the explanation of how these data are organized into groups simpler.

Edit `script.2` and add the following `RECORD` statement at the end:

    FOR EACH RECORD
       PRINT job_type, COLUMN 25, ATTR "hours", COLUMN 30,
         "$", rate, COLUMN 35, "$", income, NEWLINE;
    END;

The indentation before the `PRINT` statement is not strictly necessary, since Empress Report Writer ignores white space at the beginning of lines.

It is included to improve the readability of the script, an important point in longer scripts. Your entire script is now:

    LET PAGELENGTH = 23;
    LET DATABASE = "repairs";

    SELECT job_type, ATTR "hours", rate
       FROM rates, "hours"
       WHERE date RANGE "28 Feb 92" TO "5 Mar 92"
       AND "hours".code = rates.code
       SORT BY job_type;

    DEFINE income = ATTR "hours" * rate * 1.5;

    PRINT COLUMN 16, "SUMMARY OF INCOME BY JOB TYPE",
       "FEB 28 - MARCH 5, 1992", NEWLINE 3;
    PRINT COLUMN 25, "Job Type          Income",
       NEWLINE;
    PRINT COLUMN 25, "-------------------------", NEWLINE 2;

    FOR EACH RECORD
       PRINT job_type, COLUMN 25, ATTR "hours",
          COLUMN 30, "$", rate, COLUMN 35, "$",
          income, NEWLINE;
    END;

Run your script, using:

`emprepwr script.2`

You should see:

This output is not well laid out, but it does show quite clearly that income has come from ten different kinds of job types during this week, and shows the income derived from each separate job.

### 8.3.5 Grouping Records

We now need to sum the income for each different job type that is, group the records by `job_type` and sum `income` over each group. For this, we need a `GROUP` statement, which is similar in structure to a `RECORD` statement but operates on groups of records rather than individual records.

Edit `script.2`, remove the entire `RECORD` statement from the end, and replace it with the following lines:

    FOR EACH GROUP of job_type
       PRINT COLUMN 25, job_type;
       LET total = SUM OF income;PRINT COLUMN 43, total PICTURE "$FFF9V.99"
          WIDTH 8 RIGHT, NEWLINE;
    END;

A `GROUP` statement is identified by the keywords `FOR EACH GROUP OF`, followed by the name of an attribute or a derived attribute, and is terminated by the `END` keyword. Within this `GROUP` statement we have a `PRINT` statement to output the job type, a `LET` statement to sum the income, and another `PRINT` statement to output the sum, using a specified picture to format the dollar amount.

`PICTURE` is a keyword which tells Empress Report Writer to use a picture to print the preceding item in the `PRINT` statement.

The picture is always enclosed in quotes. This particular picture format prints a dollar sign, at least one and up to four digits, a decimal point, and two more digits. The V indicates the position of the decimal point in the values Empress Report Writer receives. The `F` print a digit if one is present, and nothing if it is not. The 9 forces a digit to be printed, printing a zero if there is no number to fill that place. Hence, the dollar amount 1234.56 prints as $1234.56, 123.45 prints as $123.45, 1.23 as $1.23, .23 as $0.23, and 12 as $12.00. The column is right-justified to align the amounts properly.

### 8.3.6 LET Statements

We have used `LET` statements several times before. They are identified by the keyword `LET`, and give a value to a variable, which is basically a named place to store a value, either numeric or character. If the variable does not already exist, it is created. If it already has a value, the old value is replaced.

Any expression which results in a numeric value, such as `4+3-2`, or `27/3`, including calculations on attribute values or derived attribute values (`amount/2, hours * rate`), may be assigned to a variable. Character strings, enclosed in quotes, may also be stored as variables for example, the whole title of the report could be stored in a variable called `title` by the `LET` statement:

    LET title = "SUMMARY OF INCOME BY JOB TYPE", concat"
       FEB 28 - MARCH 5 1992";

Wherever a variable name appears in a `PRINT` statement, the current value of the variable will be printed.

The `LET` statement in our current report sums income and stores it in the variable `total`. This brings us to another topic: built-in functions and arithmetic.

### 8.3.7 Built-in Functions and Calculations

Empress Report Writer allows all the normal arithmetic operations in calculations: addition (+), subtraction (-), multiplication (*), and division (/). As well, there is a remainder operation (%) for example, 9%4 is 1, and 27%12 is 3.

There is a second group of operations which may be performed on attributes or derived attributes. These are aggregate functions (functions that work on groups of records): `SUM`, `AVG`, `COUNT`, `MIN`, and `MAX`.

These operations cannot be performed on expressions. For example, you cannot say `SUM OF (hours * rate * 1.5)`. Instead of expressions, derived attributes are used. You can say `SUM OF income` once `income` has been defined as `hours * rate * 1.5`.

When sum, average, count, maximum, or minimum are used inside a `GROUP` statement, they operate on all the records for that group. When they are used outside a `GROUP` statement, they operate on all the records retrieved by the `SELECT` statement.

### 8.3.8 Nearly Complete Report

Your entire script should now be:

    /* Weekly summary of income by repair job type */

    LET PAGELENGTH = 23;
    LET DATABASE = "repairs";

    SELECT job_type, ATTR "hours", rate
       FROM rates, "hours"
       WHERE "hours".code = rates.code
       AND date RANGE "28 Feb 1992" TO "5 Mar 1992"
       SORT BY job_type;

    DEFINE income = ATTR "hours" * 1.5 * rate;
    PRINT COLUMN 16, "SUMMARY OF INCOME BY JOB TYPE",
       "FEB 28 - MARCH 5 1992", NEWLINE 3;
       PRINT COLUMN 25, "Job Type          Income",
          NEWLINE;
       PRINT COLUMN 25, "-------------------------",
          NEWLINE 2;

    FOR EACH GROUP OF job_type
       PRINT COLUMN 25, job_type;
       LET total = SUM OF income;PRINT COLUMN 43, total PICTURE "$FFF9V.99"
          WIDTH 8 RIGHT, NEWLINE;
    END;

Run your report. You should see:

The only things we are missing now are the overall total and the titles at the top and bottom of the page.

First, the overall total. Edit `script.2` again and add the following lines to the end:

    PRINT COLUMN 25, "--------------------",
       NEWLINE;
    PRINT COLUMN 25, "TOTAL", COLUMN 43, SUM OF income
       PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
    PRINT COLUMN 25, "--------------------",
       NEWLINE;

Note that `SUM OF income` will now apply to all the records retrieved, since it is outside the `GROUP` statement.

Run the report again. You now have:

### 8.3.9 Running Page Headers and Footers

All we need now are headings at the top of the page with the date, and the page number at the bottom of the page. We will also eventually need to reset the page length properly for paper output, rather than viewing the report on the terminal screen. For now, however, we will keep the shorter page length to show the effect of adding a header and footer more clearly.

Headers and footers are groups of lines which Empress Report Writer prints at the top or bottom of every page in a report, respectively. The ones for the report are specified as follows:

    HEADER
       NEWLINE 2;
          PRINT "Universal Automotive Services", COLUMN 66,
             TODAY, NEWLINE 2;
    END;

    FOOTER
       NEWLINE 3;
          PRINT COLUMN 72, "Page ", PAGENUMBER, NEWLINE 2;
    END;

They are identified by the keywords `HEADER` or `FOOTER`, and terminated by the `AND` keyword.

The header leaves two blank lines (since it begins at the top of the page), prints a title at the left and a date at the right, and leaves one more blank line. The system variable `TODAY` holds the current date.

If this variable is given as an argument to a `PRINT` statement, today's date will be printed.

The footer is similar in structure to the header. The number of the current page is kept by Empress Report Writer in the system variable `PAGENUMBER`.

Edit `script.2` and add the header and footer at the beginning of the script. Leave a blank line after them for readability. Your script is now:

    /* Weekly summary of income by repair job type */

    HEADER
       NEWLINE 2;
       PRINT "Universal Automotive Services",
          COLUMN 66, TODAY, NEWLINE 2;
    END;

    FOOTER
       NEWLINE 3;
       PRINT COLUMN 72, "Page ", PAGENUMBER,
          NEWLINE 2;
    END;

    LET PAGELENGTH = 23;
    LET DATABASE = "repairs";

    SELECT job_type, ATTR "hours", rate
       FROM rates, "hours"
       WHERE date RANGE "28 Feb 1992" TO "5 Mar 1992"
       AND "hours".code = rates.code
       SORT BY job_type;

    DEFINE income = ATTR "hours" * 1.5 * rate;

    PRINT COLUMN 16, "SUMMARY OF INCOME BY JOB TYPE,"
       "FEB 28 - MARCH 5, 1992",NEWLINE 3;
    PRINT COLUMN 25, "Job Type          Income", NEWLINE;
    PRINT COLUMN 25, "------------------------", NEWLINE 2;

    FOR EACH GROUP OF job_type
       PRINT COLUMN 25, job_type;
       LET total = SUM OF income;
       PRINT COLUMN 43, total PICTURE "$FFF9V.99"
       WIDTH 8 RIGHT, NEWLINE;
    END;
    PRINT COLUMN 25, "-------------------------", NEWLINE;
    PRINT COLUMN 25, "TOTAL", COLUMN 43, SUM OF income
       PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
    PRINT COLUMN 25, "-------------------------", NEWLINE;

Run it again. Your output is:

Note that the page length of 23 lines means the entire page, including any header and footer.

### 8.3.10 Final Details for the Second Report

Let us put the finishing touches on the second report. These consist of changing the page length back to the normal size for a printed page (Empress Report Writer's default), and altering the format for the date in the header.

The format in which we would like the date printed is `March 7, 1992`. This is done by specifying a picture format in the `PRINT` statement, as follows:

`PRINT` This date picture is fairly simple "`aaaaaaaaa`" indicates the month should be alphabetic ("`mm`" is used for a numeric month). The lower case "`a`" tell Empress Report Writer not to add blanks after the month for short month names. "`AAAAAAAAA`" would print all the month names 9 characters long (long enough for "`September`", the longest month name), adding blanks to the end of the short ones. "`dd`" is a numeric day, with no leading zeros for single-digit days. ("`DD`" forces a leading zero.) The comma is printed as a literal comma. Finally, "`yyyy`" prints the year out in full as "`1992`".

Edit `script.2` and make these changes. Your script is now:

    /* Weekly summary of income by repair job type */

    HEADER
       NEWLINE 2;
       PRINT "Universal Automotive Services",
          COLUMN 66, TODAY PICTURE
          "aaaaaaaaa dd, yyyy", NEWLINE 2;
    END;

    FOOTER
       NEWLINE 3;
       PRINT COLUMN 72, "Page ", PAGENUMBER,
          NEWLINE 2;
    END;

    LET DATABASE = "repairs";

    SELECT job_type, ATTR "hours", rate
       FROM rates, "hours"
       WHERE date RANGE "28 Feb 1992" TO "5 Mar 1992"
       AND "hours".code = rates.code
       SORT BY job_type;

    DEFINE income = ATTR "hours" * 1.5 * rate;

    PRINT COLUMN 16, "SUMMARY OF INCOME BY JOB TYPE",
       "FEB 28 - MARCH 5, 1992", NEWLINE 3;

    PRINT COLUMN 25, "Job Type          Income",
       NEWLINE;
    PRINT COLUMN 25, "-------------------------", NEWLINE 2;

    FOR EACH GROUP OF job_type
       PRINT COLUMN 25, job_type;
       LET total = SUM OF income;
       PRINT COLUMN 43, total PICTURE "$FFF9V.99"
       WIDTH 8 RIGHT, NEWLINE;
    END;

    PRINT COLUMN 25, "-------------------------", NEWLINE;
    PRINT COLUMN 25, "TOTAL", COLUMN 43, SUM OF income
       PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
    PRINT COLUMN 25, "-------------------------", NEWLINE;

## 8.4 Example 3 - Report Parameters

The second report, although useful, is limited in its current state because the time period it covers is fixed. Every time we wish to run the report, we will have to first edit the script to change the title and the dates in the `SELECT` statement. This is clearly undesirable.reports:

This problem is overcome by the use of parameters, values passed in to Empress Report Writer at the time the report is run which are substituted for variables in the script. To use parameters for the report, two alterations are necessary. First, Empress Report Writer must be told what parameters to expect. Second, the dates in the title and `SELECT` statement must be replaced by variables.

We will pass three parameters in for the report, calling them `titledate`, `startdate`, and `enddate`, respectively. They are specified by the following:

    PARAMETERS
       CHAR     titledate;
       DATE     startdate;
       DATE     enddate;
    END;

The keyword `PARAMETER`s begins parameter specifications, and `END` ends them. Each parameter has a type and a name as described in the table below:

Table 8-1: Parameter Types

Type | Description
`CHAR` | for characters
`DATE` | for date values
`DECIMAL` | for ordinary decimal numbers
`FLOAT` | for exponential numbers
`INTEGER` | for integers

The name is the variable which will hold the parameter value.

Edit `script.2` and add the parameter specifications at the beginning. If parameters are specified, they must always be the first statements in a report script.

To change the report title so that it uses a variable, edit the `PRINT` statement which produces the title and change it to:

    PRINT COLUMN 16, "SUMMARY OF INCOME BY JOB",
       "TYPE, ", titledate, NEWLINE 3;

Finally, edit the `SELECT` statement to alter the two dates. The `SELECT` statement should now be:

    SELECT job_type, ATTR "hours", rate
       FROM rates, "hours"
       WHERE "hours".code = rates.code
       AND date RANGE var.startdate TO var.enddate
       SORT BY job_type;

Note

Variables used in `SELECT` statements must have the prefix `var.` to be interpreted correctly by Empress Report Writer. Also, these variable names cannot contain underscores. This applies only to `SELECT` statements.

The script is now ready. It should be exactly as follows:

    /* Weekly summary of income by repair job type */

    PARAMETERS
       CHAR     titledate;
       DATE     startdate;
       DATE     enddate;
    END;

    LET DATABASE = "repairs";

    HEADER
       NEWLINE 2;
       PRINT "Universal Automotive Services", COLUMN 66,
          TODAY PICTURE "aaaaaaaaa dd, yyyy", NEWLINE 2;
    END;

    FOOTER
       NEWLINE 3;
       PRINT COLUMN 72, "Page ", PAGENUMBER, NEWLINE 2;
    END;

    SELECT job_type, ATTR "hours", rate
       FROM rates, "hours"
       WHERE date RANGE var.startdate TO var.enddate
       AND "hours".code = rates.code
       SORT BY job_type;

    DEFINE income = ATTR "hours" * 1.5 * rate;

    PRINT COLUMN 16, "SUMMARY OF INCOME BY JOB TYPE, ",
       titledate, NEWLINE 3;
    PRINT COLUMN 25, "Job Type          Income", NEWLINE;
    PRINT COLUMN 25, "------------------------",NEWLINE 2;
    FOR EACH GROUP OF job_type
       PRINT COLUMN 25, job_type;
       LET total = SUM OF income;
       PRINT COLUMN 43, total PICTURE "$FFF9V.99"
          WIDTH 8 RIGHT, NEWLINE;
    END;
    PRINT COLUMN 25, "------------------------", NEWLINE;
    PRINT COLUMN 25, "TOTAL", COLUMN 43, SUM OF income
       PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
    PRINT COLUMN 25, "------------------------", NEWLINE;

### 8.4.1 Running Reports with Parameters

To run this generalized report, use the following:

    emprepwr script.2 "FEB 28 - MARCH 5, 1992"
       19920228 19920305

Note that the character string must be enclosed in quotes, and the dates must be expressed either in universal date format, "`yyyymmdd`", or the format "`yymmdd`".

## 8.5 Example 4 - Arithmetic Calculations

This example takes you step-by-step through two quite complex reports, illustrating nested `GROUP` and `RECORD` statements, control flow, and some more complex use of variables and picture formats.reports:complex

The first report prints out weekly time sheets for employees, and the second prints invoices for customers.

### 8.5.1 Weekly Time Sheets

The weekly time sheets list the hours spent by each mechanic on repair jobs for that week. These are used to calculate each employee's wages for the week, and also serve as records of the jobs performed during the week. These reports take information from the `hours`, `rates`, and `personnel` tables.

The date, job description, hours spent, and a calculated income for each job are printed for each employee each day. Subtotals of the amount earned each day and a grand total for the whole week are also printed.

The weekly time sheets for three employees, Kilroy, Mosca, and Wladislaw, are shown on the following pages.

### 8.5.2 Starting the Time sheets

We can get some of the details for the time sheets set up quite quickly. First we will have a comment to identify the report script. Next, we will need to pass in the start and end of the time period which the report covers as parameters, just as we did for the second report. The database is still `repairs`, so that can be set as well. We will have a page width of 70 characters for this report this will eventually fit everything across the page and give us a report with three-quarter-inch margins on standard paper.

We can set up the header and footer as well. Since the title "`WEEKLY TIME SHEET`" appears on every page, it can be included in the header.

Edit a file called `script.3`, containing the following:

    /* Weekly time sheets for the employees of the Garage */

    PARAMETERS
       DATE     startdate;
       DATE     enddate;
    END;

    LET DATABASE = "repairs";
    LET PAGEWIDTH = 70;
    LET PAGELENGTH = 23;

    HEADER
       NEWLINE 3;
       PRINT "Universal Automotive Services", COL 53,
          TODAY PICTURE "dd aaaaaaaaa yyyy"
          WIDTH 17 RIGHT, NEWLINE 3;
       PRINT "WEEKLY TIME SHEET" WIDTH 70 CENTER,
          NEWLINE 3;
    END;

    FOOTER
      NEWLINE 3;
       PRINT COL 63, "Page ", PAGENUMBER PICTURE
          "ZZ", NEWLINE 3;
    END;

There is little that is new in this script. We are right-justifying the date in the header, and using a new picture format to print the page number. `Z` in a picture prints a number in that position, but turns it into a space if it is a leading zero. Thus, page numbers less than 10 will print with a space before the number.

### 8.5.3 The SELECT Statement

The `SELECT` statement for this script is much more complex than those for the previous reports. For each repair job in the given time period, we need the job code, the job number, the hours spent on it, its pay rate, when it was done, what it was, and who did it. The dates, hours spent, job descriptions, codes, and numbers come from the `hours` table. We will join `hours` with `personnel` to find the name of the mechanic, and with `rates` to find the pay rate for each job code. Since we are producing the week's jobs for each mechanic, we need to sort the output by the mechanics' names. Jobs for each day must be kept together, so we sort by date as well, and finally by job number, to keep all the different bits of the same job together.

The `SELECT` statement is:

    SELECT code, ATTR 'job no.', date, ATTR 'hours',
       rate, description, name
       FROM rates, 'hours', personnel
       WHERE date RANGE var.startdate TO var.enddate
       AND 'hours'.code = rates.code
       AND 'hours'.number = personnel.number
       SORT BY name, date, ATTR 'job no.';

Edit `script.3` and add the `SELECT` statement at the end.

We will next need a definition for the amount each mechanic earns, which is the job rate times the hours spent. Edit `script.3` and add the following line at the end:

`DEFINE amount = ATTR 'hours' * rate;`

### 8.5.4 Setting Up the GROUP Statements

We need to print the name of each mechanic and a title for the various columns of the report. These will be done within with a `GROUP` statement grouping on name.

Edit `script.3` and add:

    FOR EACH GROUP OF name
       PRINT "NAME: ", name, COL 47,
          "Week Ending: ",
          enddate PICTURE "dd aaa yyyy"
          ,NEWLINE 2;
       PRINT "Day            Job #   ","
          Job Description      Code",
          "Hours   Rate    Total", NEWLINE;
    PRINT "---            -----   ",
          "---------------      ----",
          "-----   ----    -----", NEWLINE;

At this point we need to set up the daily groupings and print the date. We will then go through each record for that day, printing the relevant information, and finally subtotal the amount earned for that day.

Edit `script.3` and add the following:

    FOR EACH GROUP OF date
       PRINT date PICTURE "www aaa dd";
       FOR EACH RECORD
          PRINT COL 15, ATTR 'job no.' PICTURE
                "9999"
             COL 23, description, COL 43,
                code PICTURE "99",
             COL 47, ATTR 'hours' PICTURE
                "F9V.9" WIDTH 4 RIGHT,
             COL 54, rate PICTURE "$F9/hr",
                COL 62, amount PICTURE "$FFF9V.99"
                WIDTH 8 RIGHT, NEWLINE;
       END;

       PRINT COL 62, "--------", NEWLINE;
       PRINT COL 29, "Subtotal", COL 62,
          SUM OF amount PICTURE "$FFF9V.99"
           WIDTH 8 RIGHT, NEWLINE;
       PRINT COL 62, "--------", NEWLINE 2;
    END;

This is another `GROUP` statement, nested within the first one. It causes grouping by date within name groups. Note that the `SELECT` statement that does the retrieval must sort the data in the order in which groups will be nested in the report.

Note the day of the week used in the picture format for the date. Date formats can be extremely flexible. You should refer to Chapter 7.5 for the full list of date pictures available.

Next we print the job number, description, job code, pay rate, hours spent on it, and amount the mechanic earned for every job done that day. This is done in a `RECORD` statement, nested within the inner `GROUP` statement.

After the inner `GROUP` statement (which goes through each day) is finished, we need to work out the grand total of the mechanic's income for the week, and print it out in suitable style. This completes the outer `GROUP` statement, which will then start on the next mechanic. We want the next time sheet to start on a new page, however, so we must include a `NEWPAGE` statement just before the end of the `GROUP` statement. We also want page numbering to restart at 1 for each mechanic, rather than increasing through the whole report, so we set the system variable `PAGENUMBER` back to 1 just after the `NEWPAGE` statement.

Edit `script.3` and add:

    PRINT "----------------------------------------",
       "------------------------------", NEWLINE;
    PRINT COL 4, "TOTALS FOR WEEK", COL 44, SUM OF ATTR
       'hours' PICTURE "FF9V.9" WIDTH 7 RIGHT,
       COL 62, SUM OF amount PICTURE "$FFF9V.99"
       WIDTH 8 RIGHT, NEWLINE;
    PRINT "----------------------------------------",
       "------------------------------", NEWLINE;
    NEWPAGE;
    LET PAGENUMBER = 1;
    END;

### 8.5.5 Complete Time sheet Report

The time sheet report is now complete, except for re-setting the page length to the default. Edit `script.3` and remove the page length instruction.

Your script should now appear as shown below. Note the use of indentation to indicate logical groupings in the script (`GROUP` and `RECORD` statements, headers and footers), and blank lines to improve readability. These are important in aiding others to understand the purpose of a report script when reading through it, and to help you keep your logic clear when creating the script.

    /* Weekly time sheets for the employees of the Garage */

    PARAMETERS
       DATE    startdate;
       DATE    enddate;
    END;

    LET DATABASE = "repairs";
    LET PAGEWIDTH = 70;

    HEADER
       NEWLINE 3;
       PRINT "Universal Automotive Services", COL 53,
          TODAY PICTURE "dd aaaaaaaaa yyyy"
          WIDTH 17 RIGHT, NEWLINE 3;
       PRINT "WEEKLY TIME SHEET" WIDTH 65 CENTER,
          NEWLINE 3;
       END;

    FOOTER
       NEWLINE 3;
       PRINT COL 63, "Page", PAGENUMBER PICTURE
          "ZZ", NEWLINE 3;
    END;

    SELECT code, ATTR 'job no.', date, ATTR 'hours',rate, description, name
       FROM rates, 'hours', personnel
       WHERE date RANGE var.startdate TO var.enddate
       AND 'hours'.code = rates.code
       AND 'hours'.number = personnel.number
       SORT BY name, date, ATTR 'job no.';

    DEFINE amount = ATTR 'hours' * rate;

       FOR EACH GROUP OF name
          PRINT "NAME: ", name, COL 47, "Week Ending:",
          enddate PICTURE "dd aaa yyyy", NEWLINE 2;
       PRINT "Day            Job #   ",
          "Job Description      Code",
          "Hours   Rate    Total", NEWLINE;
       PRINT "---            -----   ",
          "---------------      ----",
          "-----   ----    -----", NEWLINE 2;

       FOR EACH GROUP OF date
          PRINT date PICTURE "www aaa dd";

       FOR EACH RECORD
       PRINT COL 15, ATTR 'job no.' PICTURE "9999",
          COL 23, description, COL 43, code PICTURE "99",
          COL 47, ATTR 'hours' PICTURE "F9V.9"
          WIDTH 4 RIGHT,
          COL 54, rate PICTURE "$F9/hr", COL 62,
          amount PICTURE "$FFF9V.99" WIDTH 8 RIGHT,
       NEWLINE;
       END;

       PRINT COL 62, "--------", NEWLINE;
       PRINT COL 29, "Subtotal", COL 62, SUM OF amount
          PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
       PRINT COL 62, "--------", NEWLINE 2;
    END;

    PRINT "----------------------------------------",
       "------------------------------", NEWLINE;
    PRINT COL 4, "TOTALS FOR WEEK", COL 44,
       SUM OF ATTR 'hours' PICTURE "FF9V.9" WIDTH 7 RIGHT,
       COL 62, SUM OF amount
       PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
    PRINT "----------------------------------------",
       "------------------------------", NEWLINE;
    NEWPAGE;
       LET PAGENUMBER = 1;
    END;

Run the report, using

`report writer script.3 19920228 19920305`

Note how the lines drawn above and below the subtotals and grand totals make them stand out well from the rest of the report.

## 8.6 Example 5 - Text Attributes

The last report prints out the invoices for customers. For accounting reasons, each job is invoiced separately, so if a customer has brought in his automobile twice in one week, he will get two invoices. Each invoice gives a breakdown of all the separate parts of each job.

The invoice is designed so that the customer's name and address will appear in the lower window of a window envelope when the invoice is folded for mailing, and the address of the garage will appear in the upper window. Invoices for two customers are shown on the next pages.

### 8.6.1 Starting the Invoices

For the invoices, we again want to pass in the time period it covers as parameters, and set the database to repairs. We will ask for a page width of 75, leaving half-inch margins on standard size paper. We will not define the header and footer yet, since we will want to use attribute values in them. This means they must come after the `SELECT` statement.

Edit a file called `script.4`, and add the following:

    /* Current Invoices for the Garage */

    PARAMETERS
       DATE          startdate;
       DATE          enddate;
    END;
    LET PAGEWIDTH = 75;
    LET DATABASE = "repairs";

We will not set a short page length for this report, since the screen will not be large enough to display an entire invoice.

You will have to use a paging program such as `more`, `pr`, or `pg` when you run it, or use terminal control flow (<Ctrl+S>, <Ctrl+Q>, scrolling, etc.).

### 8.6.2 The SELECT Statement

This report takes information from the `jobs`, `customers`, `rates`, and `hours` table, and the `SELECT` statement is consequently rather complex. We will need each customer's number, name, and address, and the date the job was started. For each part of this job, we need its description, the number of hours spent on it, the rate it is charged at, and the number of the mechanic who did it. Finally, we need the job number, since this is what we use to number invoices. We need to sort our output by job number to keep all the parts of any one job together. We will also restrict the names to Watson and Wong while we are setting up the report script, to avoid excessive amounts of output.

Edit `script.4` and add the following `SELECT` statement to the end:

    SELECT name, ATTR 'customer no.', address, 'hours'.date,
       ATTR 'job no.', description, 'hours'.number,
       ATTR 'hours', rate
    FROM customers, 'hours', rates, jobs
       WHERE customers.number = jobs.'customer no.'
       AND jobs.'job no.' = 'hours'.'job no.'
       AND 'hours'.code = rates.code
       AND name MATCH "*Wong*|*Watson*"
       AND jobs.date RANGE var.startdate TO var.enddate
    SORT BY ATTR 'job no.';

In this case an attribute with a complex name, job no., is also being used in the join condition of the `SELECT` statement. Here it must be qualified by the table name, rather than the keyword `ATTR`, to identify the two tables correctly. (Note that the table name may always be used to qualify an attribute name, so that `customers`.'`customer no`' is equivalent to '`customer no`'. When two attributes have the same name but contain different data, it is essential to qualify them with their table names.)

We are also going to need a defined attribute for the total charged to the customer. For each section of the job we charge at 1.5 times the hours times the rate from the `rates` table (the rate paid to employees).

Edit `script.4` again and add the following definition right after the `SELECT` statement.

`DEFINE total = rate * ATTR 'hours' * 1.5;`

### 8.6.3 Setting Up Some Variables

We are going to be printing the lines of the box around the invoice items, and will find it convenient to have these long character strings stored as variables so we do not have to continually repeat them. We will also store a picture format for printing dates as a variable which we will use several times.

Edit `script.4` and add the following lines. We have written the `LET` statements for drawing the box on two lines, to keep the long character strings from running over the edge of this manual page.

    LET boxtop =
    "____________________________________________________________";
    LET boxline =
    "|       |                                         |        |";
    LET boxmid =
    "|-------|-----------------------------------------|--------|";
    LET boxbase =
    "|_______|_________________________________________|________|";
    LET boxhead =
    "| Date  | Job Description       Mechanic Hours Rate| Total |";
    LET datepic = "AAAAAAAAA dd, yyyy";

### 8.6.4 Header and Footer

Let us now set up the header and footer. They are both more complex than the simple header and footer we have used before, but only because they have more information in them. Edit `script.4` and add the following at the end:

    HEADER
       NEWLINE 4;
       PRINT "Universal Automotive Services",
          COL 42, "FOR THE BEST IN AUTO SERVICE",
         NEWLINE;
       PRINT "1757 El Camino Real", COL 54,
          "(415) 957-8203", NEWLINE;
       PRINT "Bayville, CA 92304", NEWLINE 2;
       PRINT COL 30, "INVOICE", NEWLINE, COL 30,
          "-------", NEWLINE;
    END;

    FOOTER
       PRINT "Invoice due on receipt.", NEWLINE;
       PRINT "Please make check payable to: ",
          "Universal Automotive Services", NEWLINE 2;
       PRINT "- - - - - - - - - - - - - - - - - -",
          " - - - - - - -", NEWLINE 2;
       PRINT "RETURN THIS STUB WITH YOUR PAYMENT",
          NEWLINE 2;
       PRINT "Invoice No.: 92-0", ATTR 'job no.',
          COL 41, "Total Amount: ", grandtotal
          PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
       PRINT COL 25, " _____________ ", NEWLINE;
       PRINT COL 8, "AMOUNT ENCLOSED", COL 25,
          "|$            |", NEWLINE;
       PRINT COL 25, "|_____________|", NEWLINE 2;
       PRINT "Customer number: ", ATTR 'customer no.',
          COL 41, "Date: ", TODAY PICTURE datepic,
          NEWLINE;
       NEWLINE 3;
    END;

The header really has nothing fancy in it; it just prints out the address on the left, a bit of advertising and a phone number on the right of the page, and the "`INVOICE`" title.

The footer prints the return stub for the invoice, which we assume will be one page long. It uses the attribute job no. to generate the invoice number, and prints the total amount for the invoice (the variable `grandtotal`, which will be calculated in the body of the report). It then draws a little box for the customer to indicate how much he has paid, and finally prints the customer number (so our office staff can credit the payment to the right account), and the date on which the bill was printed. Note that we use the variable for the date picture format which we had set up earlier.

### 8.6.5 Printing the Invoice Box and Totals

Our next step is to set up the `GROUP` statement which prints out the customer's name and address, the invoice date and number, draws the invoice box, and sums up the amount owing. This is quite straightforward except for `address`, which is a `TEXT` data type attribute and must be handled specially.

Edit `script.4` again and add the following at the end:

    FOR EACH GROUP OF ATTR 'job no.'
       PRINT COL 40, "Invoice No.: 92-0",
          ATTR 'job no.', NEWLINE;
       PRINT COL 40, "Invoice Date: ", TODAY
          PICTURE datepic, NEWLINE 2;
       PRINT "Customer: ", name, COL 54, "Page: ",
          PAGENUMBER, NEWLINE;
       PRINT boxtop, NEWLINE, boxline, NEWLINE,
          boxhead, NEWLINE, boxmid, NEWLINE,
          boxline, NEWLINE;
       PRINT boxline, NEWLINE, boxmid, NEWLINE;
       LET grandtotal = SUM OF total;
       PRINT "|     |     TOTAL FOR THIS INVOICE        |",
          COL 61, grandtotal PICTURE "G$FFF9V.99",
          " |", NEWLINE;
       PRINT boxbase, NEWLINE 3;
       NEWPAGE;
    END;

Note how setting variables for the box drawing has made this `GROUP` statement far simpler to read than it would have been with all the long box strings in the middle of it.

We encounter a new picture format here: `G$FFF9V.99`. The new part is the `G`, which forces a space to be printed where the `G` occurs for every digit which is not printed where an `F` occurs later in the picture. This provides another way to have a dollar amount of constant width printed, with the dollar sign against the leading digit. For example, `G$FFF9V.99` can also be expressed as `$FFF9V.99 WIDTH 8 RIGHT`.

To print out the address, we need the following statements:

    LET temp = address;
    WHILE (temp != "")
    DO
       PRINT COL 12, temp WIDTH 40 OVERFLOW INTO
       temp, NEWLINE;
    END;
    NEWLINE;

This construction is known as a `WHILE` loop.

The phrase "`temp != ""`" following the keyword `WHILE` is known as a condition; if the test it makes is true, all statements between the `DO` and the `END` are executed. Empress Report Writer then returns to the `WHILE` line, and checks the condition again. As soon as the condition is false, Empress Report Writer skips to the next statement after the `END` and continues with the rest of the report.

Conditions may test for equality (=), inequality (!=), greater than (>), greater than or equal to (>=), less than (<), or less than or equal to (<=).

Here, we first copy the value of `address` to a temporary variable, appropriately named `temp`. (This is done in case we need the address again, because the process of printing out the value will end up with `temp` as an empty character string.) Then we test to see if there is anything in `temp` to print. If there is, we print 40 characters of it and a new line command, and then replace the previous contents of `temp` with whatever remains beyond the first 40 characters. This is done by the phrase `OVERFLOW INTO temp`. When there is nothing left of `temp` to print, the loop stops and the `NEWLINE` after its end is printed.

Edit `script.4` again and place the above lines immediately after the line which prints the customer's name.

Let's have a look at how the entire script should appear:

    /* Current Invoices for the Garage */

    PARAMETERS
       DATE          startdate;
       DATE          enddate;
    END;

    LET PAGEWIDTH = 75;
    LET DATABASE = "repairs";

    SELECT name, ATTR 'customer no.', address, 'hours'.date,
       ATTR 'job no.', description, 'hours'.number,
       ATTR 'hours', rate
    FROM customers, 'hours', rates, jobs
    WHERE customers.number = jobs.'customer no.'
       AND jobs.'job no.' = 'hours'.'job no.'
       AND 'hours'.code = rates.code
       AND name MATCH "*Wong*|*Watson*"
       AND jobs.date RANGE var.startdate TO var.enddate
    SORT BY ATTR 'job no.';

    DEFINE total = rate * ATTR 'hours' * 1.5;
    LET boxtop =
    "____________________________________________________________";
    LET boxline =
    "|       |                                         |        |";
    LET boxmid =
    "|-------|-----------------------------------------|--------|";
    LET boxbase =
    "|_______|_________________________________________|________|";
    LET boxhead =
    "| Date  | Job Description      Mechanic Hours Rate|  Total |";
    LET DATEPIC = "AAAAAAAAA dd, yyyy";

    HEADER
       NEWLINE 4;
       PRINT "Universal Automotive Services",
          COL 42, "FOR THE BEST IN AUTO SERVICE",
          NEWLINE;
       PRINT "1757 El Camino Real", COL 54, "(415) 957-8203", NEWLINE;
       PRINT "Bayville, CA 92304", NEWLINE 2;
       PRINT COL 30, "INVOICE", NEWLINE, COL 30, "--------", NEWLINE;
       END;

    FOOTER
       PRINT "Invoice due on receipt.", NEWLINE;
       PRINT "Please make check payable to: ",
          "Universal Automotive Services",
          NEWLINE 2;
       PRINT "- - - - - - - - - - - - - - - - - -",
          " - - - - - - -", NEWLINE 2;
       PRINT "RETURN THIS STUB WITH YOUR PAYMENT",
          NEWLINE 2;
       PRINT "Invoice No.: 92-0", ATTR 'job no.',
          COL 41, "Total Amount: " grandtotal
          PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
       PRINT COL 25, "_____________ ", NEWLINE;
       PRINT COL 8, "AMOUNT ENCLOSED", COL 25,
          "|$           |", NEWLINE;
       PRINT COL 25, "|_____________|", NEWLINE 2;
       PRINT "Customer number: ", ATTR 'customer no.',
          COL 41, "Date: ", TODAY PICTURE datepic,
          NEWLINE;
       NEWLINE 3;
    END;

    FOR EACH GROUP OF ATTR 'job no.'
       PRINT COL 40, "Invoice No.: 92-0", ATTR
          'job no.', NEWLINE;
       PRINT COL 40, "Invoice Date: ", TODAY PICTURE
          datepic, NEWLINE 2;
       PRINT "Customer: ", name, COL 54, "Page: ",
          PAGENUMBER, NEWLINE;
       LET temp = address;
       WHILE (temp != "")
       DO
          PRINT COL 12, temp WIDTH 40 OVERFLOW INTO temp,
             NEWLINE;
       END;
       PRINT boxtop, NEWLINE, boxline, NEWLINE,
          boxhead, NEWLINE, boxmid,NEWLINE,
          boxline, NEWLINE;
       PRINT boxline, NEWLINE, boxmid, NEWLINE;
       LET grandtotal = SUM OF total;
       PRINT "|     | TOTAL FOR THIS INVOICE      |",
          COL 61, grandtotal PICTURE
          "G$FFF9V.99", " |", NEWLINE
       PRINT boxbase, NEWLINE 3;
       NEWPAGE;
       LET PAGENUMBER = 1;
    END;

Run the script, using:

`emprepwr script.4 19920228 19920305`

The report will be much longer than the screen. Remember to pipe the output through a paging program or use terminal control flow.

You should see:

### 8.6.6 Printing the Detailed Job Breakdown

We are getting fairly close to the desired results now. All we need is to print the various details for each job, and we're there. For this, we add a `RECORD` statement nested within the `GROUP` statement, just before we print the total and the box base.

Edit `script.4` and insert the `RECORD` statement as follows:

    FOR EACH GROUP OF ATTR 'job no.'
       PRINT COL 40, "Invoice No.: 92-0", ATTR 'job no.',
          NEWLINE;
       PRINT COL 40, "Invoice Date: ", TODAY
          PICTURE datepic, NEWLINE 2;
       PRINT "Customer: ", name, COL 54, "Page: ",
          PAGENUMBER, NEWLINE;
       LET temp = address;
       WHILE (temp != "")
       DO
          PRINT COL 12, temp WIDTH 40 OVERFLOW
             INTO temp, NEWLINE;
       END;
       NEWLINE;
       PRINT boxtop, NEWLINE, boxline, NEWLINE,
          boxhead, NEWLINE, boxmid, NEWLINE,
          boxline, NEWLINE;

       FOR EACH RECORD
       PRINT "| ", 'hours'.date PICTURE "MM/DD",
          COL 8, " | ", description,
          COL 34, 'hours'.number WIDTH 8 CENTER,
          COL 44, ATTR 'hours' PICTURE "GF9V.9",
          COL 49, rate * 1.5 PICTURE "G$FF9V.99",
          "/hr |",
          COL 61, total PICTURE "G$FFF9V.99",
          " |", NEWLINE;
    END;

       PRINT boxline, NEWLINE, boxmid, NEWLINE;
       LET grandtotal = SUM OF total;
       PRINT "|     |     TOTAL FOR THIS INVOICE   ",]
          "     |", COL 61, grandtotal PICTURE
          "G$FFF9V.99", "  |", NEWLINE;
       PRINT boxbase, NEWLINE 3;
       NEWPAGE;
       LET PAGENUMBER = 1;
    END;

Run the report. You should see:

### 8.6.7 Improving the Invoices

The invoices are now adequate as they stand, but there are two areas in which they can be improved:

1. The first is a question of layout and aesthetics. The invoice box varies in length depending on the number of detailed items there are for any one job. If there is only one item, as for the second invoice, the box is quite small and looks peculiar. It would be better to have a box of constant length, which looks much more like a standard invoice.

2. The second improvement is partially linked to the first. What if a job has a great many items? Currently, the script attempts the following: keep on printing items until the footer should be printed, print the footer, start a new page, print its header, and continue the items. If this worked, it would look quite unprofessional. What actually happens, however, is that an error message is printed for the first footer, because the grand total for the invoice which the footer prints has not been calculated yet, and the report stops.

What should happen is that the invoice box stops at a given length, with a message that the invoice is continued on the next page, and a proper base to the box is printed. The next page should have the customer information repeated, and a proper top and title for the invoice box. Finally, there should be only one return stub printed per invoice; the first page should just have a plain footer with a bit of advertising in it.

### 8.6.8 Filling Out the Invoice Box

The invoice box is filled out to a constant length by repeating a series of statements which check how far down the page the report has got, and print lines of box sides until a pre-set line number is reached.

This is accomplished by another `WHILE` loop, as follows:

    LET boxend = 39;
    WHILE LINENUMBER < boxend
    DO
       PRINT boxline, NEWLINE;
    END;

`LINENUMBER` is another Empress Report Writer system variable, which always holds the number of the current report line. It is incremented automatically by Empress Report Writer, so we do not need to do so. `boxend` is a variable holding the line number at which the box should end. We are using a variable rather than just making this a number so that it is obvious what this number means. We will also use it later when we fix the problem of long invoices not working. It is always a good idea to assign numbers like these to variables, so you only have to change the number in one place if you decide later you would like a different length for the box.

Edit `script.4` and put the while loop right after the end of the `RECORD` statement. Put the "`LET boxend = 39`" line up at the beginning of the script with the other `LET` statements; it will be easy to find and change later. Run the script again and note the nice even boxes.

### 8.6.9 Dealing With Long Invoices

If the invoice is very long (that is, the list of items has got to a given line number), the report should print a message saying the invoice continues on the next page, draw the bottom of the box, put a plain footer on the current page, start a new page, reprint the name, address, invoice date, and invoice number, and then draw a new top for the box.

All this is done by the following statements:

    IF LINENUMBER > boxend
       THEN
       USE FOOTER TWO;
       PRINT boxline, NEWLINE;
       PRINT "|     |   CONTINUED ON NEXT PAGE ",
          "     |          |", NEWLINE;
       PRINT boxbase;
       NEWPAGE;
       PRINT COL 40, "Invoice No.: 92-0", ATTR
          'job no.', NEWLINE;
       PRINT COL 40, "Invoice Date: ", enddate
          PICTURE datepic, NEWLINE 2;
       PRINT "Customer: ", name, COL 54, "Page: ",
          PAGENUMBER, NEWLINE;
       LET temp = address;
       WHILE (temp != "")
          DO
          PRINT COL 12, temp WIDTH 40 OVERFLOW
             INTO temp, NEWLINE;
       END;
       NEWLINE;
       PRINT boxtop, NEWLINE, boxline, NEWLINE,
          boxhead, NEWLINE, boxmid, NEWLINE,
          boxline, NEWLINE;
    END;

`IF` followed by a condition allows the statements between the `THEN` and `END` to be executed if the condition is true. Unlike a while loop, the statements are only executed once. This provides a branch point in a report script. Before the `END` of the `IF` statement you may place the keyword ELSE, followed by one or more statements. These statements are to be executed if the condition is false. For example, the following:

    IF amount < 0
       THEN
       PRINT "Account Overdrawn";
    ELSE
       PRINT "Amount is: ", amount;
    END;

will print the "`Account Overdrawn`" message if `amount` is zero or negative, and will print the amount if amount is positive.

If you only want something to happen if the condition is false, you may simply write:

    IF condition
       THEN
       ;
    ELSE
       statements;
    END;

Edit `script.4` and put the `IF` statement just before the end of the `RECORD` statement.

### 8.6.10 Using Different Headers and Footers

We now define the footer used to replace the return stub on all but the last page of very long invoices. We have invoked a new footer with the rather cryptic statement "`USE FOOTER two`" straight after the `THEN` of the `IF` statement; this "`FOOTER two`" must now be defined.

You may have several different headers and footers, labeled as "`HEADER one`", "`FOOTER rightpage`", "`FOOTER leftpage`", etc., and use specific ones through your report by statements saying "`USE HEADER one`", "`USE FOOTER rightpage`", and so on. A request to use a new header takes effect on the next page; a request to use a new footer takes effect on the current page (provided there is enough left of the current page to fit the new footer).

Edit `script.4` and change the line beginning the footer to:

`FOOTER one`

Right after the end of this footer add the following:

    FOOTER two
       NEWLINE 2;
       PRINT COL 3, "FOR THE BEST IN PARTS AND SERVICE ",
          "UNIVERSAL AUTOMOTIVE SERVICES", NEWLINE 2;
       PRINT COL 15, "1757 El Camino Real, Bayville,",
          "CA 92304", NEWLINE;
       PRINT COL 27, "(415) 957-8203", NEWLINE 3;
    END;

Now, just before the final `NEWPAGE` in the script, add this line:

`USE FOOTER one;`

Now the return stub footer will be used just before the end of an invoice, and the plain footer will be used on the first pages of any long invoices.

Let us simulate the effect of having a very long invoice by reducing the length of the invoice box. Edit `script.4` again and change the line:

`LET boxend = 39`

to:

`LET boxend = 27;`

Your script is now:

    /* Current Invoices for the Garage */

    PARAMETERS
       DATE          startdate;
       DATE          enddate;
    END;

    LET PAGEWIDTH = 75;
    LET DATABASE = "repairs";
    LET boxend = 27

    SELECT name, ATTR 'customer no.', address, date,
       ATTR 'job no.', description, number, ATTR
       'hours', rate
       FROM customers, 'hours', rates, jobs
       WHERE customers.number = jobs.'customer no.'
       AND jobs.'job no.' = 'hours'.'job no.'
       AND 'hours'.code = rates.code
       and name MATCH "*Wong*|*Watson*"
       AND jobs.date RANGE var.startdate TO var.enddate
       SORT BY ATTR 'job no.';
    DEFINE total = rate * ATTR 'hours' * 1.5;

    LET boxtop =
    "____________________________________________________________";
    LET boxline =
    "|       |                                         |        |";
    LET boxmid =
    "|-------|-----------------------------------------|--------|";
    LET boxbase =
    "|_______|_________________________________________|________|";
    LET boxhead =
    "| Date  | Job Description      Mechanic Hours Rate|  Total |";

    LET datepic = "AAAAAAAAA dd, yyyy";
    HEADER
      NEWLINE 4;
          PRINT "Universal Automotive Services",
             COL 42, "FOR THE BEST IN AUTO SERVICE", NEWLINE;
          PRINT "1757 El Camino Real", COL 54,
             "(415) 957-8203", NEWLINE;
             PRINT "Bayville, CA 92304", NEWLINE 2;
          PRINT COL 30, "INVOICE", NEWLINE,
             COL 30, "-------", NEWLINE;
    END;

    FOOTER one
       PRINT "Invoice due on receipt.", NEWLINE;
       PRINT "Please make check payable to: ",
          "Universal Automotive Services",
          NEWLINE 2;
       PRINT "- - - - - - - - - - - - - - - - - -",
          " - - - - - - -", NEWLINE 2;
       PRINT "RETURN THIS STUB WITH YOUR PAYMENT", NEWLINE 2;
       PRINT "Invoice No.: 92-0", ATTR 'job no.',
          COL 41, "Total Amount: ", grandtotal
          PICTURE "$FFF9V.99" WIDTH 8 RIGHT, NEWLINE;
       PRINT COL 25, "_____________", NEWLINE;
       PRINT COL 8, "AMOUNT ENCLOSED", COL 25,
          "|$           |",NEWLINE;
       PRINT COL 25, "|_____________|", NEWLINE 2;
       PRINT "Customer number: ", ATTR 'customer no.',
          COL 41, "Date: ", TODAY PICTURE datepic,
          NEWLINE 3;
    END;

    FOOTER two
       NEWLINE 2;
       PRINT COL 3, "FOR THE BEST IN PARTS AND SERVICE",
          " UNIVERSAL AUTOMOTIVE SERVICES", NEWLINE 2;
       PRINT COL 15, "1757 El Camino Real, Bayville, CA 92304",
          NEWLINE;
       PRINT COL 27, "(415) 957-8203", NEWLINE 3;
    END;
    FOR EACH GROUP OF ATTR 'job no.'
       PRINT COL 40, "Invoice No.: 92-0", ATTR 'job no.',
          NEWLINE;
       PRINT COL 40, "Invoice Date: ", TODAY
          PICTURE datepic, NEWLINE 2;
       PRINT "Customer: ", name, COL 54, "Page: ",
          PAGENUMBER, NEWLINE;
       LET temp = address;
       WHILE (temp != "")
       DO
          PRINT COL 12, temp WIDTH 40 OVERFLOW
             INTO temp, NEWLINE;
       END;
       NEWLINE;
       PRINT boxtop, NEWLINE, boxline, NEWLINE,
          boxhead, NEWLINE, boxmid, NEWLINE,
          boxline, NEWLINE;

    FOR EACH RECORD
       PRINT "| ", 'hours'.date PICTURE "MM/DD",
          COL 8, " | ", description,
          COL 34, 'hours'.number WIDTH 8 CENTER,
          COL 44, ATTR 'hours' PICTURE "GF9V.9",
          COL 49, rate * 1.5 PICTURE "G$FF9V.99", "/hr |",
          COL 61, total PICTURE "G$FFF9V.99", " |", NEWLINE;
       IF LINENUMBER > boxend
       THEN
       USE FOOTER two;
       PRINT boxline, NEWLINE;
       PRINT "|     |          CONTINUED ON NEXT PAGE
          "    |          |", NEWLINE;
       PRINT boxbase;
       NEWPAGE;
       PRINT COL 40, "Invoice No.: 92-0", ATTR 'job no.',
          NEWLINE;
       PRINT COL 40, "Invoice Date: ", enddate
          PICTURE datepic, NEWLINE 2;
       PRINT "Customer: ", name, COL 54, "Page: ",
          PAGENUMBER, NEWLINE;
       LET temp = address;
       WHILE (temp != "")
       DO
          PRINT COL 12, temp WIDTH 40
             OVERFLOW INTO temp, NEWLINE;
       END;
       NEWLINE;
          PRINT boxtop, NEWLINE, boxline, NEWLINE,
             boxhead, NEWLINE,boxmid,
             NEWLINE, boxline, NEWLINE;
          END;
       END;
       WHILE LINENUMBER < boxend
       DO
          PRINT boxline, NEWLINE;
       END;
       PRINT boxline, NEWLINE, boxmid, NEWLINE;
       LET grandtotal = SUM OF total;
       PRINT "|          |          "
          "TOTAL FOR THIS INVOICE ",
          "     |", COL 61, grandtotal PICTURE
          "G$FFF9V.99", " |", NEWLINE;
       PRINT boxbase, NEWLINE 3;
       USE FOOTER one;
       NEWPAGE;
       LET PAGENUMBER = 1;
    END;

Run the script. You should see:

We now have a report script which behaves gracefully when the unusual occurs, an important point which is often overlooked.

This concludes the chapter on Empress Report Writer examples. You should now have enough knowledge of Empress Report Writer to produce a wide variety of reports. We encourage you to consult the reference chapter, particularly the sections on line, page and column controls, for details which have not been illustrated in these examples.

## 8.7 Features Covered

To summarize, we have covered the following features of Empress Report Writer in this chapter:

- Printing output

- Using picture formats

- Setting page lengths

- Calculating values

- Using system variables

- Assigning values

- Setting column widths

- Running titles (headers)

- Centering and justifying output

- Running footers

- Printing blank lines

- Printing the current date

- Selecting data

- Printing page numbers

- Using attribute values

- Generalizing reports

- Defining derived attributes

- Using parameters

- Operations on all records

- Control Flow

- Operations on groups of records

- Nested Group

- Using ordinary variables