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 A few more of my favourite SET parameters SUBTOTALS ONFIELD TESTDATE DATETIME BUSDAYS HDAY EQTEST EXPANDABLE, EXPANDBYROW

SET Parameters – SUBTOTALS Controls where all subtotals are placed Options: ABOVE, BELOW Default: BELOW SET SUBTOTALS=BELOW SET SUBTOTALS=ABOVE

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

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

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

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

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

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

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

SET Parameters – BUSDAYS COMPUTE TODAY/MtDYY = '&MDYY'; COMPUTE NEXT_BUSDAY/MtDYY = DATEADD(TODAY, 'BD', 1); SET TESTDATE = 20120602 SET BUSDAYS = _MTWTF_ Friday Monday

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

SET Parameters – HDAY SET HDAY = USXX SET HDAY = CANX Friday Friday Monday Tuesday

SET Parameters – EQTEST Controls how $ are interpreted in a WHERE/IF Options: WILDCARD, EXACT Default: WILDCARD

SET Parameters – EQTEST Applies to ‘$’ and ‘$*’ … TABLE FILE GGSALES IF NEW_PRODUCT_ID EQ 'G10$' END SET EQTEST = WILDCARD SET EQTEST = EXACT

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

SET Parameters – EXPANDABLE, EXPANDBYROW SET EXPANDABLE = OFFSET EXPANDBYROW = OFF

SET Parameters – EXPANDABLE, EXPANDBYROW SET EXPANDABLE = ON

SET Parameters – EXPANDABLE, EXPANDBYROW SET EXPANDBYROW = ON SET EXPANDBYROW = 2

Reporting

Reporting Conditional page headings and footings HOLD into an APP folder MORE FILEDEF with wildcards

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

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

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

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 ,$

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

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

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

Reporting – Filedef with wildcards Enables concatenation of like flat files h_cty_england.ftm h_cty_japan.ftm

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

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

Synonyms (Master Files and Access Files)

Synonyms FILEDEF with wildcards MFD Profiles CHECK FILE ?FF

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

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

Synonyms – MFD Profiles Profile runs first ggfilter turns on FILTER TABLE FILE GGSALES SUM DOLLARS BY REGION BY PRODUCT END

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

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

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

Dialogue Manager

Dialogue Manager -READFILE Date format support NOCLOSE Immediate execution following a label

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

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

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.

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

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

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

Dialogue Manager – NOCLOSE Using NOCLOSE -REPEAT READ_LOOP 5 TIMES -READ HCOUNTRY NOCLOSE &COUNTRY.A10. -TYPE &COUNTRY -RUN -READ_LOOP hcountry does not close

Dialogue Manager – NOCLOSE Using -READFILE -REPEAT READ_LOOP 5 TIMES -READFILE HCOUNTRY -TYPE &COUNTRY -RUN -READ_LOOP hcountry does not close

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

Dialogue Manager – –CLOSE To explicitly close a file at anytime: Next –READ or –WRITE is at the top of the file -CLOSE filename

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’

Functions

Functions A few more of my favourite functions TRIM LCWORD, LCWORD2, LCWORD3 SLEEP

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);

Functions - TRIM Trim trailing dots TRIMMED/A10=TRIM('T', VALUE, 10, '.', 1, TRIMMED);

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)

Functions – LCWORD, LCWORD2, LCWORD3

Functions - SLEEP Puts WebFOCUS into a wait state Two ways implement it SLEEP n SLEEP(delay, output);

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

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

APP Commands

APP Commands – APP DELETE DELETE a file from an APP folder APP DELETEFILE summit ROBHOLD FOCTEMP APP DELETEFILE appname {filename|*} filetype

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

Troubleshooting

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

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

Efficiency

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

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

Thank You! Come back next year for: Still More Things You Can Use on Monday Maybe

Even More Things You Can Use on Monday

Télécharger la présentation

Plus de contenu associé

Even More Things You Can Use on Monday