I'm trying to average 5 fields
in my report. I do get a number
to display. However, the calculation
is not correct. I suspect that my
many missing values are causing
the problem.

I have pasted my code below:

-*FILE TSO5.FEX
-*DEFAULT MODE: REPORT FEX
-SET NODATA=' ';

-SET ECHO='ALL';
-DEFAULT &FMT='HTML';
-DEFAULT &WIR='01';
-DEFAULT ®ION='Northwest';
-DEFAULT &LOC_NAME='Andrew
County Community Action
Partnership';
-DEFAULT &BDATE='APR-01-2005';
-DEFAULT &EDATE='JUL-01-2005';
-INCLUDE TBIDT01

JOIN
APP_SAP.APP_SAP.APP_ID
IN app_sap TO APP_TEST_SCORES.APP_TEST_SCORES.APP_ID
IN app_test_scores AS J0
END
DEFINE FILE APP_SAP
NAME/A66=LASTNAME ||
(', ' |FIRSTNAME);
PREREADSKILLS/D9.1S=IF ( APP_TEST_SCORES.TEST_TYPE EQ '19')
AND ( APP_TEST_SCORES.TEST_SUBTYPE EQ 'P1')
THEN SKILLS_LEVEL ELSE 0;
PREMATHSKILLS/D9.1S=IF
(TEST_TYPE EQ '19') AND
(TEST_SUBTYPE EQ 'P4')
THEN SKILLS_LEVEL ELSE 0;
READSKILLS/D9.1S=IF
(TEST_TYPE EQ '19') AND
(TEST_SUBTYPE EQ '1')
THEN SKILLS_LEVEL ELSE 0;
MATHSKILLS/D9.1S=IF
(TEST_TYPE EQ '19') AND
(TEST_SUBTYPE EQ '4')
THEN SKILLS_LEVEL ELSE 0;
LOCATINGSKILLS/D9.1S=IF
(TEST_TYPE EQ '19') AND
(TEST_SUBTYPE EQ '9')
THEN SKILLS_LEVEL ELSE 0;
END

TABLE FILE APP_SAP
SUM
'APP_TEST_SCORES.TEST_DATE' AS 'Date'
'DST.APP_SAP.SSN' AS 'SSN'
'APP_SAP.NAME' AS 'Name'
'APP_TEST_SCORES.PREREADSKILLS'
AS 'Reading,Pre-Test'
'APP_TEST_SCORES.PREMATHSKILLS'
AS 'Math,Pre-Test'
'APP_TEST_SCORES.READSKILLS'
AS 'Reading,Test,Score'
'APP_TEST_SCORES.MATHSKILLS'
AS 'Math,Test,Score'
'APP_TEST_SCORES.LOCATINGSKILLS'
AS 'Loc. In,Test,Score'
-*'APP_SAP.WKKEY_LEVEL
'Level**'
-
AND COMPUTE Level/A6 =
DECODE APP_SAP.WKKEY_LEVEL
(S 'Silver' B 'Bronze' G 'Gold');
BY
'APP_SAP.SSN' NOPRINT
BY
'APP_TEST_SCORES.TEST_DATE'
NOPRINT AS 'Date'
BY
'APP_SAP.NAME' NOPRINT AS 'Name'
HEADING
"WorkKeys Test Scores"
"Region: ®ION "
"&LOC_NAME"
"&BDATE"
"Through"
"&EDATE"
"==============<0X
========================"
FOOTING
"*Pre-Test scores apply
only to WorkKeys."
"**(B) bronze:
lowest score must be at least 3"
" (S) silver: lowest
score must be at least 4"
" (G) gold: lowest
score must be at least 5"
WHERE ( APP_TEST_SCORES.PREREADSKILLS NE 0)
OR (APP_TEST_SCORES.PREMATHSKILLS NE 0)
OR (APP_TEST_SCORES.READSKILLS NE 0)
OR (APP_TEST_SCORES.MATHSKILLS NE 0)
OR (APP_TEST_SCORES.LOCATINGSKILLS NE 0);
WHERE ( APP_TEST_SCORES.PREREADSKILLS IS-NOT MISSING)
OR (APP_TEST_SCORES.PREMATHSKILLS IS-NOT MISSING)
OR (APP_TEST_SCORES.READSKILLS IS-NOT MISSING)
OR (APP_TEST_SCORES.MATHSKILLS IS-NOT MISSING)
OR (APP_TEST_SCORES.LOCATINGSKILLS IS-NOT MISSING);
WHERE WIR EQ '&WIR.EVAL';
ON TABLE RECOMPUTE AVE.
PREREADSKILLS AVE. PREMATHSKILLS
AVE. READSKILLS AVE. MATHSKILLS AVE. LOCATINGSKILLS


ON TABLE SET PAGE-NUM ON
ON TABLE SET BYDISPLAY ON
-*ON TABLE COLUMN-TOTAL
AS 'TOTAL'
ON TABLE SET ONLINE-FMT
EXL2K
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Legal',
LEFTMARGIN=0.013889,
RIGHTMARGIN=0.013889,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.013889,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=15,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=TITLE,
STYLE=NORMAL,
$
TYPE=TABFOOTING,
LINE=1,
JUSTIFY=RIGHT,
$
TYPE=HEADING,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=FOOTING,
STYLE=BOLD,
$
TYPE=FOOTING,
LINE=2,
STYLE=NORMAL,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=3,
STYLE=NORMAL,
JUSTIFY=RIGHT,
$
TYPE=FOOTING,
LINE=4,
STYLE=NORMAL,
JUSTIFY=RIGHT,
$
TYPE=SUBHEAD,
STYLE=BOLD,
$
TYPE=SUBFOOT,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=8,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

bsneln

This message has been edited. Last edited by: <Mabel>,