旼컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴� � DB/C Newsletter � � April 1993 � 읕컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴� Editor's Notes This months newsletter focuses on the Report Writer. From the initial responses we have had to it, we recognize that one of the major problem areas is in setting up the data dictionary correctly. We hope that this article will help you in understanding the Report Writer. DNW Technical Information about The Report Writer The Report Writer is an important new feature in DB/C 8.0. The Report Writer is designed to be used primarily by end users. It is simple to use and easy to understand. The end user does not need to have in-depth knowledge of the database structure or the strategies used to create a report. An interesting aspect of the Report Writer is that it is written entirely in DB/C and the source code is available on the DB/C BBS. This gives the programmer options for customization that are not available in other report generators. The Report Writer documentation included with DB/C 8.0 is oriented to the end-user. In this article, we will describe some of the technical aspects of the Report Writer including the algorithms used to generate a report. In addition, we will explain how the file linkage aspects of the database structure are used in the report generation. The RW.MOD source code module (RW.DBC when compiled) contains the main-line code for the Report Writer. The actual report generation code is contained in the RW1.MOD source code module (RW1.DBC when compiled). The main-line code LOADMOD's RW1 and then calls the entry point in RW1 with a parameter that identifies which report is to be generated. The report to be generated corresponds with one record in the xxRWR.TXT file, where xx is the system code. The RW1 module makes calls into the data dictionary support module, RW2.MOD (RW2.DBC when compiled). The RW2 module contains code to access the data dictionary file xxDDA.TXT, where xx is the system code. The format of the xxDDA file is described in the READ.ME file and Read Me First book that are both distributed with DB/C 8.0. The Report Writer's report generation process can be broken into these phases: Phase 1: Examine all files specified for this report and the links between them. Create a "map" or "chart" of the links between every file. Phase 2: Initialize the necessary tables with report format and calculation field information, with selection criteria information, and with break and totaling information. Phase 3: Traverse the "map" of file links until a read order can be obtained that includes every file in the report. This ordering will provide the sorted sequence of reads necessary to complete one report line. Phase 4: Open the files used in the report. Create temporary indexes for on those files that have fields that are used in file linkages and for which appropriate indexes are not available. Phase 5: Examine the fields specified for ordering this report. If the first file in the read sequence (determined in Phase 3) can be sorted to produce the specified order, it is done so now. If the report ordering includes fields from more than one file or fields of a non-primary read sequence file, a merge occurs. A merge is where the report is created (applying any selection criterion) and written to a temporary file. This file is then sorted into the specified report order. Phase 6: Run the report by either reading from the temporary file or from the report files (in the sequence determined by Phase 3). Build the retrieved information into the correct report format and print with any necessary totaling and field breaking. If file merging was not used in this report, selection criterion for each retrieved record is evaluated in this phase. Phase 7: Print grand totals and item count. Phase 8: Delete all temporary files. In order for the Report Writer to be able to retrieve information from multiple files, these files must be logically "linked" together. By specifying this linkage, the Report Writer knows what fields two files have in common. Linkage fields are used to build a key from a record in a source file. This key is used to retrieve a record or records from another file via an index file. The "link-from" file is defined as the file that is read from initially. Using the information retrieved from the link-from record, corresponding record(s) are read from the "link-to" file. There are two types of file linkage: 1. One-to-One Link: Each record from one file has exactly one record in another file that contains related information. The link-to file has index whose key contains only the linkage fields for the Report Writer to interpret this linkage to be a One-to-One Link. 2. One-to-Many Link: Each record from one file is related to one or more records in another file. This relationship is commonly described as a header-detail type relationship. In order for the Report Writer to interpret this linkage to be a One-to-Many Link, the link-to file must have an index whose key contains the linkage fields followed by any additional fields used to make each one of the "many" records unique. Note: A "reversed" One-to-Many Link typically looks like a One-to-One Link (although not in all cases). This is in the case where each header record points to many corresponding detail records. But, from the point of view of the "many" file, each detail record will have only one header record. The Report Writer only works with One-to-Many Links that look like One-to-One-Links when reversed or links that look like One-to-One Links in both directions. File linkage is defined in the Fast Programming Data Dictionary, which is then stored in the xxDDA.TXT data dictionary file. The linkage between two files may be specified in either file definition or in both. The linkage defined in a file definition assumes that this is the link-from file and that the file code of the link-to file will be given. The following are samples of One-to-One Links and One-to-Many Links. The sample file structure is as follows: File 1: Sales Header File (SALES) Field Layout: sales.num NUM 6 sales record number sales.date CHAR 8 date of sale (YYYYMMDD) sales.dept NUM 4 sales department number sales.saleperson NUM 6 sales person code Key 1: sales.num Key 2: sales.dept, sales.saleperson, sales.num Key 3: sales.saleperson, sales.num File 2: Sales Detail File (SDETAIL) Field Layout: sdet.num NUM 6 sales record number sdet.line NUM 3 detail line number sdet.item CHAR 10 sale item code sdet.desc CHAR 40 sale item description sdet.amount NUM 9.2 dollar amount Key 1: sdet.num, sdet.line Key 2: sdet.item, sdet.num, sdet.line File 3: Sales Person File (SPERSON) Field Layout: sper.num NUM 6 sales person code sper.lastname CHAR 20 last name sper.firstname CHAR 20 first name sper.addr CHAR 40 address sper.city CHAR 15 city sper.state CHAR 2 state sper.zip CHAR 10 zip code sper.hiredate CHAR 8 hiredate (YYYYMMDD) Key 1: sper.num Key 2: sper.lastname, sper.firstname, sper.num Each SALES record relates to one sales person and thus a One-to-One Link exists from the SALES file to the SPERSON file. It should be specified like this: In the SALES file definition (File 1): Link to file: SPERSON Link from field: sales.saleperson Link to field: sper.num Each SALES record relates to one or more sales detail file records and thus a One-to-Many Link exists between the SALES and SDETAIL files. It should be specified look like this: In the SALES file definition (File 1): Link to file: SDETAIL Link from field: sales.num Link to field: sdet.num The reverse linkage from the SDETAIL file definition back to the SALES file definition is a One-to-One Link that looks like this like this: In the SDETAIL file definition (File 2): Link to file: SALES Link from field: sdet.num Link to field: sales.num This last link (the reverse link from SDETAIL to SALES) does not actually need to be specified. The Report Writer automatically creates the link in the reverse direction between any two files that are linked. It does not matter in which file the link is actually specified - it just must be specified in one file or the other. It is acceptable to specify the link in both files also. The Report Writer chooses the sequence in which files are to be read before it starts to create the report. The sequence is chosen by examining the network of links between files and by using the report ordering chosen by the user. If the report ordering is not specified, the report will be ordered by the fields that make up the first key of the file that is the first specified in the Files Used in Report view box in the definition of the report. The Report Writer assumes that if a One-to-Many Link is defined, the header file (the link-from file) must be read before the detail file (the link-to file) is read. This causes each header file record to be read before the corresponding detail file records are read. This is overridden if the user specifies a report ordering based on an index in the detail file. The following example uses the files defined above. It shows how the report ordering can change the way files are read by the Report Writer: The SALES file is linked to the SDETAIL file in a One-to-Many fashion. If no report ordering is specified, or if ordering based on a field in the SALES file is given, the Report Writer will read through the SALES file retrieving multiple corresponding SDETAIL records. If the report order is by the SDETAIL sale item code field, then the Report Writer will read through the SDETAIL records retrieving single corresponding SALES records. Note that in this case SALES records with no SDETAIL records will not appear on the report. Hopefully, this will give you a better idea of how the Report Writer works so that you can use it in the most productive ways possible. We welcome all feedback you have about the Report Writer. DB/C Class Schedule The next DB/C class is tentatively scheduled for June 7-10, 1993. As always, it will be held in the Oak Brook, Illinois office of Subject, Wills & Company. For more information, contact Judi Tamkevic at (708) 572-0240.