Focal Point
[CLOSED] coding for [LAST YEAR]

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6517094196

August 20, 2019, 10:33 AM
Donal Mattimoe
[CLOSED] coding for [LAST YEAR]
I am having a problem with displaying fields for school improvement. The code does not display all select year designated in filter and does not display full contents of field from database. EXIT1EXT has CSI: No - row 1, null - row 2, TSI FRL:No -row 3, TSI Hispanic:No -row 4 and last 2 rows null. These should all display on header line expect nulls but only displaying CSI. Field is large enough but it is being limited some how? What am I doing wrong?

[code] TABLE FILE REPORTING_ALTERNATIVE
PRINT
COMPUTE EXIT1EXT/A200V = IF SCHOOL_ID EQ LAST SCHOOL_ID AND SCHOOL_YEAR EQ LAST SCHOOL_YEAR AND MET_FIRST_YEAR_EXIT NOT EQ '' THEN EXIT1EXT |', '|REPORTING_ALTERNATIVE.REPORTING_ESSA.MET_FIRST_YEAR_EXIT
ELSE IF MET_FIRST_YEAR_EXIT NOT EQ '' THEN REPORTING_ALTERNATIVE.REPORTING_ESSA.MET_FIRST_YEAR_EXIT ELSE 'N/A';
BY EXIT1EXT
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_EXIT1EXT
END

TABLE FILE PH_EXIT1EXT
SUM EXIT1EXT
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_EXIT1EXT
END

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_TSI AS J04;

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_ATS AS J05;

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_CSI AS J055;

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_EXIT1EXT AS J066;

Define[code]
FOOT8/A100V = 'Met First Year Exit Criteria = ';
FOOT81/A200V = EXIT1EXT;

Heading [code]
" < 0> < 0> < 0> < 0> < 0>
Format [code]
TYPE=HEADING,
LINE=19,
SIZE=8,
$

Should be displaying 'Met First Year Criteria = CSI:No, TSI FRL: No, TSI Hispanic: No'

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
August 20, 2019, 11:15 AM
Doug
1) Not sure what "NOT EQ" means. Don't you mean "NE"?
2) Please use the </> tags as seen above for enclosing your code.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
August 20, 2019, 11:25 AM
Doug
There are also several syntax errors:
[code] placed within the code.
DEFINE code with no END.
Missing quote at the end of the line after heading.
format [code]: Is that just a snippet of your ON TABLE SET STYLE code?

Perhaps you can place all your code withinthe red </> tags as seen above for enclosing your code when you reply.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
August 20, 2019, 12:57 PM
Donal Mattimoe
PRINT
COMPUTE EXIT1EXT/A200V = IF SCHOOL_ID EQ LAST SCHOOL_ID AND SCHOOL_YEAR EQ LAST SCHOOL_YEAR AND MET_FIRST_YEAR_EXIT NOT EQ '' THEN EXIT1EXT |', '|REPORTING_ALTERNATIVE.REPORTING_ESSA.MET_FIRST_YEAR_EXIT
ELSE IF MET_FIRST_YEAR_EXIT NOT EQ '' THEN REPORTING_ALTERNATIVE.REPORTING_ESSA.MET_FIRST_YEAR_EXIT ELSE 'N/A';
BY EXIT1EXT
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_EXIT1EXT
END

TABLE FILE PH_EXIT1EXT
SUM EXIT1EXT
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_EXIT1EXT
END

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_TSI AS J04;

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_ATS AS J05;

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_CSI AS J055;

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_EXIT1EXT AS J066;>


FOOT81/A200V = EXIT1EXT;>


<" < 0> < 0> < 0> < 0> < 0>

LINE=19,
SIZE=8,
$>


WebFOCUS 8
Windows, All Outputs
Hi Doug, Here is full code for report
<
JOIN CLEAR *
JOIN SCHOOL_YEAR AND SUBGROUP IN REPORTING_ALTERNATIVE TO SCHOOL_YEAR AND SUBGROUP IN REPORTING_CUTS AS J01;
JOIN SUBGROUP AND SCHOOL_YEAR AND SCHOOL_ID AND DISTRICT_ID IN REPORTING_ALTERNATIVE TO SUBGROUP AND SCHOOL_YEAR AND SCHOOL_ID AND DISTRICT_ID IN REPORTING_ESSA AS J02;
-*JOIN SUBGROUP AND SCHOOL_ID AND DISTRICT_ID IN REPORTING_ALTERNATIVE TO SUBGROUP AND SCHOOL_ID AND DISTRICT_ID IN REPORTING_ESSA AS J02;

TABLE FILE REPORTING_ALTERNATIVE
PRINT SCHOOL_YEAR
DISTRICT_NAME
SCHOOL_NAME
GRADES_SERVED
ENROLLMENT
SPL
ALTERNATIVE_WEIGHTED_SCORE_FINAL
ALT_WAEA_CUT1
ALT_WAEA_CUT2
ALT_WAEA_CUT3
SUBGROUP
AVERAGE_INDICATOR_SCORE_FINAL
AVERAGE_ACHIEVE_GROWTH_CSI
FOUR_YEAR_GRAD_RATE
CSI_YEAR_IDENTIFIED
ATS_YEAR_IDENTIFIED
MET_FIRST_YEAR_EXIT
COMPUTE CLASS/A25V = IF UNCLASSIFIED EQ '1' THEN 'Not Identified' ELSE IF UNCLASSIFIED EQ '' THEN 'Under Review' ELSE 'Identified';
WHERE REPORTING_ALTERNATIVE.REPORTING_ALTERNATIVE.SCHOOL_YEAR EQ &SCHOOL_YEAR.(FIND REPORTING_ALTERNATIVE.REPORTING_ALTERNATIVE.SCHOOL_YEAR IN REPORTING_ALTERNATIVE|FORMAT=A7V).SCHOOL_YEAR:.QUOTEDSTRING;
WHERE REPORTING_ALTERNATIVE.REPORTING_ALTERNATIVE.DISTRICT_ID EQ &DISTRICT_ID.(FIND REPORTING_ALTERNATIVE.REPORTING_ALTERNATIVE.DISTRICT_ID IN REPORTING_ALTERNATIVE|FORMAT=A7V).DISTRICT_ID:.QUOTEDSTRING;
WHERE REPORTING_ALTERNATIVE.REPORTING_ALTERNATIVE.SCHOOL_ID EQ &SCHOOL_ID.(FIND REPORTING_ALTERNATIVE.REPORTING_ALTERNATIVE.SCHOOL_ID IN REPORTING_ALTERNATIVE|FORMAT=A7V).SCHOOL_ID:.QUOTEDSTRING;
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS PRIMARY_HOLD
END

JOIN CLEAR *
JOIN SCHOOL_ID AND DISTRICT_ID AND SCHOOL_YEAR IN REPORTING_ALTERNATIVE TO SCHOOL_ID AND DISTRICT_ID AND SCHOOL_YEAR IN REPORTING_ESSA AS J03;

TABLE FILE REPORTING_ALTERNATIVE
PRINT
COMPUTE CSITEXT/A200V = IF CSI EQ LAST CSI AND SCHOOL_ID EQ LAST SCHOOL_ID AND SCHOOL_YEAR EQ LAST SCHOOL_YEAR AND CSI EQ 1 THEN CSITEXT
ELSE IF CSI EQ 1 THEN 'Identified' |': '|REPORTING_ALTERNATIVE.REPORTING_ESSA.CSI_YEAR_IDENTIFIED ELSE 'N/A';
BY CSI
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_CSI1
END

TABLE FILE PH_CSI1
SUM CSITEXT
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_CSI
END

TABLE FILE REPORTING_ALTERNATIVE
PRINT
COMPUTE TSITEXT/A200V = IF TSI EQ LAST TSI AND SCHOOL_ID EQ LAST SCHOOL_ID AND SCHOOL_YEAR EQ LAST SCHOOL_YEAR AND TSI EQ 1 THEN TSITEXT |', '|REPORTING_ALTERNATIVE.REPORTING_ESSA.SUBGROUP
ELSE IF TSI EQ 1 THEN REPORTING_ALTERNATIVE.REPORTING_ESSA.SUBGROUP ELSE 'N/A';
BY TSI
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_TSI1
END

TABLE FILE PH_TSI1
SUM TSITEXT
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_TSI
END

TABLE FILE REPORTING_ALTERNATIVE
PRINT
COMPUTE ATSTEXT/A200V = IF ATS EQ LAST ATS AND SCHOOL_ID EQ LAST SCHOOL_ID AND ATS EQ 1 THEN ATSTEXT |', '|REPORTING_ALTERNATIVE.REPORTING_ESSA.SUBGROUP
ELSE IF ATS EQ 1 THEN 'Identified' |': '| REPORTING_ALTERNATIVE.REPORTING_ESSA.ATS_YEAR_IDENTIFIED ELSE 'N/A';
BY ATS
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_ATS1
END

TABLE FILE PH_ATS1
SUM ATSTEXT
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_ATS
END

TABLE FILE REPORTING_ALTERNATIVE
PRINT
COMPUTE EXIT1EXT/A200V = IF SCHOOL_ID EQ LAST SCHOOL_ID AND SCHOOL_YEAR EQ LAST SCHOOL_YEAR AND MET_FIRST_YEAR_EXIT NE '' THEN EXIT1EXT |', '|REPORTING_ALTERNATIVE.REPORTING_ESSA.MET_FIRST_YEAR_EXIT
ELSE IF MET_FIRST_YEAR_EXIT NE '' THEN REPORTING_ALTERNATIVE.REPORTING_ESSA.MET_FIRST_YEAR_EXIT ELSE 'N/A';
BY EXIT1EXT
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_EXIT1EXT
END

TABLE FILE PH_EXIT1EXT
SUM EXIT1EXT
BY DISTRICT_NAME
BY SCHOOL_NAME
ON TABLE HOLD AS PH_EXIT1EXT
END

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_TSI AS J04;

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_ATS AS J05;

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_CSI AS J055;

JOIN LEFT_OUTER DISTRICT_NAME AND SCHOOL_NAME IN PRIMARY_HOLD TO DISTRICT_NAME AND SCHOOL_NAME IN PH_EXIT1EXT AS J066;

DEFINE FILE PRIMARY_HOLD
HEAD1/A100V = 'District Name: ';
HEAD11/A100V = DISTRICT_NAME;
HEAD2/A100V = 'School Name: ';
HEAD21/A100V = SCHOOL_NAME;
HEAD3/A100V = 'Grades Served: ';
HEAD31/A100V = GRADES_SERVED;
HEAD4/A100V = 'Enrollment: ';
HEAD41/A100V = TRIM_(LEADING, ' ',FTOA(ENROLLMENT, '(F11)', 'A11'));
FOOT1/A100V = 'WAEA School Performance Level = ';
FOOT11/A100V= SPL;
FOOT2/A100V = 'WAEA Weighted Average Indicator Score = ';
FOOT21/P6.1 = ALTERNATIVE_WEIGHTED_SCORE_FINAL;
FOOT22/A20V = '(Cut Scores = ';
FOOT23/P6.1 = ALT_WAEA_CUT1;
FOOT24/A2V = ';';
FOOT25/P6.1 = ALT_WAEA_CUT2;
FOOT26/A2V = ';';
FOOT27/P6.1 = ALT_WAEA_CUT3;
FOOT28/A2V = ')';
FOOT29/A150V = '';
FOOT3/A100V = 'ESSA School Performance Level = ';
FOOT31/A100V = IF (CLASS EQ 'Under Review' AND (CSITEXT NE '' OR TSITEXT NE '' OR ATSTEXT NE '')) THEN 'Identified' ELSE CLASS;
FOOT301/A100V = IF FOOT31 EQ 'Not Identified' THEN '' ELSE 'Comprehensive Support and Improvement =';
FOOT302/A100V = IF FOOT31 EQ 'Not Identified' THEN '' ELSE IF CLASS EQ 'Unclassified' THEN 'Not Identified' ELSE CSITEXT;
FOOT311/A100V = IF FOOT31 EQ 'Not Identified' THEN '' ELSE 'Targeted Support and Improvement =';
FOOT312/A100V = IF FOOT31 EQ 'Not Identified' THEN '' ELSE IF CLASS EQ 'Unclassified' THEN 'Not Identified' ELSE TSITEXT;
FOOT321/A100V = IF FOOT31 EQ 'Not Identified' THEN '' ELSE 'Additional Targeted Support and Improvement =';
FOOT322/A100V = IF FOOT31 EQ 'Not Identified' THEN '' ELSE IF CLASS EQ 'Unclassified' THEN 'Not Identified' ELSE ATSTEXT;
FOOT4/A100V = 'ESSA Average Indicator Score = ';
FOOT41/P7.1 = AVERAGE_INDICATOR_SCORE_FINAL;
FOOT5/A100V = 'ESSA Average Growth & Achievement Score = ';
FOOT51/A10V = IF AVERAGE_ACHIEVE_GROWTH_CSI EQ 0 THEN 'N/A' ELSE PTOA(AVERAGE_ACHIEVE_GROWTH_CSI,'(P6.1)','A8');
FOOT6/A100V = 'ESSA Graduation Rate = ';
FOOT61/P7.1 = FOUR_YEAR_GRAD_RATE;
FOOT7/A200V = '(ESSA Average Growth & Achievement Scores are only reported for the 5% of Title I schools with the lowest scores)';
FOOT8/A100V = 'Met First Year Exit Criteria = ';
FOOT81/A200V = EXIT1EXT;
COL4/D2= IF SPL EQ 'Exceeding Alternative School Expectations' THEN 0 ELSE IF SPL EQ 'Meeting Alternative School Expectations' THEN 1 ELSE IF SPL EQ 'Partially Meeting Alternative School Expectations' THEN 2 ELSE IF SPL EQ 'Not Meeting Alternative School Expectations' THEN 3 ELSE 4;
END

TABLE FILE PRIMARY_HOLD
PRINT
PRIMARY_HOLD.PRIMARY_.COL4 NOPRINT
HEADING
" <+0> <+0> <+0> "
" " " " " "
" " <+0> <+0> <+0> <+0> <+0> " <+0> <+0> <+0> <+0> <+0> " "
" " <+0> <+0> <+0> " <+0> <+0> <+0> " <+0> <+0> <+0> " <+0> <+0> <+0> <+0> <+0> " <+0> <+0> <+0> <+0> <+0> " <+0> <+0> <+0> <+0> <+0> " <+0> <+0> <+0> <+0> <+0> "" <+0> <+0> <+0> "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/WFC/Repository/Common/style/Wyoming.sty,
$
TYPE=HEADING,
BORDER-TOP=MEDIUM,
BORDER-BOTTOM=MEDIUM,
BORDER-LEFT=MEDIUM,
BORDER-RIGHT=MEDIUM,
BORDER-TOP-COLOR='BLACK',
BORDER-BOTTOM-COLOR='BLACK',
BORDER-LEFT-COLOR='BLACK',
BORDER-RIGHT-COLOR='BLACK',
SIZE=8,
$
TYPE=HEADING,
LINE=7,
SIZE=8,
$
TYPE=HEADING,
LINE=8,
SIZE=8,
$
TYPE=HEADING,
LINE=11,
SIZE=8,
$
TYPE=HEADING,
LINE=12,
SIZE=8,
$
TYPE=HEADING,
LINE=13,
SIZE=8,
$
TYPE=HEADING,
LINE=14,
SIZE=8,
$
TYPE=HEADING,
LINE=15,
SIZE=8,
$
TYPE=HEADING,
LINE=16,
SIZE=8,
$
TYPE=HEADING,
LINE=17,
SIZE=8,
$
TYPE=HEADING,
LINE=18,
SIZE=8,
$
TYPE=HEADING,
LINE=19,
SIZE=8,
$
TYPE=REPORT,
BACKCOLOR=RGB(0 161 224),
WHEN=N1 EQ 0,
$
TYPE=REPORT,
BACKCOLOR='LIME',
WHEN=N1 EQ 1,
$
TYPE=REPORT,
BACKCOLOR='YELLOW',
WHEN=N1 EQ 2,
$
TYPE=REPORT,
BACKCOLOR='ORANGE',
WHEN=N1 EQ 3,
$
TYPE=REPORT,
BACKCOLOR=RGB(211 211 211),
WHEN=N1 EQ 5,
$
ENDSTYLE
END
>


WebFOCUS 8
Windows, All Outputs
I would try using fixed charachter instead of the Variable, so A200 instead of A200V.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.