WITHIN: A Noble Word
By Noreen Redden
Calculations used within a report request (COMPUTE) use aggregate values as the components of the calculation.
For instance, in the EMPLOYEE file, if cost of education is to be determined by multiplying the ED_HRS times the hourly salary, it could be specified as a COMPUTE.
DEFINE FILE EMPLOYEE
HRLY_SAL/F8.2 = CURR_SAL / 1000;
END
TABLE FILE EMPLOYEE
SUM ED_HRS NOPRINT HRLY_SAL NOPRINT
COMPUTE COST_OF_EDUCATION = ED_HRS * HRLY_SAL;
BY DEPARTMENT BY LAST_NAME
END
DEPARTMENT LAST_NAME COST_OF_EDUCATION
---------- --------- -----------------
MIS BLACKWOOD 1,633.50
CROSS 1,217.79
GREENSPAN 225.00
JONES 924.00
MCCOY .00
SMITH 475.20
PRODUCTION BANNING .00
IRVING 805.86
MCKNIGHT 805.00
ROMANS 105.60
SMITH 95.00
STEVENS 275.00
Certainly, fields used in COMPUTES need not be mentioned explicitly, as FOCUS will determine the need for the field and create it as a NOPRINTed verb object if not previously mentioned. However, as reports get more complicated, actually explicitly mentioning the components of the calculation allows the developer to avoid any confusion (DEFAULT and DISASTER both begin with D).
In addition, if when testing the need arises to actually see the components, LET NOPRINT = ; changes the value of NOPRINT to blank, and running the procedure will allow the display, without having to EDIT the procedure in either a painter or text editor.
DEPARTMENT LAST_NAME ED_HRS HRLY_SAL COST_OF_EDUCATION
---------- --------- ------ -------- -----------------
MIS BLACKWOOD 75.00 21.78 1,633.50
CROSS 45.00 27.06 1,217.79
GREENSPAN 25.00 9.00 225.00
JONES 50.00 18.48 924.00
MCCOY .00 18.48 .00
SMITH 36.00 13.20 475.20
PRODUCTION BANNING .00 29.70 .00
IRVING 30.00 26.86 805.86
MCKNIGHT 50.00 16.10 805.00
ROMANS 5.00 21.12 105.60
SMITH 10.00 9.50 95.00
STEVENS 25.00 11.00 275.00
But suppose that instead of calculating the cost of education based on the individual’s hourly salary, I wanted to use, in all cases, the average hourly salary for the department:
Enter WITHIN.
DEFINE FILE EMPLOYEE
HRLY_SAL/F8.2 = CURR_SAL / 1000;
END
TABLE FILE EMPLOYEE
SUM ED_HRS NOPRINT AVE.HRLY_SAL NOPRINT WITHIN DEPARTMENT
COMPUTE COST_OF_EDUCATION = ED_HRS * AVE.HRLY_SAL;
BY DEPARTMENT BY LAST_NAME
IF DEPARTMENT NE ' '
END
AVE
DEPARTMENT LAST_NAME ED_HRS HRLY_SAL COST_OF_EDUCATION
---------- --------- ------ -------- -----------------
MIS BLACKWOOD 75.00 18.00 1,350.02
CROSS 45.00 18.00 810.01
GREENSPAN 25.00 18.00 450.01
JONES 50.00 18.00 900.02
MCCOY .00 18.00 .00
SMITH 36.00 18.00 648.01
PRODUCTION BANNING .00 19.05 .00
IRVING 30.00 19.05 571.41
MCKNIGHT 50.00 19.05 952.35
ROMANS 5.00 19.05 95.23
SMITH 10.00 19.05 190.47
STEVENS 25.00 19.05 476.17
Because AVE.HRLY_SAL WITHIN DEPARTMENT was specified as the component, the value is the same for all individuals within the department. The is result clearing the LET NOPRINT = ;
LET CLEAR NOPRINT
DEPARTMENT LAST_NAME COST_OF_EDUCATION
---------- --------- -----------------
MIS BLACKWOOD 1,350.02
CROSS 810.01
GREENSPAN 450.01
JONES 900.02
MCCOY .00
SMITH 648.01
PRODUCTION BANNING .00
IRVING 571.41
MCKNIGHT 952.35
ROMANS 95.23
SMITH 190.47
STEVENS 476.17
By using WITHIN, I can specify exactly which values to use in each calculation. Remember that even if columns have the same names and are NOPRINTed, if you have specified the column, SET CNOTATION = EXPLICIT gives the user the control of specifying the exact column needed for the calculation.
So,
DEFINE FILE EMPLOYEE
HRLY_SAL/F8.2 = CURR_SAL / 1000;
END
TABLE FILE EMPLOYEE
SUM AVE.HRLY_SAL NOPRINT WITHIN DEPARTMENT ED_HRS NOPRINT
AVE.HRLY_SAL NOPRINT
COMPUTE COST1 = ED_HRS * C1; AS 'COST BASED,ON DEPART AVERAGE'
COMPUTE COST2 = ED_HRS * C3; AS 'COST BASED,ON INDIVIDUAL'
BY DEPARTMENT BY LAST_NAME
IF DEPARTMENT NE ' '
END
COST BASED COST BASED
DEPARTMENT LAST_NAME ON DEPART AVERAGE ON INDIVIDUAL
---------- --------- ----------------- -------------
MIS BLACKWOOD 1,350.02 1,633.50
CROSS 810.01 1,217.79
GREENSPAN 450.01 225.00
JONES 900.02 924.00
MCCOY .00 .00
SMITH 648.01 475.20
PRODUCTION BANNING .00 .00
IRVING 571.41 805.86
MCKNIGHT 952.35 805.00
ROMANS 95.23 105.60
SMITH 190.47 95.00
STEVENS 476.17 275.00
By the way, the same rule holds true for the customer who prefers multiple sort groups (Report Painter in Developer Studio), or multiple verbs. Consider the following:
DEFINE FILE EMPLOYEE
HRLY_SAL/F8.2 = CURR_SAL / 1000;
END
TABLE FILE EMPLOYEE
SUM AVE.HRLY_SAL NOPRINT BY DEPARTMENT
SUM ED_HRS NOPRINT
AVE.HRLY_SAL NOPRINT
COMPUTE COST1 = ED_HRS * C1; AS 'COST BASED,ON DEPART AVERAGE'
COMPUTE COST2 = ED_HRS * C3; AS 'COST BASED,ON INDIVIDUAL'
BY DEPARTMENT BY LAST_NAME
IF DEPARTMENT NE ' '
END
Remember that if CNOTATION = EXPLICIT, each verb object explicitly mentioned (with or without NOPRINT) is assigned a unique number in the order in which they are mentioned. So the AVE.HRLY_SAL WITHIN DEPARTMENT (or in the first verb) is C1, ED_HRS is C2, and the HRLY_SAL for the individual is C3.
We can even show calculations on multiple levels, using WITHIN.
DEFINE FILE EMPLOYEE
HRLY_SAL/F8.2 = CURR_SAL / 1000;
END
TABLE FILE EMPLOYEE
SUM AVE.HRLY_SAL WITHIN DEPARTMENT NOPRINT
AVE.ED_HRS WITHIN DEPARTMENT NOPRINT
ED_HRS NOPRINT
HRLY_SAL NOPRINT
COMPUTE COST1 = C2 * C1; NOPRINT
COMPUTE COST2 = C3 * C4; AS 'COST BASED,ON INDIVIDUAL'
BY DEPARTMENT BY LAST_NAME
IF DEPARTMENT NE ' '
ON DEPARTMENT SUBHEAD
"</1 THE AVERAGE COST FOR EDUCATION IN <DEPARTMENT IS <COST1 "
END
COST BASED
DEPARTMENT LAST_NAME ON INDIVIDUAL
---------- --------- -------------
THE AVERAGE COST FOR EDUCATION IN MIS IS 693.01
MIS BLACKWOOD 1,633.50
CROSS 1,217.79
GREENSPAN 225.00
JONES 924.00
MCCOY .00
SMITH 475.20
THE AVERAGE COST FOR EDUCATION IN PRODUCTION IS 380.94
PRODUCTION BANNING .00
IRVING 805.86
MCKNIGHT 805.00
ROMANS 105.60
SMITH 95.00
STEVENS 275.00
