1 / 38

“You Asked For It, You Got It!” Best New [Backend ] Features of WebFOCUS 7.7.03

“You Asked For It, You Got It!” Best New [Backend ] Features of WebFOCUS 7.7.03. Renee Teatro Information Builders December 2011. Active Reports for FOCUS Removal of interactive ?F and ?FF prompting Business Views for FOCUS _FOC_NULL Reformatting BY and ACROSS fields

webb
Download Presentation

“You Asked For It, You Got It!” Best New [Backend ] Features of WebFOCUS 7.7.03

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. “You Asked For It, You Got It!” Best New [Backend] Features of WebFOCUS 7.7.03 Renee Teatro Information Builders December 2011

  2. Active Reports for FOCUS Removal of interactive ?F and ?FF prompting Business Views for FOCUS _FOC_NULL Reformatting BY and ACROSS fields Support WHERE clause in DBA VALUE restriction &DATEfmt with date-time formats ACROSS COLUMNS x AS 'title' OR y AS 'title' Across value format in EXL07 ALIAS=INSTANCE COLLATION in the Master CRINCLUDE=ALL CRJOINTYPE DATASET for HOLD DEFINE FUNCTION automatic inclusion DYNAM DEFAULT Extension to SET DROPBLNKLINE Extension to SET HOLDATTR File qualification for MFD constant FILEDEF for holiday file HHMS Function HOLD FORMAT MAGNIFY IOTYPE in Master MFD Profile phase 2 Multi-parent Masters (MFACT) Multipath as MATCH (MFACT) Preserving leading and trailing blanks in DFIX Retrieve data associated with a prefixed field SET DISTRIBUTE SET ROWTOTFORM Simplify FOCUS cache Subqueries in Master Support C notation wherever fieldnames are supported Support for global variables as index for indexed variables TOC Styling Validating &vars to prevent SQL insertion WHERE fld EQ (FILENAME) DATASET for HOLD on PDS MISSING test support for TX fields Optimizing SUBSTR Unequal size GROUPS/RANGES Fiscal year subroutines LCWORD3 BYTEORDER in Master Extended currency symbol for Thailand and China HOLD FORMAT DB2 and index creation Sort objects in Master Support MFD DBA Restrictions in nested Adapter calls Null value testing in left outer JOIN request Ignore ON phrases for absent fields Increased length of all fields Access restrictions in multi-file structure 1024 segments 128 sort phrases in one request 32 gigabyte XFOCUS databases Longer DEFINE FUNCTION names Add and configure fonts Overflow Excel worksheets Position ACROSS titels on report Multiple DST operators in one report Multiple conditional headings and footings Summary lines above the data Record selection without wildcard characters Absolute value display TITLE and DESC for virtual fields FPRINT field conversion function Extract date component as an integer 64-character Dialogue Manager labels -READFILE to read mfd fields into DM variables You Asked For It, You Got It! Parade of Over 75 New Features

  3. You Asked For It, You Got It!WebFOCUS and FOCUS are Like Twins • 7.7.03 finally brings together WebFOCUS and FOCUS • Virtually identical backend features and functionality • New features hit all products and platforms together • Minimal difference between internal code paths • Focexecs work identically in either product • Bugs are fixed once for everyone

  4. You Asked For It, You Got It! The best features come from customer suggestions MASTER TABLE OUTPUT and Efficiencies

  5. You Asked For It, You Got It! MASTER TABLE MetaTable in the Master (Compute/Filter) DBA WHERE Extended Limits Absolute Value format Business Views OUTPUT and Efficiencies

  6. You Asked For It, You Got It!FILTER in MFD Put your global filters into the metadata for general availability * FILE = filename SEGMENT = FIELD=FIELD1,, A3,$ FIELD=FIELD2,, A4,$ DEFINE DEFFIELD/A7 = FIELD1 | FIELD2 ; ,$ FILTER FILTFIELD = FIELD1 NE FIELD2; ,$ MASTER Filter format is always I1 Expression must resolve to 0 or 1 (false or true) TABLE FILE filename PRINT FIELD1 FIELD2 DEFFIELD WHERE FILTFIELD END FOCEXEC No operator, no expression * Works independently of FILTER FILE syntax

  7. You Asked For It, You Got It!COMPUTE in MFD Put global Computes into the metadata for general availability FILE = filename SEGMENT = FIELD=FIELD1,, A3,$ FIELD=FIELD2,, A4,$ DEFINE DEFFIELD/A7 = FIELD1 | FIELD2 ; ,$ COMPUTE COMPFIELD/D12.2 = IF…THEN…ELSE… ; ,$ MASTER Any format Any expression TABLE FILE filename PRINT FIELD1 FIELD2 DEFFIELD COMPUTE COMPFIELD; END FOCEXEC No operator, no expression, add semicolon

  8. You Asked For It, You Got It!WHERE in DBA Use WHERE in DBA instead of IF FILE = SEGMENT = FIELD= END DBA USER=XYZ, …RESTRICT=VALUE,VALUE=ITEM NE RADIO MASTER IF FILE = SEGMENT = FIELD= END DBA USER=XYZ, …RESTRICT=VALUE_WHERE, VALUE=ITEM NE RADIO USER=ABC, …RESTRICT=VALUE_WHERE, VALUE=ITEM NE ‘RADIO’ AND BB NE AA MASTER WHERE More complex expressions

  9. You Asked For It, You Got It!Absolute Value Format Option IF implied A – absolute value A – absolute value FILE = filename SEGMENT = FIELD= VALUE1,, FORMAT = F8.2 ,$ DEFINE VALUE2/F8.2AM = VALUE1; ,$ DEFINE VALUE3/D8.2B = VALUE1; ,$ DEFINE VALUE4/D12.2Ac = VALUE1; ,$ MASTER VALUE1VALUE2VALUE3VALUE4 -1484.27$1484.27[1,484.27]1484.27 !! Column totals add original signed values REPORT

  10. You Asked For It, You Got It!Business “View” of a Database A VIEW into the original file – no new data, no separate extraction • Reflect the business instead of physical field arrangement • Have as many different Business Views as required; Direct via USE • BV recognized only for extract (TABLE[F], GRAPH, MATCH, MORE) • JOIN a Business View to other files • Limit the fields accessible in the main file, without DBA • Make OS impose a View via security Business View Original MFD MFD FILE=anyname, CRFILE=ORIG FOLDER= FILE=ORIG, SUFFIX=any

  11. You Asked For It, You Got It!Business “View” of a Database FILE=ORIG, SUFFIX=FOC SEGNAME=SEG1, … FIELD=SOCSECNO, … FIELD=NAME, … FIELD=SALARY, … SEGNAME=SEG2, PARENT=SEG1 FIELD=COMPANY, … FILE=BUSVIEW, … SEGNAME=SEG1,CRFILE=ORIG, CRINCLUDE=ALL, $ FOLDER=FOLDER1,$ FIELD=FULLNAME,, ALIAS=NAME,$ FIELD=SSN,, ALIAS=SOCSECNO,$ FIELD=COMPANY,,TITLE=‘CO NAME’,$ This Business View omits SALARY • Rename or rearrange fields without touching original file • Each Business View field relates (maps) to an original field • TABLE with BV field names translates to original names • Attributes and relationships are taken from the original field • New DEFINEs permitted in Business View master SET FOCTRANSFORM = ON

  12. You Asked For It, You Got It!Extended Limits 256K total length of all fields in a request 1022 segments in JOIN or COMBINE 64 character Define Function name 32 gigabyte XFOCUS partition 128 BY sort phrases

  13. You Asked For It, You Got It! Across Print Hide Null Across Heading When Subtotal Above MASTER TABLE OUTPUT and Efficiencies

  14. You Asked For It, You Got It! ACROSSPRT and HIDENULLACRS One line per record retrieved TABLE FILE EMPDATA PRINT 'EMPDATA.EMPDATA.SALARY' BY 'EMPDATA.EMPDATA.MAJ' NOPRINT BY 'EMPDATA.EMPDATA.LCAREA' AS 'Area' ACROSS 'EMPDATA.EMPDATA.LCDEPT' AS 'Dept:‘ WHERE LCDEPT EQ 'Admin Services' OR 'Consulting' OR 'Accounting' OR 'Marketing' ON EMPDATA.EMPDATA.MAJ SUBTOTAL AS '' ON EMPDATA.EMPDATA.MAJ PAGE-BREAK HEADING "Compensation Report“ … One column for each value

  15. COMPENSATION REPORT DEPT Accounting Admin Services AREA Central . . . . . $25,400.00 . . . . . . . . . . . . . . North Eastern . . . . . . . . . . PAGE     1 You Asked For It, You Got It! ACROSSPRT and HIDENULLACRS One column for each ACROSS value No values in Accounting column

  16. You Asked For It, You Got It! ACROSSPRT and HIDENULLACRS • SET ACROSSPRT = COMPRESS • Eliminates MISSING cells • Moves up non-missing values within BY • Default is NORMAL • SET HIDENULLACRS = ON • Null ACROSS columns are removed when no value exists for that ACROSS column within the BY group • BY group must specify PAGE-BREAK • Higher “BY” SUBTOTAL may force column display • Ignored with ACROSS FIELD COLUMNS value … • Default is OFF

  17. COMPENSATION REPORT DEPT: Admin Services Consulting Marketing AREA Central $25,400.00 . $62,500.00 North Eastern . . $55,500.00 . . $52,000.00 . . $32,300.00 South Eastern . $49,500.00 $62,500.00 . $35,900.00 $50,500.00 Western $30,800.00 $40,900.00 $43,400.00 . . $58,800.00 Branch Admin $56,200.00 $126,300.00 $417,500.00 PAGE     1 You Asked For It, You Got It! ACROSSPRT and HIDENULLACRS SET ACROSSPRT=COMPRESS Empty rows are removed SET HIDENULLACRS=ON Accounting column is suppressed Columns may change per pagebreak

  18. COMPENSATION REPORT DEPT: Accounting Marketing AREA Corporate $83,000.00 $55,500.00 $32,400.00 $62,500.00 $79,000.00 $35,200.00 $62,500.00 . $26,400.00 . Total Corp $283,300.00 $153,200.00 PAGE     2 You Asked For It, You Got It! ACROSSPRT and HIDENULLACRS SET HIDENULLACRS=ON Accounting column returns on this page

  19. You Asked For It, You Got It!Subtotals Above the Line SET SUMMARYLINES = EXPLICIT SET SUBTOTALS = ABOVE TABLE FILE EMPDATA PRINT PIN SALARY AS 'Pay' BY MAJ NOPRINT PAGE-BREAK REPAGE SUBTOTAL BY AREA NOPRINT PAGE-BREAK ON AREA SUBTOTAL AS 'Total Area' WHEN MAJ NE 'Corporate' BY DEPT AS 'Department' Code continues…

  20. You Asked For It, You Got It!Headings WHEN HEADING CENTER "&DATEMTRDYY Compensation Report " HEADING "Corporate Division " WHEN MAJ EQ 'Corporate'; HEADING "<MAJ " "<AREA " WHEN MAJ NE 'Corporate' AND TABPAGENO EQ 1; HEADING "<MAJ (Continued) " "<AREA " WHEN MAJ NE 'Corporate’ AND TABPAGENO GT 1 ; END This heading always appears

  21.                     Compensation Report Branch Admin Central Department PIN Pay Total Branch Admin $1,592,700.00 Total Area Central $493,700.00 ADMIN SERVICES 000000180 $25,400.00 MARKETING 000000040 $62,500.00 PERSONNEL 000000240 $33,300.00 PERSONNEL 000000250 $25,000.00 PERSONNEL 000000390 $45,000.00 PROGRMING & DVLPMT 000000260 $49,500.00 PROGRMING & DVLPMT 000000290 $40,900.00 SALES 000000050 $54,100.00 SALES 000000200 $115,000.00 SALES 000000360 $43,000.00 Page     1  of     4 You Asked For It, You Got It!Subtotals Above and Headings WHEN HEADING WHEN 2 headings selected SET SUBTOTALS = ABOVE Subtotals moved above detail lines

  22.                Compensation Report Branch Admin  (Continued) North Eastern Department PIN Pay Total Area North Eastern $304,200.00 CUSTOMER SUPPORT 000000110 $19,300.00 CUSTOMER SUPPORT 000000140 $62,500.00 MARKETING 000000060 $55,500.00 MARKETING 000000310 $52,000.00 MARKETING 000000410 $32,300.00 SALES 000000190 $39,000.00 SALES 000000210 $43,600.00 Page     2  of     4 You Asked For It, You Got It!Subtotals Above and Headings WHEN HEADING WHEN 2 different headings selected (due to page 2) Subtotal above detail

  23.                    Compensation Report Corporate Division Department PIN Pay Total Corporate $436,500.00 ACCOUNTING 000000070 $83,000.00 ACCOUNTING 000000100 $32,400.00 ACCOUNTING 000000300 $79,000.00 ACCOUNTING 000000370 $62,500.00 ACCOUNTING 000000400 $26,400.00 MARKETING 000000010 $55,500.00 MARKETING 000000130 $62,500.00 MARKETING 000000320 $35,200.00 Page     1  of     1 You Asked For It, You Got It!Subtotals Above and Headings WHEN HEADING WHEN just Corporate heading selected Subtotal above detail

  24. You Asked For It, You Got It! MASTER Active Reports Output to Powerpoint SQL Efficiencies Character Collation MFACT – Multipath Handling TABLE OUTPUT and Efficiencies

  25. You Asked For It, You Got It!Output and Efficiencies Active Reports for FOCUS • Provide enduser the same ability to manipulate report • Limited to HTML reports • Complete report including javascript created in FOCUS TABLE FILE filename SUM …. … ON TABLE HOLD AS ABC FORMAT AHTML END

  26. You Asked For It, You Got It!Active Reports for FOCUS ON TABLE HOLD FORMAT HTML

  27. You Asked For It, You Got It!Active Reports for FOCUS ON TABLE HOLD FORMAT AHTML

  28. You Asked For It, You Got It!Active Reports for FOCUS • Additional Stylesheet Controls allow you to – • Apply calculations to columns and control display location • Control data display by hiding or freezing columns • Control the user menu display • Limit the number of rows per page • Add graphic visualization of numeric data • Customize colors for most of the report components • Control the Report Menu options available to the user

  29. You Asked For It, You Got It!Output to Powerpoint Instant Presentations HOLD FORMAT PPT TABLE FILE CAR HEADING CENTER "CAR SALES - CURRENT STATISTICS" "CREATED ON &DATEMTRDYY" "FOR COUNTRY <COUNTRY" " " SUM RCOST DCOST SALES BY COUNTRY PAGE-BREAK NOPRINT BY CAR UNDER-LINE NOPRINT ON CAR SUBHEAD "Current Sales for <CAR " " " BY MODEL SUB-TOTAL MULTILINES ON TABLE HOLD AS PPT4 FORMAT PPT ON TABLE SET STYLE *

  30. You Asked For It, You Got It!Output to Powerpoint WebFOCUS Graphics ! WebFOCUS Reports Dashboard, Graphics Visual Discovery Excel,HTML, PDF

  31. You Asked For It, You Got It!SQL Efficiencies More Optimization to speed your requests! • DEFINE FUNCTION – optimized where possible • Boolean expressions – optimized as SQL CASE statements • SQL.<sqlfunctionname> -- passed directly to the relational engine • DEFINE and IFTHENELSE – optimized as SQL CASE or SQL COUNT statements • WebFOCUS sorting optimized to ORDER BY expression

  32. You Asked For It, You Got It!SET COLLATION • When does DIGITAL = digital? • In 7.7 – with SET COLLATION ! • SET COLLATION = {BINARY|SRV_CI|SRV_CS|CODEPAGE} • Case Insensitive -- all WHERE tests and sorts ignore the case of the elements being compared. • Affects sorting, WHERE conditions, Comparison in Calculations, StyleSheets • ** Server only Setting ****

  33. You Asked For It, You Got It!Collation DEFINE FILE CENTINV FLAG/A1=IF CENTINV.INVINFO.PRODNAME CONTAINS 'ZT' THEN '$' ELSE 'Y'; END SET EQTEST = &EQTEST TABLE FILE CENTINV PRINT 'CENTINV.INVINFO.FLAG' 'CENTINV.INVINFO.PRICE' BY 'CENTINV.INVINFO.PRODNAME' HEADING " Setting of EQTEST is &EQTEST “ “ COLLATION is set to SRV_CI “ " Filter: WHERE CENTINV.INVINFO.FLAG EQ '$'; “ " AND PRODTYPE EQ 'DIGITAL'; " FOOTING "" WHERE ( CENTINV.INVINFO.FLAG EQ '$' ) AND ( CENTINV.INVINFO.PRODTYPE EQ 'Digital' ); …

  34. You Asked For It, You Got It!SET COLLATION Setting of EQTEST is EXACT COLLATION is set to SRV_CI Filter: WHERE CENTINV.INVINFO.FLAG EQ '$' AND PRODUCT CONTAINS 'DIGITAL'; Product Name: FLAG Price: ZT Digital PDA – Commercial $ 499.00

  35. You Asked For It, You Got It!MFACT for Multi-Path • Sort within request must be consistent - all sort fields within path of all verb objects • Multiple parents requires change to this requirement • SET FOCTRANSFORM = MFACT supports requests where sorts are applicable to only some of the verb objects • Request is decomposed to MATCH to sort the appropriate fields , followed by TABLE to format and display the results

  36. You Asked For It, You Got It!MFACT for Multi-Path EMPINFO BY EID BY LAST_NAME PAYINFO SALINFO BY PAY_DATE BY DAT_INC TABLE FILE EMPLOYEE SUM SALARY GROSS BY EID BY LAST_NAME BY HIGHEST DAT_INC BY PAY_DATE END

  37. You Asked For It, You Got It!MFACT for Multi-Path PAGE 1 EMP_ID LAST_NAME DAT_INC PAY_DATE SALARY GROSS ------ --------- ------- -------- ------ ----- 071382660 STEVENS 82/01/01 81/12/31 $11,000.00 $833.33 81/01/01 81/11/30 $10,000.00 $833.33 . 82/01/29 . $916.67 82/02/26 . $916.67 82/03/31 . $916.67 82/04/30 . $916.67 82/05/28 . $916.67 82/06/30 . $916.67 82/07/30 . $916.67 82/08/31 . $916.67 112847612 SMITH 82/01/01 82/01/29 $13,200.00 $1,100.00 . 82/02/26 . $1,100.00 82/03/31 . $1,100.00 82/04/30 . $1,100.00 82/05/28 . $1,100.00 82/06/30 . $1,100.00 82/07/30 . $1,100.00

  38. Thank You! THANK YOU THANK YOU

More Related