1 / 21

TABLE FILE: The Basics

TABLE FILE: The Basics. Noreen Redden FOCWizard June, 2008. 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

xue
Download Presentation

TABLE FILE: The Basics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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}

More Related