September 01, 2005, 09:37 PM
LeahOkay, it takes counting, so this may or may not work for you.
TABLE FILE CAR
SUM CAR.BODY.RETAIL_COST
BY CAR.COMP.CAR
ACROSS CAR.ORIGIN.COUNTRY
ACROSS CAR.BODY.SEATS
ON TABLE SET STYLE *
TYPE=REPORT,
GRID=ON, ORIENTATION = LANDSCAPE,FONT=COURIER,SIZE=8, $
$
TYPE=DATA, ACROSSCOLUMN=CAR.BODY.RETAIL_COST,
FONT=COURIER,
$
TYPE=TITLE, ACROSSCOLUMN=CAR.BODY.RETAIL_COST,
FONT=COURIER,
$
TYPE=DATA, COLUMN=CAR.COMP.CAR,
SIZE=14,
$
TYPE=TITLE, COLUMN=CAR.COMP.CAR,
FONT=COURIER,
SIZE=14,
$
TYPE = DATA, COLUMN = 11 ,COLOR = GREEN,BACKCOLOR=YELLOW, $
TYPE = GRANDTOTAL, COLUMN = 11 ,COLOR = GREEN,BACKCOLOR=YELLOW, $
ENDSTYLE
ON TABLE PCHOLD FORMAT PDF
ON TABLE ROW-TOTAL
ON TABLE COLUMN-TOTAL
END
September 12, 2005, 09:56 PM
focusqueenOk. This program is in three pieces. The first is the following. I've had to take out the htmlform section that actually displays the report because I couldn't submit the form with it in there.
-* Program: STUpStat.fex
-* Location: wfsrv1/admissions
-* Author: Sue Ours
-* Date: June 6, 2005
-*
-* Launch Page: STUpStat.htm
-*
-* Input: Term (multiple ones)
-* Year, Month and Day of activity date for each term selected
-*
-* Included: STUpStat_Style.fex (style sheet for output)
-* STUpStat_Middle.fex (Sum and Where statement for report.
-* Repeated for all 4 student types.)
-*
-* This report creates a statistical report for Upland comparing each of the
-* selected terms for the activity date selected for each term.
-**************************************************************************
-* Combining the year, month and date fields together for each activity date.
-* There will be at least two dates. Checks to see if there are more than that
-* before trying to create the combined date fields.
-SET &DATE1 = &AYear|| &AMonth || &ADay;
-SET &DATE2 = &BYear || &BMonth || &BDay;
-IF &TERMNUM EQ '2' THEN GOTO L$DONEDATES;
-SET &DATE3 = &CYear || &CMonth || &CDay;
-IF &TERMNUM EQ '3' THEN GOTO L$DONEDATES;
-SET &DATE4 = &DYear || &DMonth || &DDay;
-IF &TERMNUM EQ '4' THEN GOTO L$DONEDATES;
-SET &DATE5 = &EYear || &EMonth || &EDay;
-L$DONEDATES
-* Setting page up so there won't be any page breaks or line numbers.
SET PAGE=NOPAGE
SET LINES=99999
-* Combining all of the tables needed from the admissions datamart.
JOIN CLEAR
JOIN PROSPECT_KEY IN FACT_ADMISSIONS TO PROSPECT_KEY IN DIM_PROSPECT AS J1
JOIN TERM_KEY IN FACT_ADMISSIONS TO TERM_KEY IN DIM_TERM AS J2
JOIN CAMPUS_KEY IN FACT_ADMISSIONS TO CAMPUS_KEY IN DIM_CAMPUS AS J3
JOIN ADMISSIONS_DECISION_CODE_KEY IN FACT_ADMISSIONS TO
ADMISSIONS_DECISION_CODE_KEY IN DIM_ADMISSIONS_DECISION_CODE AS J4
END
JOIN PROSPECT_PROFILE_KEY IN FACT_ADMISSIONS TO
PROSPECT_PROFILE_KEY IN DIM_PROSPECT_PROFILE AS J5
END
JOIN APPLICATION_DATE_KEY IN FACT_ADMISSIONS TO
APPLICATION_DATE_KEY IN DIM_APPLICATION_DATE AS J6
END
JOIN PROSPECT_ADDRESS_KEY IN FACT_ADMISSIONS TO
PROSPECT_ADDRESS_KEY IN DIM_PROSPECT_ADDRESS AS J7
END
JOIN BEGIN_DATE_KEY IN FACT_ADMISSIONS TO
BEGIN_DATE_KEY IN DIM_BEGIN_DATE AS J8
END
JOIN END_DATE_KEY IN FACT_ADMISSIONS TO
END_DATE_KEY IN DIM_END_DATE AS J9
END
-* Defining calculations for the statistical report. These will be summed
-* below for each student type.
DEFINE FILE FACT_ADMISSIONS
INQUIRED/I5 = 1;
INPRO/I4 = (DECISION_CODE EQ 'IP' OR 'HD');
GENERAL/I4 = (DECISION_CODE EQ 'GN');
FILEC/I4 = (DECISION_CODE EQ 'FC' OR 'FI');
REVIEW/I4 = (DECISION_CODE EQ 'RV');
QUAL/I4 = (DECISION_CODE EQ 'QP' OR 'QU');
APPLIED/I4 = (APPLICATION_SHORT_DATE NE ' ');
ACCEPTED/I4 = (DECISION_CODE EQ 'AC' OR 'AN' OR 'AP' OR 'PN' OR
'PP' OR 'PD' OR 'NG');
DENIED/I4 = (DECISION_CODE EQ 'DN');
PAID/I4 = (DECISION_CODE EQ 'PD' OR 'PN' OR 'PP');
CANCACC/I4 = (DECISION_CODE EQ 'CA' OR 'CP');
CANCPD/I4 = (DECISION_CODE EQ 'CP');
CANCNOAC/I4 = (DECISION_CODE EQ 'CN' OR 'CW' OR 'C1' OR 'C2' OR 'C3');
CANCWAIT/I4 = (DECISION_CODE EQ 'CW' OR 'C1' OR 'C2' OR 'C3');
WAIT_POOL/I4 = (DECISION_CODE EQ 'WP' OR 'W2');
-* CAMP_LDT/A10 = HCNVRT(UP_CAMP_VISIT_DATE,'(HYYMD)',10,'A10');
-* CAMP_LDT2/A8 = EDIT(CAMP_LDT,'9999$99$99');
INTV_LDT/A10 = HCNVRT(UP_INTERVIEW_DATE,'(HYYMD)',10,'A10');
INTV_LDT2/A8 = EDIT(INTV_LDT,'9999$99$99');
-* UVIS/I4 = (CAMP_LDT2 GT '0');
INTV/I4 = (INTV_LDT2 GT '0') AND (DECISION_CODE EQ '00');
SEX/A6 = DECODE GENDER (M Male F Female ELSE 'Needed');
END
-* Creating Report for Freshman Prospective Students.
TABLE FILE FACT_ADMISSIONS
ON TABLE SET PAGE-NUM OFF
HEADING
"Freshman Prospect Students"
-INCLUDE STUpStat_Middle
WHERE STUDENT_TYPE_CODE EQ 'F'
ON TABLE SET HTMLCSS ON
-INCLUDE STUpStat_Style
ON TABLE HOLD AS FRESHRPT FORMAT HTMLTABLE
END
-RUN
-* Creating Report for Transfer Prospective Students.
TABLE FILE FACT_ADMISSIONS
ON TABLE SET PAGE-NUM OFF
HEADING
"Transfer Prospect Students"
-INCLUDE STUpStat_Middle
WHERE STUDENT_TYPE_CODE EQ 'T'
ON TABLE SET HTMLCSS ON
-INCLUDE STUpStat_Style
ON TABLE HOLD AS TRANSRPT FORMAT HTMLTABLE
END
-RUN
-* Creating Report for Guest Prospective Students.
TABLE FILE FACT_ADMISSIONS
ON TABLE SET PAGE-NUM OFF
HEADING
"Guest Prospect Students"
-INCLUDE STUpStat_Middle
WHERE STUDENT_TYPE_CODE EQ 'G'
ON TABLE SET HTMLCSS ON
-INCLUDE STUpStat_Style
ON TABLE HOLD AS GUESTRPT FORMAT HTMLTABLE
END
-RUN
-* Creating Report for ReAdmit Prospective Students.
TABLE FILE FACT_ADMISSIONS
ON TABLE SET PAGE-NUM OFF
HEADING
"ReAdmit Prospect Students"
-INCLUDE STUpStat_Middle
WHERE STUDENT_TYPE_CODE EQ 'E'
ON TABLE SET HTMLCSS ON
-INCLUDE STUpStat_Style
ON TABLE HOLD AS READMRPT FORMAT HTMLTABLE
END
-RUN
-* Displays all four sections of the report in HTML format. Each
-* section is displayed one at a time starting with the freshman
-* page. There are buttons at the top that allow you to select
-* the other 3 student types.
***This is where the htmlform went. If you need to see this, let me know where I can send you the full code.
The program above calls STUpStat_Middle.fex. This is the code for this part.
-* Program: STUpStat_Middle.fex
-* Location: wfsrv1/admissions
-* Author: Sue Ours
-* Date: June 6, 2005
-*
-* This code is included four times in the STUpStat.fex program.
-* It is used to find the correct records for the statistical
-* for the four student types that appear on the report.
-**************************************************************************
SUM INQUIRED AS 'Inquired' OVER
GENERAL AS 'General File' OVER
INTV AS 'Interviewed' OVER
INPRO AS 'In Process' OVER
FILEC AS 'File Complete' OVER
REVIEW AS 'Review' OVER
DENIED AS 'Denied' OVER
CANCNOAC AS 'Can/Not Acc' OVER
CANCACC AS 'Can/Acc' OVER
CANCPD AS 'Can/Paid' OVER
WAIT_POOL AS 'Waiting Pool' OVER
CANCWAIT AS 'Can/Wait Pool' OVER
QUAL AS 'Qualified Status' OVER
ACCEPTED AS ' Accepted' OVER
PAID AS 'Paid' OVER
APPLIED AS 'Applied'
ACROSS HIGHEST TERM_CODE AS ''
ACROSS LOWEST SEX AS '' ACROSS-TOTAL AS 'Total'
WHERE (((TERM_CODE EQ '&TERM1') AND (BEGIN_NUMERIC_DATE LE '&DATE1')
AND (END_NUMERIC_DATE GT '&DATE1'))
OR
((TERM_CODE EQ '&TERM2') AND (BEGIN_NUMERIC_DATE LE '&DATE2')
AND (END_NUMERIC_DATE GT '&DATE2'))
-IF &TERMNUM EQ '2' THEN GOTO L$DONEWHERE1;
OR
((TERM_CODE EQ '&TERM3') AND (BEGIN_NUMERIC_DATE LE '&DATE3')
AND (END_NUMERIC_DATE GT '&DATE3'))
-IF &TERMNUM EQ '3' THEN GOTO L$DONEWHERE1;
OR
((TERM_CODE EQ '&TERM4') AND (BEGIN_NUMERIC_DATE LE '&DATE4')
AND (END_NUMERIC_DATE GT '&DATE4'))
-IF &TERMNUM EQ '4' THEN GOTO L$DONEWHERE1;
OR
((TERM_CODE EQ '&TERM5') AND (BEGIN_NUMERIC_DATE LE '&DATE5')
AND (END_NUMERIC_DATE GT '&DATE5'))
-L$DONEWHERE1
)
WHERE CAMPUS_CODE EQ 'U'
WHERE GENDER EQ 'M' OR 'F'
The first program also calls STUpStat_Style.fex. This is the code for that one.
-* Program: STUpStat_Style.fex
-* Location: wfsrv1/admissions
-* Author: Sue Ours
-* Date: June 6, 2005
-*
-* This code sets the style for the Upland Statistical report. It is
-* called 4 times in the STUpStat.fex program for each student type.
-**************************************************************************
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
PAGECOLOR=RGB(204 204 255),
$
TYPE=REPORT,
GRID=ON,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
WRAP='OFF',
$
TYPE=HEADING,
SIZE=14,
COLOR='MAROON',
STYLE=BOLD+ITALIC,
JUSTIFY=CENTER,
$
TYPE=TITLE,
BACKCOLOR=RGB(240 240 255),
WRAP='OFF',
$
TYPE=ACROSSVALUE,
BACKCOLOR=RGB(240 240 255),
$
TYPE=ACROSSVALUE,
ACROSS=1,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=ACROSSVALUE,
ACROSS=2,
COLOR='MAROON',
JUSTIFY=RIGHT,
$
ENDSTYLE
Thanks for your help.
September 12, 2005, 11:31 PM
susannahhere you go.
on the car file, given that we'll be doing "ACROSS COUNTRY ACROSS MODEL" and say we don't know how many of either that we have.
SET ASNAMES=ON
SET HOLDLIST = PRINTONLY
-* count how many of variable 2 are in each variable 1
TABLE FILE CAR
SUM CNT.DST.MODEL AS NUM BY COUNTRY NOPRINT ON TABLE HOLD
END
-RUN
-* count how many variable 1 we have
-SET &HOWMANY = &LINES;
DEFINE FILE HOLD
-* get the column number for each variable 2 acrosstotal
-* remembering to accumulate them and account for the total column itself
NUM2/I5 = 1 + NUM + LAST NUM2 ;
ANUM/A5 = EDIT(NUM2);
-* takes those blasted leading zeros out of the edit conversion
BNUM/A6 ='C' | TRIM('L', ANUM, 5,'0',1,'A5');
END
TABLE FILE HOLD PRINT BNUM ON TABLE HOLD AS HOWMANY
END
-RUN
-SET &MYCOLNUM = ' ' ;
-SET &KOUNTER = 0 ;
-REPEAT end.loop &HOWMANY TIMES
-SET &KOUNTER=&KOUNTER + 1 ;
-SET &CN&KOUNTER= 0;
-READ HOWMANY &MYCOLNUM.A6
-SET &CN.&KOUNTER = &MYCOLNUM ;
-*TYPE &CN.&KOUNTER
-end.loop
-SET &KOUNTER = 0 ;
TABLE FILE CAR
SUM SEATS
ACROSS COUNTRY ACROSS MODEL ACROSS-TOTAL
ON TABLE SET STYLE *
-*(here's a slight variation on the k.lane idea)
-REPEAT end.loop.2 &HOWMANY TIMES
-SET &KOUNTER = &KOUNTER + 1 ;
-SET &COL = &CN.&KOUNTER ;
TYPE=DATA,COLUMN= &COL ,COLOR=RED,$
-end.loop.2
ENDSTYLE
END
-* interestingly, TYPE=TITLE won't work.
if you want to color both the title and the data, TYPE=REPORT, COLUMN= &COL , works.
Maybe someone else can tell us how to get TYPE=TITLE to work, as well.