1 / 0

Even More Things You Can Use on Monday

Even More Things You Can Use on Monday. Rob Palmer Solution Director. More Things You Can Use on Monday. Agenda SET Parameters Reporting Synonyms Dialogue Manager Functions App Commands Troubleshooting Efficiency. SET Parameters. SET Parameters.

willow
Download Presentation

Even More Things You Can Use on Monday

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. Even More Things You Can Use on Monday

    Rob Palmer Solution Director
  2. More Things You Can Use on Monday Agenda SET Parameters Reporting Synonyms Dialogue Manager Functions App Commands Troubleshooting Efficiency
  3. SET Parameters
  4. SET Parameters A few more of my favourite SET parameters SUBTOTALS ONFIELD TESTDATE DATETIME BUSDAYS HDAY EQTEST EXPANDABLE, EXPANDBYROW
  5. SET Parameters – SUBTOTALS Controls where all subtotals are placed Options: ABOVE, BELOW Default: BELOW SET SUBTOTALS=BELOW SET SUBTOTALS=ABOVE
  6. SET Parameters – ONFIELD Controls the outcome when a field referenced by “ON field …” is not used in the report Options: IGNORE, ALL Default: ALL CATEGORY used on a SUBTOTAL but not as a BY field TABLE FILE GGSALES SUM DOLLARS BY REGION BY PRODUCT ON CATEGORY SUBTOTAL END
  7. SET Parameters – ONFIELD SET ONFIELD = ALL 0 ERROR AT OR NEAR LINE 7 IN PROCEDURE onfield FOCEXEC *(FOC013) THE 'ON FIELDNAME' FIELD IS NOT A SORT FIELD: CARBYPASSING TO END OF COMMAND(FOC009) INCOMPLETE REQUEST STATEMENT TABLE FILE GGSALES SUM DOLLARS BY REGION BY PRODUCT ON CATEGORY SUBTOTAL END SET ONFIELD = IGNORE
  8. SET Parameters – TESTDATE Changes current WebFOCUS “Today” date Options: YYMD, date Default: Current system date SET TESTDATE = 20120602 -TYPE &YYMD -TYPE &MDYY -TYPE &DATEMtrDYY 2012060206022012June 2, 2012
  9. SET Parameters – DATETIME Controls &TOD (current time variable) Options: STARTUP, CURRENT, RESET Default: STARTUP -TYPE &TOD TABLE FILE -TYPE &TOD TABLE FILE -RUN -TYPE &TOD … … SET DATETIME = STARTUP 15.07.57 0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5 15.07.57 0 NUMBER OF RECORDS IN TABLE= 10 LINES= 10 15.07.57
  10. SET Parameters – DATETIME -TYPE &TOD TABLE FILE -TYPE &TOD TABLE FILE -RUN -TYPE &TOD … … SET DATETIME = CURRENT 15.07.57 0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5 15.08.03 0 NUMBER OF RECORDS IN TABLE= 10 LINES= 10 15.08.13
  11. SET Parameters – DATETIME -TYPE &TOD TABLE FILE -TYPE &TOD SET DATETIME = RESET TABLE FILE -RUN -TYPE &TOD … … SET DATETIME = CURRENT 15.07.57 0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5 15.08.03 0 NUMBER OF RECORDS IN TABLE= 10 LINES= 10 15.08.03
  12. SET Parameters – BUSDAYS Controls definition of business days Options: SMTWTFS, _ to skip Default: _MTWTF_ COMPUTE TODAY/MtDYY='&MDYY'; COMPUTE NEXT_BUSDAY/MtDYY = DATEADD(TODAY, 'BD', 1); SET TESTDATE = 20120602 SET BUSDAYS = SMTWTFS Friday Saturday
  13. SET Parameters – BUSDAYS COMPUTE TODAY/MtDYY = '&MDYY'; COMPUTE NEXT_BUSDAY/MtDYY = DATEADD(TODAY, 'BD', 1); SET TESTDATE = 20120602 SET BUSDAYS = _MTWTF_ Friday Monday
  14. SET Parameters – HDAY Identifies holidays; affects business days Options: xxxx (filename: /ibi/srv77/wfs/etc/hdayxxxx.err) Default: none hdayusxx.err hdaycanx.err 20120102 New Year's Day 20120116 Martin Luther King, Jr. 20120220 President's Day 20120528 Memorial Day 20120704 Independence Day 20120903 Labor Day 20121008 Columbus Day 20121112 Veterans Day 20121122 Thanksgiving Day 20121225 Christmas Day 20120102 New Year's Day 20120220 Family Day 20120406 Good Friday 20120409 Easter Monday 20120521 Victoria Day 20120701 Canada Day 20120806 Civic Holiday 20120903 Labour Day 20121008 Thanksgiving Day 20121111 Remembrance Day 20121225 Christmas Day 20121226 Boxing Day
  15. SET Parameters – HDAY SET HDAY = USXX SET HDAY = CANX Friday Friday Monday Tuesday
  16. SET Parameters – EQTEST Controls how $ are interpreted in a WHERE/IF Options: WILDCARD, EXACT Default: WILDCARD
  17. SET Parameters – EQTEST Applies to ‘$’ and ‘$*’ … TABLE FILE GGSALES IF NEW_PRODUCT_ID EQ 'G10$' END SET EQTEST = WILDCARD SET EQTEST = EXACT
  18. SET Parameters – EXPANDABLE, EXPANDBYROW Controls accordion reports EXPANDABLE Expands by column (horizontal) Options: ON, OFF Default: OFF EXPANDBYROW Expands by row (vertical) Options: ON, OFF, n Default: OFF
  19. SET Parameters – EXPANDABLE, EXPANDBYROW SET EXPANDABLE = OFFSET EXPANDBYROW = OFF
  20. SET Parameters – EXPANDABLE, EXPANDBYROW SET EXPANDABLE = ON
  21. SET Parameters – EXPANDABLE, EXPANDBYROW SET EXPANDBYROW = ON SET EXPANDBYROW = 2
  22. Reporting
  23. Reporting Conditional page headings and footings HOLD into an APP folder MORE FILEDEF with wildcards
  24. Reporting – Conditional page headings and footings Control HEADING and FOOTING with WHEN TABLE FILE GGSALES HEADING "Heading for Midwest" WHEN REGION EQ 'Midwest'; HEADING "Standard Heading" WHEN REGION NE 'Midwest'; SUM DOLLARS BY REGION PAGE-BREAK END
  25. Reporting – HOLD into an APP folder APP HOLD appname ON TABLE HOLD AS appname\filename Data and MFD written to temp Default TABLE FILE CAR SUM SALES BY COUNTRY BY CAR ON TABLE HOLD AS HSALES END
  26. Reporting – HOLD into an APP folder APP HOLD appname Puts hsales.ftm AND hsales.mas into the summit app folder APP HOLDMETA appname just holds .mas APP HOLDDATA appname just holds .ftm APP HOLD summit TABLE FILE CAR SUM SALES BY COUNTRY BY CAR ON TABLE HOLD AS HSALES END
  27. Reporting – Hold Files ON TABLE HOLD directly to an app folder HOLD AS app/filename Puts hsales.mas and hsales.ftm into the summit app folder Includes DATASET attribute in .mas TABLE FILE CAR SUM SALES BY COUNTRY BY CAR ON TABLE HOLD AS summit/HSALES END FILENAME=HSALES, SUFFIX=FIX, DATASET=summit/hsales.ftm ,$
  28. Reporting – MORE Universal concatenation Enables concatenation of like Internal Matrix DEFINE FILE CAR DOLLARS/I8=SALES; REGION/A11=COUNTRY; END GGSALES TABLE FILE filename SUM DOLLARS BY REGION END CAR
  29. Reporting – MORE Use the same report for both files Internal Matrix concatenated before output generated DEFINE FILE CAR DOLLARS/I8=SALES; REGION/A11=COUNTRY; END TABLE FILE GGSALES SUM DOLLARS BY REGION MORE FILE CAR END
  30. Reporting – MORE Can independently filter on each file All other formatting and styling goes with main TABLE FILE Now supported in Report Painter TABLE FILE GGSALES SUM DOLLARS BY REGION IF REGION EQ ‘Midwest’ ON TABLE COLUMN-TOTAL MORE FILE CAR IF COUNTRY EQ ‘ENGLAND’ OR ‘JAPAN’ END
  31. Reporting – Filedef with wildcards Enables concatenation of like flat files h_cty_england.ftm h_cty_japan.ftm
  32. Reporting – Filedef with wildcards Could use MORE to concatenate them TABLE FILE H_CTY_ENGLAND SUM SALES BY COUNTRY BY CAR MORE FILE H_CTY_JAPAN END
  33. Reporting – Filedef with wildcards Not only same Internal Matrix, but same file structure Filedef with a wildcard APP FI H_CTY DISK summit/h_cty*.ftm TABLE FILE H_CTY SUM SALES BY COUNTRY BY CAR END h_cty_england h_cty_japan
  34. Synonyms (Master Files and Access Files)
  35. Synonyms FILEDEF with wildcards MFD Profiles CHECK FILE ?FF
  36. Synonyms – Filedef with wildcards Works on DATASET attribute in synonym FILENAME=H_CTY ,SUFFIX=FIX ,DATASET=summit/h_cty*.ftm ,$ SEGMENT=H_CTY ,SEGTYPE=S2, $ FIELDNAME=COUNTRY ,ALIAS=E01 ,USAGE=A10 ,ACTUAL=A12 ,$ FIELDNAME=CAR ,ALIAS=E02 ,USAGE=A16 ,ACTUAL=A16 ,$ FIELDNAME=SALES ,ALIAS=E03 ,USAGE=I6 ,ACTUAL=I04 ,$ h_cty_england h_cty_japan
  37. Synonyms – MFD Profiles Defines a profile to run when the MFD is accessed MFD_PFOFILE = appname/profile ggfilter.fex FILENAME=GGSALES, SUFFIX=FOC, MFD_PROFILE=summit/ggfilter FILTER FILE GGSALES NAME = REGION WHERE REGION EQ 'Midwest'; END SET FILTER = REGION IN GGSALES ON
  38. Synonyms – MFD Profiles Profile runs first ggfilter turns on FILTER TABLE FILE GGSALES SUM DOLLARS BY REGION BY PRODUCT END
  39. Synonyms – CHECK FILE Syntax check of MFD/AFD Provides vital statistics of the synonym CHECK FILE synonym [HOLD] NUMBER OF ERRORS= 0 NUMBER OF SEGMENTS= n ( REAL= n VIRTUAL= n ) NUMBER OF FIELDS= n INDEXES= n FILES= n NUMBER OF DEFINES= n TOTAL LENGTH OF ALL FIELDS= n
  40. Synonyms – CHECK FILE CHECK FILE EMPLOYEE Creates a hold file and mfd with key components of the original synonym Field names Formats etc. CHECK FILE EMPLOYEE HOLD
  41. Synonyms – ?FF Shows all the fields in an MFD ?FF GGSALES FILENAME= GGSALES SEQ_NO SEQ I5 CATEGORY E02 A11 PCD E03 A04 PRODUCT E04 A16 REGION E05 A11 ST E06 A02 CITY E07 A20 STCD E08 A05 DATE E09 I8YYMD UNITS E10 I08 DOLLARS E11 I08 BUDUNITS E12 I08 BUDDOLLARS E13 I08
  42. Dialogue Manager
  43. Dialogue Manager -READFILE Date format support NOCLOSE Immediate execution following a label
  44. Dialogue Manager – -READFILE Reads a synonym and gives you & variables for all fields in an MFD Traditional technique: TABLE FILE CAR SUM SALES BY COUNTRY BY CAR ON TABLE HOLD AS HSALES END -RUN -SET &CARS=&RECORDS; -REPEAT LOOP_SALES FOR &I FROM 1 TO &CARS -READ HSALES &COUNTRY.A15. &CAR.A15. &SALES.A11. -LOOP_SALES
  45. Dialogue Manager – -READFILE Using –READFILE Creates &COUNTRY, &CAR, &SALES TABLE FILE CAR SUM SALES BY COUNTRY BY CAR ON TABLE HOLD AS HSALES END -RUN -SET &CARS=&RECORDS; -REPEAT LOOP_SALES FOR &I FROM 1 TO &CARS -READFILE HSALES
  46. Dialogue Manager – Date format support DM recognizes date literals Enables direct calculations using date & variables -SET &START = 'JUN 3 2012' ; -SET &END = '2012 7 JUN' ; -SET &DAYS = (&END - &START) + 1 ; -TYPE Summit is &DAYS days long. Summit is 5 days long.
  47. Dialogue Manager – Date display formats &DATEfmt – displays current date in selected format &DATXfmt – truncates display value -TYPE &DATEYYMD -TYPE &DATEMDYY -TYPE &DATEMtrDYY 2012/06/0306/03/2012 June 3, 2012 -TYPE &DATEMtrDYY is the current date June 3, 2012 is the current date -TYPE &DATXMtrDYY is the current date June 3, 2012 is the current date
  48. Dialogue Manager – NOCLOSE Enables file to processed with –READ and –WRITEwithout closing on a –RUN hcountry.ftm No -RUN -REPEAT READ_LOOP 5 TIMES -READ HCOUNTRY &COUNTRY.A10. -TYPE &COUNTRY -READ_LOOP
  49. Dialogue Manager – NOCLOSE With -RUN -REPEAT READ_LOOP 5 TIMES -READ HCOUNTRY &COUNTRY.A10. -TYPE &COUNTRY . . . Some other stuff happens -RUN -READ_LOOP -RUN closes hcountry
  50. Dialogue Manager – NOCLOSE Using NOCLOSE -REPEAT READ_LOOP 5 TIMES -READ HCOUNTRY NOCLOSE &COUNTRY.A10. -TYPE &COUNTRY -RUN -READ_LOOP hcountry does not close
  51. Dialogue Manager – NOCLOSE Using -READFILE -REPEAT READ_LOOP 5 TIMES -READFILE HCOUNTRY -TYPE &COUNTRY -RUN -READ_LOOP hcountry does not close
  52. Dialogue Manager – NOCLOSE and –WRITE Works the same way with –WRITE Appends after first write but not on first write Avoids pre-delete and (APPEND APP FI LOGFILE DISK summit/logfile.ftm -WRITE LOGFILE Part 1 -RUN -WRITE LOGFILE Part 2 -WRITE LOGFILE NOCLOSE Part 1 -RUN -WRITE LOGFILE NOCLOSE Part 2 logfile.ftm Part 2 logfile.ftm Part 1 Part 2
  53. Dialogue Manager – –CLOSE To explicitly close a file at anytime: Next –READ or –WRITE is at the top of the file -CLOSE filename
  54. Dialogue Manager – Immediate execution following a label Dialogue manager commands can follow a label Labels can now be 64 characters long -labelnamecommand -GOTO MESSAGE -MESSAGE TYPE Here is a message Here is a message -GOTO SET_COUNTRY -SET_COUNTRY SET &COUNTRY='ENGLAND'; &COUNTRY set to ‘ENGLAND’
  55. Functions
  56. Functions A few more of my favourite functions TRIM LCWORD, LCWORD2, LCWORD3 SLEEP
  57. Functions - TRIM Removes leading or trailing occurrences of a character TRIM(trim_where, source_string, length, pattern, sublength, output) trim_where: L=Leading, T=Trailing, B=Both Trim leading 0`s TRIMMED/A10=TRIM('L', VALUE, 10, '0', 1, TRIMMED);
  58. Functions - TRIM Trim trailing dots TRIMMED/A10=TRIM('T', VALUE, 10, '.', 1, TRIMMED);
  59. Functions – LCWORD, LCWORD2, LCWORD3 Convert strings to mixed case Each handles special situations differently Syntax is the same for all 3 LCWORD (length, string, output) LCWORD2 (length, string, output) LCWORD3 (length, string, output)
  60. Functions – LCWORD, LCWORD2, LCWORD3
  61. Functions - SLEEP Puts WebFOCUS into a wait state Two ways implement it SLEEP n SLEEP(delay, output);
  62. Functions - SLEEP SLEEP n SET DATETIME = CURRENT -RUN -TYPE &TOD SLEEP 5 -RUN -TYPE &TOD SLEEP 10 -RUN -TYPE &TOD SLEEP 5 -RUN -TYPE &TOD 5 10 5
  63. Functions - SLEEP SLEEP(delay, output); TABLE FILE CAR PRINT COUNTRY NOPRINT COMPUTE STIME/A8=HHMMSS('A8'); DELAY=SLEEP(5, 'I1'); ETIME/A8=HHMMSS('A8'); IF RECORDLIMIT EQ 1 END 5
  64. APP Commands
  65. APP Commands – APP DELETE DELETE a file from an APP folder APP DELETEFILE summit ROBHOLD FOCTEMP APP DELETEFILE appname {filename|*} filetype
  66. APP Commands – Explicit reference to a procedure By default procedures found in current APP PATH Procedures can also be reference by APP name Can run procedures anywhere without changing the path Includes monthly_report.fex out of sales APP folder Execute turnover_report out of hr APP folder -INCLUDE sales/monthly_report EX hr/turnover_report
  67. Troubleshooting
  68. Troubleshooting - LET NOPRINT = ; SET HOLDLIST=PRINTONLY TABLE FILE GGSALES SUM UNITS DOLLARS BY PRODUCT BY REGION BY ST NOPRINT BY CITY BY DATE ON TABLE HOLD AS HOLD END ?FF HOLD FILENAME= HOLDPRODUCT E01 A16REGION E02 A11CITY E03 A20 DATE E04 I8YYMD UNITS E05 I08DOLLARS E06 I08
  69. Troubleshooting - LET NOPRINT = ; LET NOPRINT=; SET HOLDLIST=PRINTONLY TABLE FILE GGSALES SUM UNITS DOLLARS BY PRODUCT BY REGION BY ST NOPRINT BY CITY BY DATE ON TABLE HOLD AS HOLD END ?FF HOLD1 FILENAME= HOLD1PRODUCT E01 A16REGION E02 A11ST E03 A02CITY E04 A20DATE E05 I8YYMDUNITS E06 I08DOLLARS E07 I08
  70. Efficiency
  71. Efficiency - SQL SET LOADONLY ON Backend MODIFY to load data MATCH = select, INCLUDE = insert MODIFY FILE synonym FIXFORM FROM ftm MATCH keys ON NOMATCH INCLUDE ON MATCH REJECT DATA ON ftm END
  72. Efficiency - SQL SET LOADONLY ON Backend MODIFY to load data Use Load Only MATCH = nothing, INCLUDE = insert INSERTs only MODIFY FILE synonym SQL SET LOADONLY ON FIXFORM FROM ftm MATCH keys ON NOMATCH INCLUDE ON MATCH REJECT DATA ON ftm END
  73. Thank You! Come back next year for: Still More Things You Can Use on Monday Maybe
More Related