5: Creating the Demonstration System Reports

5.7 Level Breaks and Lookups

Sorting a file places all records with equal sort fields together. For instance, if you were to sort the subscriber file by zip code, all of the records with a zip code of 97011 would appear together. When you scan through a sorted file, you may find places where the value of the sort field changes (e.g., 97010 to 97011). This change is called a break.

If the file has been sorted by more than one field, there are breaks at more than one level. For example, if the file is sorted first by zip code and then by last name, all the subscribers with the same zip code appear together, and they are listed alphabetically within each zip code ‘group’. When the zip code changes, it is referred to as a level 1 break. This is because the zip code was the first sort field. When the last name changes, it is called a level 2 break, since the last name was the second sort field. Up to three levels of sorting may be specified with the Report Generator.

It is often necessary to indicate on a report where a level break begins and ends. For example, if you are listing invoices by customer, you would probably want to print the name of the customer at the beginning of all of their invoices and print the total of their invoices at the end. Thus, the customer name would be printed in the heading, and the customer total in the footing, of that particular level.

When you add sort fields in the Report Description screen, the corresponding Report Section screens are added to the report to handle the level break headings and footings. Likewise, when sort fields are deleted from the Report Description screen, the correlating Report Section screens are also deleted.

To help illustrate the use of level break Report Section screens, our next example lists magazine subscriptions sorted by subscriber. The subscription file must be sorted by the subscriber code. The report must show the subscriber name once, followed by a list of the magazines to which they subscribe.

To create this report, select choice D1 - Create Report Program from the Report Generator Menu and perform the steps indicated in the instructions that follow. We will name this report program scriptm. (Note: It is assumed that no report file by this name already exists.)

You should now be familiar with the basics of making entries to the various forms used in the Report Generator. If not, take time to review the preceding sections of this chapter prior to continuing with this section.

After storing the contents of the Report Description screen, the Report Section screen is displayed. The Page Heading section should then be filled in as follows:

We stated earlier that the subscriber name is to be printed once. Since the report is sorted by the subscriber code, all of the subscriptions for the same subscriber appear together. The Level 1 Heading section can be used to print the subscriber name before the list of subscriptions for that subscriber. Pressing the NEXT key <F3> displays the Level 1 Heading.

In our example, we want to print the subscriber name in this section. However, if you examine the dictionary of the subscription file, you will find that the subscriber name (i.e., name) is not a data field in that file. The field that associates subscribers with their subscriptions is the subscriber field. This is the subscriber code of the subscriber who has subscribed to the magazine identified by the entry in the magazine field. We can use the value of this field (i.e., subscriber) to determine, and consequently print, the subscriber’s name. This is called a lookup and is similar to the verify/lookup feature used in the C/Base data entry forms.

Lookups are performed by specifying a field in the report RMSfile whose value is to be used as the key value for the lookup, the name of the lookup’s RMSfile, and the name of the field in the lookup’s RMSfile to print. A lookup uses the key value to find a particular record in the lookup’s RMSfile. (Note: Refer to the diagram depicted earlier in the chapter for additional illustration.) When the record is found, only the value of the lookup field is printed. If no record is found, the value of the field used as a key is printed.

To print the subscriber name in our example, we would use the subscriber code in the subscription file as the key field and print the subscriber name from the subscriber file as the lookup field. Add this field by pressing the ADD key <F6> and filling in the entry form.

Notice the field labeled Field Length on your screen. The Report Generator assumes its value from the length of the report RMSfile’s field (i.e., subscriber), not the lookup RMSfile’s field (i.e., name). Therefore, you must change the entry in the Field Length field to an appropriate value for the lookup field being printed.

Now that we have taken the necessary steps to print the subscribers’ names, we can move on to printing their subscriptions.

The subscriptions must be printed in the Detail section. Pressing the NEXT key <F3> displays the Detail section. As we stated earlier, this report must print the title of every magazine subscribed to by each subscriber. Like the subscriber name, the field containing the magazine title (i.e., title) is not in the report’s RMSfile. There is, however, a field named magazine that is the code of the magazine which has been subscribed to. This code can be used as a lookup value to the magazine file which does contain the title field.

To add this field, start with the cursor in the upper left-hand corner of the screen and press the Space Bar 33 times. Press the ADD key <F6> and fill in the Field Description screen.

After pressing the STORE key <F5>, the Report Section screen is redisplayed.

At this point, you may select the PICTURE function (by pressing the GOLD key <F1> followed by the P key) to make sure things are ‘lined up’ as they should be.

You have now successfully recreated the report which is run by selecting choice 4 - List Subscriptions from the Demonstration Subscription System menu. After pressing any key to redisplay the current Report Section, press the EXIT key <F8> to save the reportfile and return to the Report Generator Menu.