Writing Advantage CA-Earl Report Requests: Advanced Postsort Processing Techniques: Samples and Instructions

Document ID : KB000011504
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

This article expands on the post sort processing introduced in previous KDs and the sample files provided in the install of the product. 

Question:

Writing Advantage CA-Earl Report Requests: Advanced Post sort Processing Techniques: Samples and Instructions

Answer:

See KDs - TEC291378, TEC323377, TEC265260, TEC265262. TEC274567

This article expands on the postsort processing introduced in previous KDs and the sample files provided in the install of the product. Using the standard AIRPORTS file, we will produce a report showing the total number of passengers for each airport, the percentage that passenger total represents with respect to all of the airports included in the report and the average number of passengers per airport within each country. This report will be printed in order by city name within country code, and a subtotal number and percent of passengers will be printed for each country.

Instead of printing the country code, we want to print the full country name. We could include a series of IF-THEN-ELSE statements, or a DECODE statement, in the presort section of the report to assign the country name for each input record. However, it is far more efficient to assign the country name with a DECODE(D) in the postsort section of the report. By testing the BREAKQSEQ variable, we will only have to assign the country name once for each country processed.

Here is the final CA-Earl request:

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=121                                                      
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 COUNTRY (X 24) = NONE         'Country'  'Name'                     
                                                                        
DEF BRK_PASS (N 12.0) = 0         '# PASSENGERS' 'FOR COUNTRY'          
                                  PIC 'ZZZ,ZZZ,ZZZ,ZZ9'                 
DEF WK_PCT   (N 3.4) = 0                                                
DEF BRK_PCT  (N 3.2) = 0          '% OF TOTAL' 'PASSENGERS'             
                                  PIC 'ZZ9.99'                          
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 VOLUME PER'  
'AIRPORT IN COUNTRY'                                    PIC 'Z,ZZZ,ZZZ,ZZ9'                   
DEF COUNT (N 3.0) = 1                                                   
!                                                                       
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                                 
!                                                                
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 CITY WITH TOTALS'                   
CONTROL (A_COUNTRY)   A_CITY                                     
!                                                                
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                       
!    
IF(D) BREAKQSEQ = 1                                           
   DECODE(D) A_COUNTRY INTO COUNTRY                           
      'AU  ' = 'Australia               '                     
      'DEN ' = 'Denmark                 '                     
      'FRG ' = 'Germany                 '                     
      'FR  ' = 'France                  '                     
      'IT  ' = 'Italy                   '                     
      'UK  ' = 'United Kingdom          '                     
      'NE  ' = 'Netherlands             '                     
      'SW  ' = 'Switzerland             '                     
      'CAN ' = 'Canada                  '                     
      'JA  ' = 'Japan                   '                     
      'ME  ' = 'Mexico                  '                     
      'SI  ' = 'Singapore               '                     
      'US  ' = 'United States of America'                     
    ELSE    '*COUNTRY NOT IN TABLE*'                          
  ELSE SET(D) COUNTRY = ' '                                   
ENDIF                                                         
!                                                             
IF(T) &CTRLBREAK < 2                                          
   SET(T) BRK_PASS = A_PASS                                   
   SET(T) WK_PCT = BRK_PASS / &TOT_PASS * 100                 
   SET(T) BRK_PCT ROUNDED = WK_PCT                            
ELSE                                                          
   SET(T) BRK_PASS = &TOT_PASS                                
   SET(T) BRK_PCT = 100.00                                    
ENDIF                                                         
!                                                             
SET(T) AVG_PASS = A_PASS / COUNT                              
!                                                             
PRINT COUNTRY A_CITY A_NAME A_PASS PCT_PASS AVG_PASS          
!                                                             
PRINT @A_PASS BRK_PASS @PCT_PASS BRK_PCT                      
!                                                             
! =======================================================     
!    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.