Transcript IBI Overhead Transparency Template

FOCUS 7.3 Product Update
Information Builders
October, 2003
Note: Until 7.3 is officially released, features listed in this presentation are subject to change or removal.
1
DN 9602 000.0001
1
FOCUS Release 7.3 – Parade of New Features
Feature list
2
Go to end
FOCUS Release 7.3 – Parade of New Features
TABLE REPORTING
01 Multiple FOLD-LINEs
02 Display BY value on every row
03 Prefixes on summary lines
04 Displaying PAGE n OF max
05 First page and last page #s
06 IN-RANGES-OF
07 Right-side minus sign display
08 Create cover pages
09 Extensions to FORECAST
10 Multivariate regression forecast
11 NORMSINV and NORMSDST
12 Decimal alignment in headings
13 Multiple reports in one PDF/PS
14 Stylesheet enhancements
PERFORMANCE BOOSTERS
15 New DEFINEs Compiler
16 Parse masters only once
17 User function argument checker
18 Optimized TX search in rdbms
19 DB2 FETCH FIRST n ROWS
Next Key Feature
3
OUTPUT MECHANISMS
20 Working with EXCEL 2000
21 – Cascading Stylesheets
22 FML Hierarchy
23 – Reuse FOR values in FML
24 – Use FOR value in calculations
25 – FML INDENT
26 – Indenting FML Hierarchy
27 Missing values in HOLD
28 NODATA char in HOLD
29 HOLD in alpha or binary
OPERATING SYSTEM
30 &FOCUSER userid variable
31 Wide line files and IEDIT
32 Relative GDG +1
MAINTAIN
33 MNT filetype/extension
34 FOCUS SETs from Maintain
35 New screening in MAINTAIN
Next Slide
RAISED LIMITS
36 Unlimited IF/THEN/ELSE
37 63 MODIFY COMBINEs
38 Long amper variables
39 Long qualified fieldnames
40 More ACROSS values
41 1024 display fields
42 More sort headings/footings
43 Increased column title space
44 More external index partitions
DATABASE and MASTER
45 XFOCUS database
46 – Long segment names
47 – Long index names
48 Multi-dimensional index (MDI)
49 GROUPs in FOCUS masters
50 DATASET at segment level
51 Read-only fields in master
52 TAB-delimited files
53 Varchar support - AnV
Feature list
Go to end
TABLE REPORTING
FOCUS 7.3
Feature list
4
Go to end
Multiple FOLD-LINE

Specify up to 16 FOLD-LINE clauses in a TABLE request
TABLE FILE
PRINT field1 field2 field3 FOLD-LINE
field4 field5 field6 field7 FOLD-LINE
field8 field9
END
RESULT
field1 field2 field3
field4 field5 field6 field7
Indent 2 spaces
field8 field9
Feature list
5
Go to end
Show BY Values on Every Line
SET BYDISPLAY
 Display the sort field values on all lines of a report
 For STYLE’d output only, including EXL2K, HTML, PDF, PS
 Avoid the PRINT FNAME … BY FNAME NOPRINT game
TABLE FILE CENTHR
PRINT LNAME PLANT
BY FNAME UNDER-LINE
ON TABLE HOLD FORMAT EXL2K
END
FNAME
----DAVID
LNAME
PLANT
--------SMITH
SOUTH
JONES
NORTH
MCKNIGHT
EAST
--------------------------MARK
SMITH
WEST
GREENSPAN
NORTH
Next Key Feature
6
SET BYDISPLAY=ON
TABLE FILE CENTHR
PRINT LNAME PLANT
BY FNAME UNDER-LINE
ON TABLE HOLD FORMAT EXL2K
END
FNAME LNAME
PLANT
----- --------DAVID SMITH
SOUTH
DAVID JONES
NORTH
DAVID MCKNIGHT
EAST
--------------------------MARK
SMITH
WEST
MARK
GREENSPAN
NORTH
Feature list
Go to end
Prefix Operators on Summary Lines
 Use with SUBTOTAL, SUB-TOTAL, RECOMPUTE and SUMMARIZE at

both the sort break and grand total levels
Summed values become the detail lines in the report:
Space after the prefix
ASQ. AVE. SUM. CNT. FST. LST. MAX. MIN.
COUNTRY
------ITALY
CAR
--ALFA ROMEO
MASERATI
*TOTAL ITALY
W GERMANY
MODEL
MODEL
COUNT
----3
1
(syntax requirement)
TABLE FILE CAR
SUM CNT.MODEL
BY COUNTRY BY CAR
ON COUNTRY SUB-TOTAL CNT. MODEL
WHERE COUNTRY IS 'ITALY' OR 'W GERMANY'
END
2
4
AUDI
BMW
1
6
*TOTAL W GERMANY
2
7
TOTAL
Back to Feature List DONE
7
Count
Total
of of
all instances
Counts
after Summarization
4
11
Feature list
Go to end
Capture the Last Page of a Report
TABLASTPAGE
TABLE FILE CAR
HEADING
"PAGE <TABPAGENO OF <TABLASTPAGE </1"
SUM DCOST BY COUNTRY PAGE-BREAK
ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
Page 4 of 4
TYPE=REPORT,FONT=ARIAL,$
Page 3 of 4
ENDSTYLE
END
Page 2 of 4
Page 1 of 4
 Supported for FORMAT HTML/PDF/PS only
REPORT
Notes: &FOCNEXTPAGE is set to TABLASTPAGE
Feature list
8
Go to end
Capture and Adjust Page Numbers
FOCFIRSTPAGE and &FOCNEXTPAGE
Make multiple reports
look like one!
Page 3
Page 2
Page 1
REPORT 1
Page 5
Page 4
REPORT 2
Next Key Feature
9
FOCFIRSTPAGE – SET variable declares
the first page number of a report
&FOCNEXTPAGE – reserved amper
variable set to one greater than the last
page number of the last report
TABLE FILE
PRINT …
HEADING
“PAGE <TABPAGENO “
END
-RUN (&FOCNEXTPAGE automatically set to 4)
SET FOCFIRSTPAGE = &FOCNEXTPAGE
TABLE FILE
PRINT …
HEADING
“PAGE <TABPAGENO “
END
Feature list
Go to end
Display the Entire Range
IN-RANGES-OF


Display the min and max of a range of values
Similar to IN-GROUPS-OF
TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY CURR_SAL IN-RANGES-OF 5000
END
CURR_SAL
-------5000.00 -
9999.99
10000.00 - 14999.99
15000.00 - 19999.99
LAST_NAME
--------SMITH
GREENSPAN
STEVENS
SMITH
JONES
MCCOY
MCKNIGHT
IN-GROUPS-OF (for comparison)
TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY CURR_SAL IN-GROUPS-OF 5000
END
CURR_SAL LAST_NAME
-------- --------5000.00 SMITH
GREENSPAN
10000.00 STEVENS
SMITH
15000.00 JONES
MCCOY
MCKNIGHT
Feature list
10
Go to end
Right-Side Minus (-) Display Option
 Put the minus sign on the right side of negative numbers
TABLE FILE
PRINT FIELDA/I8 FIELDB/I8B FIELDC/I8R FIELDD/I8BY COUNTRY
COUNTRY
Default
negative
FIELDA
Bracket
negative
FIELDB
ENGLAND
-123
(123)
123 CR
123-
FRANCE
-1234
(1234)
1234 CR
1234-
-2
(2)
2 CR
2-
-125
(125)
125 CR
125-
END
GERMANY
RUSSIA
Credit
negative
FIELDC
Feature list
11
Right-side
negative
Go to end
FIELDD
Create Cover Pages
NEWPAGE


Pagebreak after
a subhead or before
a subfoot
Create a “cover page”
for each section
TABLE FILE CENTORD
SUM LINEPRICE AS ‘ ‘ QUANTITY AS ‘ '
BY REGION NOPRINT PAGE-BREAK
BY STATE AS ‘ '
ON REGION SUBHEAD NEWPAGE
"<REGION REGION"
END
PAGE
1
EAST REGION
PAGE
CT
DC
DE
MA
MD
2
$16,238,158.37
$70,928,546.26
$2,500,849.39
$34,010,314.29
$24,978,362.10
PAGE
3
NORTH REGION
PAGE
4
IA
$2,469,227.24
IL
$34,444,984.60
IN
$12,477,236.78
KS
$2,136,103.34
MI
$47,979,137.95
Feature list
12
65,979
274,714
10,226
131,956
94,827
Go to end
10,068
134,351
50,124
7,870
191,671
FORECAST Extensions
 EXPAVE “bootstraps” the last real data
value as a factor in every predicted value
 Double and triple exponential smoothing
for trends and seasonal adjustments
 SUM with REGRESS creates a new report
column with the regression based on the
summed values
 GRAPH facility supports FORECAST
ON TRANSDATE RECAP SEASONAL/D10.1 = FORECAST(TRANS,1,3,'SEASONAL', 3,3,1000,1);
Feature list
13
Go to end
Perform Multi-Variate Regression Forecasting
REGRESS



REGRESS method derives a best-fit linear equation for a
set of numeric data points
Can be based on 1, 2 or 3 independent variables
Creates a new column in the report
DEFINE FILE GGSALES
SDATE/YYM = DATE;
SYEAR/Y = SDATE;
END
TABLE FILE GGSALES
PRINT BUDUNITS AS ‘Budget,Units’
UNITS AS ‘Unit,Sales’
BUDDOLS AS ‘Budget,Dollars’
DOLLARS AS ‘Dollar,Sales’
WHERE CATEGORY EQ ‘Coffee’
WHERE REGION EQ ‘West’
WHERE UNITS GT 1670 AND UNITS LT 1700
Budget
Units
-----1796
1825
1613
1568
1665
1457
1662
1653
Unit
Sales
----1696
1695
1685
1682
1678
1671
1674
1694
Budget
Dollars
------17960
23725
22582
23520
21645
21855
24930
21489
Dollar
Sales
-----25440
25425
18535
25230
23492
20052
18414
16940
EST
----24428
23018
20954
20167
22021
19528
21043
21611
ON TABLE RECAP EST/F8 = REGRESS (3,BUDUNITS,UNITS, BUDDOLS, DOLLARS);
END
Feature list
14
Go to end
NORMSINV and NORMSDST Functions
Equivalent to the
Excel functions
NORMSDST - Calculates cumulative
standard normal distribution function
NORMSINV – Returns upper
boundary of standard normal
curve (inverse of NORMSDST)
P(x x0)
x0
standard
deviation
mean
NORMSD/D12.5 = NORMSDST ( Z, ‘D8’);
NORMSI/D12.5 = NORMSINV (NORMSD,’D8’);
SIZE/D12 = NORMSI * STDEV + MEAN;
Feature list
15
Go to end
Decimal Alignment in Headings
 Align decimal points in a multi-line heading or footing
 Supported for FORMAT HTML/PDF/PS reports only
TABLE FILE
BY REGION NOPRINT SUBHEAD
"SALES FOR: <REGION "
" "
"Units: <JUNITS"
"Dollar Sales: <JDOLLARS"
"Budgeted Sales: <JBUDDOLLARS"
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE = REPORT, GRID=OFF, $
TYPE=SUBHEAD, ITEM=1, WIDTH=1.5,$
TYPE=SUBHEAD, ITEM=2, WIDTH=2,
SALES FOR: Midwest
Units:
905,045.
Dollar Sales:
11,400,665.00
Budgeted Sales: 11,194,373.00000
SALES FOR: Northeast
Units:
916,675.
Dollar Sales:
11,392,310.00
Budgeted Sales: 11,576,932.00000
SALES FOR: Southeast
Units:
935,232.
Dollar Sales:
11,710,379.00
Budgeted Sales: 11,807,981.00000
JUSTIFY=DECIMAL(1), $
END
HEADING, FOOTING, SUBHEAD,
SUBFOOT, TABHEADING, TABFOOTING
Feature list
16
Go to end
Compound Reports in one PDF/PS File
OPEN/CLOSE
 Combine multiple reports into a single PDF or PS file
 Make separate pages or one continuous report
 Combine any output formats with PDF/PS
ON TABLE PCHOLD FORMAT {PDF/PS} {OPEN/CLOSE} NOBREAK
TABLE
TABLE
TABLE
Next Key Feature
17
Feature list
Go to end
Stylesheet Enhancements
 Styling capabilities of
WebFOCUS are now
in mainframe FOCUS
Cascading Stylesheets
Compound Reports in one PDF / PS file
GRID = ON / FILL / OFF
WRAP = ON / OFF
PAGECOLOR = color
STYLE = [+/-] UNDERLINESET
SET PSPAGESETUP = ON / OFF
SET STYLEMODE = PAGED
Row/Cell Borders With Style/Color
Stylesheet Macros
Conditional Styling
TOPGAP / BOTTOMGAP Around Report
Heading / Footing Element Alignment
Feature list
18
Go to end
Stylesheet Enhancements
Example
TABLE FILE GGSALES
SUM UNITS DOLLARS
ON TABLE HOLD FORMAT HTML
ON TABLE SET PAGE_NUM OFF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=FILL,$
END
Next Key Feature
19
Feature list
Go to end
PERFORMANCE BOOSTERS
FOCUS 7.3
Feature list
20
Go to end
New Compiler for DEFINEs
SET DEFINES
 New, more efficient compiler
 Compiles only the DEFINES needed to run the request
 Compiles at runtime of TABLE, not at DEFINE parsing
SET DEFINES = COMPILED
DEFINE FILE CAR
CNTRY3/A3 = EDIT(COUNTRY,’999’);
PROFIT = RCOST – DCOST ;
FEET = LENGTH / 12 ;
Compiles FEET and PROFIT
END
but not CNTRY3
TABLE FILE CAR
PRINT LENGTH RCOST DCOST FEET PROFIT
BY COUNTRY BY CAR BY MODEL
END
Next Key Feature
21
Feature list
Go to end
Parse Masters Only Once
SET SAVEDMASTERS
 Up to 99 parsed masters may be stored in memory
 Most effective with large MFDs reused many times during
one session or job
CODE
EFFECT IN MEMORY
SET SAVEDMASTERS=3
TABLE FILE CAR
Stores CAR
TABLE FILE EMPLOYEE
Stores EMPLOYEE
TABLE FILE CAR
Re-uses CAR; moves it to top of list
TABLE FILE PERSONEL
Stores PERSONEL
TABLE FILE JOBS
Stores JOBS (drops EMPLOYEE)
TABLE FILE EMPLOYEE
Re-stores EMPLOYEE (drops CAR)
Next Key Feature
22
Feature list
Go to end
Optimize RDBMS Variable Length Character Data
and Increase Row Size
 IF criteria can now reference RDBMS variable character data types
 VARCHAR, LONG VARCHAR, and CLOB – now up to 32K
 Described in the Master with USAGE=TX, ACTUAL=TX
 Read-only selection performed by RDBMS improves performance
 Must use CONTAINS or OMITS against a TX field
 In the SQL, CONTAINS becomes LIKE, and OMITS becomes NOT LIKE
FILENAME=EMPINFO ,SUFFIX=SQLDS,$
SEGNAME=EMPINFO ,SEGTYPE=S0,$
FIELD=EMP_ID
,ALIAS=EID ,USAGE=A9 ,ACTUAL=A9 ,$
FIELD=LAST_NAME ,ALIAS=LN ,USAGE=A15 ,ACTUAL=A15 ,$
FOCUS
DB2
RDBMS column name
....
FIELD=JOBDESC, ALIAS=JDSC, USAGE=TX50, ACTUAL=TX ,$
FIELD=ED_HRS
TX,ALIAS=OJT
Field
TABLE FILE EMPINFO
PRINT EMP_ID LN FN DPT CJC
IF JOBDESC CONTAINS 'PR'
END
DONE
23
,$
CLOB
Field
,USAGE=F6.2 ,ACTUAL=F4
SELECT 1."EID",T1."LN",T1."FN",T1."DPT",T1."CJC"
FROM USER1."EMPINFO" T1
WHERE (T1."JDSC" LIKE '%PR%')
FOR FETCH ONLY;
Feature list
Go to end
Pre-Check Subroutine Arguments
SET USERFCHK


Verify and fix arguments of FOCUS functions or DEFINE functions
Adjust the level of error checking of parameter lengths and type
specifications
SET USERFCHK = ON / FULL / OFF / ALERT
ON
FULL
OFF
ALERT
(default) Attempts to fix a length problem.
If fixed, displays a warning; if not, displays an error
Same as ON but also checks functions in MFD
Turns verification off except in extreme cases
Same as OFF but a warning message is issued
ADDRESS/A30 = ‘1600 PENNSYLVANIA AVENUE’;
TOKEN3/A3 = GETTOK (ADDRESS, 20, 3, ' ', 10, TOKEN);
(FOC36335) PARAMETER LENGTH CONFLICT IN FUNCTION name ARG number
Feature list
24
Go to end
Support for READLIMIT in DB2 V7.1
FETCH FIRST n ROWS


Allow DB2 to limit rows returned
Acts like RECORDLIMIT, but handled by DB2
TABLE FILE CAR
PRINT MPG SEATS BY MODEL
IF MPG GT 20
IF READLIMIT EQ 2
END
SELECT T1.”MODEL”,T1.”MPG”,T1.”SEATS”
FROM USER1.”CAR” T1 ORDER BY T1.”MODEL”
FETCH FIRST 2 ROWS ONLY;
DONE
25
Feature list
Go to end
OUTPUT MECHANISMS
FOCUS 7.3
Feature list
26
Go to end
Working with Excel 2000
HOLD FORMAT EXL2K
 Supports most StyleSheet attributes
 Allows for full report formatting
 Displays heading, footings, subtotals
Also
HOLD FORMAT EXCEL97
TABLE FILE CAR
HEADING
"FORMAT EXL2K
EXCEL OUTPUT </1"
SUM DEALER_COST
BY COUNTRY SUBTOTAL MULTILINES BY CAR
ON TABLE SET STYLE *
TYPE=HEADING,STYLE=BOLD+ITALIC,$
TYPE=DATA,COLOR=RED,$
ENDSTYLE
ON TABLE HOLD AS OUT2K
OUTEXC
FORMAT
FORMAT
EXL2K
EXCEL
END
Next Key Feature
27
DONE
Feature list
Go to end
Cascading Style Sheets
 Define formatting in statements called rules.
Example: BODY {background: yellow}
 Control line height and letter spacing
 Exercise more control over positioning items in a report
 Generate more concise HTML output
TYPE=REPORT, CSSURL=http://websrv2/css/report01.css, $
TYPE=HEADING, CLASS=headText, $
TYPE=TITLE, CLASS=reportTitles, $
TYPE=DATA, CLASS=lowCost, WHEN=N3 LT 27, $
Stylesheet
Commands
BODY {font-family:Arial, sans-serif}
External CSS File
TABLE {border:0}
TD {border:0}
reportTitles {font-weight:bolder; background:lightblue;}
lowCost {color:green; font-style:italic;}
headText {font-family:Times New Roman, serif; font-size:larger;text-lign:center}
Feature list
28
Go to end
Reuse FOR Values on Multiple FML Rows
FORMULTIPLE


Allows use of data value as the FOR object unlimited times
Reduces the need for RECAP statements to duplicate
previous data lines
TABLE FILE CAR
SUM DEALER_COST
FOR COUNTRY
ENGLAND LABEL R1 OVER
FRANCE LABEL R2 OVER
ITALY LABEL R3 OVER
‘W GERMANY’ LABEL R4 OVER
RECAP R5=R1+R2+R3+R4; AS TOTAL
END
Use Label
References
Reuse FOR
objects
DONE
29
SET FORMULTIPLE=ON
DEALER_COST
TABLE FILE CAR
----------SUM
DEALER_COST
ENGLAND
37,853
FOR
COUNTRY
FRANCE
4,631
ITALY OVER
41,235
ENGLAND
W GERMANY
54,563
FRANCE
OVER
TOTAL
138,282
ITALY
OVER
‘W GERMANY’ OVER
ENGLAND OR FRANCE OR ITALY
OR ‘W GERMANY’ AS TOTAL
END
Feature list
Go to end
FML Hierarchy



Dynamically generate FML code based on a hierarchical
relationship, such as a chart of accounts or org chart
Up to 99 levels deep
Two new Master File elements: PROPERTY, REFERENCE
FIELDNAME =PRODUCT_MEMBER ,E04 Field,A7
,A07 ,$
Link
Used for
=PRODUCT_CAPTION ,E05
,A18 ,A18 ,
“AS”FIELDNAME
name
PROPERTY=CAPTION, REFERENCE=PRODUCT_MEMBER, $
FIELDNAME =PRODUCT_PARENT
,E06
,A7
,A07
,
PROPERTY=PARENT_OF, REFERENCE=PRODUCT_MEMBER, $
Parent
Data Info
Feature list
30
Go to end
Use FML Row Information
FMLINFO Function
 Returns the FOR value of a row in an FML report
 Use in COMPUTE for drilldowns, sign changes, etc.
CENTGL contains the
SET FORMULTIPLE = ON
hierarchy for CENTSYSF
JOIN ACCOUNT IN CENTGL TO ALL ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
hardcoded
Make PRINT_AMT the negative of
SUM NAT_AMOUNT/D10 AS ‘ ‘
NAT_AMOUNT for accounts < 2500
COMPUTE PRINT_AMT/D10 =
IF FMLINFO('FORVALUE','A7') LT '2500‘ THEN -NAT_AMOUNT ELSE NAT_AMOUNT ;
COMPUTE FORV/A4 = FMLINFO('FORVALUE', 'A4') ;
COMPUTE ACTION/A9 = IF FORV LT '2500' THEN 'CHANGED' ELSE 'UNCHANGED‘ ;
FOR GL_ACCOUNT
2000 WITH CHILDREN 2 ADD AS CAPTION
END
Actual
PRINT_AMT
Gross Margin
-25,639,223
Sales Revenue
-62,362,490
Retail Sales
-49,355,184
Mail Order Sales
-6,899,416
Cost Of Goods Sold
36,723,267
Variable Material Costs 27,438,625
25,639,223
62,362,490
49,355,184
6,899,416
36,723,267
27,438,625
Feature list
31
FORV
2000
2100
2200
2300
2500
2600
Go to end
ACTION
CHANGED
CHANGED
CHANGED
CHANGED
UNCHANGED
UNCHANGED
Indenting FML Reports
INDENT

Indent FML tags, labels or captions by a specific amount
TABLE FILE CENTGL
PRINT GL_ACCOUNT_PARENT AS ‘Parent’
FOR GL_ACCOUNT
1000
AS 'Not Indented‘ OVER
2000
INDENT 5 AS 'Indented 5‘
OVER
3000 GET CHILDREN 2
INDENT 5 AS 'Hierarchy Indented 5'
END
Not Indented
Indented 5
Hierarchy Indented 5
Hierarchy Indented
Hierarchy Indented
Hierarchy Indented
Explanation
The label of the second row for tag value 3000 is
Hierarchy Indented 5
indented five spaces. The GET CHILDREN phrase
Hierarchy Indented
causes the first line of the FML hierarchy to indent
Hierarchy Indented
seven spaces (five + two).
Feature list
32
Go to end
5
5
5
5
5
Parent
-----1000
1000
3000
3100
3100
3100
3000
3200
3200
Indenting FML Hierarchy
SET BLANKINDENT
SET BLANKINDENT = OFF | ON | number




Specify the indentation between FML
hierarchy levels
Valid in HTML, PDF and Postscript
reports
Indents captions 0.125 or ‘n’ units per
normal space
Use in conjunction with stylesheet
commands SQUEEZE and
POSITION
Feature list
33
Go to end
Differentiate Missing Data in HOLD File
SET HOLDMISS
 Distinguish in HOLD files between missing data and default
values (blank for character data and zero for numeric data)
SET HOLDMISS=ON
TABLE FILE CAR
SUM DCOST BY COUNTRY ACROSS SEATS
ON TABLE HOLD FORMAT ALPHA
END
ENGLAND
11719
14940
FRANCE
ITALY
36320
11194
ENGLAND
4631
FRANCE
4915
ITALY
JAPAN
5512
JAPAN
W GERMANY
6000
48563
Blanks replaced by
Missing character
11719
.
.
36320
.
W GERMANY .
Feature list
34
14940
11194
4631
4915.
5512.
6000
Go to end
48563
Declare NODATA Character for HOLD Files
SET HNODATA


NODATA – Substitutes for missing data in a report
HNODATA – Same functionality for files created using
HOLD FORMAT ALPHA
FOCEXEC
Forces
MISSING Attribute
HOLD FILE
ALFA
ALFA ROMEO
ROMEO
AUDI
AUDI
SET HNODATA=BLANK
BMW
BMW
DATSUN
SET ALL=ON
DATSUN
JAGUAR
TABLE FILE CAR
JAGUAR
JENSEN
JENSEN
PRINT WARRANTY
MASERATI
MASERATI
BY CAR
PEUGEOT
ON TABLE HOLD FORMAT ALPHA PEUGEOT
TOYOTA
TOYOTA
END
TRIUMPH
TRIUMPH
SET HOLDMISS=ON
BLANK
12
12 MONTHS
MONTHS OR
OR 20000
20000 MILES
MILES
BLANK
12
12 MONTHS
MONTHS OR
OR 12000
12000 MILES
MILES
12
12 MONTHS
MONTHS OR
OR 12000
12000 MILES
MILES
12000
12000 MILES
MILES OR
OR 12
12 MONTHS
MONTHS
12
12
12
12
12
12
MONTHS
MONTHS
MONTHS
MONTHS
MONTHS
MONTHS
ON
ON
OR
OR
OR
OR
Truly blank value
Feature list
35
Go to end
12000
12000
12500
12500
12000
12000
MILES
MILES
MILES
MILES
MILES
MILES
Represent Missing Values in HOLD Files
SET NULL=ON


Propogate missing values to HOLD files with
visually consecutive delimiters
HOLD Formats COM, COMT, TAB, TABT only
SET NULL = ON
TABLE FILE
PRINT FIELD1 FIELD2 FIELD3
ON TABLE HOLD FORMAT COMT
END
“NEW YORK”,2,3
“NEW YORK”,0,1
“NEW YORK”,0,0
“NEW YORK”,,3
“NEW YORK”,2,
“NEW YORK”,,
Next Key Feature
36
Both fields present
FIELD2 is zero
Both fields are zero
FIELD2 is missing
FIELD3 is missing
Both fields are missing
Feature list
Go to end
Create HOLD Files in Alphanumeric Format
SET HOLDFORMAT


Create HOLD files in alphanumeric format
FTP hold files to the PC
Default
SET HOLDFORMAT = BINARY / ALPHA
Binary Data
Alpha Data
Feature list
37
Go to end
DATABASE AND MASTER
FOCUS 7.3
Feature list
38
Go to end
XFOCUS Database




XFOCUS database file may grow to 16 gigabytes
With partitioning, up to 4 terabytes for data warehousing
No change to focexecs
Convert with standard REBUILD
XFOCUS
Database
FOCUS
Database
Will be activated via SET XFC=ON in subsequent 7.3 Service Pack
Feature list
39
Go to end
XFOCUS Database
Comparison Chart
FOCUS DATABASE
4K page size
Alpha fields up to A3964
Up to 2-gig per physical file
Up to 255 2-gig partitions
Up to ½ terabyte total size
Segment names up to 8 chars
Index field names up to 12 chars
Page 1 contains FDT and data
More records per page
More pages per file
Larger files
-
XFOCUS DATABASE
16k page size; 2X as many pages
Alpha fields up to A4096
Up to 16-gig per physical file
Up to 255 16-gig partitions
Up to 4 terabytes total size
Segment names up to 64 chars
Index field names up to 66 chars
Page 1 dedicated to FDT
Reduced i/o
Fewer partitions
TRUE DATA WAREHOUSE
Feature list
40
Go to end
XFOCUS Database
Replacing Legacy Files
Converting is easy!
Just REBUILD!
FILE=filename, SUFFIX=FOCUS
SEGNAME=
DUMP
FILE=filename, SUFFIX=XFOCUS
SEGNAME=
LOAD
Feature list
41
Go to end
XFOCUS Database
For Data Warehousing
XFOCUS Database is oriented
toward Data Warehouse applications
rather than transactional updates
All FOCUS syntax and capabilities work with XFOCUS files,
except as follows (may be supported in later releases):
 Maintain
 HLI calls
 Sink machine (SU)
 External Index and Multi-Dimensional Index (MDI)
 Static cross references (KU, KL, KM)
 Permitted, but indexes not resolved (acts as DKU/DKM)
Next Key Feature
42
Feature list
Go to end
High Performance Multi-Dimensional Index – MDI
MDI provides
virtually instant
access to the
data you need
MDI has been
in use for years
in our FUSION
product
CAR
MODEL
COUNTRY
MDIs can have more than 3 dimensions
DONE
43
Feature list
Go to end
High Performance Multi-Dimensional Index – MDI
(FOCUS and XFOCUS Files)
ACCESS FILE
MASTERNAME CAR
DATANAME 'CARACX1 FOCUS M'
DATANAME 'CARACX2 FOCUS M'
DATANAME 'CARACX3 FOCUS M'
TABLE FILE CAR
MDILOCATION CARMDI
PRINT BODYTYPE SEATS
TARGET_OF ORIGIN
WHERE CAR EQ 'JAGUAR'
DIMENSION CAR
WHERE COUNTRY EQ 'ENGLAND'
DIMENSION COUNTRY
END
DIMENSION MODEL
DATANAME 'CARMDI MDI M'
Unlike RDBMS Clustered index,
MDI fields can be in any order
and all fields need not be used
REBUILD MDINDEX
creates new MDIs, or adds new partitions
to an existing MDI
Next Key Feature
44
Feature list
Go to end
Long Segment Names in XFOC Files
 Up to 64 characters long
Pre 7.3
Error generated
8 char max
SEGNAME = VERYLONGNAMEFORASEGMENT,SEGTYPE=…,$
7.3
Now
64 char max
SEGNAME = VERYLONGNAMEFORASEGMENT,SEGTYPE=…,$
Feature list
45
Go to end
Long Index Names in XFOC Files

Up to 66 characters long
Pre 7.3
FIELD = VERYLONGNAMEFORANINDEX,, I5, INDEX=I,$
Error generated
12 char max
7.3
Now
66 char max
FIELD = VERYLONGNAMEFORANINDEX,, I5, INDEX=I,$
Feature list
46
Go to end
GROUP Fields in Master
Now for FOCUS Files Too
Consecutive fields in a master that
represent a logical collection, such as
Firstname, Middle initial, Lastname
FILENAME=EMPLOYEE, SUFFIX=FOC
SEGNAME=EMPINFO, SEGTYPE=S1
FIELDNAME=EMP_ID, ,FORMAT=A9,$
GROUP = FULLNAME, ,FORMAT=A29,$
FIELDNAME=LAST, ,
FORMAT=A15,$
FIELDNAME=FIRST, , FORMAT=A10,$
FIELDNAME=HIRED,, FORMAT=I6YMD,$
Group length =
LAST+ FIRST+HIRED
(HIRED is integer with
internal length of 4)
Components
TABLE FILE EMPGROUP
may be separated
PRINT EMP_ID LAST FIRST HIRED
by slashes
BY FULLNAME NOPRINT
WHERE FULLNAME GT 'CROSS/BARBARA/991102'
END
Next Key Feature
47
Feature list
Go to end
Physical Name of LOCATION File
DATASET At Segment Level
 DATASET attribute already available at the file level in a FOCUS
Master File
 Now specify the physical file name of a LOCATION segment, or a
cross-referenced segment with field redefinitions
DATASET was already
available on the FILE level
FILE = ... , DATASET
SEGNAME=BODY,SEGTYPE=S1,PARENT=CARREC,LOCATION=BODYSEG,
DATASET='USER1.XYZ2.FOCUS',$
FIELDNAME=BODYTYPE,TYPE,A12,$
FIELDNAME=SEATS,SEAT,I3,$
FIELDNAME=DEALER_COST,DCOST,D7,$
XYZ1
XYZ2
XYZ3
Segment now in its
own physical file
XYZ2
Feature list
48
Go to end
Read-only Fields in Master
FIELDTYPE = R

FIELDTYPE=R identifies a field as read-only
 UPDATE issued for a read-only field is ignored
by MODIFY and MAINTAIN

Supports relational data sources with auto-increment
columns (automatically incremented by the RDBMS)
 IDENTIFY or timestamp columns in DB2
FILE=MNTAUTO , SUFFIX=SQLMSS ,$
SEGNAME=MNTAUTO, SEGTYPE=S0 ,$
FIELD=CONTROL ,CONTROL ,I11 ,I4 ,MISSING=OFF,FIELDTYPE=R,$
FIELD=LASTNAME ,LAST
,A12 ,A12,MISSING=OFF,$
FIELD=FIRSTNAME,FIRST
,A12 ,A12,MISSING=OFF,$
FIELD=ITEM
,ITEM
,A20 ,A20,MISSING=OFF,$
FIELD=AMOUNT
,AMOUNT ,P19 ,P10,MISSING=OFF,$
Feature list
49
Go to end
Read Tab-Delimited Files
SUFFIX = TAB
 Tab-delimited files for output or input
 Create using HOLD FORMAT TAB
 Similar to TABT, without leading column heading record
TABLE FILE MOVIES
PRINT COPIES TITLE WHOLESALEPR
BY CATEGORY
IF CATEGORY EQ 'ACTION' OR 'CHILD‘
ON TABLE HOLD AS TAB1 FORMAT TAB
END
FILE=TAB1
,SUFFIX=TAB
SEGNAME=TAB1 ,SEGTYPE=S01
FIELDNAME =CATEGORY
,E01
FIELDNAME =COPIES
,E02
FIELDNAME =TITLE
,E03
FIELDNAME =WHOLESALEPR ,E04
> implies tab
ACTION>2>JAWS>10.99
ACTION>3>ROBOCOP>11.50
ACTION>4>TOTAL RECALL>11.99
ACTION>2>TOP GUN>9.99
ACTION>3>RAMBO III>10.99
CHILD>1>SMURFS, THE>10.00
CHILD>2>SHAGGY DOG, THE>29.99
,A8
,I3
,A39
,F6.2
,A08
,A03
,A39
,A06
Feature list
50
,$
,$
,$
,$
Go to end
Varchar Support
AnV Field Format
AnV
A1V – A4096V
DB2
varchar
5 bytes
A5
FUNCTIONS
LENV
LOCASV
POSITV
SUBSTV
TRIMV
UPCASV
= ‘ABC’;
A
B
C
A5V = ‘ABC’;
3
A
B
Number of
non-blank
bytes
6 bytes
??? TX vs. AnV ???
Feature list
51
C
Go to end
RAISED LIMITS
FOCUS 7.3
Feature list
52
Go to end
Unlimited IF/THEN/ELSE
Ever hit that magic number?
17 If/Then/Else’s in one statement
DEFINE or COMPUTE
FL/A2= IF (VALUE EQ 1) THEN ‘A’ ELSE
IF (VALUE EQ 2) THEN ‘B’ ELSE
IF (VALUE EQ 3) THEN ‘C’ ELSE
....
IF (VALUE EQ 16) THEN ‘P’ ELSE
IF (VALUE EQ 17) THEN ‘Q’ ELSE ‘NO’;
FL =
FL =
IF (FL NE ‘NO’) THEN FL ELSE
IF (VALUE EQ 18) THEN ‘R’ ELSE
IF (VALUE EQ 19) THEN ‘S’ ELSE
....
IF (VALUE EQ 33) THEN ‘H’ ELSE ‘NO’;
Unlimited
IF/THEN/ELSEs
in one calculation
(limited by memory)
IF (FL NE ‘NO’) THEN FL ELSE
....
Next Key Feature
53
Now
you don’t have to!
Feature list
Go to end
63 COMBINEs for MODIFY
 Up to 64* total segments in a COMBINE structure

Non-root segments lower the # of files to COMBINE
COMBINE file1 AND file2 AND file3 AND …. file63 AS ONEBIG
FOCUS creates imaginary root
segment, which counts as one
SYSTEM
segment
File 2
File 3
Root segment
Root segment
Root segment
File
Sub-structure
File 1
...
Root segment
*The old limit was 16 COMBINEs
Feature list
54
File 63
Go to end
Long Amper Variables



Amper variables (& or &&) may now be up to 32k chars
 Use in -READ, -WRITE, or argument on EX
Up to 4K chars per –SET calculation
Up to 32K total space for all local ampers (&)
-SET &PART1 = ‘123456789112345678921234567893’;
-SET &PART2 = ‘123456789412345678951234567896’;
-SET &PART3 = ‘123456789712345678981234567899’;
-SET &PARTX = &PART1 | &PART2 | &PART3 ;
TABLE FILE CAR
PRINT CARPART IF CARPART EQ &PARTX
Previously gave
line
overflow errors due to
>80 substitution
Feature list
55
Go to end
Long Qualified Field Names

Compound length limit raised to accommodate the
longest name for all components
FIELD/A1 =
LONGFILENAME.LONGSEGMENTNAME.LONGFIELDNAME ;
Up to 64 chars
Up to 64 chars*
Up to 66 chars
Up to 196 total characters (including dots)
Specify via Wide-Lines focexecs or amper variable substitution
*64-character segment names only in XFOC files.
Segment names in FOC files remain up to 8 chars.
Feature list
56
Go to end
More ACROSS Values

Number of displayable ACROSS field values increased
from 95 to 1,056
Up to 1,056 ACROSS field values
MODEL
CAR BB
CC
DD
JAG 10
22
5
9 159
25
BMW
EE
XU
……TT
UU
VV
WW
XX
……
9
12
63
DF
1
……
7
32
--
DE
2
FF
GG
HH
C9
11
P2
P4
1
P2
Feature list
57
Go to end
1,024 Display Fields
 Number if fields in a single or pooled TABLE request
increased from 495 to approximately 1,024
 Excludes sort fields
 MATCH limited to 495
Great for extracts to Data Warehouse! What else?
 Actual maximum for a specific report is influenced by:
 Hidden fields (NOPRINT)
 Temporary fields (COMPUTE and DEFINE)
 Internal fields, like TABPAGENO
 The size of the fields
 Field references in headings and footings
Feature list
58
Go to end
More Sort Headings and Footings




Now up to 32K reserved for sort headings and footings
Up to 64 total SUBHEADs and SUBFOOTs
 Great power combined with WHEN
Up to 64 nested headings
Independent of area reserved for HEADING and FOOTING
BY COUNTRY BY CAR BY MODEL
ON COUNTRY SUBHEAD “TEXT FOR ENGLAND”
WHEN COUNTRY EQ ‘ENGLAND’
ON COUNTRY SUBHEAD “TEXT FOR FRANCE”
WHEN COUNTRY EQ ‘FRANCE’
....
Feature list
59
Go to end
Increased Space for Column Titles


Automatically adjusts to memory available
Initialized to 6K bytes
Feature list
60
Go to end
More External Index Partitions


External Index partitions increased from 120 to 240 max
Dependent on File Control Table* spares during session
(External Index file up to 2gig)
Create 152 concatenated partitions
USE
ORDERS1 AS ORDERS
ORDERS2 AS ORDERS
.
.
.
ORDERS150 AS ORDERS
ORDERS151 AS ORDERS
ORDERS152 AS ORDERS
END
Build the new external index
THEN
*FCT for FOCUS, Fusion, external index, MDI files
REBUILD
EXTERNAL INDEX
NEW
ORDIDX
ORDERS
L_SHIPDATE
NO
NO
Feature list
61
Go to end
OPERATING SYSTEM
FOCUS 7.3
Feature list
62
Go to end
Get User Name from Amper Variable
&FOCUSER
 System amper (&) variable retrieves the logon ID
 Use instead of the GETUSER function
 Supported anywhere an amper variable can be used
Examples:
USER/A8 = ‘&FOCUSER’;
“Report run by &FOCUSER”
IF EDIT(&FOCUSER,’$$$$9999’) . . .
 Ok in MODIFY, but not in Compiled MODIFY. Why not??
Feature list
63
Go to end
Wide Lines for Focexecs, Masters, Access Files
 Lrecl >80 for focexecs, masters, access files
 Up to 32K wide
 Entire TABLE or SQL statement on one line
 Continuous lines for:

 HEADINGs and FOOTINGs
 CRTFORM
 Comments
 ’EX’ argument strings
 Long amper variables
 Function parameter list
Some commands require separate lines
 MODIFY syntax, END, HEADING and its text
Feature list
64
Go to end
Wide Lines for Focexecs, Masters, Access Files
Examples
DEFINE FILE CAR
FIELD/A4096 =‘ABCDEFGHI ……………………TUVWXYZ’ ;
J
TABLE FILE CAR
PRINT FIELD1 FIELD2 FIELD3…………………FIELD1024
-* Pass a long argument
EX SOMEFOC ‘VERY LENGTHY……………………STRING’
In the Master
SEGMENT=…..
FIELD=AC,,A4, INDEX=I,……………………,ACCEPT,TITLE,$
Feature list
65
Go to end
Call Your System Editor for Wide-line Files
IEDIT



Work with files of lrecl > 80
 Needed for Wide Lines which TED does not support
Call to your system editor (XEDIT or ISPF)
 Your editor profiles and macros remain in force
Several TED-like conveniences
 IEDIT with no argument recalls last-run focexec
 File displays at line number of last error
 RUN command saves and executes the focexec
Feature list
66
Go to end
Relative GDG + 1


Relative GDG (Generation DataSet) may be a positive
number as well as negative number
‘Plus’ creates a new generation entry in the catalog to
be generated later with HOLD or SAVE
DYNAM ALLOC FILE DYNHLD DS ABCD.GDG.FIX(+1) NEW CATLG
Feature list
67
Go to end
MAINTAIN
FOCUS 7.3
Feature list
68
Go to end
MAINTAIN Filetype Extension

MAINTAIN procedures can have a file extension of
“MAINTAIN” instead of FOCEXEC (for both MVS and CMS)

MNTCON is required to work with MAINTAIN procedures
that have their own extensions
MVS JCL
//MAINTAIN DD=myqual.MAINTAIN.DATA,DISP=SHR
VM
maintproc MAINTAIN A1
MNTCON EX maintproc
MNTCON COMPILE maintproc
MNTCON RUN maintproc
will look for the
new extension
still FOCCOMP
Feature list
69
Go to end
New Screening Conditions for MAINTAIN


NOT_IN – true when a field is not in a list of values
e.g. For All Next Country Where Country NOT_IN
("ENGLAND","FRANCE");
EQ_MASK and NE_MASK – Compares a database field
to a computed field using wildcard character ($)
Example: Compute afld/a8="$$$LAND";
For All Next Country Where Country EQ_MASK afld;
FOR ALL NEXT fields [INTO stackname]
NOT_IN list (‘item1’, ‘item2’ [,…] )
WHERE field
EQ_MASK mask “MASK” may contain ‘$’ wildcards
NE_MASK mask
CONTAINS string
OMITS string
Ex: ABC$E$$G looks for any
character in those positions
Feature list
70
Go to end
FOCUS SETs from MAINTAIN

Use SYS_MGR.FOCSET function to pass a
option and its value
Example: SYS_MGR.FOCSET("CDN","ON");
Settable Options
EMGSRV
MESSAGE
WARNING
TRACEON
TRACEOFF
TRACEUSER
CDN
COMMIT
DEFCENT
YRTHRESH
DATEDISPLAY
NODATA
LANGUAGE
USER
PASS
Feature list
71
Go to end
SET
THANK YOU
72
Start
Feature list
Go to end
FOCUS 7.3 Presentation
Selected Features in order of discussion
Index
(45)
(48)
(20)
(14)
(04)
(49)
(36)
(16)
(02)
(13)
(15)
(27)
(03)
Feature
XFOCUS Database
Multi-Dimensional Index (MDI)
Working with EXCEL 2000
STYLESHEET enhancements
Displaying PAGE n OF max
GROUPS in FOCUS Masters
Unlimited IF/THEN/ELSE
Parse masters only once
Display BY value on every row
Multiple reports in one PDF/PS
New DEFINEs compiler
Missing value in HOLD
Prefixes on summary lines
Handout
Page 10 (bottom left)
Page 11 (bottom left)
Page 7 (bottom left)
Page 5 (top right)
Page 2 (bottom right)
Page 12 (bottom left)
Page 14 (top left)
Page 5 (top right)
Page 2 (top right)
Page 5 (top left)
Page 6 (top left)
Page 9 (top right)
Page 2 (bottom left)
Feature list
73
Go to end