Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN9002.ZIP
Filename : IIFY_REP.TXT

Making IIFy Reports

Joel Saltzman

This article hopes to show you how to get more out of one of the most
outstanding features of dBASE IV Ä the report generator. The
immediate IF function, IIF(), is the vehicle by which many useful and
interesting ideas can come to life in a readable form as numbers and
text. We will begin by discussing the calculated field, the
environment within which the IIF() performs its many acts.

Fields in a Report

Most of the field types in the report form generator supply ready-made
information. When you select the Add Field option from the Fields
menu, a four column list appears. The first column has a list of
available fields in the current database or view. The predefined
column supplies report-wide information such as page number and report
date. Totals and other aggregate functions on groups of data are
included under the summary column. The calculated field column is the
only place where the user can use his creativity to manipulate the
data to produce customized results, signified by the icon
which rests there.

If you already have a report created to which you'd like to apply this
example, bypass the instructions for creating a report and proceed to
modify. Remember to place a database or query in use before
attempting to create or modify a report. dBASE IV will remind you to
do so if you forget.

Calculated Fields

In order to create a calculated field, select under the
Reports panel in the Control Center. After pressing Escape to exit
the Layout menu, move the cursor to where you'd like to place the
field. Then press F5, a short-cut alternative to the Add field or
Modify field option of the Fields menu. Then, select under
the Calculated column.

The name and description are optional Ä only necessary for documenting
your report or for referencing this newly created field in another
calculated or summary field. The Expression prompt contains the IIF()
statement that will actually determine what is printed and how. The
template determines the type and size of the field to be printed,
which is adjustable. The picture functions can give a more formatted
appearance to the printed output. The hidden selection will prevent
the display of intermediate values, if you so choose. We will
concentrate primarily on the expression, as that is where the IIF()
will perform its function.

Our star, the IIF()

Allow me to introduce IIF(), also known as the immediate IF function,
a dBASE IV function that can be used in any program as well as in the
Report generator. Within the IIF() function, different elements may
be specified based on the result of a stated condition. Here is an
example of an immediate IF:

IIF(Age >= 18, "May Vote", "Too Young")

In this example, the part before the first comma (AGE >= 18) is the
condition of the IIF(). The part between the commas ("May Vote")
tells the report what to print if the condition is true, that is, if
the age is greater than or equal to eighteen. The part after the
comma ("Too Young") is printed if the condition shows to be false, if
the age is less than eighteen. All IIF() statements share this basic
format: IIF(condition, expression if true, expression if false). We
will now present some wonderful uses of this function.

Two Line Addresses and Blank Address Line Suppression

Within form letters, and some times in reports, you may need to print
names and addresses, one on top of the other. In a mailing list
database, there will usually be a field for the second line of
address, company or suite. Quite often this field may be blank. When
printed on the report, it is desirable to print only those lines that
contain actual data, without any intervening empty lines. The IIF()
can help accomplish the blank line suppression. Assuming you will
always have data in Address1 and the fields list is as follows:

Name Character 30 (First Name and Last Name)
Address1 Character 30 (First Line of an Address)
Address2 Character 30 (Second Line of an Address)
CitStZip Character 30 (City, State, and Zip )

Put the Name field where you would like the name to appear. Skip a
line, and then place the CitStZip field after the skipped line. Now,
return to the beginning of the skipped line. Create a calculated
field by pressing F5. Type Address for the name of the field. For
the expression, put in

Address1 + IIF(Address2 = "", " ", ";" + Address2)

The above expression would check for data in Address2. If the field
is equal to null (""), we would simply print a space. If, however,
there is information in the field, we send a semicolon, which is
interpreted by the Report Generator as a carriage return/line feed.
It wraps to the beginning of the next line and then prints the
Address2.

In the template field, enter the letter X 61 times. You need this
many Xs to accommodate the 30 characters of Address1, the semicolon,
and the 30 characters when Address2 is actually printed. Here's a
tip: Use the status bar as a ruler. The 61st X in the template would
be positioned 4 columns over from the colon (:) in the word File in
the last part of the status bar. The vertical bar separators are at
positions 9, 34 and 52.

After you press Enter on the template line, press F9 to see the template all the way across the
bottom of the screen, so you can see more of the template at once. Yes, you will have to count
them, or your Address2 may get cut off.

In the picture functions field, press Enter followed by a semicolon,
to turn the wrap semicolons function on. This will tell dBASE to
interpret the semicolon as a command to go to the next line.
Press CTRL-END, an alternative shortcut for saving, equivalent to the
Save and Exit menu option. When you print the report, no blank lines
will appear in your address.

NOTE
More information about blank line suppression can be found in the
December 1988 issue of TechNotes/dBASE IV. See "Suppressing Blank
Lines", page 5 and "More on Suppressing Blank Lines", page 11.

Selective Counting

A user may have a need to count occurrences of a field value without
sorting the file by that field. For example, one may want to know how
many people in a particular election district have a birthday before a
specific cutoff date Ä to know who is eligible to vote, for example.
The solution is to create a hidden field with our old "functional"
friend, the IIF(). The summary of this hidden field is then displayed
in the group summary band.
Here are some of the fields in the voters database:

Name Character 30 (Resident's Name)
BirthDate Date 8 (Resident's Birth Date)

In the detail band, the Name field would be displayed. With the
cursor anywhere within the Detail Band (except where the Name field is
displayed), press the F5 key to add a field. Choose to create a
calculated field from the second panel. For the name of the field,
type in Voters. For the expression, enter the following:

IIF(BirthDate <= {11/03/74}, 1, 0)

BIRTHDATE <= {11/03/74} represents all those birthdays on or before
November 3, 1974; all those eligible to vote in the 1992 presidential
election. The 1 signifies that the person will be counted as one old
enough to vote. If a person will still be too young to vote, this
field will be set to 0.

Place a single 9 in the template field, as the value of this field can
only be 0 or 1. Then type H, to turn the Hidden Field option ON.
This field should be a hidden field since there is no need to display
it on the report. We will use it to keep track of how many voters
there are.

In the report summary band, type the words "Total of Eligible Voters:"
where you would like to display the number of eligible voters. After
this text, press F5 to add a sum field in the Summary column.
Highlight the option Field to summarize on and press Enter. Choose
the calculated field VOTERS that we created in the detail band. Make
the template 9999, so that we can accommodate up to 9,999 eligible
voters.

This summary field works because it accumulates 1 for each record that
has the appropriate age, while adding 0 (not adding anything) for
those people who do not qualify.

Selective Averaging

There may be certain questions that are not answered by all
individuals taking a survey. If a response in a survey consisted of
the numbers 1 through 5, a blank or zero response would unfairly tilt
the average down. Therefore, it would be worthwhile to eliminate all
blank entries from the average of responses to that question. The
IIF() can be used to keep track of the number of qualifying scores,
and produce the correct denominator for the average of responses.
This is a subset of the survey database:

SurveyNo Numeric 5 (Survey Number)
Ques24 Numeric 1 (Question 24 Response)

As the Ques24 field is numeric, it will have a value of 0 if nothing
was entered into it. If that field equals 0, it would look better to
display a dash for that question to signify that it was not answered.
For the display of the response to question 24 in the detail band, use
the following IIF() in the expression of a calculated field:

IIF(Ques24 = 0, "-", Ques24)

This expression says that if Ques24 was not answered, display a dash;
otherwise display the value of Ques24. The template on this field
should be one X, so that either a dash or the number in Ques24 can be
shown.

Remember, a numeric field with no data entry will still be interpreted
as zero. So, if a need exists to differentiate between a blank
(non-entered) field and a zero filled field, use Character type rather
than Numeric for a statistical field such as the one in this example,
then employ the VAL function to convert a number in a character field
so it can be used arithmetically.

We will now create a hidden calculated field in the detail band,
similar to the Voters field in the previous example. The name of this
field should be Ques24Cnt, representing the count of actual
responses. The IIF() in the expression looks like this:

IIF(Ques24 > 0, 1, 0)

If Ques24 has a value, then this response should be included in the
selective average, giving the Ques24Cnt field a 1. Ques24Cnt will get
a 0 for this record if the response to question 24 is zero or blank.
Type H to suppress display or printing of the field, as shown by the
YES near the Hidden Field option.

In the Report Summary Band, add a hidden summary field to add up all
of the responses for question 24. Press F5 to add a summary field
with the SUM operation. As this field will be used in another
calculation, the name will be Ques24Tot. The field to summarize on
should be the Ques24 field.

Next, add another hidden summary field to count the number of
qualifying responses to question 24. Again, press F5 to add a summary
field with SUM as the selected operation. The name for this field
will be Ques24TotC, the total count of valid responses to the
question. The Field to summarize on will be the field we created in
the detail band, Ques24Cnt, which as we stated before, is also hidden.
Finally, place the cursor where you would like the selective average
of the responses of question 24 to appear. Then press F5 to add a
calculated field. Give this calculated field a name, if you like.
For the expression, the following IIF() is utilized:

IIF(Ques24TotC = 0, 0, Ques24Tot/Ques24TotC)

The IIF() is used here to protect against division by 0, a
mathematical no-no. If the number of qualifying responses is 0, a 0
will be displayed. Otherwise, the average of valid responses will be
accurately displayed.

Debit and Credit Printing

Now for a real accounting application of the IIF(). There may be
times when a field should print in different locations based on a
certain condition. Debits and credits on a general ledger are a
perfect example. An IIF() condition will be put on both the debit and
credit fields, with a blank displayed in the debit column if the
record represents a credit, and vice versa. Here is a simple general
ledger record structure:

AcctNo Character 9 (Account )
TransCode Character 1 (Transaction
D - Debit
C - Credit)
Amount Numeric 7,2 (Transaction Amount)

For our report, within the detail band, debits will display at column
30, with credits at column 45 across the line. First, let's work on
the debit amount. Place the cursor at column 30. Then press F5 to
create a calculated field. Name the field Debit. Within the
expression, include the following:

IIF(TransCode = "D", Amount, 0)

If the transaction equals "D", representing a debit, display the
transaction amount, otherwise, the amount must be a credit. In the
picture functions, select blank if zero, so that if this transaction
is not a debit, nothing is displayed.

Follow the same procedure at column 45, except the IIF() expression is
now:

IIF(TransCode = "C", Amount, 0)

You will now have perfectly positioned debits and credits in the
detail band. I will leave summarizing and totalling the debits and
credits in the report summary band as an exercise for you in IIF()
usage.

Three Condition IIF() Statments

A school district has a master list of all students in its elementary,
junior high, and senior high schools. Instead of printing a one
digit signifying what school level the student is in, the school
board director would like to see an alphabetical list of students with
the words "Elementary" or "Junior High" or "Senior High" next to each
of them. Normally, the IIF() function can only operate on one
condition being either true or false. However, the IIF() can be
extended to include more conditions. By nesting IIF()s within each
other, three or more conditions can be represented with one long IIF()
statement. In this school district roster database, there are the
following fields:

StudName Character 25 (Student Name)
LevelCode Character 1 (School Level
E - Elementary
J - Junior High School
S - Senior High School)

On the detail band, we will create a calculated field by pressing F5
and selecting the create option in the calculated column. In the
expression for this complex field is the following versatile nested
IIF():

IIF(LevelCode = "E", "Elementary", IIF(LEVELCODE = "J", "Junior High",
"Senior High")

The condition following the first IIF() checks whether the student is
in the elementary school. If so, then our job is done, and we may
simply display "Elementary" in the school level column. If the
student is not in elementary school, then we must execute the second
nested IIF(). If the is "J", then display "Junior High". If not,
then the student must be in senior high school and "Senior High"
should be printed. Be sure to include enough Xs (11 in this example)
in the template to allow for any of the three possibilities to be
displayed. Also, the above expression does not error check for the
possibility of a blank in this field. It is possible to expand the
given expression with further nesting if this type of error checking
is necessary.

To Print or Not to Print

For our final example, we will show that a combined condition can be
used in an IIF() to determine whether something should be printed or
not. A teacher has a list of grades for her students' test scores for
the year. She would like to place an asterisk (*) by every test score
since December 1, 1989 that exceeded a 94 to show her, at a glance,
who her recent star performers are. We will use the following
database fragment for this example.

Studname Character 25 (Student Name)
TestDate Date 8 (Date of Test)
Score Numeric 3 (Score on Test)

In the detail band of the report, the Studname, TestDate, and Score
will all be displayed. Next, place the cursor two spaces to the left
of the student name. Create a calculated field at this location. For
the expression in the field, use the following IIF():

IIF(Score > 94 .AND. TestDate >= {12/01/89}, "*", " ")

If the student's score is greater than 94 and the test was taken on or
since December 1, 1989, then display an asterisk; otherwise, no
special notation is printed. In order for the asterisk to print, both
the date condition and the score requirement must be met, signified by
the logical operator .AND. in the statement.

Conclusion

I hope this presentation has whetted your appetite for further
advanced use of the dBASE IV Report generator. Many other
applications using this versatile function are possible if one is
creative and resourceful. The IIF() function can also be used in
similar ways in your programs, as well as in Labels and Screen Forms.
IIF() away!