1 / 73

DN 9602 000.0001

FOCUS 7.3 Product Update. Information Builders October, 2003. Note: Until 7.3 is officially released, features listed in this presentation are subject to change or removal. 1. DN 9602 000.0001. TABLE REPORTING Multiple FOLD-LINEs Display BY value on every row Prefixes on summary lines

erik
Download Presentation

DN 9602 000.0001

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 7.3 Product Update Information Builders October, 2003 Note: Until 7.3 is officially released, features listed in this presentation are subject to change or removal. 1 DN 9602 000.0001

  2. TABLE REPORTING Multiple FOLD-LINEs Display BY value on every row Prefixes on summary lines Display last page # on all pages Display PAGE n OF max IN-RANGES-OF Right-side minus sign display Create cover pages Extensions to FORECAST Multivariate regression forecast NORMSINV and NORMSDST Decimal alignment in headings Multiplereports in one PDF/PS Text field enhancements Stylesheet enhancements PERFORMANCE BOOSTERS New DEFINEs Compiler Parse masters only once User function argument checker Optimized TX search in rdbms DB2 FETCH FIRST n ROWS OUTPUT MECHANISMS Working with EXCEL 2000 Cascading Stylesheets FML Hierarchy Reuse FOR values in FML Use FOR value in calculations FML INDENT Indenting FML hierarchy NODATA char in HOLD Missing values in HOLD SET NULL=ON HOLD in alpha or binary DATABASE and MASTER XFOCUS database Long segment names Long index names Multi-dimensional index (MDI) GROUPs in FOCUS masters DATASET at segment level Read-only fields in master TAB-delimited files Varchar support - AnV RAISED LIMITS Unlimited IF/THEN/ELSE 63 MODIFY COMBINEs Long amper variables Long qualified fieldnames More ACROSS values Increased row size for rdbms 1024 display fields More sort headings/footings Increased column title space More external index partitions OPERATING SYSTEM &FOCUSER userid variable Wide line files and IEDIT Relative GDG +1 FOCUS Release 7.3 – Parade of New Features Parade of New Features

  3. FOCUS Release 7.3 – Parade of New Features OUTPUT MECHANISMS 20Working with EXCEL 2000 21– Cascading Stylesheets 22FML Hierarchy 23– Reuse FOR values in FML 24– Use FOR value in calculations 25– FML INDENT 26– Indenting FML Hierarchy 27Missing values in HOLD 28NODATA char in HOLD 29HOLD in alpha or binary TABLE REPORTING 01Multiple FOLD-LINEs 02Display BY value on every row 03Prefixes on summary lines 04Displaying PAGE n OF max 05First page and last page #s 06IN-RANGES-OF 07Right-side minus sign display 08Create cover pages 09Extensions to FORECAST 10Multivariate regression forecast 11NORMSINV and NORMSDST 12Decimal alignment in headings 13Multiple reports in one PDF/PS 14Stylesheet enhancements RAISED LIMITS 36Unlimited IF/THEN/ELSE 3763 MODIFY COMBINEs 38Long amper variables 39Long qualified fieldnames 40More ACROSS values 411024 display fields 42More sort headings/footings 43Increased column title space 44More external index partitions DATABASE and MASTER 45XFOCUS database 46– Long segment names 47– Long index names 48Multi-dimensional index (MDI) 49GROUPs in FOCUS masters 50DATASET at segment level 51Read-only fields in master 52TAB-delimited files 53Varchar support - AnV OPERATING SYSTEM 30&FOCUSER userid variable 31Wide line files and IEDIT 32Relative GDG +1 PERFORMANCE BOOSTERS 15New DEFINEs Compiler 16Parse masters only once 17User function argument checker 18Optimized TX search in rdbms 19DB2 FETCH FIRST n ROWS MAINTAIN 33MNT filetype/extension 34FOCUS SETs from Maintain 35New screening in MAINTAIN Next Key Feature Next Slide

  4. TABLE REPORTING FOCUS 7.3 TABLE Reporting

  5. Multiple FOLD-LINE • Specify up to 16 FOLD-LINE clauses in a TABLE request TABLE FILE PRINT field1 field2 field3FOLD-LINE field4 field5 field6 field7FOLD-LINE field8 field9 END RESULT field1 field2 field3 field4 field5 field6 field7 field8 field9 Indent 2 spaces

  6. Show BY Values on Every LineSET BYDISPLAY • Display the sort field values on all lines of a report • For STYLE’d output only, including EXL2K, HTML, PDF, PS • Avoid the PRINT FNAME … BY FNAME NOPRINT game TABLE FILE CENTHRPRINT LNAME PLANTBY FNAME UNDER-LINE ON TABLE HOLD FORMAT EXL2K END SET BYDISPLAY=ONTABLE FILE CENTHRPRINT LNAME PLANTBY FNAME UNDER-LINEON TABLE HOLD FORMAT EXL2K END FNAME LNAME PLANT ----- ----- ----- DAVID SMITH SOUTH JONES NORTH MCKNIGHT EAST --------------------------- MARK SMITH WEST GREENSPAN NORTH FNAME LNAME PLANT ----- ----- ----- DAVID SMITH SOUTHDAVID JONES NORTH DAVID MCKNIGHT EAST --------------------------- MARK SMITH WEST MARK GREENSPAN NORTH Next Key Feature

  7. Total of all Counts Prefix Operators on Summary Lines • Use with SUBTOTAL, SUB-TOTAL, RECOMPUTE and SUMMARIZE at both the sort break and grand total levels • Summed values become the detail lines in the report: ASQ. AVE. SUM. CNT. FST. LST. MAX. MIN. Space after the prefix (syntax requirement) TABLE FILE CAR SUM CNT.MODEL BY COUNTRY BY CAR ON COUNTRY SUB-TOTAL WHERE COUNTRY IS 'ITALY' OR 'W GERMANY' END MODEL COUNTRY CAR COUNT ------- --- ----- ITALY ALFA ROMEO 3 MASERATI 1 *TOTAL ITALY2 W GERMANY AUDI 1 BMW 6 *TOTAL W GERMANY2 TOTAL4 MODEL COUNTRY CAR COUNT ------- --- ----- ITALY ALFA ROMEO 3 MASERATI 1 *TOTAL ITALY4 W GERMANY AUDI 1 BMW 6 *TOTAL W GERMANY7 TOTAL11 CNT. MODEL Count of instances after Summarization Back to Feature List DONE

  8. Capture the Last Page of a ReportTABLASTPAGE TABLE FILE CAR HEADING "PAGE <TABPAGENO OF <TABLASTPAGE </1" SUM DCOST BY COUNTRY PAGE-BREAK ON TABLE HOLD FORMAT HTML ON TABLE SET STYLE * TYPE=REPORT,FONT=ARIAL,$ ENDSTYLE END Page 4 of 4 Page 3 of 4 Page 2 of 4 Page 1 of 4 REPORT • Supported for FORMAT HTML/PDF/PS only Notes: &FOCNEXTPAGE is set to TABLASTPAGE

  9. Capture and Adjust Page NumbersFOCFIRSTPAGE and &FOCNEXTPAGE Make multiple reports look like one! FOCFIRSTPAGE – SET variable declares the first page number of a report &FOCNEXTPAGE – reserved amper variable set to one greater than the last page number of the last report Page 3 Page 2 Page 1 REPORT 1 TABLE FILE PRINT … HEADING “PAGE <TABPAGENO “ END -RUN (&FOCNEXTPAGE automatically set to 4) SET FOCFIRSTPAGE = &FOCNEXTPAGE TABLE FILE PRINT … HEADING “PAGE <TABPAGENO “ END Page 5 Page 4 REPORT 2 Next Key Feature

  10. Display the Entire RangeIN-RANGES-OF • Display the min and max of a range of values • Similar to IN-GROUPS-OF TABLE FILE EMPLOYEEPRINT LAST_NAME BY CURR_SAL IN-RANGES-OF 5000 END IN-GROUPS-OF (for comparison) TABLE FILE EMPLOYEEPRINT LAST_NAME BY CURR_SAL IN-GROUPS-OF 5000 END CURR_SAL LAST_NAME-------- --------- 5000.00 SMITH GREENSPAN 10000.00 STEVENS SMITH 15000.00 JONES MCCOY MCKNIGHT CURR_SAL LAST_NAME-------- --------- 5000.00 - 9999.99 SMITH GREENSPAN10000.00 - 14999.99 STEVENS SMITH 15000.00 - 19999.99 JONES MCCOY MCKNIGHT

  11. Right-Side Minus (-) Display Option • Put the minus sign on the right side of negative numbers TABLE FILE PRINT FIELDA/I8 FIELDB/I8B FIELDC/I8R FIELDD/I8- BY COUNTRY END Right-side negative Default negative Bracket negative Credit negative COUNTRYFIELDA FIELDBFIELDCFIELDD ENGLAND -123 (123) 123 CR 123- FRANCE -1234 (1234) 1234 CR 1234- GERMANY -2 (2) 2 CR 2- RUSSIA -125 (125) 125 CR 125-

  12. Create Cover PagesNEWPAGE PAGE 1 EAST REGION PAGE 2 CT $16,238,158.37 65,979DC $70,928,546.26 274,714DE $2,500,849.39 10,226MA $34,010,314.29 131,956MD $24,978,362.10 94,827 PAGE 3NORTH REGION PAGE 4 IA $2,469,227.24 10,068IL $34,444,984.60 134,351IN $12,477,236.78 50,124KS $2,136,103.34 7,870MI $47,979,137.95 191,671 • Pagebreak after a subhead or before a subfoot • Create a “cover page” for each section TABLE FILE CENTORD SUM LINEPRICE AS ‘ ‘QUANTITY AS ‘ ' BY REGION NOPRINT PAGE-BREAK BY STATE AS ‘ ' ON REGION SUBHEADNEWPAGE"<REGION REGION"END

  13. FORECAST Extensions Single Exponential Smoothing * Double Exponential Smoothing * Triple Exponential Smoothing * Linear Regression Column * Graphing * • EXPAVE “bootstraps” the last real data value as a factor in every predicted value • Double and triple exponential smoothing for trends and seasonal adjustments • SUM with REGRESS creates a new report column with the regression based on the summed values • GRAPH facility supports FORECAST ON TRANSDATE RECAP SEASONAL/D10.1 = FORECAST(TRANS,1,3,'SEASONAL', 3,3,1000,1);

  14. Perform Multi-Variate Regression Forecasting REGRESS • REGRESS method derives a best-fit linear equation for a set of numeric data points • Can be based on 1, 2 or 3 independent variables • Creates a new column in the report DEFINE FILE GGSALES SDATE/YYM = DATE; SYEAR/Y = SDATE;ENDTABLE FILE GGSALES PRINT BUDUNITS AS ‘Budget,Units’ UNITS AS ‘Unit,Sales’ BUDDOLS AS ‘Budget,Dollars’ DOLLARS AS ‘Dollar,Sales’ WHERE CATEGORY EQ ‘Coffee’ WHERE REGION EQ ‘West’ WHERE UNITS GT 1670 AND UNITS LT 1700 END Budget Unit Budget Dollar Units Sales Dollars Sales EST ------ ----- ------- ------ ----- 1796 1696 17960 25440 24428 1825 1695 23725 25425 23018 1613 1685 22582 18535 20954 1568 1682 23520 25230 20167 1665 1678 21645 23492 22021 1457 1671 21855 20052 19528 1662 1674 24930 18414 21043 1653 1694 21489 16940 21611 ON TABLE RECAP EST/F8 = REGRESS (3,BUDUNITS,UNITS, BUDDOLS, DOLLARS);

  15. NORMSINV and NORMSDST Functions Equivalent to the Excel functions NORMSDST - Calculates cumulative standard normal distribution function NORMSINV – Returns upper boundary of standard normal curve (inverse of NORMSDST) NORMSD/D12.5 = NORMSDST ( Z, ‘D8’); NORMSI/D12.5 = NORMSINV (NORMSD,’D8’); SIZE/D12 = NORMSI * STDEV + MEAN;

  16. Decimal Alignment in Headings • Align decimal points in a multi-line heading or footing • Supported for FORMAT HTML/PDF/PS reports only TABLE FILE BY REGION NOPRINT SUBHEAD"SALES FOR: <REGION "" ""Units: <JUNITS""Dollar Sales: <JDOLLARS""Budgeted Sales: <JBUDDOLLARS"ON TABLE SET PAGE-NUM OFFON TABLE HOLD FORMAT HTMLON TABLE SET HTMLCSS ONON TABLE SET STYLE *TYPE = REPORT, GRID=OFF, $TYPE=SUBHEAD, ITEM=1, WIDTH=1.5,$TYPE=SUBHEAD, ITEM=2, WIDTH=2, JUSTIFY=DECIMAL(1), $END SALES FOR: MidwestUnits: 905,045.Dollar Sales: 11,400,665.00Budgeted Sales: 11,194,373.00000SALES FOR: NortheastUnits: 916,675.Dollar Sales: 11,392,310.00Budgeted Sales: 11,576,932.00000SALES FOR: SoutheastUnits: 935,232.Dollar Sales: 11,710,379.00Budgeted Sales: 11,807,981.00000 HEADING, FOOTING, SUBHEAD, SUBFOOT, TABHEADING, TABFOOTING

  17. Compound Reports in one PDF/PS FileOPEN/CLOSE • Combine multiple reports into a single PDF or PS file • Make separate pages or one continuous report • Combine any output formats with PDF/PS ON TABLE PCHOLD FORMAT {PDF/PS}{OPEN/CLOSE} NOBREAK PDF TABLE TABLE TABLE EXL2K PDF HTML Next Key Feature

  18. Stylesheet Enhancements Cascading Stylesheets Compound Reports in one PDF / PS file GRID = ON / FILL / OFF WRAP = ON / OFF PAGECOLOR = color STYLE = [+/-] UNDERLINESET SET PSPAGESETUP = ON / OFF SET STYLEMODE = PAGED Row/Cell Borders With Style/Color Stylesheet Macros Conditional Styling TOPGAP / BOTTOMGAP Around Report Heading / Footing Element Alignment Direct from WebFOCUS • Styling capabilities of WebFOCUS are now in mainframe FOCUS

  19. Stylesheet EnhancementsExample TABLE FILE GGSALES SUM UNITS DOLLARS ON TABLE HOLD FORMAT HTML ON TABLE SET PAGE_NUM OFF ON TABLE SET STYLE * TYPE=REPORT, GRID=FILL,$ END Next Key Feature

  20. PERFORMANCE BOOSTERS FOCUS 7.3 Performance Boosters

  21. New Compiler for DEFINEsSET DEFINES • New, more efficient compiler • Compiles only the DEFINES needed to run the request • Compiles at runtime of TABLE, not at DEFINE parsing SET DEFINES = COMPILED DEFINE FILE CAR CNTRY3/A3 = EDIT(COUNTRY,’999’); PROFIT = RCOST – DCOST ; FEET = LENGTH / 12 ; END TABLE FILE CAR PRINT LENGTH RCOST DCOST FEETPROFIT BY COUNTRY BY CAR BY MODEL END Compiles FEET and PROFIT but not CNTRY3 Next Key Feature

  22. Parse Masters Only OnceSET SAVEDMASTERS • Up to 99 parsed masters may be stored in memory • Most effective with large MFDs reused many times during one session or job CODE SET SAVEDMASTERS=3 TABLE FILE CAR TABLE FILE EMPLOYEE TABLE FILE CAR TABLE FILE PERSONEL TABLE FILE JOBS TABLE FILE EMPLOYEE EFFECT IN MEMORY Stores CAR Stores EMPLOYEE Re-uses CAR; moves it to top of list Stores PERSONEL Stores JOBS (drops EMPLOYEE) Re-stores EMPLOYEE (drops CAR) Next Key Feature

  23. FILENAME=EMPINFO ,SUFFIX=SQLDS,$ SEGNAME=EMPINFO ,SEGTYPE=S0,$ FIELD=EMP_ID ,ALIAS=EID ,USAGE=A9 ,ACTUAL=A9 ,$ FIELD=LAST_NAME ,ALIAS=LN ,USAGE=A15 ,ACTUAL=A15 ,$ . . . . FIELD=JOBDESC, ALIAS=JDSC, USAGE=TX50, ACTUAL=TX ,$FIELD=ED_HRS ,ALIAS=OJT ,USAGE=F6.2 ,ACTUAL=F4 ,$ FOCUS DB2 FOCUS DB2 RDBMS column name TX Field CLOB Field TABLE FILE EMPINFO PRINT EMP_ID LN FN DPT CJC IF JOBDESC CONTAINS 'PR'END SELECT 1."EID",T1."LN",T1."FN",T1."DPT",T1."CJC" FROM USER1."EMPINFO" T1 WHERE (T1."JDSC" LIKE '%PR%') FOR FETCH ONLY; TX Field CLOB Field Optimize RDBMS Variable Length Character Dataand Increase Row Size • IF criteria can now reference RDBMS variable character data types • VARCHAR, LONG VARCHAR, and CLOB – now up to 32K • Described in the Master with USAGE=TX, ACTUAL=TX • Read-only selection performed by RDBMS improves performance • Must use CONTAINS or OMITS against a TX field • In the SQL, CONTAINS becomes LIKE, and OMITS becomes NOT LIKE DONE

  24. Pre-Check Subroutine ArgumentsSET USERFCHK • Verify and fix arguments of FOCUS functions or DEFINE functions • Adjust the level of error checking of parameter lengths and type specifications • SET USERFCHK = ON / FULL / OFF / ALERT • ON (default) Attempts to fix a length problem. • If fixed, displays a warning; if not, displays an error • FULL Same as ON but also checks functions in MFD • OFF Turns verification off except in extreme cases • ALERT Same as OFF but a warning message is issued ADDRESS/A30 = ‘1600 PENNSYLVANIA AVENUE’; TOKEN3/A3 = GETTOK (ADDRESS, 20, 3, ' ', 10, TOKEN); (FOC36335) PARAMETER LENGTH CONFLICT IN FUNCTION name ARG number

  25. Support for READLIMIT in DB2 V7.1 FETCH FIRST n ROWS TABLE FILE CAR PRINT MPG SEATS BY MODEL IF MPG GT 20 IF READLIMIT EQ 2 END • Allow DB2 to limit rows returned • Acts like RECORDLIMIT, but handled by DB2 SELECT T1.”MODEL”,T1.”MPG”,T1.”SEATS” FROM USER1.”CAR” T1 ORDER BY T1.”MODEL” FETCH FIRST 2 ROWS ONLY; DONE

  26. OUTPUT MECHANISMS FOCUS 7.3 Output Mechanisms

  27. Working with Excel 2000HOLD FORMAT EXL2K • Supports most StyleSheet attributes • Allows for full report formatting • Displays heading, footings, subtotals Also HOLD FORMAT EXCEL97 TABLE FILE CAR HEADING "FORMAT EXCEL OUTPUT </1" SUM DEALER_COST BY COUNTRY SUBTOTAL MULTILINES BY CAR ON TABLE SET STYLE * TYPE=HEADING,STYLE=BOLD+ITALIC,$ TYPE=DATA,COLOR=RED,$ ENDSTYLE ON TABLE HOLD AS OUTEXC FORMAT EXCEL END TABLE FILE CAR HEADING "FORMAT EXL2K OUTPUT </1" SUM DEALER_COST BY COUNTRY SUBTOTAL MULTILINES BY CAR ON TABLE SET STYLE * TYPE=HEADING,STYLE=BOLD+ITALIC,$ TYPE=DATA,COLOR=RED,$ ENDSTYLE ON TABLE HOLD AS OUT2K FORMAT EXL2K END Next Key Feature DONE

  28. Cascading Style Sheets • Define formatting in statements called rules. Example: BODY {background: yellow} • Control line height and letter spacing • Exercise more control over positioning items in a report • Generate more concise HTML output TYPE=REPORT, CSSURL=http://websrv2/css/report01.css, $ TYPE=HEADING, CLASS=headText, $ TYPE=TITLE, CLASS=reportTitles, $ TYPE=DATA, CLASS=lowCost, WHEN=N3 LT 27, $ Stylesheet Commands BODY {font-family:Arial, sans-serif} TABLE {border:0} TD {border:0} reportTitles {font-weight:bolder; background:lightblue;} lowCost {color:green; font-style:italic;} headText {font-family:Times New Roman, serif; font-size:larger;text-lign:center} External CSS File

  29. Reuse FOR Values on Multiple FML RowsFORMULTIPLE • Allows use of data value as the FOR object unlimited times • Reduces the need for RECAP statements to duplicate previous data lines SET FORMULTIPLE=ON TABLE FILE CAR SUM DEALER_COST FOR COUNTRY ENGLAND OVER FRANCE OVER ITALY OVER ‘W GERMANY’ OVER ENGLAND OR FRANCE OR ITALY OR ‘W GERMANY’ AS TOTAL END TABLE FILE CAR SUM DEALER_COST FOR COUNTRY ENGLAND LABEL R1 OVER FRANCE LABEL R2 OVER ITALY LABEL R3 OVER ‘W GERMANY’ LABEL R4 OVER RECAP R5=R1+R2+R3+R4; AS TOTAL END DEALER_COST ----------- ENGLAND 37,853 FRANCE 4,631 ITALY 41,235 W GERMANY 54,563 TOTAL 138,282 Use Label References Reuse FOR objects DONE

  30. FML Hierarchy • Dynamically generate FML code based on a hierarchical relationship, such as a chart of accounts or org chart • Up to 99 levels deep • Two new Master File elements: PROPERTY, REFERENCE Field Link FIELDNAME =PRODUCT_MEMBER ,E04 ,A7 ,A07 ,$ FIELDNAME =PRODUCT_CAPTION ,E05 ,A18 ,A18 , PROPERTY=CAPTION, REFERENCE=PRODUCT_MEMBER, $ FIELDNAME =PRODUCT_PARENT ,E06 ,A7 ,A07 , PROPERTY=PARENT_OF, REFERENCE=PRODUCT_MEMBER, $ Used for “AS” name Parent Data Info

  31. Use FML Row InformationFMLINFO Function • Returns the FOR value of a row in an FML report • Use in COMPUTE for drilldowns, sign changes, etc. SET FORMULTIPLE = ON JOIN ACCOUNT IN CENTGL TO ALL ACCOUNT IN CENTSYSF TABLE FILE CENTGL SUM NAT_AMOUNT/D10 AS ‘ ‘ COMPUTE PRINT_AMT/D10 = IF FMLINFO('FORVALUE','A7') LT '2500‘ THEN -NAT_AMOUNT ELSE NAT_AMOUNT ; COMPUTE FORV/A4 = FMLINFO('FORVALUE', 'A4'); COMPUTE ACTION/A9 = IF FORV LT '2500' THEN 'CHANGED' ELSE 'UNCHANGED‘ ; FOR GL_ACCOUNT 2000 WITH CHILDREN 2 ADD AS CAPTIONEND CENTGL contains the hierarchy for CENTSYSF hardcoded Make PRINT_AMT the negative of NAT_AMOUNT for accounts < 2500 ActualPRINT_AMTFORVACTIONGross Margin -25,639,223 25,639,223 2000 CHANGED Sales Revenue -62,362,490 62,362,490 2100 CHANGED Retail Sales -49,355,184 49,355,184 2200 CHANGED Mail Order Sales -6,899,416 6,899,416 2300 CHANGED Cost Of Goods Sold 36,723,267 36,723,267 2500 UNCHANGED Variable Material Costs 27,438,625 27,438,625 2600 UNCHANGED

  32. Indenting FML ReportsINDENT • Indent FML tags, labels or captions by a specific amount TABLE FILE CENTGLPRINT GL_ACCOUNT_PARENT AS ‘Parent’ FOR GL_ACCOUNT1000 AS 'Not Indented‘ OVER2000 INDENT 5 AS 'Indented 5‘ OVER3000 GET CHILDREN 2 INDENT 5 AS 'Hierarchy Indented 5' END Parent ------Not Indented 1000 Indented 5 1000 Hierarchy Indented 5 3000 Hierarchy Indented 5 3100 Hierarchy Indented 5 3100 Hierarchy Indented 5 3100 Hierarchy Indented 5 3000 Hierarchy Indented 5 3200 Hierarchy Indented 5 3200 Explanation The label of the second row for tag value 3000 is indented five spaces. The GET CHILDREN phrase causes the first line of the FML hierarchy to indent seven spaces (five + two).

  33. Indenting FML HierarchySET BLANKINDENT • Specify the indentation between FML hierarchy levels • Valid in HTML, PDF and Postscript reports • Indents captions 0.125 or ‘n’ units per normal space • Use in conjunction with stylesheet commands SQUEEZE and POSITION SET BLANKINDENT = OFF | ON | number

  34. Differentiate Missing Data in HOLD FileSET HOLDMISS • Distinguish in HOLD files between missing data and default values (blank for character data and zero for numeric data) SET HOLDMISS=ON Blanks replaced by Missing character TABLE FILE CAR SUM DCOST BY COUNTRY ACROSS SEATS ON TABLE HOLD FORMAT ALPHA END ENGLAND 11719 14940 11194 FRANCE 4631 ITALY 36320 4915 JAPAN 5512 W GERMANY 6000 48563 ENGLAND 11719 14940 11194 FRANCE . . 4631 ITALY 36320 4915. JAPAN . 5512. W GERMANY . 6000 48563

  35. Declare NODATA Character for HOLD FilesSET HNODATA • NODATA – Substitutes for missing data in a report • HNODATA – Same functionality for files created using HOLD FORMAT ALPHA Forces MISSING Attribute FOCEXEC HOLD FILE SET HOLDMISS=ON SET HNODATA=BLANK ALFA ROMEO BLANK AUDI 12 MONTHS OR 20000 MILES BMW BLANK DATSUN 12 MONTHS OR 12000 MILES JAGUAR 12 MONTHS OR 12000 MILES JENSEN 12000 MILES OR 12 MONTHS MASERATI PEUGEOT 12 MONTHS ON 12000 MILES TOYOTA 12 MONTHS OR 12500 MILES TRIUMPH 12 MONTHS OR 12000 MILES ALFA ROMEO AUDI 12 MONTHS OR 20000 MILES BMW DATSUN 12 MONTHS OR 12000 MILES JAGUAR 12 MONTHS OR 12000 MILES JENSEN 12000 MILES OR 12 MONTHS MASERATI PEUGEOT 12 MONTHS ON 12000 MILES TOYOTA 12 MONTHS OR 12500 MILES TRIUMPH 12 MONTHS OR 12000 MILES SET ALL=ON TABLE FILE CAR PRINT WARRANTY BY CAR ON TABLE HOLD FORMAT ALPHA END Truly blank value

  36. Represent Missing Values in HOLD FilesSET NULL=ON • Propogate missing values to HOLD files with visually consecutive delimiters • HOLD Formats COM, COMT, TAB, TABT only SET NULL = ON TABLE FILE PRINT FIELD1 FIELD2 FIELD3 ON TABLE HOLD FORMAT COMT END “NEW YORK”,2,3 “NEW YORK”,0,1 “NEW YORK”,0,0 “NEW YORK”,,3 “NEW YORK”,2, “NEW YORK”,, Both fields present FIELD2 is zero Both fields are zero FIELD2 is missing FIELD3 is missing Both fields are missing Next Key Feature

  37. Create HOLD Files in Alphanumeric FormatSET HOLDFORMAT • Create HOLD files in alphanumeric format • FTP hold files to the PC Default SET HOLDFORMAT = BINARY / ALPHA Binary Data Alpha Data

  38. DATABASE AND MASTER FOCUS 7.3 Database and Master

  39. XFOCUS Database FOCUS Database XFOCUS Database • XFOCUS database file may grow to 16 gigabytes • With partitioning, up to4 terabytesfor data warehousing • No change to focexecs • Convert with standard REBUILD Will be activated via SET XFC=ON in subsequent 7.3 Service Pack

  40. FOCUS DATABASE 4K page size Alpha fields up to A3964 Up to 2-gig per physical file Up to 255 2-gig partitions Up to ½ terabyte total size Segment names up to 8 chars Index field names up to 12 chars Page 1 contains FDT and data XFOCUS DATABASE 16k page size; 2X as many pages Alpha fields up to A4096 Up to 16-gig per physical file Up to 255 16-gig partitions Up to 4 terabytes total size Segment names up to 64 chars Index field names up to 66 chars Page 1 dedicated to FDT XFOCUS DatabaseComparison Chart - - - - - - - - More records per page More pages per file Larger files Reduced i/o Fewer partitions TRUE DATA WAREHOUSE

  41. XFOCUS DatabaseReplacing Legacy Files Converting is easy! Just REBUILD! FILE=filename, SUFFIX=FOCUS SEGNAME= DUMP FILE=filename, SUFFIX=XFOCUS SEGNAME= LOAD

  42. XFOCUS DatabaseFor Data Warehousing All FOCUS syntax and capabilities work with XFOCUS files, except as follows (may be supported in later releases): • Maintain • HLI calls • Sink machine (SU) • External Index and Multi-Dimensional Index (MDI) • Static cross references (KU, KL, KM) • Permitted, but indexes not resolved (acts as DKU/DKM) XFOCUS Database is oriented toward Data Warehouse applications rather than transactional updates Next Key Feature

  43. High Performance Multi-Dimensional Index – MDI MDI provides virtually instant access to the data you need MDI has been in use for years in our FUSION product CAR MODEL COUNTRY MDIs can have more than 3 dimensions DONE

  44. High Performance Multi-Dimensional Index – MDI(FOCUS and XFOCUS Files) REBUILD MDINDEX creates new MDIs, or adds new partitions to an existing MDI • MASTERNAME CAR • DATANAME 'CARACX1 FOCUS M' • DATANAME 'CARACX2 FOCUS M' • DATANAME 'CARACX3 FOCUS M' • MDILOCATION CARMDI • TARGET_OF ORIGIN • DIMENSION CAR • DIMENSION COUNTRY • DIMENSION MODEL • DATANAME 'CARMDI MDI M' ACCESS FILE TABLE FILE CAR PRINT BODYTYPE SEATS WHERE CAR EQ 'JAGUAR' WHERE COUNTRY EQ 'ENGLAND' END Unlike RDBMS Clustered index, MDI fields can be in any order and all fields need not be used Next Key Feature

  45. Long Segment Names in XFOC Files • Up to 64 characters long SEGNAME = VERYLONGNAMEFORASEGMENT,SEGTYPE=…,$ Error generated 8 char max Pre 7.3 Now 64 char max 7.3 SEGNAME = VERYLONGNAMEFORASEGMENT,SEGTYPE=…,$

  46. Long Index Names in XFOC Files • Up to 66 characters long FIELD = VERYLONGNAMEFORANINDEX,, I5, INDEX=I,$ Pre 7.3 Error generated 12 char max Now 66 char max 7.3 FIELD = VERYLONGNAMEFORANINDEX,, I5, INDEX=I,$ Now, index names can be the full length of field names

  47. FILENAME=EMPLOYEE, SUFFIX=FOC SEGNAME=EMPINFO, SEGTYPE=S1 FIELDNAME=EMP_ID, ,FORMAT=A9,$GROUP = FULLNAME, ,FORMAT=A29,$ FIELDNAME=LAST, , FORMAT=A15,$ FIELDNAME=FIRST, , FORMAT=A10,$ FIELDNAME=HIRED,, FORMAT=I6YMD,$ GROUP Fields in MasterNow for FOCUS Files Too What is a GROUP? Consecutive fields in a master that represent a logical collection, such as Firstname, Middle initial, Lastname Group length =LAST+ FIRST+HIRED (HIRED is integer with internal length of 4) Index a group for JOIN and MODIFY Components may be separated by slashes TABLE FILE EMPGROUP PRINT EMP_ID LAST FIRST HIRED BY FULLNAME NOPRINTWHERE FULLNAME GT 'CROSS/BARBARA/991102'END Next Key Feature

  48. Physical Name of LOCATION FileDATASET At Segment Level • DATASET attribute already available at the file level in a FOCUS Master File • Now specify the physical file name of a LOCATION segment, or a cross-referenced segment with field redefinitions DATASET was already available on the FILE level FILE = ... , DATASETSEGNAME=BODY,SEGTYPE=S1,PARENT=CARREC,LOCATION=BODYSEG,DATASET='USER1.XYZ2.FOCUS',$FIELDNAME=BODYTYPE,TYPE,A12,$FIELDNAME=SEATS,SEAT,I3,$FIELDNAME=DEALER_COST,DCOST,D7,$ Segment now in its own physical file XYZ1 XYZ2 XYZ3 XYZ2

  49. Read-only Fields in MasterFIELDTYPE = R • FIELDTYPE=R identifies a field as read-only • UPDATE issued for a read-only field is ignored by MODIFY and MAINTAIN • Supports relational data sources with auto-increment columns (automatically incremented by the RDBMS) • IDENTIFY or timestamp columns in DB2 FILE=MNTAUTO , SUFFIX=SQLMSS ,$SEGNAME=MNTAUTO, SEGTYPE=S0 ,$FIELD=CONTROL ,CONTROL ,I11 ,I4 ,MISSING=OFF,FIELDTYPE=R,$FIELD=LASTNAME ,LAST ,A12 ,A12,MISSING=OFF,$FIELD=FIRSTNAME,FIRST ,A12 ,A12,MISSING=OFF,$FIELD=ITEM ,ITEM ,A20 ,A20,MISSING=OFF,$FIELD=AMOUNT ,AMOUNT ,P19 ,P10,MISSING=OFF,$

  50. Read Tab-Delimited FilesSUFFIX = TAB • Tab-delimited files for output or input • Create using HOLD FORMAT TAB • Similar to TABT, without leading column heading record TABLE FILE MOVIES PRINT COPIES TITLE WHOLESALEPR BY CATEGORY IF CATEGORY EQ 'ACTION' OR 'CHILD‘ ON TABLE HOLD AS TAB1 FORMAT TABEND > implies tab ACTION>2>JAWS>10.99 ACTION>3>ROBOCOP>11.50 ACTION>4>TOTAL RECALL>11.99 ACTION>2>TOP GUN>9.99 ACTION>3>RAMBO III>10.99CHILD>1>SMURFS, THE>10.00CHILD>2>SHAGGY DOG, THE>29.99 FILE=TAB1 ,SUFFIX=TABSEGNAME=TAB1 ,SEGTYPE=S01 FIELDNAME =CATEGORY ,E01 ,A8 ,A08 ,$FIELDNAME =COPIES ,E02 ,I3 ,A03 ,$FIELDNAME =TITLE ,E03 ,A39 ,A39 ,$FIELDNAME =WHOLESALEPR ,E04 ,F6.2 ,A06 ,$

More Related