This next section deals with reports for master and detail files. Examples of a master file could be invoice headers, voucher headers, or (from our example) subscribers, or magazines. The detail files for these masters are the line items of invoices or vouchers, or in our example, the subscriptions. These kinds of reports can be done in the style of the previous section; however, it becomes difficult to select certain master file records, for instance, printing products sold during a given time period, or all products for a given customer, or all subscriptions from a certain state (or maybe all subscriptions not in our state).
When a portion of the master file is desired, it becomes easier to start with the master and then look up the detail. This is illustrated with the following example:
Produce a list of all subscriptions where the subscriber is not in the state of Oregon.
The list should be sorted by subscriber.
Standard headings are to be used.
The detail layout is up to you.
Information wanted is the subscriber name, the magazine code, the date started, and the number of issues left.
This report requires the subscriptions file and the subscriber file. We need the following fields from the subscriber file:
subscriber
name
state
The state field is needed to do the selection. The subscriber file is the master file and is the one that is sorted. From the subscription file we need the following fields:
subscriber
magazine
issues
started
By now you should be able to figure out the necessary FILE and FIELD declarations, so they are not presented at this time. Instead, we are starting with the MAIN procedure. The main process for this report is to set the break for page headings, select those subscribers who are not in the state of Oregon, sort them by the subscriber code, then for each one print out the name followed by all of their subscriptions. Notice that it is not necessary to set a break for the subscriber. Since we are reading the subscriber file, for each subscriber selected we can print the name and then print the subscriptions.
Previously, we have mentioned selection. Selection is the process of marking records in a file for later processing. The SELECT statement makes a selection and has a form that looks like:
SELECT FROM file
WHERE condition;
In this form, all of the records in file which satisfy the condition are selected for reading by the next FOR statement which reads file. We want to select records that have a state other than Oregon. Assuming that the state field contains the USPS standard two letter abbreviation in capital letters, the condition we use is:
state NE "OR"
Remember that we also have to sort the records by the subscriber code. This requires that we add the sort clause to the SELECT statement. The end result is the following statement:
SELECT FROM sub
WHERE state NE "OR"
SORTED BY subscriber;
The MAIN procedure then becomes:
MAIN
BEGIN
AT TOP OF PAGE
DO headings;
SELECT FROM sub
WHERE state NE "OR"
SORTED BY subscriber;
FOR EACH sub
DO details;
END
The procedure, detail, prints the subscriber name, and finds all of the subscriptions for the subscriber. For each subscription we want to print the magazine code, the date the subscription was started, and the number of issues left. The detail process should be divided into two procedures: one to handle the subscriber and one to handle a subscription. The first procedure (which we continue to call details) prints the subscriber name, selects the subscriptions for that subscriber, and for each selected subscription calls the second procedure to print the subscription information. The selection in this procedure does not have to be sorted so use the following form of the SELECT statement:
SELECT FROM file
WHERE condition;
We want to select records from the subscription file that have a subscriber code matching the current subscriber (from the subscriber file). This look something like:
SELECT FROM scripts
WHERE subscriber EQ sub.subscriber;
You may have noticed how similar this condition is to the one used for look ups. There is one big difference - with look ups, we were using the FIND statement that only returns a single record. In this last example, we are using the SELECT statement that prepares a subset of records for the FOR statement. To summarize, FIND finds a single record, SELECT finds one or more records (or possibly none).
When all of this is put together, the detail procedure looks like:
PROCEDURE details /* print one subscriber */
BEGIN
PRINT NL, name;
SELECT FROM scripts
WHERE subscriber EQ sub.subscriber;
FOR EACH scripts
DO a_magazine;
END
The procedure a_magazine only needs to print out the magazine code, the date started, and the number of issues left. This procedure might look like:
PROCEDURE a_magazine /* print one subscription */
BEGIN
PRINT TAB (36), magazine, TAB (52),
started, TAB (60), issues, NL;
END
Another reason for having the PRINT statement in a_magazine as a separate procedure is to make future modifications easier and more readable. Suppose at a later time you have to modify this report to show the full title of the magazine instead of just the code? As we have done it, all we would need to do is add the necessary declarations and add a FIND statement to do the magazine look up. As we saw earlier, adding more statements to the FOR statement (as in the detail procedure) makes the statement hard to read. The final program looks like:
/* list subscriptions by subscriber */
FILE scripts IS "script" /* subscriptions file */
FILE sub IS "sub" /* subscriber file */
FIELDS IN scripts ARE
subscriber, magazine,
started, issues;
FIELDS IN sub ARE
subscriber, name,
state;
MAIN
BEGIN
AT TOP OF PAGE
DO headings;
SELECT FROM sub
WHERE state NE "OR"
SORTED BY subscriber;
FOR EACH sub
DO details;
END
PROCEDURE headings /* print page headings */
BEGIN
PRINT TAB (20), "S u b s c r i p t i o n L i s t",
TAB (60), $todays_date:10, "Page ", $page, NL;
PRINT "Subscriber name", TAB (36), "Magazine",
TAB (52), "Date Started", TAB (68),
"Issues Left", NL, NL;
END
PROCEDURE details /* print one subscriber */
BEGIN
PRINT NL, name;
SELECT FROM scripts
WHERE subscriber EQ sub.subscriber;
FOR EACH script
DO a_magazine;
END
PROCEDURE a_magazine /* print one subscription */
BEGIN
PRINT TAB (36), magazine, TAB (52),
started, TAB (68), issues, NL;
END
PROBLEM 3
Produce a report that lists all of the magazines which have more than 3 subscribers. Standard report headings are to be used, detail layout is left up to you. The detail should show the magazine title (shown only once), the name of the subscriber, and the date the subscription was started.