Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Excel and PDF/html output setting

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Excel and PDF/html output setting
 Login/Join
 
Platinum Member
posted
I have a report that sometimes the user will run to PDF or html and other times they will want to run it to excel for sorting. I am wondering if there is some coding or line that I can include in my .fex that will make it so if the output is to excel, then the BYDISPLAY will be turned to ON for easy sorting in excel but if the output is to PDF or excel then the BYDISPLAY will be off so that the output is displayed nicely and without the repeating sort values.

Thanks!
Malinda

This message has been edited. Last edited by: Malinda,


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
  
-SET &BY_DISP = IF &FMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'BYDISPLAY = ON';
SET ASNAMES = ON, LINES = 998, NODATA = ' ', &BY_DISP, SPACES = 1
-RUN


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
Yes how cool! Do I put that right after my

ON TABLE PCHOLD FORMAT &WFFMT

??

malinda


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
Hi Malinda,

No, put it at the top of the fex, and, you may need to add this:
  
-DEFAULTS &WFFMT = 'HTML'
-SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'BYDISPLAY = ON';
SET ASNAMES = ON, LINES = 998, NODATA = ' ', &BY_DISP, SPACES = 1
-RUN

I don't know if you are PROMPTing are bringing the values in via a HTML Form?

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
I have 1 parameter that you select the values from the box but it is not in a html form. But if I do set defaults but not using the html form do I need to include that line?

malinda


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
Wrap your PROMPT code within the RED </> so I can see how you are PROMPTing..

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
quote:
-SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'BYDISPLAY = ON';
SET ASNAMES = ON, LINES = 998, NODATA = ' ', &BY_DISP, SPACES = 1
-RUN


I tried this and it runs okay to html and pdf but when I run it to excel 2000 it is still not repeating my values?


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Platinum Member
posted Hide Post
 WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM EQ &STRM.(OR(FIND STRM,CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM IN csprdsc_class_tbl_se_vw)).Select Term EQ.;
 


This is the only prompt I have, everything else is hard coded.


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
Malinda,

How do they select a format?


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
Sorry, it my last table request there is this code:

 ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>,<Excel Formula,EXL2K FORMULA>,<HTML Active Report,AHTML>,<Active Report Flash/Flex,FLEX>,<PowerPoint,PPT>).Select type of display output.
 


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
Here is an example:

  
-DEFAULTS &WFFMT = 'HTML'
-*****************************************************************************
-PROMPT &WFFMT.(<HTML,HTML>,<Active Report,AHTML><PDF,PDF>,<Excel,EXL2K>,<PowerPoint,PPT>).Select Output Format.
-PROMPT &STRM.(OR(FIND STRM,CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM IN csprdsc_class_tbl_se_vw)).Select Term.

-SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'BYDISPLAY = ON';
SET ASNAMES = ON, LINES = 998, NODATA = ' ', &BY_DISP, SPACES = 1
-RUN
DEFINE FILE XXXXXX
. 
END
TABLE FILE XXXXX
SUM/PRINT
.
.
  BY 
  BY 
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM EQ &STRM;
  ON TABLE PCHOLD FORMAT &WFFMT
  ON TABLE SET STYLE *
ETC..

ENDSTYLE
END
-EXIT



Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
Slight modification to Tom's suggestion at the top should do it.

 
-SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'BYDISPLAY = ON';
SET ASNAMES = ON, LINES = 998, NODATA = ' ', &BY_DISP.EVAL, SPACES = 1
-RUN
 


Sandeep Mamidenna


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Tom and BlueZone,

neither of those worked when running to excel it still did not repeat my sort values......

Malinda


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
Malinda,

1. Take out the -DEFAULTS
2. Put this at the top, like so:

 
-SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'BYDISPLAY = ON';
-? &
-EXIT


Select EXCEL. Click RUN - What is the Value for &WFFMT and &BY_DISP?


Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
&BY_DISP shows BYDISPLAY = ON
&WFFMT shows EXL2K

strange


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
Now show me your SET command and where it is.

Forget that, just put ALL your code within the RED code tags...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
 -SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'BYDISPLAY = ON';
-? &
-EXIT
-*Pull all students enrolled and their course info for the term
DEFINE FILE CSPRDSC_CLASS_TBL_SE_VW
COURSE_ID/A25=CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.SUBJECT || CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CATALOG_NBR | ' ' | CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_SECTION;
CLASS_NBR_A/A5=EDIT ( CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_NBR, '99999');
CLASS_KEY/A20=CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM|| CLASS_NBR_A|| CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.EMPLID;
END
TABLE FILE CSPRDSC_CLASS_TBL_SE_VW
PRINT
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_NBR'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.EMPLID'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.ACAD_CAREER'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.ACAD_GROUP'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.COURSE_ID'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.SESSION_CODE'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STDNT_ENRL_STATUS'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CRSE_GRADE_INPUT'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CRSE_GRADE_OFF'
BY 'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_KEY'
HEADING
""
FOOTING
""
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM EQ &STRM.(OR(FIND STRM,CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM IN csprdsc_class_tbl_se_vw)).Select Term EQ.;
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STDNT_ENRL_STATUS EQ 'E';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL FORMAT FOCUS INDEX 'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_KEY'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Create a hold file of all students and all classes with their midterm grades from midterm grade roster.
JOIN
 CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM
 AND CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CLASS_NBR
 AND CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.GRD_RSTR_TYPE_SEQ IN
CSPRDSC_GRADE_ROSTER TO MULTIPLE
 CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.STRM
 AND CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.CLASS_NBR
 AND CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.GRD_RSTR_TYPE_SEQ
 IN CSPRDSC_GRADE_RSTR_TYPE TAG J0 AS J0
 END
DEFINE FILE CSPRDSC_GRADE_ROSTER
CLASS_NBR_A/A5=EDIT ( CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CLASS_NBR, '99999');
MID_GRD_KEY/A20=CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM|| CLASS_NBR_A|| CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.EMPLID;
END
TABLE FILE CSPRDSC_GRADE_ROSTER
PRINT
     'J0.CSPRDSC_GRADE_RSTR_TYPE.STRM'
     'J0.CSPRDSC_GRADE_RSTR_TYPE.CLASS_NBR'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.EMPLID'
     'J0.CSPRDSC_GRADE_RSTR_TYPE.GRADE_ROSTER_TYPE'
     'J0.CSPRDSC_GRADE_RSTR_TYPE.GRD_RSTR_TYPE_SEQ'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.ACAD_CAREER'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.LAST_NAME_SRCH'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.FIRST_NAME_SRCH'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CRSE_GRADE_INPUT'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.GRADE_ROSTER_STAT'
BY 'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.MID_GRD_KEY'
HEADING
""
FOOTING
""
WHERE CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.INSTITUTION EQ 'CSC01';
WHERE J0.CSPRDSC_GRADE_RSTR_TYPE.GRADE_ROSTER_TYPE EQ 'MID';
WHERE CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM EQ &STRM;
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCMID FORMAT FOCUS INDEX 'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.MID_GRD_KEY'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Join enrl file with midterm grade file to get enrl students and their midterm (if exists) and final grade for each course
JOIN
 LEFT_OUTER CSCENRL.SEG01.CLASS_KEY IN CSCENRL TO MULTIPLE
 CSCMID.SEG01.MID_GRD_KEY IN CSCMID TAG J1 AS J1
 END
DEFINE FILE CSCENRL
MIDTERM_GRADE/A3=IF (CSCENRL.SEG01.STDNT_ENRL_STATUS EQ 'E' AND CSCENRL.SEG01.CRSE_GRADE_OFF EQ 'W') THEN 'W' ELSE J1.SEG01.CRSE_GRADE_INPUT;
END
TABLE FILE CSCENRL
PRINT
     'CSCENRL.SEG01.ACAD_CAREER'
     'CSCENRL.SEG01.ACAD_GROUP'
     'CSCENRL.SEG01.COURSE_ID'
     'CSCENRL.SEG01.SESSION_CODE'
     'CSCENRL.SEG01.STDNT_ENRL_STATUS'
     'J1.SEG01.CRSE_GRADE_INPUT'
     'J1.SEG01.MIDTERM_GRADE'
     'CSCENRL.SEG01.CRSE_GRADE_OFF'
BY 'CSCENRL.SEG01.STRM'
BY 'CSCENRL.SEG01.CLASS_NBR'
BY 'CSCENRL.SEG01.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL1 FORMAT FOCUS INDEX 'CSCENRL.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Find the main, highest effdated advisor listed for a student (advisor number 1)
JOIN
 LEFT_OUTER CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ID IN
CSPRDSC_STDNT_ADVR_HIST TO MULTIPLE
 CSPRDSC_PERSONAL_DATA.CSPRDSC_PERSONAL_DATA.EMPLID IN CSPRDSC_PERSONAL_DATA
 TAG J2 AS J2
 END
TABLE FILE CSPRDSC_STDNT_ADVR_HIST
PRINT
     'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EFFDT'
     'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR'
     'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ID'
     'J2.CSPRDSC_PERSONAL_DATA.NAME'
     'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ROLE'
BY 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EMPLID'
BY 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR' NOPRINT
HEADING
""
FOOTING
""
WHERE CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR EQ '1';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE SET FORMULTIPLE ON
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCADV FORMAT FOCUS INDEX 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
TABLE FILE CSCADV
PRINT
     'CSCADV.SEG01.ADVISOR_ID'
     'CSCADV.SEG01.NAME'
     'CSCADV.SEG01.ADVISOR_ROLE'
BY 'CSCADV.SEG01.EMPLID'
BY 'CSCADV.SEG01.STDNT_ADVISOR_NBR'
BY HIGHEST 1 'CSCADV.SEG01.EFFDT'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCADV1 FORMAT FOCUS INDEX 'CSCADV.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Join main file and advisor file
JOIN
 LEFT_OUTER CSCENRL1.SEG01.EMPLID IN CSCENRL1 TO MULTIPLE CSCADV1.SEG01.EMPLID
 IN CSCADV1 TAG J3 AS J3
 END
TABLE FILE CSCENRL1
PRINT
     'CSCENRL1.SEG01.STRM'
     'CSCENRL1.SEG01.CLASS_NBR'
     'CSCENRL1.SEG01.ACAD_CAREER'
     'CSCENRL1.SEG01.COURSE_ID'
     'CSCENRL1.SEG01.SESSION_CODE'
     'CSCENRL1.SEG01.STDNT_ENRL_STATUS'
     'CSCENRL1.SEG01.MIDTERM_GRADE'
     'CSCENRL1.SEG01.CRSE_GRADE_OFF'
     'J3.SEG01.ADVISOR_ID'
     'J3.SEG01.NAME'
BY 'CSCENRL1.SEG01.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL2 FORMAT FOCUS INDEX 'CSCENRL1.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Add student name (use name here instead of from roster - if student doesn't have midterm grade then name will be missing cause of how doing join)
JOIN
 LEFT_OUTER CSCENRL2.SEG01.EMPLID IN CSCENRL2 TO MULTIPLE CSPRDSC_PERSONAL_DATA.CSPRDSC_PERSONAL_DATA.EMPLID
 IN CSPRDSC_PERSONAL_DATA TAG J4 AS J4
END
TABLE FILE CSCENRL2
PRINT
     'J4.CSPRDSC_PERSONAL_DATA.LAST_NAME'
     'J4.CSPRDSC_PERSONAL_DATA.FIRST_NAME'
     'CSCENRL2.SEG01.STRM'
     'CSCENRL2.SEG01.ACAD_CAREER'
     'CSCENRL2.SEG01.CLASS_NBR'
     'CSCENRL2.SEG01.COURSE_ID'
     'CSCENRL2.SEG01.SESSION_CODE'
     'CSCENRL2.SEG01.MIDTERM_GRADE'
     'CSCENRL2.SEG01.CRSE_GRADE_OFF'
BY 'CSCENRL2.SEG01.ADVISOR_ID'
BY 'CSCENRL2.SEG01.NAME'
BY 'CSCENRL2.SEG01.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL3 FORMAT FOCUS INDEX 'CSCENRL2.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
DEFINE FILE CSPRDSC_SCC_EMAIL_QVW
CSCC_EMAIL/A70=IF CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.E_ADDR_TYPE EQ 'CSCC' THEN CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMAIL_ADDR ELSE ' ';
END
TABLE FILE CSPRDSC_SCC_EMAIL_QVW
SUM
     'MAX.CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.CSCC_EMAIL'
 AS 'CSCC_EMAIL'
BY 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCEMAIL FORMAT FOCUS INDEX 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N2,
     WRAP=6.000000,
$
ENDSTYLE
END
JOIN
 LEFT_OUTER CSCENRL3.SEG01.EMPLID IN CSCENRL3 TO MULTIPLE CSCEMAIL.SEG01.EMPLID
 IN CSCEMAIL TAG J5 AS J5
END
TABLE FILE CSCENRL3
PRINT
     'CSCENRL3.SEG01.LAST_NAME'
     'CSCENRL3.SEG01.FIRST_NAME'
     'CSCENRL3.SEG01.STRM'
     'CSCENRL3.SEG01.ACAD_CAREER'
     'CSCENRL3.SEG01.CLASS_NBR'
     'CSCENRL3.SEG01.COURSE_ID'
     'CSCENRL3.SEG01.SESSION_CODE'
     'CSCENRL3.SEG01.MIDTERM_GRADE' AS 'Midterm'
     'CSCENRL3.SEG01.CRSE_GRADE_OFF' AS 'Final'
     'J5.SEG01.CSCC_EMAIL'
BY 'CSCENRL3.SEG01.ADVISOR_ID'
BY 'CSCENRL3.SEG01.NAME'
BY 'CSCENRL3.SEG01.EMPLID'
BY 'CSCENRL3.SEG01.COURSE_ID' NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>,<Excel Formula,EXL2K FORMULA>,<HTML Active Report,AHTML>,<Active Report Flash/Flex,FLEX>,<PowerPoint,PPT>).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N14,
     WRAP=6.000000,
$
ENDSTYLE
END
 


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
Malinda,

Changes in RED:


-SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'ON TABLE SET BYDISPLAY = ON';
-*Pull all students enrolled and their course info for the term
DEFINE FILE CSPRDSC_CLASS_TBL_SE_VW
COURSE_ID/A25=CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.SUBJECT || CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CATALOG_NBR | ' ' | CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_SECTION;
CLASS_NBR_A/A5=EDIT ( CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_NBR, '99999');
CLASS_KEY/A20=CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM|| CLASS_NBR_A|| CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.EMPLID;
END
TABLE FILE CSPRDSC_CLASS_TBL_SE_VW
PRINT
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM'
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_NBR'
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.EMPLID'
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.ACAD_CAREER'
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.ACAD_GROUP'
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.COURSE_ID'
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.SESSION_CODE'
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STDNT_ENRL_STATUS'
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CRSE_GRADE_INPUT'
'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CRSE_GRADE_OFF'
BY 'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_KEY'
HEADING
""
FOOTING
""
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM EQ &STRM.(OR(FIND STRM,CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM IN csprdsc_class_tbl_se_vw)).Select Term EQ.;
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STDNT_ENRL_STATUS EQ 'E';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
NO NEED TO DO IT HERE - IT IS A HOLD FILE
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL FORMAT FOCUS INDEX 'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_KEY'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
-*Create a hold file of all students and all classes with their midterm grades from midterm grade roster.
JOIN
CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM
AND CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CLASS_NBR
AND CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.GRD_RSTR_TYPE_SEQ IN
CSPRDSC_GRADE_ROSTER TO MULTIPLE
CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.STRM
AND CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.CLASS_NBR
AND CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.GRD_RSTR_TYPE_SEQ
IN CSPRDSC_GRADE_RSTR_TYPE TAG J0 AS J0
END
DEFINE FILE CSPRDSC_GRADE_ROSTER
CLASS_NBR_A/A5=EDIT ( CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CLASS_NBR, '99999');
MID_GRD_KEY/A20=CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM|| CLASS_NBR_A|| CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.EMPLID;
END
TABLE FILE CSPRDSC_GRADE_ROSTER
PRINT
'J0.CSPRDSC_GRADE_RSTR_TYPE.STRM'
'J0.CSPRDSC_GRADE_RSTR_TYPE.CLASS_NBR'
'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.EMPLID'
'J0.CSPRDSC_GRADE_RSTR_TYPE.GRADE_ROSTER_TYPE'
'J0.CSPRDSC_GRADE_RSTR_TYPE.GRD_RSTR_TYPE_SEQ'
'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.ACAD_CAREER'
'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.LAST_NAME_SRCH'
'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.FIRST_NAME_SRCH'
'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CRSE_GRADE_INPUT'
'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.GRADE_ROSTER_STAT'
BY 'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.MID_GRD_KEY'
HEADING
""
FOOTING
""
WHERE CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.INSTITUTION EQ 'CSC01';
WHERE J0.CSPRDSC_GRADE_RSTR_TYPE.GRADE_ROSTER_TYPE EQ 'MID';
WHERE CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM EQ &STRM;
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
NO NEED TO DO IT HERE - IT IS A HOLD FILE
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCMID FORMAT FOCUS INDEX 'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.MID_GRD_KEY'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
-*Join enrl file with midterm grade file to get enrl students and their midterm (if exists) and final grade for each course
JOIN
LEFT_OUTER CSCENRL.SEG01.CLASS_KEY IN CSCENRL TO MULTIPLE
CSCMID.SEG01.MID_GRD_KEY IN CSCMID TAG J1 AS J1
END
DEFINE FILE CSCENRL
MIDTERM_GRADE/A3=IF (CSCENRL.SEG01.STDNT_ENRL_STATUS EQ 'E' AND CSCENRL.SEG01.CRSE_GRADE_OFF EQ 'W') THEN 'W' ELSE J1.SEG01.CRSE_GRADE_INPUT;
END
TABLE FILE CSCENRL
PRINT
'CSCENRL.SEG01.ACAD_CAREER'
'CSCENRL.SEG01.ACAD_GROUP'
'CSCENRL.SEG01.COURSE_ID'
'CSCENRL.SEG01.SESSION_CODE'
'CSCENRL.SEG01.STDNT_ENRL_STATUS'
'J1.SEG01.CRSE_GRADE_INPUT'
'J1.SEG01.MIDTERM_GRADE'
'CSCENRL.SEG01.CRSE_GRADE_OFF'
BY 'CSCENRL.SEG01.STRM'
BY 'CSCENRL.SEG01.CLASS_NBR'
BY 'CSCENRL.SEG01.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL1 FORMAT FOCUS INDEX 'CSCENRL.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
-*Find the main, highest effdated advisor listed for a student (advisor number 1)
JOIN
LEFT_OUTER CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ID IN
CSPRDSC_STDNT_ADVR_HIST TO MULTIPLE
CSPRDSC_PERSONAL_DATA.CSPRDSC_PERSONAL_DATA.EMPLID IN CSPRDSC_PERSONAL_DATA
TAG J2 AS J2
END
TABLE FILE CSPRDSC_STDNT_ADVR_HIST
PRINT
'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EFFDT'
'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR'
'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ID'
'J2.CSPRDSC_PERSONAL_DATA.NAME'
'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ROLE'
BY 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EMPLID'
BY 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR' NOPRINT
HEADING
""
FOOTING
""
WHERE CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR EQ '1';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
NO NEED TO DO IT HERE - IT IS A HOLD FILE
ON TABLE SET FORMULTIPLE ON
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCADV FORMAT FOCUS INDEX 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
TABLE FILE CSCADV
PRINT
'CSCADV.SEG01.ADVISOR_ID'
'CSCADV.SEG01.NAME'
'CSCADV.SEG01.ADVISOR_ROLE'
BY 'CSCADV.SEG01.EMPLID'
BY 'CSCADV.SEG01.STDNT_ADVISOR_NBR'
BY HIGHEST 1 'CSCADV.SEG01.EFFDT'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
NO NEED TO DO IT HERE - IT IS A HOLD FILE
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCADV1 FORMAT FOCUS INDEX 'CSCADV.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
-*Join main file and advisor file
JOIN
LEFT_OUTER CSCENRL1.SEG01.EMPLID IN CSCENRL1 TO MULTIPLE CSCADV1.SEG01.EMPLID
IN CSCADV1 TAG J3 AS J3
END
TABLE FILE CSCENRL1
PRINT
'CSCENRL1.SEG01.STRM'
'CSCENRL1.SEG01.CLASS_NBR'
'CSCENRL1.SEG01.ACAD_CAREER'
'CSCENRL1.SEG01.COURSE_ID'
'CSCENRL1.SEG01.SESSION_CODE'
'CSCENRL1.SEG01.STDNT_ENRL_STATUS'
'CSCENRL1.SEG01.MIDTERM_GRADE'
'CSCENRL1.SEG01.CRSE_GRADE_OFF'
'J3.SEG01.ADVISOR_ID'
'J3.SEG01.NAME'
BY 'CSCENRL1.SEG01.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL2 FORMAT FOCUS INDEX 'CSCENRL1.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
-*Add student name (use name here instead of from roster - if student doesn't have midterm grade then name will be missing cause of how doing join)
JOIN
LEFT_OUTER CSCENRL2.SEG01.EMPLID IN CSCENRL2 TO MULTIPLE CSPRDSC_PERSONAL_DATA.CSPRDSC_PERSONAL_DATA.EMPLID
IN CSPRDSC_PERSONAL_DATA TAG J4 AS J4
END
TABLE FILE CSCENRL2
PRINT
'J4.CSPRDSC_PERSONAL_DATA.LAST_NAME'
'J4.CSPRDSC_PERSONAL_DATA.FIRST_NAME'
'CSCENRL2.SEG01.STRM'
'CSCENRL2.SEG01.ACAD_CAREER'
'CSCENRL2.SEG01.CLASS_NBR'
'CSCENRL2.SEG01.COURSE_ID'
'CSCENRL2.SEG01.SESSION_CODE'
'CSCENRL2.SEG01.MIDTERM_GRADE'
'CSCENRL2.SEG01.CRSE_GRADE_OFF'
BY 'CSCENRL2.SEG01.ADVISOR_ID'
BY 'CSCENRL2.SEG01.NAME'
BY 'CSCENRL2.SEG01.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL3 FORMAT FOCUS INDEX 'CSCENRL2.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
DEFINE FILE CSPRDSC_SCC_EMAIL_QVW
CSCC_EMAIL/A70=IF CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.E_ADDR_TYPE EQ 'CSCC' THEN CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMAIL_ADDR ELSE ' ';
END
TABLE FILE CSPRDSC_SCC_EMAIL_QVW
SUM
'MAX.CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.CSCC_EMAIL'
AS 'CSCC_EMAIL'
BY 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCEMAIL FORMAT FOCUS INDEX 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N2,
WRAP=6.000000,
$
ENDSTYLE
END
JOIN
LEFT_OUTER CSCENRL3.SEG01.EMPLID IN CSCENRL3 TO MULTIPLE CSCEMAIL.SEG01.EMPLID
IN CSCEMAIL TAG J5 AS J5
END
TABLE FILE CSCENRL3
PRINT
'CSCENRL3.SEG01.LAST_NAME'
'CSCENRL3.SEG01.FIRST_NAME'
'CSCENRL3.SEG01.STRM'
'CSCENRL3.SEG01.ACAD_CAREER'
'CSCENRL3.SEG01.CLASS_NBR'
'CSCENRL3.SEG01.COURSE_ID'
'CSCENRL3.SEG01.SESSION_CODE'
'CSCENRL3.SEG01.MIDTERM_GRADE' AS 'Midterm'
'CSCENRL3.SEG01.CRSE_GRADE_OFF' AS 'Final'
'J5.SEG01.CSCC_EMAIL'
BY 'CSCENRL3.SEG01.ADVISOR_ID'
BY 'CSCENRL3.SEG01.NAME'
BY 'CSCENRL3.SEG01.EMPLID'
BY 'CSCENRL3.SEG01.COURSE_ID' NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
&BY_DISP
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>,<Excel Formula,EXL2K FORMULA>,<HTML Active Report,AHTML>,<Active Report Flash/Flex,FLEX>,<PowerPoint,PPT>Wink.Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N14,
WRAP=6.000000,
$
ENDSTYLE
END

Tom

P.S. Probably won't be able to open this up in the GUI any longer. I don't use the GUI...

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
Still did not work running to excel.....here is the revised code:

 -SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'ON TABLE SET BYDISPLAY ON';
SET ASNAMES = ON, LINES = 998, NODATA = ' ', &BY_DISP, SPACES = 1
-RUN
-*Pull all students enrolled and their course info for the term
DEFINE FILE CSPRDSC_CLASS_TBL_SE_VW
COURSE_ID/A25=CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.SUBJECT || CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CATALOG_NBR | ' ' | CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_SECTION;
CLASS_NBR_A/A5=EDIT ( CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_NBR, '99999');
CLASS_KEY/A20=CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM|| CLASS_NBR_A|| CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.EMPLID;
END
TABLE FILE CSPRDSC_CLASS_TBL_SE_VW
PRINT
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_NBR'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.EMPLID'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.ACAD_CAREER'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.ACAD_GROUP'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.COURSE_ID'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.SESSION_CODE'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STDNT_ENRL_STATUS'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CRSE_GRADE_INPUT'
     'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CRSE_GRADE_OFF'
BY 'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_KEY'
HEADING
""
FOOTING
""
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM EQ &STRM.(OR(FIND STRM,CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STRM IN csprdsc_class_tbl_se_vw)).Select Term EQ.;
WHERE CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.STDNT_ENRL_STATUS EQ 'E';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL FORMAT FOCUS INDEX 'CSPRDSC_CLASS_TBL_SE_VW.CSPRDSC_CLASS_TBL_SE_VW.CLASS_KEY'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Create a hold file of all students and all classes with their midterm grades from midterm grade roster.
JOIN
 CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM
 AND CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CLASS_NBR
 AND CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.GRD_RSTR_TYPE_SEQ IN
CSPRDSC_GRADE_ROSTER TO MULTIPLE
 CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.STRM
 AND CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.CLASS_NBR
 AND CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.GRD_RSTR_TYPE_SEQ
 IN CSPRDSC_GRADE_RSTR_TYPE TAG J0 AS J0
 END
DEFINE FILE CSPRDSC_GRADE_ROSTER
CLASS_NBR_A/A5=EDIT ( CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CLASS_NBR, '99999');
MID_GRD_KEY/A20=CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM|| CLASS_NBR_A|| CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.EMPLID;
END
TABLE FILE CSPRDSC_GRADE_ROSTER
PRINT
     'J0.CSPRDSC_GRADE_RSTR_TYPE.STRM'
     'J0.CSPRDSC_GRADE_RSTR_TYPE.CLASS_NBR'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.EMPLID'
     'J0.CSPRDSC_GRADE_RSTR_TYPE.GRADE_ROSTER_TYPE'
     'J0.CSPRDSC_GRADE_RSTR_TYPE.GRD_RSTR_TYPE_SEQ'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.ACAD_CAREER'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.LAST_NAME_SRCH'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.FIRST_NAME_SRCH'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CRSE_GRADE_INPUT'
     'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.GRADE_ROSTER_STAT'
BY 'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.MID_GRD_KEY'
HEADING
""
FOOTING
""
WHERE CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.INSTITUTION EQ 'CSC01';
WHERE J0.CSPRDSC_GRADE_RSTR_TYPE.GRADE_ROSTER_TYPE EQ 'MID';
WHERE CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM EQ &STRM;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCMID FORMAT FOCUS INDEX 'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.MID_GRD_KEY'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Join enrl file with midterm grade file to get enrl students and their midterm (if exists) and final grade for each course
JOIN
 LEFT_OUTER CSCENRL.SEG01.CLASS_KEY IN CSCENRL TO MULTIPLE
 CSCMID.SEG01.MID_GRD_KEY IN CSCMID TAG J1 AS J1
 END
DEFINE FILE CSCENRL
MIDTERM_GRADE/A3=IF (CSCENRL.SEG01.STDNT_ENRL_STATUS EQ 'E' AND CSCENRL.SEG01.CRSE_GRADE_OFF EQ 'W') THEN 'W' ELSE J1.SEG01.CRSE_GRADE_INPUT;
END
TABLE FILE CSCENRL
PRINT
     'CSCENRL.SEG01.ACAD_CAREER'
     'CSCENRL.SEG01.ACAD_GROUP'
     'CSCENRL.SEG01.COURSE_ID'
     'CSCENRL.SEG01.SESSION_CODE'
     'CSCENRL.SEG01.STDNT_ENRL_STATUS'
     'J1.SEG01.CRSE_GRADE_INPUT'
     'J1.SEG01.MIDTERM_GRADE'
     'CSCENRL.SEG01.CRSE_GRADE_OFF'
BY 'CSCENRL.SEG01.STRM'
BY 'CSCENRL.SEG01.CLASS_NBR'
BY 'CSCENRL.SEG01.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL1 FORMAT FOCUS INDEX 'CSCENRL.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Find the main, highest effdated advisor listed for a student (advisor number 1)
JOIN
 LEFT_OUTER CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ID IN
CSPRDSC_STDNT_ADVR_HIST TO MULTIPLE
 CSPRDSC_PERSONAL_DATA.CSPRDSC_PERSONAL_DATA.EMPLID IN CSPRDSC_PERSONAL_DATA
 TAG J2 AS J2
 END
TABLE FILE CSPRDSC_STDNT_ADVR_HIST
PRINT
     'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EFFDT'
     'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR'
     'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ID'
     'J2.CSPRDSC_PERSONAL_DATA.NAME'
     'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ROLE'
BY 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EMPLID'
BY 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR' NOPRINT
HEADING
""
FOOTING
""
WHERE CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR EQ '1';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCADV FORMAT FOCUS INDEX 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
TABLE FILE CSCADV
PRINT
     'CSCADV.SEG01.ADVISOR_ID'
     'CSCADV.SEG01.NAME'
     'CSCADV.SEG01.ADVISOR_ROLE'
BY 'CSCADV.SEG01.EMPLID'
BY 'CSCADV.SEG01.STDNT_ADVISOR_NBR'
BY HIGHEST 1 'CSCADV.SEG01.EFFDT'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCADV1 FORMAT FOCUS INDEX 'CSCADV.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Join main file and advisor file
JOIN
 LEFT_OUTER CSCENRL1.SEG01.EMPLID IN CSCENRL1 TO MULTIPLE CSCADV1.SEG01.EMPLID
 IN CSCADV1 TAG J3 AS J3
 END
TABLE FILE CSCENRL1
PRINT
     'CSCENRL1.SEG01.STRM'
     'CSCENRL1.SEG01.CLASS_NBR'
     'CSCENRL1.SEG01.ACAD_CAREER'
     'CSCENRL1.SEG01.COURSE_ID'
     'CSCENRL1.SEG01.SESSION_CODE'
     'CSCENRL1.SEG01.STDNT_ENRL_STATUS'
     'CSCENRL1.SEG01.MIDTERM_GRADE'
     'CSCENRL1.SEG01.CRSE_GRADE_OFF'
     'J3.SEG01.ADVISOR_ID'
     'J3.SEG01.NAME'
BY 'CSCENRL1.SEG01.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL2 FORMAT FOCUS INDEX 'CSCENRL1.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
-*Add student name (use name here instead of from roster - if student doesn't have midterm grade then name will be missing cause of how doing join)
JOIN
 LEFT_OUTER CSCENRL2.SEG01.EMPLID IN CSCENRL2 TO MULTIPLE CSPRDSC_PERSONAL_DATA.CSPRDSC_PERSONAL_DATA.EMPLID
 IN CSPRDSC_PERSONAL_DATA TAG J4 AS J4
END
TABLE FILE CSCENRL2
PRINT
     'J4.CSPRDSC_PERSONAL_DATA.LAST_NAME'
     'J4.CSPRDSC_PERSONAL_DATA.FIRST_NAME'
     'CSCENRL2.SEG01.STRM'
     'CSCENRL2.SEG01.ACAD_CAREER'
     'CSCENRL2.SEG01.CLASS_NBR'
     'CSCENRL2.SEG01.COURSE_ID'
     'CSCENRL2.SEG01.SESSION_CODE'
     'CSCENRL2.SEG01.MIDTERM_GRADE'
     'CSCENRL2.SEG01.CRSE_GRADE_OFF'
BY 'CSCENRL2.SEG01.ADVISOR_ID'
BY 'CSCENRL2.SEG01.NAME'
BY 'CSCENRL2.SEG01.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCENRL3 FORMAT FOCUS INDEX 'CSCENRL2.SEG01.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
DEFINE FILE CSPRDSC_SCC_EMAIL_QVW
CSCC_EMAIL/A70=IF CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.E_ADDR_TYPE EQ 'CSCC' THEN CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMAIL_ADDR ELSE ' ';
END
TABLE FILE CSPRDSC_SCC_EMAIL_QVW
SUM
     'MAX.CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.CSCC_EMAIL'
 AS 'CSCC_EMAIL'
BY 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCEMAIL FORMAT FOCUS INDEX 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N2,
     WRAP=6.000000,
$
ENDSTYLE
END
JOIN
 LEFT_OUTER CSCENRL3.SEG01.EMPLID IN CSCENRL3 TO MULTIPLE CSCEMAIL.SEG01.EMPLID
 IN CSCEMAIL TAG J5 AS J5
END
TABLE FILE CSCENRL3
PRINT
     'CSCENRL3.SEG01.LAST_NAME'
     'CSCENRL3.SEG01.FIRST_NAME'
     'CSCENRL3.SEG01.STRM'
     'CSCENRL3.SEG01.ACAD_CAREER'
     'CSCENRL3.SEG01.CLASS_NBR'
     'CSCENRL3.SEG01.COURSE_ID'
     'CSCENRL3.SEG01.SESSION_CODE'
     'CSCENRL3.SEG01.MIDTERM_GRADE' AS 'Midterm'
     'CSCENRL3.SEG01.CRSE_GRADE_OFF' AS 'Final'
     'J5.SEG01.CSCC_EMAIL'
BY 'CSCENRL3.SEG01.ADVISOR_ID'
BY 'CSCENRL3.SEG01.NAME'
BY 'CSCENRL3.SEG01.EMPLID'
BY 'CSCENRL3.SEG01.COURSE_ID' NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
&BY_DISP
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>,<Excel Formula,EXL2K FORMULA>,<HTML Active Report,AHTML>,<Active Report Flash/Flex,FLEX>,<PowerPoint,PPT>).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
TYPE=REPORT,
     COLUMN=N14,
     WRAP=6.000000,
$
ENDSTYLE
END
 


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
Hi Malinda,

Isn't this fun!!!!!

OK, take out the &BY_DISP in the last step. At the top, change this back to:

 
-SET &ECHO=ALL;
-SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'BYDISPLAY = ON';
SET ASNAMES = ON, LINES = 998, NODATA = ' ', &BY_DISP, SPACES = 1
-RUN
? SET
-RUN


Take out &BY_DISP here:

ON TABLE NOTOTAL
&BY_DISP
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>,<Excel Formula,EXL2K FORMULA>,<HTML Active Report,AHTML>,<Active Report Flash/Flex,FLEX>,<PowerPoint,PPT>Wink.Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

This is in PRODUCTION in all of our code and always works.

With &ECHO=ALL, it will reveal all the parsed FOCUS code. Look at your source and you will see:

1. If there are any errors
2. the SET commands in effect

Tom

P.S. Why do I always have to click on Options, Disable HTML every time I Edit a Post? If I do it once, it should be retained...

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
When do I as you suggest, and run it to EXL2K it runs to excel but still does not have the repeated values. It also does not tell me of any erros or SET commands in effect.....


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
Expert
posted Hide Post
Hi Malinda,

Sent you a private message...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
Malinda,

Way too much code for what you need. A little insight is that you do not need to have styling information when you have ON TABLE HOLD FORMAT FOCUS etc. - esxcessive code that is not utilised and therefore not required. I guess that you developed it within the painter and that explains why you have all the extraneous code. If you are going to start using the editor you can lose a lot of that code.

This is your code but whittled down to primary requirements.

-SET &DUMMY = &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>,<Excel Formula,EXL2K FORMULA>,<HTML Active Report,AHTML>,<Active Report Flash/Flex,FLEX>,<PowerPoint,PPT>).Select type of display output.;
-SET &BY_DISP = IF &WFFMT EQ 'PDF' OR 'PPT' THEN ' ' ELSE 'ON TABLE SET BYDISPLAY ON';
SET ASNAMES = ON, LINES = 998, NODATA = ' ', SPACES = 1
-RUN
-* Pull all students enrolled and their course info for the term
DEFINE FILE CSPRDSC_CLASS_TBL_SE_VW
  COURSE_ID/A25  = SUBJECT || CATALOG_NBR | ' ' | CLASS_SECTION;
  CLASS_NBR_A/A5 = EDIT ( CLASS_NBR, '99999');
  CLASS_KEY/A20  = STRM || CLASS_NBR_A || EMPLID;
END

TABLE FILE CSPRDSC_CLASS_TBL_SE_VW
PRINT 'STRM'
      'CLASS_NBR'
      'EMPLID'
      'ACAD_CAREER'
      'ACAD_GROUP'
      'COURSE_ID'
      'SESSION_CODE'
      'STDNT_ENRL_STATUS'
      'CRSE_GRADE_INPUT'
      'CRSE_GRADE_OFF'
   BY 'CLASS_KEY'
WHERE INSTITUTION EQ 'CSC01';
WHERE STRM EQ &STRM.(OR(FIND STRM,STRM IN csprdsc_class_tbl_se_vw)).Select Term EQ.;
WHERE STDNT_ENRL_STATUS EQ 'E';
ON TABLE HOLD AS CSCENRL FORMAT FOCUS INDEX 'CLASS_KEY'
END
-* Create a hold file of all students and all classes with their midterm grades from midterm grade roster.
JOIN
  CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM
  AND CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CLASS_NBR
  AND CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.GRD_RSTR_TYPE_SEQ IN
  CSPRDSC_GRADE_ROSTER TO MULTIPLE
  CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.STRM
  AND CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.CLASS_NBR
  AND CSPRDSC_GRADE_RSTR_TYPE.CSPRDSC_GRADE_RSTR_TYPE.GRD_RSTR_TYPE_SEQ
  IN CSPRDSC_GRADE_RSTR_TYPE TAG J0 AS J0
END

DEFINE FILE CSPRDSC_GRADE_ROSTER
  CLASS_NBR_A/A5  = EDIT (CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CLASS_NBR, '99999');
  MID_GRD_KEY/A20 = CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM || CLASS_NBR_A || CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.EMPLID;
END

TABLE FILE CSPRDSC_GRADE_ROSTER
PRINT 'J0.CSPRDSC_GRADE_RSTR_TYPE.STRM'
      'J0.CSPRDSC_GRADE_RSTR_TYPE.CLASS_NBR'
      'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.EMPLID'
      'J0.CSPRDSC_GRADE_RSTR_TYPE.GRADE_ROSTER_TYPE'
      'J0.CSPRDSC_GRADE_RSTR_TYPE.GRD_RSTR_TYPE_SEQ'
      'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.ACAD_CAREER'
      'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.LAST_NAME_SRCH'
      'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.FIRST_NAME_SRCH'
      'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.CRSE_GRADE_INPUT'
      'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.GRADE_ROSTER_STAT'
   BY 'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.MID_GRD_KEY'
WHERE CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.INSTITUTION EQ 'CSC01';
WHERE J0.CSPRDSC_GRADE_RSTR_TYPE.GRADE_ROSTER_TYPE EQ 'MID';
WHERE CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.STRM EQ &STRM;
ON TABLE HOLD AS CSCMID FORMAT FOCUS INDEX 'CSPRDSC_GRADE_ROSTER.CSPRDSC_GRADE_ROSTER.MID_GRD_KEY'
END

-* Join enrl file with midterm grade file to get enrl students and their midterm (if exists) and final grade for each course
JOIN
  LEFT_OUTER CSCENRL.SEG01.CLASS_KEY IN CSCENRL TO MULTIPLE
  CSCMID.SEG01.MID_GRD_KEY IN CSCMID TAG J1 AS J1
END

DEFINE FILE CSCENRL
  MIDTERM_GRADE/A3 = IF (CSCENRL.SEG01.STDNT_ENRL_STATUS EQ 'E' AND CSCENRL.SEG01.CRSE_GRADE_OFF EQ 'W') THEN 'W' ELSE J1.SEG01.CRSE_GRADE_INPUT;
END
TABLE FILE CSCENRL
PRINT 'CSCENRL.SEG01.ACAD_CAREER'
      'CSCENRL.SEG01.ACAD_GROUP'
      'CSCENRL.SEG01.COURSE_ID'
      'CSCENRL.SEG01.SESSION_CODE'
      'CSCENRL.SEG01.STDNT_ENRL_STATUS'
      'J1.SEG01.CRSE_GRADE_INPUT'
      'J1.SEG01.MIDTERM_GRADE'
      'CSCENRL.SEG01.CRSE_GRADE_OFF'
   BY 'CSCENRL.SEG01.STRM'
   BY 'CSCENRL.SEG01.CLASS_NBR'
   BY 'CSCENRL.SEG01.EMPLID'
ON TABLE HOLD AS CSCENRL1 FORMAT FOCUS INDEX 'CSCENRL.SEG01.EMPLID'
END

-* Find the main, highest effdated advisor listed for a student (advisor number 1)
JOIN
  LEFT_OUTER CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ID IN
  CSPRDSC_STDNT_ADVR_HIST TO MULTIPLE
  CSPRDSC_PERSONAL_DATA.CSPRDSC_PERSONAL_DATA.EMPLID IN CSPRDSC_PERSONAL_DATA
  TAG J2 AS J2
END

TABLE FILE CSPRDSC_STDNT_ADVR_HIST
PRINT 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EFFDT'
      'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR'
      'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ID'
      'J2.CSPRDSC_PERSONAL_DATA.NAME'
      'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.ADVISOR_ROLE'
   BY 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EMPLID'
   BY 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR' NOPRINT
WHERE CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.STDNT_ADVISOR_NBR EQ '1';
ON TABLE HOLD AS CSCADV FORMAT FOCUS INDEX 'CSPRDSC_STDNT_ADVR_HIST.CSPRDSC_STDNT_ADVR_HIST.EMPLID'
END

TABLE FILE CSCADV
PRINT 'CSCADV.SEG01.ADVISOR_ID'
      'CSCADV.SEG01.NAME'
      'CSCADV.SEG01.ADVISOR_ROLE'
   BY 'CSCADV.SEG01.EMPLID'
   BY 'CSCADV.SEG01.STDNT_ADVISOR_NBR'
   BY HIGHEST 1 'CSCADV.SEG01.EFFDT'
ON TABLE HOLD AS CSCADV1 FORMAT FOCUS INDEX 'CSCADV.SEG01.EMPLID'
END

-* Join main file and advisor file
JOIN
  LEFT_OUTER CSCENRL1.SEG01.EMPLID IN CSCENRL1 TO MULTIPLE CSCADV1.SEG01.EMPLID
  IN CSCADV1 TAG J3 AS J3
END

TABLE FILE CSCENRL1
PRINT 'CSCENRL1.SEG01.STRM'
      'CSCENRL1.SEG01.CLASS_NBR'
      'CSCENRL1.SEG01.ACAD_CAREER'
      'CSCENRL1.SEG01.COURSE_ID'
      'CSCENRL1.SEG01.SESSION_CODE'
      'CSCENRL1.SEG01.STDNT_ENRL_STATUS'
      'CSCENRL1.SEG01.MIDTERM_GRADE'
      'CSCENRL1.SEG01.CRSE_GRADE_OFF'
      'J3.SEG01.ADVISOR_ID'
      'J3.SEG01.NAME'
   BY 'CSCENRL1.SEG01.EMPLID'
ON TABLE HOLD AS CSCENRL2 FORMAT FOCUS INDEX 'CSCENRL1.SEG01.EMPLID'
END

-* Add student name (use name here instead of from roster - if student doesn't have midterm grade then name will be missing cause of how doing join)
JOIN
   LEFT_OUTER CSCENRL2.SEG01.EMPLID IN CSCENRL2 TO MULTIPLE CSPRDSC_PERSONAL_DATA.CSPRDSC_PERSONAL_DATA.EMPLID
   IN CSPRDSC_PERSONAL_DATA TAG J4 AS J4
END

TABLE FILE CSCENRL2
PRINT 'J4.CSPRDSC_PERSONAL_DATA.LAST_NAME'
      'J4.CSPRDSC_PERSONAL_DATA.FIRST_NAME'
      'CSCENRL2.SEG01.STRM'
      'CSCENRL2.SEG01.ACAD_CAREER'
      'CSCENRL2.SEG01.CLASS_NBR'
      'CSCENRL2.SEG01.COURSE_ID'
      'CSCENRL2.SEG01.SESSION_CODE'
      'CSCENRL2.SEG01.MIDTERM_GRADE'
      'CSCENRL2.SEG01.CRSE_GRADE_OFF'
   BY 'CSCENRL2.SEG01.ADVISOR_ID'
   BY 'CSCENRL2.SEG01.NAME'
   BY 'CSCENRL2.SEG01.EMPLID'
ON TABLE HOLD AS CSCENRL3 FORMAT FOCUS INDEX 'CSCENRL2.SEG01.EMPLID'
END

DEFINE FILE CSPRDSC_SCC_EMAIL_QVW
  CSCC_EMAIL/A70 = IF CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.E_ADDR_TYPE EQ 'CSCC' THEN CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMAIL_ADDR ELSE ' ';
END

TABLE FILE CSPRDSC_SCC_EMAIL_QVW
  SUM 'MAX.CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.CSCC_EMAIL' AS 'CSCC_EMAIL'
   BY 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
ON TABLE HOLD AS CSCEMAIL FORMAT FOCUS INDEX 'CSPRDSC_SCC_EMAIL_QVW.CSPRDSC_SCC_EMAIL_QVW.EMPLID'
END

JOIN
   LEFT_OUTER CSCENRL3.SEG01.EMPLID IN CSCENRL3 TO MULTIPLE CSCEMAIL.SEG01.EMPLID
   IN CSCEMAIL TAG J5 AS J5
END

TABLE FILE CSCENRL3
PRINT 'CSCENRL3.SEG01.LAST_NAME'
      'CSCENRL3.SEG01.FIRST_NAME'
      'CSCENRL3.SEG01.STRM'
      'CSCENRL3.SEG01.ACAD_CAREER'
      'CSCENRL3.SEG01.CLASS_NBR'
      'CSCENRL3.SEG01.COURSE_ID'
      'CSCENRL3.SEG01.SESSION_CODE'
      'CSCENRL3.SEG01.MIDTERM_GRADE'  AS 'Midterm'
      'CSCENRL3.SEG01.CRSE_GRADE_OFF' AS 'Final'
      'J5.SEG01.CSCC_EMAIL'
   BY 'CSCENRL3.SEG01.ADVISOR_ID'
   BY 'CSCENRL3.SEG01.NAME'
   BY 'CSCENRL3.SEG01.EMPLID'
   BY 'CSCENRL3.SEG01.COURSE_ID' NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
&BY_DISP.EVAL
ON TABLE PCHOLD FORMAT &WFFMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $
  TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, $
  TYPE=TITLE, STYLE=BOLD, $
  TYPE=TABHEADING, SIZE=12, STYLE=BOLD, $
  TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, $
  TYPE=HEADING, SIZE=12, STYLE=BOLD, $
  TYPE=FOOTING, SIZE=12, STYLE=BOLD, $
  TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, $
  TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, $
  TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210), $
  TYPE=ACROSSVALUE, SIZE=9, $
  TYPE=ACROSSTITLE, STYLE=BOLD, $
  TYPE=GRANDTOTAL, BACKCOLOR=RGB(210 210 210), STYLE=BOLD, $
  TYPE=REPORT, COLUMN=N14, WRAP=6.000000, $
ENDSTYLE
END

Note that I have moved the &WFFMT prompting to the top of the fex, so that the value for &WFFMT is evaluated first, and the &BY_DISP has an .EVAL appended to it (as per Sandeep) to ensure that the variable is evaluated into the code.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
I visited with Tom and we got it to work finally. We did move my &WFFMT to a -PROMPT at the top but did not use the &BY_DISP.EVAL

Also, I use the text editor to create the joins and defines but usually use the GUI to initially create my table request hence the extra styling. You are right though that in the hold file I don't need that and could delete it.

Thanks for the help guys!


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Excel and PDF/html output setting

Copyright © 1996-2020 Information Builders