1 / 36

FOCUS Internals: Part 2 Update for 7.6.x

FOCUS Internals: Part 2 Update for 7.6.x . Internals of Output Processing for TABLE/TABLEF/MATCH FILE . Renee Teatro FUN Conference August 2008. Agenda Order of Processing. Matrix processing Evaluate COMPUTEs Apply IF/WHERE TOTAL tests Secondary sort Process BY TOTAL

chul
Download Presentation

FOCUS Internals: Part 2 Update for 7.6.x

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. FOCUS Internals: Part 2 Update for 7.6.x Internals of Output Processing for TABLE/TABLEF/MATCH FILE Renee Teatro FUN Conference August 2008

  2. AgendaOrder of Processing • Matrix processing • Evaluate COMPUTEs • Apply IF/WHERE TOTAL tests • Secondary sort • Process BY TOTAL • Post matrix processing • Determine ACROSS column • Process ACROSS-TOTAL • Other totals • Format the line http://documentation.informationbuilders.com/masterindex/html/html_s390_76/mf_snf76/mf_snf76.pdf q

  3. Internal Matrix Generation • Aggregation is performed as record is sorted, with FOCUS sort • Aggregation is performed by external sort if SET EXTAGGR = ON • Numeric Fields are added. Alpha fields being SUMmed, return the LAST value within the BY field, the FST value with external sorts • (SET SUMPREFIX=LST if required) • All verb objects are also COUNTed • Once an acceptable record has been retrieved, the fields for SORTREC are moved, and the record is released to Sort q

  4. Internal Matrix Generation • When BINs are full, subsequent records will re-use BINs (EXTSORT is OFF) or be passed to the external sort (EXTSORT is ON) • After all records are retrieved, a final merge will take place • The end of the final merge is noted by: Records = Lines = • 76 Feature: Unlimited FOCSORT • Initially SORT occurs in BINs q

  5. BINs FOCSORT FOCSORT SORT MERGE FOCSORT BINs SORT MERGE BINs Internal Matrix GenerationMerge – FOCUS Sort q

  6. FOCSORT BINs ~ First 5000 Records FOCSORT S001WK01 SORT MERGE S001WK02 S001WK03 EXTSORT S001WK04 BINs Subsequent Records S001WK05 S001WK06 S001WK07 S001WK08 S001WK09 Internal Matrix GenerationEXTSORT = ON, AUTOTABLEF = OFF FOCSORT q

  7. OFFLINE BINs ~ First 5000 Records S001WK01 S001WK02 S001WK03 Or… S001WK04 Extract Subsequent Records S001WK05 S001WK06 S001WK07 S001WK08 S001WK09 Or… Hotscreen Internal Matrix GenerationEXTSORT = ON, AUTOTABLEF = ON EXTSORT q

  8. SAVEMATRIX=ON Defaults(711=on, 72 =off) S001WK01 OFFLINE FOCSORT S001WK02 S001WK03 Or… S001WK04 Subsequent Records Extract S001WK05 S001WK06 S001WK07 Or… S001WK08 S001WK09 Hotscreen Internal Matrix GenerationAUTOTABLEF = ON BINs ~ First 5000 Records EXTSORT q

  9. Retrieve an Entry Order of Processing • FOCSORT (AUTOTABLEF = OFF, MATCH FILE) • External sort files (AUTOTABLEF = ON) • Database (TABLEF) • If lowest BY field has changed, release prior line to applicable program • If BY fields change invoke control options on priorsort group q

  10. FML/EMR Processor COMPUTE’s IF/WHERE TOTAL Output Stage BY TOTAL SORT Internal Matrix Totals… Secondary Sort Post Matrix Processing FOCSML Extract Report Formatting HOLD/SAVE q

  11. BY TOTAL TABLE FILE EMPDATA SUM SALARY CNT.PIN COMPUTE AVGSAL=SALARY/CNT.PIN; AS 'AVE,SALARY' BY HIGHEST TOTAL AVGSAL NOPRINT BY DEPT END PAGE 1 PIN AVE DEPT SALARY COUNT SALARY ---- ------ ----- ------ ACCOUNTING $283,300.00 5 $56,660.00 SALES $395,200.00 7 $56,457.14 MARKETING $570,700.00 11 $51,881.82 CUSTOMER SUPPORT $198,400.00 4 $49,600.00 PROGRAMMING & DVLPMT $182,300.00 4 $45,575.00 PERSONNEL $216,800.00 5 $43,360.00 CONSULTING $126,300.00 3 $42,100.00 ADMIN SERVICES $56,200.00 2 $28,100.00 q

  12. BY TOTAL • Sorting by Report Column • [RANKED] BY [HIGHEST|LOWEST [n] ] TOTAL display field where: • RANKED – Adds a column to the report output that identifies a rank number for each row • n – Is the number of sort field values you wish to display in the report • Display field – Can be a fieldname, prefix-operator.fieldname, or calculated value q

  13. FML/EMR Processor COMPUTE’s IF/WHERE TOTAL Output Stage BY TOTAL SORT Internal Matrix Totals… Secondary Sort Post Matrix Processing FOCSML Extract Report Formatting HOLD/SAVE q

  14. ACROSS DEFINE FILE EMPLOYEE JOB_CLASS/A1 = EDIT(CJC,’9’); END TABLE FILE EMPLOYEE SUM SALARY NOPRINT CNT.SALARY NOPRINT COMPUTE AVESAL/D7 = SALARY / CNT.SALARY; BY DEPARTMENT ACROSS JOB_CLASS COMPUTE TOTAL =(C1 + C4 + C7 + C10 + C13 + C16)/ (C2 + C5 + C8 + C11 + C14 + C17); END JOB_CLASS A B TOTAL DEPARTMENT AVESAL AVESAL ------------------------------------------------- MIS 17,622 17,938 17,797.44 PRODUCTION 17,219 17,407 17,275.20 q

  15. ACROSS (NOPRINT’s Removed) C1 C2 C3 C4 C5 C6 PAGE 1 JOB_CLASS A B SALARY SALARY DEPARTMENT SALARY COUNT AVESAL SALARY COUNT AVESAL ----------------------------------------------------------------------------- MIS $70,487.00 4 17,622 $89,690.00 5 17,938 PRODUCTION $120,532.00 7 17,219 $52,220.00 3 17,407 TOTAL ---------------- 17,797.44 17,275.20 COMPUTE TOTAL =(C1 + C4 + C7 + C10 + C13 + C16)/ (C2 + C5 + C8 + C11 + C14 + C17); What Happened To: C7 - C17 q

  16. ACROSS TABLE FILE EMPLOYEE SUM SALARY NOPRINT CNT.SALARY NOPRINT BY DEPARTMENT SUM SALARY NOPRINT CNT.SALARY NOPRINT COMPUTE AVESAL/D7 = SALARY / CNT.SALARY; BY DEPARTMENT ACROSS JOB_CLASS COMPUTE TOTAL =C1 /C2 ; END JOB_CLASS A B TOTAL DEPARTMENT AVESAL AVESAL ------------------------------------------------- MIS 17,622 17,938 17,797.44 PRODUCTION 17,219 17,407 17,275.20 q

  17. Assigning Column Numbers Only to Fields Displayed on Report Output • SET CNOTATION= ALL | PRINTONLY | EXPLICIT • ALL - Assigns column reference numbers to every column in the internal matrix. ALL is the default value. • EXPLICIT - Assigns column reference numbers to all fields referenced in the request, whether displayed or not. • PRINTONLY - Assigns column reference numbers only to columns that display in the report output. CNOTATION = ALL (default) CAR MPG SEATS SEATS RCOST TEST1 C1 C2 C3 C4 C5 CNOTATION = EXPLICIT CAR MPG SEATS SEATS RCOST TEST1 C1 C2 C3 C4 CNOTATION = PRINTONLY CAR MPG SEATS SEATS RCOST TEST1 C1 C2 C3 TABLE FILE CAR PRINT MPG NOPRINT SEATS/D4.1 RCOST COMPUTE TEST1 = C1 * C2; BY CAR END q

  18. ACROSS-TOTAL Let’s say we wanted to: Count the number of courses taken across each year for each quarter Produce a total for each year and a grand total PAGE 1 YEAR 89 90 TOTAL QTR Q2 Q3 Q4 YR TOT Q1 Q2 Q3 Q4 YR TOT DIV --------------------------------------------------------------------------- CE 1 1 0 2 0 3 1 2 6 8 CORP 0 1 0 1 0 1 1 0 2 3 NE 0 0 1 1 1 1 0 2 4 5 SE 0 0 0 0 1 0 1 1 3 3 WE 2 1 0 3 1 2 1 0 4 7 q

  19. ACROSS-TOTAL JOIN PIN IN EMPDATA TO ALL PIN IN TRAINING AS XX DEFINE FILE EMPDATA SYR/Y=CSTART; SQT/Q=CSTART; END TABLE FILE EMPDATA COUNT COURSECODE ACROSS SYR AS ‘YEAR’ ACROSS-TOTAL AS 'TOTAL' ACROSS SQT AS ‘QTR’ ACROSS-TOTAL AS 'YR TOT' BY DIV IF SYR EQ 89 OR 90 END q

  20. ACROSS-TOTAL • Notice: • The Quarter Total column heading stays with correct line • The Year Total column heading stays with correct line PAGE 1 YEAR 89 90 TOTAL QTR Q2 Q3 Q4 YR TOT Q1 Q2 Q3 Q4 YR TOT DIV --------------------------------------------------------------------------- CE 1 1 0 2 0 3 1 2 6 8 CORP 0 1 0 1 0 1 1 0 2 3 NE 0 0 1 1 1 1 0 2 4 5 SE 0 0 0 0 1 0 1 1 3 3 WE 2 1 0 3 1 2 1 0 4 7 q

  21. ACROSS-TOTAL Producing Column Totals With ACROSS-TOTAL • ACROSS sortfield ACROSS-TOTAL [AS 'name'] [COLUMNS col1 AND col2 ...]column names Where: sortfield – Is the name of the field being sorted across name – Is the new name for the ACROSS-TOTAL column title col1, col2 – Are the titles of the ACROSS columns you want to include in the total q

  22. ON Sortfield Options • Subtotaling • SUBTOTAL and SUB-TOTAL • RECOMPUTE and SUMMARIZE • MULTILINE suppresses operation if only single detail line • RECAP (COMPUTE) • SUBFOOT/SUBHEAD q

  23. Totaling * NOTOTAL Suppresses Grand Totals q

  24. ON Sortfield Options • PAGE-BREAK [REPAGE] • NOSPLIT • **FOLD-LINE • **SKIP-LINE • UNDERLINE • ** May be specified on verb-objects q

  25. ON TABLE Options • Totaling – ON TABLE… • COLUMN-TOTAL • ROW-TOTAL • SUMMARIZE • NOTOTAL • RECAP • [PAGE-BREAK AND ] SUBFOOT/SUBHEAD q

  26. HEADings and FOOTings • References to fields in SUBHEAD and SUBFOOT become verb objects only if not previously mentioned. • Fields used in HEADINGs and SUBHEADs are taken from the first line within the group (BY phrase or page) • References to fields in HEADING and FOOTING become verb objects with the first verb q

  27. HEADings and FOOTings • Fields used in FOOTINGs and SUBFOOTs are taken from the last line within the group, subtotals (ST.field, totals – TOT.field, running totals – CT.field) or RECAP fields. • Maximum storage for all HEADINGs, FOOTINGs, SUBHEADs, or SUBFOOTs is 6K bytes; • </n <n <+n <-n require additional 10 bytes • <fieldname[>] require additional 30 bytes • Each line specified within “ “ requires WIDTH bytes q

  28. ON TABLE Options • Extracts • ON TABLE HOLD [FORMAT …] q

  29. ON TABLE Options(Not All Shown) • COMMA – Saves all the columns of the WebFOCUS report request and creates a CSV (Comma Separated Values) file. Alphanumeric fields are enclosed in quotation marks. Columns are separated by commas. • COM – Saves the data values as a variable-length text file with fields separated by commas and with character values enclosed in double quotation marks. Leading blanks are removed from numeric fields and trailing blanks are removed from character fields. To issue a request against this data source, the setting PCOMMA=ON is required, • COMT – Saves the column headings in the first row of the output file. It produces a variable-length text file with fields separated by commas and with character values enclosed in double quotation marks. Leading blanks are removed from numeric fields and trailing blanks are removed from character fields. This format is required by certain software packages such as Microsoft Access. q

  30. ON TABLE Options(Not All Shown) • DOC – Saves the report output as MS-DOS text with layout and line breaks • EXCEL – Saves the report output as a Microsoft Excel worksheet • EXL2K– Generates fully styled reports in Excel 2000 HTML format. Requires Excel 2000 on your PC • EXL2K PIVOT– Generates fully styled reports in Excel 2000 HTML format, with added pivoting capabilities. Requires Excel 2000 on your PC • HTML– Creates an output file that contains an HTML(Web) document • HTMTABLE– Creates an output file that contains an HTML table, not a complete HTML document q

  31. ON TABLE Options(Not All Shown) • INTERNAL – Saves report output without padding the values of integer and packed fields • PDF – Saves the report output in Adobe’s Portable Document Format, which allows precise placement of output (all formatting options) on the printed page so the report looks exactly as it would when printed • PS – Saves the report as a PostScript document. You must have installed a third-party tool capable of displaying PS • SQLMSS – Captures the report data and creates a Microsoft SQL Server data source table q

  32. ON TABLE Options(Not All Shown) • SQLODBC –Captures the report data and creates a file or data source table using the current ODBC data source driver • SQLORA –Captures the report data and creates an Oracle data source table • TABT –Creates an extract file in tab delimited format that includes column headings in the first row q

  33. INT1.MAS FILE=INT1 ,SUFFIX=FOC SEGNAME=SEG01 ,SEGTYPE=S02 FIELDNAME =EMP_ID ,E01 ,A9 , FIELDTYPE=I, $ FIELDNAME =FOCLIST ,E02 ,I5 , $ FIELDNAME =LAST_NAME ,E03 ,A15 , $ FIELDNAME =FIRST_NAME ,E04 ,A10 , $ FIELDNAME =CURR_SAL ,E05 ,D12.2M , $ FIELDNAME =NEWSAL ,E06 ,D12.2 , $ Extract SET HOLDLIST = ALL TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME COMPUTE NEWSAL = CURR_SAL * 1.5; BY EID ON TABLE HOLD AS INT1 FORMAT FOCUS INDEX EID END q

  34. Extract TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME COMPUTE NEWSAL = CURR_SAL * 1.5; BY EID ON TABLE HOLD AS INT1 FORMAT FOCUS END SET DIRECTHOLD=ON 7.6 Feature SET HOLDLIST = ALL FOC$HOLD.FEX FOCUSDATABASE CREATE FILE INT1 MODIFY FILE INT1 FIXFORM FROM FOC$HOLD DATA ON FOC$HOLD END q

  35. Review • Read Internal Matrix from FOCSORT or External Sort Files • Evaluate COMPUTEs • Apply IF/WHERE TOTAL tests • Secondary sort • Process BY TOTAL • Post Matrix processing • Determine ACROSS column • Process ACROSS-TOTAL • Perform other totals • Format The Line • Write extract file, or Format output using standard style or STYLESHEET q

  36. Questions Thank You !

More Related