Rob PalmerSolution Director
Even More Things You Can Use on Monday
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 SUBTOTALEND
SET Parameters – ONFIELD
Copyright
2010,
Information Builders. Slide 7
SET ONFIELD = ALL
SET ONFIELD = IGNORETABLE FILE GGSALES SUM DOLLARS BY REGION BY PRODUCT ON CATEGORY SUBTOTALEND
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
SET Parameters – TESTDATE
Changes current WebFOCUS “Today” date Options: YYMD, date Default: Current system date
SET TESTDATE = 20120602
2012060206022012June 2, 2012
-TYPE &YYMD-TYPE &MDYY-TYPE &DATEMtrDYY
SET Parameters – DATETIME
Controls &TOD (current time variable) Options: STARTUP, CURRENT, RESET Default: STARTUP
SET DATETIME = STARTUP
15.07.570 NUMBER OF RECORDS IN TABLE= 5 LINES= 515.07.570 NUMBER OF RECORDS IN TABLE= 10 LINES= 1015.07.57
-TYPE &TOD TABLE FILE -TYPE &TOD TABLE FILE -RUN-TYPE &TOD
……
SET Parameters – DATETIME
SET DATETIME = CURRENT
15.07.570 NUMBER OF RECORDS IN TABLE= 5 LINES= 515.08.030 NUMBER OF RECORDS IN TABLE= 10 LINES= 1015.08.13
-TYPE &TOD TABLE FILE -TYPE &TOD TABLE FILE -RUN-TYPE &TOD
……
SET Parameters – DATETIME
SET DATETIME = CURRENT
15.07.570 NUMBER OF RECORDS IN TABLE= 5 LINES= 515.08.030 NUMBER OF RECORDS IN TABLE= 10 LINES= 1015.08.03
-TYPE &TOD TABLE FILE -TYPE &TOD SET DATETIME = RESET TABLE FILE -RUN-TYPE &TOD
…
…
SET Parameters – BUSDAYS
Controls definition of business days Options: SMTWTFS, _ to skip Default: _MTWTF_
SET TESTDATE = 20120602SET BUSDAYS = SMTWTFS
COMPUTE TODAY/MtDYY='&MDYY'; COMPUTE NEXT_BUSDAY/MtDYY = DATEADD(TODAY, 'BD', 1);
Friday Saturday
SET Parameters – BUSDAYS
SET TESTDATE = 20120602SET BUSDAYS = _MTWTF_
COMPUTE TODAY/MtDYY = '&MDYY'; COMPUTE NEXT_BUSDAY/MtDYY = DATEADD(TODAY, 'BD', 1);
Friday Monday
SET Parameters – HDAY
Identifies holidays; affects business days Options: xxxx (filename: /ibi/srv77/wfs/etc/hdayxxxx.err) Default: none
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 Day20120220 Family Day20120406 Good Friday20120409 Easter Monday20120521 Victoria Day20120701 Canada Day20120806 Civic Holiday20120903 Labour Day20121008 Thanksgiving Day20121111 Remembrance Day20121225 Christmas Day20121226 Boxing Day
hdayusxx.err hdaycanx.err
SET Parameters – HDAY
Friday Monday
SET HDAY = CANX
Friday Tuesday
SET HDAY = USXX
SET Parameters – EQTEST
Controls how $ are interpreted in a WHERE/IF Options: WILDCARD, EXACT Default: WILDCARD
Applies to ‘$’ and ‘$*’
SET Parameters – EQTEST
SET EQTEST = WILDCARD SET EQTEST = EXACT
TABLE FILE GGSALES
IF NEW_PRODUCT_ID EQ 'G10$'END
…
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 DOLLARSBY REGION PAGE-BREAKEND
APP HOLD appname ON TABLE HOLD AS appname\filename
Data and MFD written to temp
Reporting – HOLD into an APP folder
TABLE FILE CARSUM SALESBY COUNTRYBY CARON TABLE HOLD AS HSALESEND
Default
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
Reporting – HOLD into an APP folder
APP HOLD summitTABLE FILE CAR SUM SALES BY COUNTRY BY CAR ON TABLE HOLD AS HSALESEND
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/HSALESEND
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
CAR
TABLE FILE filename SUM DOLLARS BY REGIONEND
Use the same report for both files Internal Matrix concatenated before output generated
Reporting – MORE
DEFINE FILE CAR DOLLARS/I8=SALES; REGION/A11=COUNTRY;END
TABLE FILE GGSALES SUM DOLLARS BY REGIONMORE FILE CAREND
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-TOTALMOREFILE CARIF 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
TABLE FILE H_CTY_ENGLAND SUM SALES BY COUNTRY BY CARMOREFILE H_CTY_JAPANEND
Could use MORE to concatenate them
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 CAREND
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
Defines a profile to run when the MFD is accessed MFD_PFOFILE = appname/profile
ggfilter.fex
Synonyms – MFD Profiles
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
TABLE FILE GGSALES SUM DOLLARS BY REGION BY PRODUCTEND
Profile runs first ggfilter turns on FILTER
Syntax check of MFD/AFD Provides vital statistics of the synonym
CHECK FILE synonym [HOLD]
Synonyms – CHECK FILE
NUMBER OF ERRORS= 0NUMBER 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
Creates a hold file and mfd with key components of the original synonym Field names Formats etc.
Synonyms – CHECK FILE
CHECK FILE EMPLOYEE
CHECK FILE EMPLOYEE HOLD
Synonyms – ?FF
Shows all the fields in an MFD
?FF GGSALESFILENAME= GGSALESSEQ_NO SEQ I5CATEGORY E02 A11PCD E03 A04PRODUCT E04 A16REGION E05 A11ST E06 A02CITY E07 A20STCD E08 A05DATE E09 I8YYMDUNITS E10 I08DOLLARS E11 I08BUDUNITS E12 I08BUDDOLLARS E13 I08
Dialogue Manager
Dialogue Manager
-READFILE Date format support NOCLOSE Immediate execution following a label
Dialogue Manager – -READFILE
TABLE FILE CAR SUM SALES BY COUNTRY BY CAR ON TABLE HOLD AS HSALESEND-RUN-SET &CARS=&RECORDS;
-REPEAT LOOP_SALES FOR &I FROM 1 TO &CARS-READ HSALES &COUNTRY.A15. &CAR.A15. &SALES.A11.-LOOP_SALES
Reads a synonym and gives you & variables for all fields in an MFD Traditional technique:
Dialogue Manager – -READFILE
TABLE FILE CAR SUM SALES BY COUNTRY BY CAR ON TABLE HOLD AS HSALESEND-RUN-SET &CARS=&RECORDS;
-REPEAT LOOP_SALES FOR &I FROM 1 TO &CARS-READFILE HSALES
Using –READFILE
Creates &COUNTRY, &CAR, &SALES
Dialogue Manager – Date format support
-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.
DM recognizes date literals Enables direct calculations using date & variables
&DATEfmt – displays current date in selected format
&DATXfmt – truncates display value
Dialogue Manager – Date display formats
-TYPE &DATEYYMD-TYPE &DATEMDYY-TYPE &DATEMtrDYY
-TYPE &DATEMtrDYY is the current date2012/06/0306/03/2012June 3, 2012
-TYPE &DATXMtrDYY is the current date
June 3, 2012 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
-REPEAT READ_LOOP 5 TIMES-READ HCOUNTRY &COUNTRY.A10.-TYPE &COUNTRY-READ_LOOP
No -RUN
Dialogue Manager – NOCLOSE
-REPEAT READ_LOOP 5 TIMES-READ HCOUNTRY &COUNTRY.A10.-TYPE &COUNTRY. . . Some other stuff happens-RUN-READ_LOOP
-RUN closes hcountry
With -RUN
Dialogue Manager – NOCLOSE
-REPEAT READ_LOOP 5 TIMES-READ HCOUNTRY NOCLOSE &COUNTRY.A10.-TYPE &COUNTRY-RUN-READ_LOOP
hcountry does not close
Using NOCLOSE
Dialogue Manager – NOCLOSE
-REPEAT READ_LOOP 5 TIMES-READFILE HCOUNTRY-TYPE &COUNTRY-RUN-READ_LOOP
hcountry does not close
Using -READFILE
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
logfile.ftmPart 2
-WRITE LOGFILE NOCLOSE Part 1-RUN-WRITE LOGFILE NOCLOSE Part 2
logfile.ftmPart 1Part 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
-labelname command
-GOTO MESSAGE
-MESSAGE TYPE Here is a message
-GOTO SET_COUNTRY
-SET_COUNTRY SET &COUNTRY='ENGLAND';
Here is a message
&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);
Trim trailing dots
Functions - TRIM
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
APP DELETEFILE appname {filename|*} filetype
DELETE a file from an APP folder
APP DELETEFILE summit ROBHOLD FOCTEMP
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
APP Commands – Explicit reference to a procedure
-INCLUDE sales/monthly_report
EX hr/turnover_report
Troubleshooting
SET HOLDLIST=PRINTONLYTABLE FILE GGSALES SUM UNITS DOLLARS BY PRODUCT BY REGION BY ST NOPRINT BY CITY BY DATE ON TABLE HOLD AS HOLDEND
Troubleshooting - LET NOPRINT = ;
FILENAME= HOLDPRODUCT E01 A16REGION E02 A11CITY E03 A20 DATE E04 I8YYMD UNITS E05 I08DOLLARS E06 I08
?FF HOLD
FILENAME= HOLD1PRODUCT E01 A16REGION E02 A11ST E03 A02CITY E04 A20DATE E05 I8YYMDUNITS E06 I08DOLLARS E07 I08
Troubleshooting - LET NOPRINT = ;
?FF HOLD1
LET NOPRINT=;
SET HOLDLIST=PRINTONLYTABLE FILE GGSALES SUM UNITS DOLLARS BY PRODUCT BY REGION BY ST NOPRINT BY CITY BY DATE ON TABLE HOLD AS HOLDEND
Efficiency
Efficiency - SQL SET LOADONLY ON
MODIFY FILE synonym FIXFORM FROM ftm MATCH keys ON NOMATCH INCLUDE ON MATCH REJECTDATA ON ftmEND
Backend MODIFY to load data
MATCH = select, INCLUDE = insert
Efficiency - SQL SET LOADONLY ON
MODIFY FILE synonym SQL SET LOADONLY ON FIXFORM FROM ftm MATCH keys ON NOMATCH INCLUDE ON MATCH REJECTDATA ON ftmEND
Backend MODIFY to load data Use Load Only
MATCH = nothing, INCLUDE = insert INSERTs only
Thank You!
Come back next year for:
Still More Things You Can Use on Monday
Maybe