Focal Point - Search WITHIN for the answer

Search WITHIN for the answer
By Mickey Grackin

Challenge

The CORE FOCUS Language contains many useful prefix operators that may be used on numeric fields to attain such things as the maximum (MAX.), minimum (MIN.), average (AVE.), count (CNT.) or percent (PCT.) value of the field. However, the contexts in which these prefix operators calculate the value may not give you the desired result. For example, the prefix operator percent (PCT.) will give the percent value which the number represents based on the grand total for the report. What if you want to calculate the percent a value represents in the context of each value of a particular sort field? For example, using the CAR file, how do you show the percent the total SALES of a particular MODEL represents for the COUNTRY in which it was manufactured?

Solution

The challenge presented above can be easily solved by using the WITHIN keyword. The following example demonstrates how to control the context in which the percent of SALES is calculated. Adding the WITHIN keyword followed by the sort field name after the field with the prefix operator percent (PCT.) will change the context of the percentage calculation. In the following code, there are two lines which use the percent (PCT.) prefix operator on the PCTSAL field. The first line uses the WITHIN keyword followed by the sort field CAR. The second line uses the WITHIN keyword followed by the sort field COUNTRY. The first line will calculate the percent of total SALES for the CAR which each MODEL represents. The second line will calculate the percent of total SALES for the COUNTRY which each MODEL represents.

The same results could be achieved with a Multi-Verb request and a number of COMPUTE statements. However, using the WITHIN command makes this request very easy to accomplish with a lot less code.

For this particular challenge, the answer truly is found within!

Code

DEFINE FILE CAR
PCTSAL/F5.1=SALES;
MODPER/F5.1=SALES;
END
TABLE FILE CAR
SUM SALES/D12 AS 'SALES,Total'
PCT.PCTSAL WITHIN CAR AS '% of,CAR'
PCT.PCTSAL WITHIN COUNTRY AS '% of,COUNTRY'
PCT.MODPER AS '% of,World'
BY COUNTRY NOPRINT
BY CAR
BY MODEL
ON COUNTRY SUBTOTAL SALES MODPER AS ''
ON COUNTRY SUBHEAD
"<COUNTRY"
ON COUNTRY SUBFOOT
" "
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT,GRID=OFF,BORDER=LIGHT,FONT='ARIAL',SIZE=10,STYLE=BOLD,$
TYPE=TITLE,BACKCOLOR=NAVY,COLOR=WHITE,STYLE=-UNDERLINE+BOLD,$
TYPE=SUBHEAD,BACKCOLOR=GREEN,COLOR=WHITE,$
TYPE=SUBFOOT,BORDER-LEFT=OFF,BORDER-RIGHT=OFF,$
TYPE=SUBTOTAL,BACKCOLOR=SILVER,COLOR=MAROON,$
TYPE=GRANDTOTAL,BACKCOLOR=MAROON,COLOR=WHITE,$
ENDSTYLE
END

If any questions, please contact Mickey at mgrackin@nist.gov.