How to write CA Earl Report Requests: Using TAG and SELECT to Produce an Array of Values
See KDs - TEC291378, TEC323377, TEC265260, TEC265262. TEC274567, TEC291378, TEC323376, TEC345252, TEC372281
CA Earl provides a variety of methods that may be used to select records. In this article, we will demonstrate the use of SELECT statements and TAG values to create a printed array of values from a single variable.
A TAG value is a one-character value assigned by a SELECT statement. By including multiple SELECT statements and TAG values in your report request, you can provide unique processing for different groups of selected data. Tags can be used to control printing at either the line or the field level. This article provides an example of controlling printing at the field level.
The following request will produce a report of the contents of our standard AIRPORTS file
<Please see attached file for image>src="/servlet/servlet.FileDownload?file=0150c000004AKrKAAW" alt="" width="500" height="118" border="0">
Now, let's modify our request so that the country code (A_COUNTRY) is converted to the full country name. We used a DECODE statement in the article "Writing Advantage CA-Earl Report Requests - Creating Multiple Reports", so we'll include that DECODE again and then use the target field (COUNTRY) in the CONTROL statement to sort and group the records for our report.
In an effort to emphasize the passenger volume, we are going to establish four columns representing different ranges in volume. In the SELECT statements below, we assign the TAG value of 'A' to represent those airports with a volume below 10 million passengers, the TAG value 'B' to those airports where volume is between 10 and 30 million passengers, the TAG value of 'C' to airports having more than 30 million but less than 50 million passengers and finally the TAG value 'D' is assigned to any airports having more than 50 million passengers.
<Please see attached file for image>src="/servlet/servlet.FileDownload?file=0150c000004AKrLAAW" alt="" width="500" height="54" border="0">
This will result in a TAG value of A, B, C or D being assigned to each record in the file. To print the four columns of passenger volume values, we will include the field A_PASS on the PRINT statement four (4) times, each time with a different TAG value. Since we also want to see the airport name on each line, we must include all four (4) TAG values with the A_NAME field. The resulting PRINT statement looks like this:
<Please see attached file for image>src="/servlet/servlet.FileDownload?file=0150c000004AKrMAAW" alt="" width="500" height="29" border="0">
Notice that we've assigned the special TAG value of 0 (zero) to the COUNTRY field. This will cause that field to be printed at each control break rather than on each detail line.
Looking over the report, you may notice that each of the four columns has the same heading ("NUMBER OF PASSENGERS"). That is because we are printing the same source field each time. To modify the column headings, we will need to define all of the column headings for the report using a series of TITLE statements. Here is the revised Report section necessary to define the column titles:
<Please see attached file for image>src="/servlet/servlet.FileDownload?file=0150c000004AKrNAAW" alt="" width="500" height="284" border="0">
Notice that we are assigning specific columns to each print field, to ensure that our column headings are properly aligned. Also, notice that the column number specified for an alphanumeric field (COUNTRY, A_NAME) is the leftmost or first print position for that field, while the column specified for a numeric field (A_PASS) is the rightmost or last print position for that field.
Please refer to the CA Earl User Guide section 5.1 Format and Contents for more information about placing fields at specific locations within the print line and section 5.5 Grouping Data for a detailed explanation of SELECT and TAG processing. Please refer to the CA Earl Reference Guide for specific information on coding an CA Earl program.