REPORT FORM - THE dBASE REPORT GENERATOR

                 REPORT FORM - THE dBASE REPORT GENERATOR

                 ========================================

                                Part One

Many people are not aware of how useful the report generator built into

dBASE can be because REPORT FORM is arguably the most "under documented"

feature of dBASE.  You would be surprised what can be done with REPORT FORM

- once you know the tricks.  It's really a shame that this valuable feature

is not better documented.  I've even met professionals who were manually

coding reports because they were not aware of all of REPORT FORM's capa-

bilities.

Although the screens presented when creating a report are very different in

dBASE III and dBASE III PLUS, the report generator actually works in very

much the same way in each version of dBASE.  .FRM files created in dBASE

III can be used in III PLUS with little or no problem although some

features such as word wrapping within columns work slightly differently.  

For those of you using FoxBASE+, you will find the dBASE III PLUS and

FoxBASE+ report generators work the same, except FoxBASE+ leaves out the

bugs and quirks.  Clipper users can import REPORT FORM files (.FRM) created

in dBASE into Clipper with no problems.

In dBASE III PLUS, when creating a report which has complex or long

statements for the contents of a column I usually return to dBASE III and

use that version of REPORT FORM to begin defining the report, then move the

.FRM file over to dBASE III PLUS for completion.  This is because I find

the scrolling display of the column contents in dBASE III PLUS makes it

very difficult to keep track of what I have already entered and almost

impossible to debug.  (FoxBASE+ takes the more sensible approach of

providing an on-screen box which shows the entire statement, similar to

dBASE III.)

In dBASE III PLUS the advanced features such as IIF() and TRANSFORM() can

be used within the report generator and, as discussed later, better

controls for printing memo fields are available.

I've found an irritating quirk which appears with attempts to erase items

in the column contents of REPORT FORM in dBASE III PLUS with the backspace

key.  Using the backspace key should erase the character immediately

preceding the cursor.  However, when attempting to erase multiple charac-

ters behind the cursor with the backspace key held down continually, the

cursor will sometimes simply move to the left and not erase the characters. 

It is necessary to then use the delete key to erase material which is now

at and to the right of the cursor.  This happens in both versions 1.0 and

1.1 of dBASE III PLUS.

The column contents block will take statements of up to 254 characters

(including blank spaces).  It is not necessary to use blank spaces to

separate concatenations with the + sign, by the way.  Both of the following

statements will work equally well in REPORT FORM:

        TRIM(FNAME) + " " + LNAME + ";" + DEPT

                          

                         or

        TRIM(FNAME)+" "+LNAME+";"+DEPT

There are several other problems which occur with REPORT FORM in dBASE III

PLUS which are not present in dBASE III.  If you move the highlight or

light bar to the "Column" item at the top of the REPORT FORM menu (which

also automatically highlights the "Contents" item in the box underneath)

and inadvertently press RETURN [ENTER] twice, an empty column is created in

the report form in III PLUS.  

At this point in time, dBASE III PLUS does not trap this as an error but,

instead, places a question mark in the box which provides an outline of the

columns in the report.  (According to the Ashton-Tate manual, the ?

represents a memo field.)

When you attempt to display or print the report the error message:

        Syntax error in field expression

shows up.  Trying to figure out what is going on without a little extra

help can cause hair to go gray overnight.  Unhappily, this problem was not

cleared up in release 1.1 of dBASE III PLUS.

MULTIPLE DATA LINES

===================

The most frequent question I receive about REPORT FORM is how to obtain

multiple data lines in a column.  This can be done by using the semi-colon 

";" (with the quotes).

The following statement:

   TRIM(FNAME) + " " + LNAME + ";" + STREET + ";" + 

      TRIM(CITY) + " " + STATE + " " + ZIP

would print the individual's name and address on separate lines:

        Mary Smith

        201 Main Street

        Metropolis, NY  10021

INSERTING A NEW COLUMN

======================

In dBASE III the Ashton-Tate manual did not document the fact that REPORT

FORM has the ability to insert a new column during the CREATE REPORT or

MODIFY REPORT routines.  CONTROL/N will insert a new column.  (The CONTROL-

/U command to delete a column is documented and appears in the menu.)  This

feature is, of course, also available in dBASE III PLUS and is better

documented.

DISAPPEARING LINES OF DATA

==========================

It is very common to find the last line(s) of a report not being printed -

and then sometimes mysteriously appearing when another report is printed. 

To cure the problem in dBASE III issue an EJECT after the REPORT FORM

command.  In dBASE III PLUS setting the "Page eject after printing" option

to Yes will accomplish the same thing.

The problem occurs because someone at Ashton-Tate decided dBASE printing

should use a "reverse Polish" methodology.   Most printers have buffers

which store the text prior to printing.  (The size of the buffer varies

with the printer.)  Most of these printer buffers flush text to the printer

after receiving a control code such as a carriage return or line feed, or

sensing they are full.  

dBASE works the other way around and sends its carriage return/line feed

control code at the beginning of each new line rather than at the end. 

Therefore, a line of data could remain in your printer buffer.  The EJECT

command provides the control code which will flush any remaining data from

the printer buffer for printing (prior to the page eject).

DEFINING THE CONTENTS OF A COLUMN

=================================

dBASE will not permit direct mixing of data types in the REPORT FORM column

or field contents.  Dates and numeric data must be converted to character

strings to be used with one another or with character type data.  Memo

fields must be placed in their own separate column.

Memory variables may be used in the field contents.  However, the memory

variable must be created before CREATing the REPORT FORM or MODIFYing the

REPORT FORM.  Otherwise, error messages on the field contents will result

and dBASE will not let you proceed.

Assume you have a database with a field called ADATE which contains data on

the day a patient was admitted to the hospital.  You now want to know how

many days have elapsed since the admission (i.e., how many days each

patient has been hospitalized).  First, create a memvar which contains

today's date:

        DDAY = DATE()

Now you can CREATE REPORT or MODIFY REPORT and use the memory variable in

the report.

The heading for the column or field contents in the REPORT FORM would be

DAYS HOSPITALIZED and the statement in the Field Contents block of the

REPORT FORM would be:

        DDAY - ADAY

dBASE would subtract the admission date from today's date and provide an

integer representing the number of days.

        

The column or field contents can be the result of various mathematical or

date calculations rather than data currently contained in fields of the

database.

For example, assume you have a personnel database which provides informa-

tion on date of hire (DHIRE), the current annualized salary (CSALARY) for

each employee and their salary at date of hire (HSALARY).  A report is now

required on the dollar amount of salary increase from date of hire, the

percentage of the increase; and the number of years employed.  To simplify

matters, assume each item will be a separate column or field in the REPORT

FORM.  A memory variable called DDAY has been created for today's date.

To obtain the dollar amount of the salary increase the statement in the

field contents would be:

        CSALARY - HSALARY

Field contents to obtain the percentage of salary increase would be:

        STR((((CSALARY-HSALARY)/HSALARY)*100),3) + " %"

Notice that it is possible to do mathematical calculations within the

nesting or parentheses and then convert the result to a character string

with STR() so it can be used with other character type data.

The statement in the field contents to obtain the number of years employed

would be:

        YEAR(DDAY,4) - YEAR(HDAY,4)

We'll look at more ways to use REPORT FORM next month.