In this section: |
Starting in FOCUS 7.6.3, the WITHIN phrase can be added to a SUBFOOT command.
How to: Reference: |
By adding the WITHIN phrase to a SUBFOOT command, you can create a subfooting whose fields are calculated within a sort field break. Without the WITHIN phrase, each SUBFOOT takes its field values from the report row directly above it on the report output. Calculating field values within a sort break is useful when applying a prefix operator to fields in the subfooting. If you do not specify a prefix operator when using WITHIN, the field is assigned the default prefix operator SUM. and produces a subtotal within the sort break.
You can specify a subfoot in a BY phrase or in an ON phrase.
{ON|BY} byfield SUBFOOT [WITHIN] [MULTILINES]
"text [<prefop.fieldname ... ]"
[WHEN expression;]
where:
Is the sort field whose change in value generates the SUBFOOT.
Causes the fields in the SUBFOOT to be calculated within each value of byfield. Without this option, a field in the SUBFOOT is taken from the last line of report output above the subfooting.
Is the text you supply between double quotation marks that will be printed following the SUBFOOT phrase. The total number of subheads and subfoots in one report request may not exceed nine.
Is one of the following prefix operators: AVE., MIN., MAX., CNT., SUM., ASQ. If no prefix operator is specified, the default operator applied is SUM.
Is field whose value will be printed in the subfooting calculated within the sort field value.
Is used to suppress the SUBFOOT when there is only one line of output for the BY group. Note that MULTI-LINES is a synonym for MULTILINES.
Specifies a conditional subfoot whose appearance on the report output is determined by a Boolean expression. WHEN must be placed on the line following the text you enclose in double quotation marks.
The following request displays the average and minimum salary values first within department, then within department and job class, and last within department, job calss and employee ID. Subfootings are generated on the department and jobcode sort fields. The DEFINE FILE command created two additional fields with the SALARY value, one for each sort break:
DEFINE FILE EMPDATA
SALDEPT/D6 WITH SALARY = SALARY;
SALDEPTJOB/D6 WITH SALARY = SALARY;
DEPT/A4 WITH SALARY = EDIT(DEPT, '9999');
JOB/A8 WITH SALARY = JOBCLASS;
END
TABLE FILE EMPDATA
SUM AVE.SALDEPT AS 'DEPT,AVE'
MIN.SALDEPT AS 'DEPT,MIN'
BY DEPT
SUM AVE.SALDEPTJOB AS 'DEPT/JOB, AVE' IN 32
MIN.SALDEPTJOB AS 'DEPT/JOB, MIN' IN 42
BY DEPT
BY JOB
PRINT AVE.SALARY/D6 AS 'AVE' IN 52
MIN.SALARY/D6 AS 'MIN' IN 61
BY DEPT
BY JOB
BY PIN NOPRINT
ON DEPT SUBFOOT
"******************DEPARTMENT <DEPT SUBFOOT**************************"
"NOT WITHIN: AVE=<AVE.SALARY MIN=<MIN.SALARY "
ON DEPT SUBFOOT WITHIN
" WITHIN: AVE=<AVE.SALARY MIN=<MIN.SALARY "
"*******************************************************************"
ON JOB SUBFOOT
"</1 ***************DEPARTMENT <DEPT / JOB <JOB SUBFOOT***************"
"NOT WITHIN: AVE=<AVE.SALARY MIN=<MIN.SALARY "
ON JOB SUBFOOT WITHIN
" WITHIN: AVE=<AVE.SALARY MIN=<MIN.SALARY "
"******************************************************************* </1"
WHERE DEPT EQ 'MARK'
WHERE JOBCLASS EQ '257PSB' OR '257PTB'
END The report output shows that each SUBFOOT without the WITHIN phrase uses the report line above the subfooting in the calculations. The SUBFOOT within both department and jobcode uses the calculations that were specified in the second SUM command (by department by jobcode), and the SUBFOOT within department only uses the calculations that were specified in the first SUM command (by department):
DEPT DEPT DEPT/JOB DEPT/JOB
DEPT AVE MIN JOB AVE MIN AVE MIN
---- ---- ---- --- -------- -------- --- ---
MARK 56,757 50,500 257PSB 55,860 50,500 55,500 55,500
62,500 62,500
50,500 50,500
52,000 52,000
58,800 58,800
***************DEPARTMENT MARK / JOB 257PSB SUBFOOT***************
NOT WITHIN: AVE= $58,800.00 MIN= $58,800.00
WITHIN: AVE= $55,860.00 MIN= $50,500.00
*******************************************************************
257PTB 59,000 55,500 62,500 62,500
55,500 55,500
***************DEPARTMENT MARK / JOB 257PTB SUBFOOT***************
NOT WITHIN: AVE= $55,500.00 MIN= $55,500.00
WITHIN: AVE= $59,000.00 MIN= $55,500.00
*******************************************************************
******************DEPARTMENT MARK SUBFOOT**************************
NOT WITHIN: AVE= $55,500.00 MIN= $55,500.00
WITHIN: AVE= $56,757.14 MIN= $50,500.00
*******************************************************************
| Information Builders |