Transcript Title of Your Presentation

Even More Things You Can Use on Monday
Walter F. Blood
Technical Director of Product Management
1
More Things You Can Use on Monday









Agenda
SET Parameters
Reporting
Synonyms
Dialogue Manager
Functions
App Commands
Troubleshooting
Efficiency
Top 10 Things You Can Use on Monday
2
SET Parameters
3
SET Parameters
 A few more of my favourite SET parameters
 SUBTOTALS
 ONFIELD
 TESTDATE
 DATETIME
 BUSDAYS
 HDAY
 EQTEST
 EXPANDABLE, EXPANDBYROW
4
SET Parameters – SUBTOTALS
 Controls where SUBTOTALs are placed
 Options: ABOVE, BELOW
 Default: BELOW
SET SUBTOTALS=BELOW
SET SUBTOTALS=ABOVE
5
SET Parameters – ONFIELD
 Controls the outcome when a field referenced by


“ON field …” is not used in the report
Options: IGNORE, ALL
Default: ALL
TABLE FILE GGSALES
SUM DOLLARS
BY REGION
BY PRODUCT
ON CATEGORY SUBTOTAL
END
 CATEGORY used on a SUBTOTAL but not as a BY field
6
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: CAR
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
SET ONFIELD = IGNORE
TABLE FILE GGSALES
SUM DOLLARS
BY REGION
BY PRODUCT
ON CATEGORY SUBTOTAL
END
C
SET Parameters – TESTDATE
 Changes current WebFOCUS “Today” date
 Options: YYMD date
 Default: Current system date
SET TESTDATE = 20120912
-TYPE &YYMD
-TYPE &MDYY
-TYPE &DATEMtrDYY
20120912
09122012
September 12, 2012
8
SET Parameters – DATETIME
 Controls &TOD (current time variable)
 Options: STARTUP, CURRENT, RESET
 Default: STARTUP
-TYPE &TOD
TABLE FILE
…
…
-TYPE &TOD
TABLE FILE
SET DATETIME = STARTUP
-RUN
-TYPE &TOD
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
9
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
10
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
11
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
12
SET Parameters – BUSDAYS
COMPUTE TODAY/MtDYY = '&MDYY';
COMPUTE NEXT_BUSDAY/MtDYY = DATEADD(TODAY, 'BD', 1);
SET TESTDATE = 20120602
SET BUSDAYS = _MTWTF_
Friday
Monday
13
SET Parameters – HDAY
 Identifies holidays; affects business days
 Options: xxxx (filename: /ibi/srv77/wfs/etc/hdayxxxx.err)
 Default: none
hdayus.err
20120102
20120116
20120220
20120528
20120704
20120903
20121008
20121112
20121122
20121225
New Year's Day
Martin Luther King, Jr.
President's Day
Memorial Day
Independence Day
Labor Day
Columbus Day
Veterans Day
Thanksgiving Day
Christmas Day
hdaycan.err
20120102
20120220
20120406
20120409
20120521
20120701
20120806
20120903
20121008
20121111
20121225
20121226
New Year's Day
Family Day
Good Friday
Easter Monday
Victoria Day
Canada Day
Civic Holiday
Labour Day
Thanksgiving Day
Remembrance Day
Christmas Day
Boxing Day
14
SET Parameters – HDAY
SET HDAY = USXX
Monday
Friday
SET HDAY = CANX
Friday
Tuesday
15
SET Parameters – EQTEST
 Controls how $ are interpreted in a WHERE/IF
 Options: WILDCARD, EXACT
 Default: WILDCARD
16
SET Parameters – EQTEST
…
TABLE FILE GGSALES
IF NEW_PRODUCT_ID EQ 'G10$'
END
SET EQTEST = WILDCARD
SET EQTEST = EXACT
 Applies to ‘$’ and ‘$*’
17
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
18
SET Parameters – EXPANDABLE, EXPANDBYROW
SET EXPANDABLE = OFF
SET EXPANDBYROW = OFF
19
SET Parameters – EXPANDABLE, EXPANDBYROW
SET EXPANDABLE = ON
20
SET Parameters – EXPANDABLE, EXPANDBYROW
SET EXPANDBYROW = ON
SET EXPANDBYROW = 2
21
Reporting
22
Reporting
 Conditional page headings and footings
 HOLD into an APP folder
 MORE
 FILEDEF with wildcards
23
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
24
Reporting – HOLD into an APP folder
 APP HOLD appname
 ON TABLE HOLD AS appname/filename
Default
TABLE FILE CAR
SUM SALES
BY COUNTRY
BY CAR
ON TABLE HOLD AS HSALES
END
 Data and MFD written to temp
25
Reporting – HOLD into an APP folder
 APP HOLD appname
APP HOLD summit
TABLE FILE CAR
SUM SALES
BY COUNTRY
BY CAR
ON TABLE HOLD AS HSALES
END
 Puts hsales.ftm AND hsales.mas into the summit app folder
 APP HOLDMETA appname just holds .mas
 APP HOLDDATA appname just holds .ftm
26
Reporting – Hold Files
 ON TABLE HOLD directly to an app folder
 HOLD AS app/filename
TABLE FILE CAR
SUM SALES
BY COUNTRY
BY CAR
ON TABLE HOLD AS summit/HSALES
END
 Puts hsales.mas and hsales.ftm into the summit app folder
 Includes DATASET attribute in .mas
FILENAME=HSALES, SUFFIX=FIX,
DATASET=summit/hsales.ftm ,$
27
Reporting – MORE
 Universal concatenation
 Enables concatenation of like Internal Matrix
DEFINE FILE CAR
DOLLARS/I8=SALES;
REGION/A11=COUNTRY;
END
TABLE FILE filename
SUM DOLLARS
BY REGION
END
GGSALES
CAR
28
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
29
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
30
Reporting – Filedef with wildcards
 Enables concatenation of like flat files
h_cty_england.ftm
h_cty_japan.ftm
31
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
32
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
33
Synonyms (Master Files and Access Files)
34
Synonyms
 FILEDEF with wildcards
 MFD Profiles
 CHECK FILE
 -READFILE
35
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
36
Synonyms – MFD Profiles
 Defines a profile to run when the MFD is accessed
 MFD_PFOFILE = appname/profile
FILENAME=GGSALES, SUFFIX=FOC,
MFD_PROFILE=summit/ggfilter
 ggfilter.fex
FILTER FILE GGSALES
NAME = REGION
WHERE REGION EQ 'Midwest';
END
SET FILTER = REGION IN GGSALES ON
37
Synonyms – MFD Profiles
TABLE FILE GGSALES
SUM DOLLARS
BY REGION
BY PRODUCT
END
 Profile runs first
 ggfilter turns on FILTER
38
Synonyms – CHECK FILE
 Syntax check of MFD/AFD
 Provides vital statistics of the synonym
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
 CHECK FILE synonym [HOLD]
39
Synonyms – CHECK FILE
CHECK FILE EMPLOYEE
CHECK FILE EMPLOYEE HOLD
 Creates a hold file and mfd with key components of the original synonym
 Field names
 Formats
 etc.
40
Synonyms – ?FF
 Shows all the fields in an MFD
?FF GGSALES
FILENAME= GGSALES
SEQ_NO
SEQ
CATEGORY
E02
PCD
E03
PRODUCT
E04
REGION
E05
ST
E06
CITY
E07
STCD
E08
DATE
E09
UNITS
E10
DOLLARS
E11
BUDUNITS
E12
BUDDOLLARS E13
I5
A11
A04
A16
A11
A02
A20
A05
I8YYMD
I08
I08
I08
I08
41
Dialogue Manager
42
Dialogue Manager
 -READFILE
 Date format support
 NOCLOSE
 Immediate execution following a label
43
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. &CAR.A11.
-LOOP_SALES
44
Dialogue Manager – -READFILE
 Using –READFILE
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
 Creates &COUNTRY, &CAR, &SALES
45
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.
46
Dialogue Manager – Date display formats
 &DATEfmt – displays current date in selected format
-TYPE &DATEYYMD
-TYPE &DATEMDYY
-TYPE &DATEMtrDYY
2012/06/03
06/03/2012
June 3, 2012
-TYPE &DATEMtrDYY is the current date
June 3, 2012
is the current date
 &DATXfmt – truncates display value
-TYPE &DATXMtrDYY is the current date
June 3, 2012 is the current date
47
Dialogue Manager – NOCLOSE
 Enables file to processed with –READ and –WRITE
without closing on a –RUN
hcountry.ftm
No -RUN
-REPEAT READ_LOOP 5 TIMES
-READ HCOUNTRY &COUNTRY.A10.
-TYPE &COUNTRY
-READ_LOOP
48
Dialogue Manager – NOCLOSE
With -RUN
-REPEAT READ_LOOP 5 TIMES
-READ HCOUNTRY &COUNTRY.A10.
-TYPE &COUNTRY
-RUN
-READ_LOOP
-RUN closes hcountry
49
Dialogue Manager – NOCLOSE
Using NOCLOSE
-REPEAT READ_LOOP 5 TIMES
-READ HCOUNTRY NOCLOSE &COUNTRY.A10.
-TYPE &COUNTRY
-RUN
-READ_LOOP
hcountry does not close
50
Dialogue Manager – NOCLOSE
Using -READFILE
-REPEAT READ_LOOP 5 TIMES
-READFILE HCOUNTRY
-TYPE &COUNTRY
-RUN
-READ_LOOP
hcountry does not close
51
Dialogue Manager – NOCLOSE and –WRITE
 Works the same way with –WRITE
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
 Appends after first write but not on first write
 Avoids pre-delete and (APPEND
logfile.ftm
Part 1
Part 2
52
Dialogue Manager – NOCLOSE
 To explicitly close a file at anytime:
-CLOSE filename
 Next –READ or –WRITE is at the top of the file
53
Dialogue Manager – Immediate execution following a label
 Dialogue manager commands can follow a label
-labelname command
-GOTO MESSAGE
-MESSAGE TYPE Here is a message
Here is a message
-GOTO SET_COUNTRY
-SET_COUNTRY SET &COUNTRY='ENGLAND';
&COUNTRY set to ‘ENGLAND’
 Labels can now be 64 characters long
54
Functions
55
Functions
 A few more of my favorite functions
 TRIM
 LCWORD, LCWORD2, LCWORD3
 SLEEP
56
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
TRIMMED/A10=TRIM('L', VALUE, 10, '0', 1, TRIMMED);
57
Functions - TRIM
TRIMMED/A10=TRIM('T', VALUE, 10, '.', 1, TRIMMED);
58
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)
59
Functions – LCWORD, LCWORD2, LCWORD3
60
Functions - SLEEP
 Puts WebFOCUS into a wait state
 Two ways implement it
 SLEEP n
 SLEEP(delay, output);
61
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
62
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
63
APP Commands
64
APP Commands – APP DELETE
 DELETE a file from an APP folder
APP DELETEFILE app/appname {filename|*} filetype
 APP DELETEFILE app/summit ROBHOLD FOCTEMP
65
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
-INCLUDE sales/monthly_report
 Includes monthly_report.fex out of sales APP folder
EX hr/turnover_report
 Execute turnover_report out of hr APP folder
66
Troubleshooting
67
Troubleshooting - LET NOPRINT = ;
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= HOLD
PRODUCT E01
A16
REGION E02
A11
CITY
E03
A20
DATE
E04
I8YYMD
UNITS
E05
I08
DOLLARS E06
I08
68
Troubleshooting - LET NOPRINT = ;
LET NOPRINT=;
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= HOLD1
PRODUCT E01
A16
REGION E02
A11
ST
E03
A02
CITY
E04
A20
DATE
E05
I8YYMD
UNITS
E06
I08
DOLLARS E07
I08
69
Efficiency
70
Efficiency - SQL SET LOADONLY ON
 Backend MODIFY to load data
MODIFY FILE synonym
FIXFORM FROM ftm
MATCH keys
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON ftm
END
 MATCH = select, INCLUDE = insert
71
Efficiency - SQL SET LOADONLY ON
 Backend MODIFY to load data
 Use Load Only
MODIFY FILE synonym
SQL SET LOADONLY ON
FIXFORM FROM ftm
MATCH keys
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON ftm
END
 MATCH = nothing, INCLUDE = insert
 INSERTs only
72
Top 10 Things You Can Use on Monday
73
Top 10 Things You Can Use on Monday
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
WHENCE
DB_LOOKUP
FILTER FILE
-SET &ECHO=ON;
-SET &STACK=OFF;
MISSING ON NEEDS ALL
APP HOLD
Leave a trail and leave no trash
.QUOTEDSTRING
FPRINT
SQL Traces and XRETRIEVAL
74
Thank You!
Come back next year for:
Still More Things You Can Use on Monday
Maybe
75