[SOLVED]Excel and PDF/html output setting - Topic

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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.

Focal Point Forums

Hop To Forum Categories

WebFOCUS/FOCUS Forum on Focal Point

[SOLVED]Excel and PDF/html output setting

Read-Only Topic

Go

Search

Notify

Tools

Personal Zone	»
Member Directory
Focal Point Forums	»

Add to My Favorites

Printer Friendly Format

[SOLVED]Excel and PDF/html output setting

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, 2010

Report This Post

Expert

posted

  
-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, 2006

Report This Post

Ignored post by Tom Flynn posted

Platinum Member

posted

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, 2010

Report This Post

Ignored post by Malinda posted

Expert

posted

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, 2006

Report This Post

Ignored post by Tom Flynn posted

Platinum Member

posted

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, 2010

Report This Post

Ignored post by Malinda posted

Expert

posted

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, 2006

Report This Post

Ignored post by Tom Flynn posted

Platinum Member

posted

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, 2010

Report This Post

Ignored post by Malinda posted

Platinum Member

posted

 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, 2010

Report This Post

Ignored post by Malinda posted

Expert

posted

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, 2006

Report This Post

Ignored post by Tom Flynn posted

Platinum Member

posted

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, 2010

Report This Post

Ignored post by Malinda posted

Expert

posted

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, 2006

Report This Post

Ignored post by Tom Flynn posted

Platinum Member

posted

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 !!

Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006

Report This Post

Ignored post by BlueZone posted

Platinum Member

posted

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, 2010

Report This Post

Ignored post by Malinda posted

Expert

posted

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, 2006

Report This Post

Ignored post by Tom Flynn posted

Platinum Member

posted

&BY_DISP shows BYDISPLAY = ON
&WFFMT shows EXL2K

strange

WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)

Posts: 149 | Registered: April 15, 2010

Report This Post

Ignored post by Malinda posted

Expert

posted

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, 2006

Report This Post

Ignored post by Tom Flynn posted

Platinum Member

posted

 -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, 2010

Report This Post

Ignored post by Malinda posted

Expert

posted

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>

.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, 2006

Report This Post

Ignored post by Tom Flynn posted

Platinum Member

posted

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, 2010

Report This Post

Ignored post by Malinda posted

Expert

posted

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>

.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, 2006

Report This Post

Ignored post by Tom Flynn posted

Platinum Member

posted

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, 2010

Report This Post

Ignored post by Malinda posted

Expert

posted

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, 2006

Report This Post

Ignored post by Tom Flynn posted

Expert

posted

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, 2004

Report This Post

Ignored post by Tony A posted

Platinum Member

posted

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, 2010

Report This Post

Ignored post by Malinda posted

Powered by Social Strata

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point Forums

Hop To Forum Categories

WebFOCUS/FOCUS Forum on Focal Point

[SOLVED]Excel and PDF/html output setting

Focal Point | Privacy Statement | Terms of Service

Copyright © 1996-2020 Information Builders

	View $GS_USERNAME's Public Profile
	Add $GS_USERNAME to my Ignore ListRemove $GS_USERNAME from my Ignore List
	View Recent Posts by $GS_USERNAME
	Notify me of New Posts by $GS_USERNAME

New Since your Last Visit