How to write CA Earl Report Requests: Postsort Processing
Article ID: 11419
Updated On:
Products
Issue/Introduction
How to write CA Earl Report Requests: Postsort Processing
Environment
Component:
Resolution
See KDs - TEC291378, TEC323377, TEC265260, TEC265262. TEC274567, TEC291378, TEC323376
This is another article about writing CA Earl programs. In this article, we'll look at how the sorted data can be used to provide additional information for our report. This is called postsort processing, and it can be used to calculate values either for detail or total lines in an CA Earl report.
CA Earl postsort commands that contain the modifier (D) are executed as each detail record is read from the sorted hitfile. Referencing a field by its fieldname will give the value from the current hitfile record, while referencing the field by its &fieldname will give the last value of that field at presort time.
CA Earl commands that contain the (T) modifier are processed only when a control break occurs. Referencing a numeric field by either its fieldname or &fieldname will give you the accumulated value of that field for the current control break only. However, the &fieldname value of a numeric field is not accumulated, so it will always contain the accumulated value for the current control break, unless it has been modified by a SET(T) command. Alphabetic fields will contain the value from the last hitfile record read or the value from a previous SET(D) or SET(T) command, regardless of whether the fieldname or &fieldname value is referenced.
If this seems confusing at first, don't despair! Once you've actually worked with the syntax and seen the output, it will be more clear.
Now, suppose that, instead of showing millions of passengers for European airports, we want to see what percentage of the total passenger volume for the top 50 airports worldwide each airport's passenger volume represents. In addition, we want to see for each country what the average number of passengers was for that country's airports that are in the top 50 worldwide. The first thing we need to do is identify what values are needed to produce the desired results.
To calculate the percentage that each airport's passenger volume represents, we need to know the total number of passengers for the entire report. That value needs to be available for each detail record being processed. So we've added the field TOT_PASS to the GSA section, and in our presort section we will accumulate the number of passengers into this field. Then, when each detail record is being processed during postsort processing, the last value of TOT_PASS from the presort will contain the total number of passengers for the entire report. To access this value we reference the variable &TOT_PASS. We also added the field PCT_PASS to the GSA section to contain the calculated percentage.
Notice that we have removed the fields MILLIONS_PASS and COUNTRY from the GSA section and the SET and IF statements from the presort section. We also changed the label for the field A_COUNTRY in the file definitions section from COUNTRY ABBREVIATION to COUNTRY CODE so that it will take less space in the print line. In the report section, we modified the report heading, added a second heading line with the TITLE command and removed the SELECT statement. Notice also that the CONTROL statement now defines A_COUNTRY as both the primary sort field and the control break field. The field A_PASS is the secondary sort field, and will be sorted from highest to lowest.
If we had referenced the TOT_PASS field in the SET(D) statement instead of the &TOT_PASS field, our report would be incorrect.
Calculating the average number of passengers for each country's airports that are in the top 50 worldwide will require an accumulated total number of passengers for each country and the number of airports for each country. To accomplish this, we will add 3 more fields to our sample program's GSA; AVG_PASS will be the calculated average number of passengers, COUNT and TOT_COUNT will be used to get the number of airports for each country. Presort processing is used to initialize the field COUNT to a value of 1. This represents 1 detail record. Advantage CA-Earl will accumulate this field automatically, and we can get the number of airports in each country by saving the accumulated value at total time using the SET(T) command. Another SET(T) command is used to calculate the average number of passengers, and the new fields are added to the PRINT statement. The final report request is as follows:
NOTE =======================================================
NOTE This is the OPTIONS section - used to override the
NOTE default options specified at install time
NOTE =======================================================
OPTION LIST ON
OPTION OMIT ALL BLANK LINES
OPTION PRINTER=80
NOTE =======================================================
NOTE This is the File Definitions section - the file and
NOTE fields to be used are identified here"
NOTE =======================================================
AIRPORTS: FILE DISK RECORD=80
DEF A_NAME 1-18 X 'NAME OF' 'AIRPORT'
DEF A_CITY 20-35 X 'CITY'
DEF A_COUNTRY 40-43 X 'COUNTRY' 'CODE'
DEF A_PASS 50-57 N 'NUMBER OF' 'PASSENGERS'
PIC 'ZZ,ZZZ,ZZ9'
NOTE =======================================================
NOTE This is the GSA section - comparable to the COBOL
NOTE working-storage section
NOTE =======================================================
!
DEF TOT_PASS (N 15.0) = 0
DEF PCT_PASS (N 3.2) = 0 '% OF TOTAL' 'PASSENGERS'
PIC 'ZZ9.99'
DEF AVG_PASS (N 10.0) = 0 'AVERAGE PASSENGER' 'VOLUME/AIRPORT'
PIC 'Z,ZZZ,ZZZ,ZZ9'
DEF COUNT (N 3.0) = 0
DEF TOT_COUNT (N 3.0) = 0 '# ' 'AIRPORTS'
!
NOTE =======================================================
NOTE This is the PRESORT section - where you can specify
NOTE operations to be carried out before the data is
NOTE copied to the hit file and sorted.
NOTE =======================================================
!
SET TOT_PASS = A_PASS + TOT_PASS
SET COUNT = 1
!
NOTE =======================================================
NOTE This is the Report section - one or more reports
NOTE may be defined here.
NOTE =======================================================
!
REPORT 'WORLD''S 50 BUSIEST AIRPORTS'
TITLE 'SORTED BY COUNTRY AND PASSENGER VOLUME'
CONTROL (A_COUNTRY) A_PASS DOWN
!
NOTE =======================================================
NOTE This is the POSTSORT section - where you can specify
NOTE operations to be carried out for detail or total
NOTE processing after the data has been sorted.
NOTE =======================================================
!
SET(D) PCT_PASS = A_PASS / &TOT_PASS * 100
!
SET(T) TOT_COUNT = COUNT
SET(T) AVG_PASS = A_PASS / COUNT
!
PRINT A_COUNTRY TOT_COUNT AVG_PASS A_NAME A_PASS PCT_PASS
!
! =======================================================
! This is the required END statement
! =======================================================
END ! This is the last statement of the request
Additional Information
For more information about writing CA-Earl requests, refer to the publications, CA-Earl User Guide and CA-Earl Reference Guide.
Feedback
