TABLE FILE: The Basics

1. TABLE FILE: The Basics Noreen Redden FOCWizard June, 2008

2. Agenda • Components of a Report Request • Grouping/Sorting/Collation • Vertical or Horizontal ? • Aggregation or Detail? • SUBHEADs/SUBFOOTs • Columns • Verb objects • Calculated Columns and Prefixes • Totals • Report Totals • Subtotals • Report HEADING/FOOTING • Page HEADING/FOOTING

3. Report Basics

4. Report Basics

5. Report Basics

6. Step 1: Select the FILE Syntax: TABLE FILE filename TABLE FILE EMPDATA

7. Step 2: Aggregation or Detail Syntax: SUM/WRITE/COUNT PRINT/LIST SUM/WRITE -- Sum the values of COUNT -- Count the instances of PRINT -- Display the details of LIST -- Display the details of + line number TABLE FILE EMPDATA SUM

8. Step 3: Verb Objects Syntax: fieldname[/format] [NOPRINT] [AS ‘column title’] [OVER] • Where: fieldname is • FIELDNAME or ALIAS specified in MFD • FIELDNAME or ALIAS specified in JOINed MFD • DEFINE fieldname from MFD • DEFINE fieldname from dynamic DEFINE FILE fn TABLE FILE EMPDATA SUM SALARY

9. Step 4: Special Prefixes Syntax: prefix.fieldname TABLE FILE EMPDATA SUM SALARY PCT.SALARY/F7.2% AS 'Percentage of,Total Salary'

10. Step 5: Calculations – Using Summed Data Syntax: COMPUTE field/format = expression; [NOPRINT] [AS ‘title’] [OVER] TABLE FILE EMPDATA SUM SALARY PCT.SALARY/F7.2% AS 'Percentage of,Total Salary‘ COMPUTE Monthly_Sal/D11.2 = SALARY / 12;

11. Step 6: Select Sort Order Syntax: BY [HIGHEST] field [NOPRINT] [AS ‘title’] [ROWS value OVER value … ACROSS [HIGHEST] field [NOPRINT] [AS ‘title’] [COLUMNS value AND …] TABLE FILE EMPDATA SUM SALARY PCT.SALARY/F7.2% AS 'Percentage of,Total Salary' COMPUTE Monthly_Sal/D11.2 = SALARY / 12; BY DIV NOPRINT BY DEPT NOPRINT BY LASTNAME

12. Step 7: Select Action when Sort Value Changes Syntax: ON sortfield action where action = SUBHEAD/SUBFOOT SUBTOTAL/SUB-TOTAL/RECOMPUTE/SUMMARIZE PAGE-BREAK SKIP-LINE FOLD-LINE TABLE FILE EMPDATA SUM SALARY PCT.SALARY/F7.2% AS 'Percentage of,Total Salary' COMPUTE Monthly_Sal/D11.2 = SALARY / 12; BY DIV NOPRINT BY DEPT NOPRINT BY LASTNAME ON DIV SUBTOTAL AS '*TOTAL DIV‘ ON DIV PAGE-BREAK ON DEPT SUBTOTAL MULTILINES AS '*TOTAL'

13. Step 8: Specify Selection Criteria Syntax: WHERE filter WHERE expression relation {expression} {value OR value} WHERE (expression) [{AND/OR} expression … TABLE FILE EMPDATA SUM SALARY PCT.SALARY/F7.2% AS 'Percentage of,Total Salary' COMPUTE Monthly_Sal/D11.2 = SALARY / 12; BY DIV NOPRINT BY DEPT NOPRINT BY LASTNAME ON DIV SUBTOTAL AS '*TOTAL DIV‘ ON DIV PAGE-BREAK ON DEPT SUBTOTAL MULTILINES AS '*TOTAL‘ WHERE DIV NE 'CORP';

14. Step 9: Headings and Footings Syntax: HEADING [CENTER] “ text <fieldname[>] FOOTING [CENTER] [BOTTOM] “ text <fieldname> ON sortfield SUBHEAD “ text <fieldname[>] “ ON sortfield SUBFOOT “ text <fieldname[>] <ST.fieldname[>] ON TABLE [PAGE-BREAK AND] SUBHEAD “ … “ ON TABLE [PAGE-BREAK AND] SUBFOOT “… “

15. Step 9: Headings and Footings TABLE FILE EMPDATA SUM SALARY PCT.SALARY/F7.2% AS 'Percentage of,Total Salary' COMPUTE Monthly_Sal/D11.2 = SALARY / 12; BY DIV NOPRINT BY DEPT NOPRINT BY LASTNAME ON DIV SUBTOTAL AS '*TOTAL DIV‘ ON DIV PAGE-BREAK ON DEPT SUBHEAD "Department: <DEPT “ ON DEPT SUBTOTAL MULTILINES AS '*TOTAL‘ HEADING "Salary Report as of <+0>&DATEDMYY<+0> " "Division: <DIV “ "Page <TABPAGENO of <TABLASTPAGE “ WHERE DIV NE 'CORP';

16. Step 10: Use the TOTALs Syntax: BY [HIGHEST] TOTAL {column-name COMPUTE expression;} [NOPRINT] Syntax: WHERE TOTAL expression • After Sorting/Summing, • WHERE TOTAL tests are applied to the RESULT of SUM/Sort • Matrix is RE-SORTED by specified column

17. Step 10: Using the Totals TABLE FILE EMPDATA SUM SALARY PCT.SALARY/F7.2% AS 'Percentage of,Total Salary' COMPUTE Monthly_Sal/D11.2 = SALARY / 12; BY DIV NOPRINT BY DEPT NOPRINT BY TOTAL HIGHEST SALARY NOPRINT BY LASTNAME ON DIV SUBTOTAL AS '*TOTAL DIV' ON DIV PAGE-BREAK ON DEPT SUBHEAD "Department: <DEPT " ON DEPT SUBTOTAL MULTILINES AS '*TOTAL'

18. Step 11: Output Format ON TABLE SET STYLE * … ENDSTYLE ON TABLE SET PRINT {OFFLINE/ONLINE} ON TABLE SET ONLINE-FMT {HTML EXL2K} ON TABLE HOLD FORMAT WP ON TABLE SAVE [AS filename] ON TABLE [PC]HOLD [AS filename] [FORMAT {BINARY ALPHA HTML EXL2K INTERNAL}

19. Report Basics TABLE FILE filename HEADING [CENTER]"text" {display command} [SEG.] field [/R|/L|/C] [/format]{display command} [prefixop.] [field] [/R|/L|/C] [/format][NOPRINT|AS 'title1,...,title5'] [AND|OVER] [obj2...obj1024][WITHIN field] [IN n] COMPUTE field [/format] = expression; [AS 'title,...,title5'] [IN n][AND] ROW‑TOTAL [/R|/L|/C] [/format][AS 'name'][AND] COLUMN‑TOTAL [/R|/L|/C] [AS 'name'] ACROSS [HIGHEST] sortfieldn [IN‑GROUPS‑OF qty][NOPRINT| AS 'title1,...,title5']BY [HIGHEST] sortfieldn [IN‑GROUPS‑OF qty][NOPRINT| AS 'title1,...,title5']

20. Report Basics BY [HIGHEST|LOWEST{n}] TOTAL [prefix_operator] {field|code_value}RANKED BY {TOP|HIGHEST|LOWEST} [n] field [IN-GROUPS-OF qty [TILES [TOP m]] [AS 'heading']][NOPRINT|AS'title1,...,title5']{BY|ACROSS} sortfield IN-RANGES-OF value [TOP limit] ON sfld option1 [AND] option2 [WHEN expression;...]ON sfld RECAP fld1 [/fmt] = expression; {BY|ON} fieldname SUBHEAD [NEWPAGE]"text"{BY|ON} fieldname SUBFOOT [MULTILINES][NEWPAGE]"text"{BY|ON} sfld [AS 'text1'] {SUBTOTAL|SUB-TOTAL|SUMMARIZE|RECOMPUTE}[MULTILINES] [pref. ] [field1 [pref. ] field2 ...] [AS 'text2'][WHEN expression;] WHERE [TOTAL] expression WHERE {RECORDLIMIT|READLIMIT} EQ n

21. Report Basics {BY|ON} sfld [AS 'text1'] {SUBTOTAL|SUB-TOTAL|SUMMARIZE|RECOMPUTE}[MULTILINES] [pref. ] [field1 [pref. ] field2 ...] [AS 'text2'][WHEN expression;] ON TABLE HOLD [VIA program][AS name] [FORMAT format] [MISSING {ON|OFF}] ON TABLE {PCHOLD|SAVE|SAVB} [AS name] [FORMAT format] [MISSING {ON|OFF}] ON TABLE NOTOTAL ON TABLE COLUMN‑TOTAL [/R|/L|/C] [AS 'name'] fieldname ON TABLE {ROW‑TOTAL|ACROSS-TOTAL}[/R|/L|/C][format] [AS 'name'] fldname ON TABLE {SUBTOTAL|SUB-TOTAL|SUMMARIZE|RECOMPUTE} [pref. ] [field1 [pref. ] field2 ...] [AS 'text2'] FOOTING [CENTER] [BOTTOM]"text“ MOREFILE file2 [IF field relation value [OR value...]|WHERE expression] {END|RUN|QUIT}