Transcript Internals of Output Processing For TABLE/TABLEF/MATCH FILE

Internals of Output Processing for
TABLE/TABLEF/MATCH FILE
Cesare Petrizio
Information Builders
Copyright 2007, Information Builders. Slide 1
Presentation Information
Author: Cesare Petrizio
Company: Information Builders
Presentation Title: Internals of Output Processing for
TABLE/TABLEF/MATCH FILE
Abstract: Learn how prefixes work and whether they can be
referenced in SUBTOTAL or SUMMARIZE lines. Is it better to do a
RETYPE or reissue the request? Once data has been retrieved for a
report request, the next step is to use that invoked output data. Sorted
data is retrieved depending on EXTSORT, AUTOTABLEF, and
SAVEMATRIX parameters. At that point, COMPUTEs, WHERE
TOTAL tests, BY n instances, and BY TOTAL functions must be
performed. The coding of these calculations can affect
the output and the efficiency of the request.
Copyright 2007, Information Builders. Slide 2
Agenda
Order of Processing



Matrix processing
 Evaluate COMPUTEs
 Apply IF/WHERE TOTAL tests
Secondary sort
 Process BY TOTAL
Post matrix processing
 Determine ACROSS column



Process ACROSS-TOTAL
Other totals
Format the line
Copyright 2007, Information Builders. Slide 3
Internal Matrix Generation
Once an acceptable record has been retrieved, the
fields for SORTREC are moved, and the record is
released to Sort
Aggregation is performed as record is sorted, with
FOCUS sort
Aggregation is performed by external sort if SET
EXTAGGR = ON
Numeric Fields are added. Alpha fields being
SUMmed, return the LAST value within the BY
field, the FST value with external sorts
(SET SUMPREFIX=LST if required)
All verb objects are also COUNTed

Copyright 2007, Information Builders. Slide 4
Internal Matrix Generation
 Initially SORT occurs in BINS
 When BINs are full, subsequent records will re-use



BINs (EXTSORT is OFF) or be passed to the external
sort (EXTSORT is ON)
After all records are retrieved, a final merge will take
place
The end of the final merge is noted by:
Records =
Lines =
Copyright 2007, Information Builders. Slide 5
Internal Matrix Generation
Merge – FOCUS Sort
BINs
FOCSORT
SORT
MERGE
BINs
FOCSORT
FOCSORT
SORT
MERGE
BINs
Copyright 2007, Information Builders. Slide 6
Internal Matrix Generation
EXTSORT = ON, AUTOTABLEF = OFF
BINs ~ First
5000
Records
FOCSORT
S001WK01
S001WK02
S001WK03
BINs
Subsequent
Records
SORT
MERGE
FOCSORT
S001WK04
EXTSORT
S001WK05
S001WK06
S001WK07
S001WK08
S001WK09
Copyright 2007, Information Builders. Slide 7
Internal Matrix Generation
EXTSORT = ON, AUTOTABLEF = ON
OFFLINE
BINs ~ First
5000
Records
S001WK01
S001WK02
S001WK03
Subsequent
Records
Or…
S001WK04
EXTSORT
S001WK05
S001WK06
Extract
S001WK07
S001WK08
S001WK09
Or…
Hotscreen
Copyright 2007, Information Builders. Slide 8
Internal Matrix Generation
AUTOTABLEF = ON
SAVEMATRIX=ON
Defaults(711=on, 72 =off)
BINs ~ First
5000
Records
S001WK01
OFFLINE
S001WK02
S001WK03
Subsequent
Records
Or…
S001WK04
EXTSORT
S001WK05
Extract
FOCSORT
S001WK06
S001WK07
S001WK08
S001WK09
Or…
Hotscreen
Copyright 2007, Information Builders. Slide 9
Order of Processing
Retrieve an Entry
FOCSORT (AUTOTABLEF = OFF, MATCH FILE)
 External sort files (AUTOTABLEF = ON)
 Database (TABLEF)
If lowest BY field has changed, release prior line to
applicable program
If BY fields change invoke control options on prior
sort group



Copyright 2007, Information Builders. Slide 10
Output Stage
SORT
Internal Matrix
COMPUTE’s
IF/WHERE TOTAL
Secondary Sort
BY TOTAL
Post Matrix Processing
Totals…
FOCSML
Report
FML/EMR
Processor
Formatting
Extract
HOLD/SAVE
Copyright 2007, Information Builders. Slide 11
BY TOTAL
TABLE FILE EMPDATA
SUM SALARY
CNT.PIN
COMPUTE AVGSAL=SALARY/CNT.PIN; AS 'AVE,SALARY'
BY HIGHEST TOTAL AVGSAL NOPRINT
BY DEPT
END
PAGE
1
DEPT
---ACCOUNTING
SALES
MARKETING
CUSTOMER SUPPORT
PROGRAMMING & DVLPMT
PERSONNEL
CONSULTING
ADMIN SERVICES
SALARY
-----$283,300.00
$395,200.00
$570,700.00
$198,400.00
$182,300.00
$216,800.00
$126,300.00
$56,200.00
PIN
COUNT
----5
7
11
4
4
5
3
2
AVE
SALARY
-----$56,660.00
$56,457.14
$51,881.82
$49,600.00
$45,575.00
$43,360.00
$42,100.00
$28,100.00
Copyright 2007, Information Builders. Slide 12
BY TOTAL
 Sorting by Report Column
[RANKED] BY [HIGHEST|LOWEST [n] ] TOTAL display field where:
RANKED – Adds a column to the report output that identifies a
rank number for each row
n – Is the number of sort field values you wish to display in the
report
Display field – Can be a fieldname, prefix-operator.fieldname, or
calculated value
Copyright 2007, Information Builders. Slide 13
Output Stage
SORT
Internal Matrix
COMPUTE’s
IF/WHERE TOTAL
Secondary Sort
BY TOTAL
Post Matrix Processing
Totals…
FOCSML
Report
FML/EMR
Processor
Formatting
Extract
HOLD/SAVE
Copyright 2007, Information Builders. Slide 14
ACROSS
DEFINE FILE EMPLOYEE
JOB_CLASS/A1 = EDIT(CJC,’9’);
END
TABLE FILE EMPLOYEE
SUM
SALARY NOPRINT
CNT.SALARY NOPRINT
COMPUTE AVESAL/D7 = SALARY / CNT.SALARY;
BY DEPARTMENT
ACROSS JOB_CLASS
COMPUTE TOTAL =(C1 + C4 + C7 + C10 + C13 + C16)/
(C2 + C5 + C8 + C11 + C14 + C17);
END
JOB_CLASS
A
B
TOTAL
DEPARTMENT
AVESAL
AVESAL
------------------------------------------------MIS
17,622
17,938
17,797.44
PRODUCTION
17,219
17,407
17,275.20
Copyright 2007, Information Builders. Slide 15
ACROSS (NOPRINT’s Removed)
PAGE
1
C1
C2
C3
C4
C5
C6
JOB_CLASS
A
B
SALARY
SALARY
DEPARTMENT
SALARY
COUNT AVESAL
SALARY
COUNT
AVESAL
-------------------------------------------------------------------MIS
$70,487.00
4
17,622
$89,690.00
5
17,938
PRODUCTION $120,532.00
7
17,219
$52,220.00
3
17,407
COMPUTE TOTAL =(C1 + C4 + C7 + C10 + C13 + C16)/
(C2 + C5 + C8 + C11 + C14 + C17);
TOTAL
---------------17,797.44
17,275.20
What Happened To:
C7 - C17
Copyright 2007, Information Builders. Slide 16
ACROSS
TABLE FILE EMPLOYEE
SUM
SALARY
NOPRINT
CNT.SALARY
NOPRINT
BY DEPARTMENT
SUM
SALARY
NOPRINT
CNT.SALARY
NOPRINT
COMPUTE AVESAL/D7 = SALARY / CNT.SALARY;
BY DEPARTMENT
ACROSS JOB_CLASS
COMPUTE TOTAL =C1 /C2 ;
END
JOB_CLASS
A
B
TOTAL
DEPARTMENT
AVESAL
AVESAL
------------------------------------------------MIS
17,622
17,938
17,797.44
PRODUCTION
17,219
17,407
17,275.20
Copyright 2007, Information Builders. Slide 17
ACROSS-TOTAL
Let’s say we wanted to: Count the number of courses taken
across each year for each quarter
Produce a total for each year and a grand total
PAGE
1
YEAR
89
QTR
Q2
90
Q3
Q4
YR TOT
Q1
TOTAL
Q2
Q3
Q4
YR TOT
DIV
--------------------------------------------------------------------------CE
1
1
0
2
0
3
1
2
6
8
CORP
0
1
0
1
0
1
1
0
2
3
NE
0
0
1
1
1
1
0
2
4
5
SE
0
0
0
0
1
0
1
1
3
3
WE
2
1
0
3
1
2
1
0
4
7
Copyright 2007, Information Builders. Slide 18
ACROSS-TOTAL
JOIN PIN IN EMPDATA TO ALL PIN IN TRAINING AS XX
DEFINE FILE EMPDATA
SYR/Y=CSTART;
SQT/Q=CSTART;
END
TABLE FILE EMPDATA
COUNT COURSECODE
ACROSS SYR AS ‘YEAR’ ACROSS-TOTAL AS 'TOTAL'
ACROSS SQT AS ‘QTR’ ACROSS-TOTAL AS 'YR TOT'
BY DIV
IF SYR EQ 89 OR 90
END
Copyright 2007, Information Builders. Slide 19
ACROSS-TOTAL
Notice:
The Quarter Total column heading stays with correct line
The Year Total column heading stays with correct line
PAGE
1
YEAR
89
QTR
Q2
90
Q3
Q4
YR TOT
Q1
TOTAL
Q2
Q3
Q4
YR TOT
DIV
--------------------------------------------------------------------------CE
1
1
0
2
0
3
1
2
6
8
CORP
0
1
0
1
0
1
1
0
2
3
NE
0
0
1
1
1
1
0
2
4
5
SE
0
0
0
0
1
0
1
1
3
3
WE
2
1
0
3
1
2
1
0
4
7
Copyright 2007, Information Builders. Slide 20
ACROSS-TOTAL
Producing Column Totals With ACROSS-TOTAL
ACROSS sortfield ACROSS-TOTAL [AS 'name'] [COLUMNS col1 AND
col2 ...]column names
Where:
sortfield – Is the name of the field being sorted across
name – Is the new name for the ACROSS-TOTAL column title
col1, col2 – Are the titles of the ACROSS columns you want to include
in the total
Copyright 2007, Information Builders. Slide 21
ON Sortfield Options
Subtotaling
SUBTOTAL and SUB-TOTAL
RECOMPUTE and SUMMARIZE
 MULTILINE suppresses operation if only


single detail line
RECAP (COMPUTE)
SUBFOOT/SUBHEAD
Copyright 2007, Information Builders. Slide 22
Totaling
BY specified only
and TABLE *
BY specified plus
Higher Bys
TABLE *
ADD’s all numeric
columns
SUBTOTAL
COLUMN-TOTAL
SUB-TOTAL
Recalculates
COMPUTE’s
ADD’s up other
numeric columns
RECOMPUTE
SUMMARIZE
* NOTOTAL Suppresses Grand Totals
Copyright 2007, Information Builders. Slide 23
ON Sortfield Options
PAGE-BREAK [REPAGE]
NOSPLIT
 **FOLD-LINE
 **SKIP-LINE
 UNDERLINE
** May be specified on verb-objects
Copyright 2007, Information Builders. Slide 24
ON TABLE Options
 Totaling – ON TABLE…
 COLUMN-TOTAL
 ROW-TOTAL
 SUMMARIZE
 NOTOTAL
 RECAP
 [PAGE-BREAK AND ] SUBFOOT/SUBHEAD
Copyright 2007, Information Builders. Slide 25
HEADings and FOOTings
 References to fields in HEADING and FOOTING
become verb objects with the first verb
 References to fields in SUBHEAD and SUBFOOT
become verb objects only if not previously
mentioned.
 Fields used in HEADINGs and SUBHEADs are taken
from the first line within the group (BY phrase or
page)
Copyright 2007, Information Builders. Slide 26
HEADings and FOOTings
 Fields used in FOOTINGs and SUBFOOTs are
taken from the last line within the group,
subtotals (ST.field, totals – TOT.field, running
totals – CT.field) or RECAP fields.
 Maximum storage for all HEADINGs,
FOOTINGs,
SUBHEADs, or SUBFOOTs is 6K bytes;
 </n <n <+n <-n require additional 10 bytes
 <fieldname[>]
require additional 30 bytes
 Each line specified within “ “ requires WIDTH
bytes
Copyright 2007, Information Builders. Slide 27
ON TABLE Options
Extracts
ON TABLE HOLD [FORMAT …]
Copyright 2007, Information Builders. Slide 28
ON TABLE Options
(Not All Shown)
COMMA – Saves all the columns of the WebFOCUS report request and
creates a CSV (Comma Separated Values) file. Alphanumeric fields are
enclosed in quotation marks. Columns are separated by commas.
COM – Saves the data values as a variable-length text file with fields
separated by commas and with character values enclosed in double
quotation marks. Leading blanks are removed from numeric fields and
trailing blanks are removed from character fields. To issue a request
against this data source, the setting PCOMMA=ON is required,
COMT – Saves the column headings in the first row of the output file. It
produces a variable-length text file with fields separated by commas and
with character values enclosed in double quotation marks. Leading
blanks are removed from numeric fields and trailing blanks are removed
from character fields. This format is required by certain software
packages such as Microsoft Access.
Copyright 2007, Information Builders. Slide 29
ON TABLE Options
(Not All Shown)
DOC – Saves the report output as MS-DOS text with layout
and line breaks
EXCEL – Saves the report output as a Microsoft Excel
worksheet
EXL2K – Generates fully styled reports in Excel 2000
HTML format. Requires Excel 2000 on your PC
EXL2K PIVOT – Generates fully styled reports in Excel
2000 HTML format, with added pivoting capabilities.
Requires Excel 2000 on your PC
HTML – Creates an output file that contains an
HTML(Web) document
HTMTABLE – Creates an output file that contains an
HTML table, not a complete HTML document
Copyright 2007, Information Builders. Slide 30
ON TABLE Options
(Not All Shown)
INTERNAL – Saves report output without padding the
values of integer and packed fields
PDF – Saves the report output in Adobe’s Portable
Document Format, which allows precise placement of
output (all formatting options) on the printed page so the
report looks exactly as it would when printed
PS – Saves the report as a PostScript document. You must
have installed a third-party tool capable of displaying PS
SQLMSS – Captures the report data and creates a
Microsoft SQL Server data source table
Copyright 2007, Information Builders. Slide 31
ON TABLE Options
(Not All Shown)
SQLODBC – Captures the report data and creates a file or
data source table using the current ODBC data source
driver
SQLORA – Captures the report data and creates an Oracle
data source table
TABT – Creates an extract file in tab delimited format that
includes column headings in the first row
Copyright 2007, Information Builders. Slide 32
Extract
SET HOLDLIST = ALL
TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME COMPUTE NEWSAL = CURR_SAL * 1.5;
BY EID
ON TABLE HOLD AS INT1 FORMAT FOCUS INDEX EID
END
INT1.MAS
FILE=INT1
,SUFFIX=FOC
SEGNAME=SEG01
,SEGTYPE=S02
FIELDNAME
=EMP_ID
,E01
FIELDNAME
=FOCLIST
,E02
FIELDNAME
=LAST_NAME
,E03
FIELDNAME
=FIRST_NAME
,E04
FIELDNAME
=CURR_SAL
,E05
FIELDNAME
=NEWSAL
,E06
,A9
,I5
,A15
,A10
,D12.2M
,D12.2
,
,
,
,
,
,
FIELDTYPE=I, $
$
$
$
$
$
Copyright 2007, Information Builders. Slide 33
Extract
SET HOLDLIST = ALL
TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME COMPUTE NEWSAL = CURR_SAL * 1.5;
BY EID
ON TABLE HOLD AS INT1 FORMAT FOCUS
END
FOC$HOLD.FEX
CREATE FILE INT1
MODIFY FILE INT1
FIXFORM FROM FOC$HOLD
DATA ON FOC$HOLD
END
Copyright 2007, Information Builders. Slide 34
Review
35
Copyright 2007, Information Builders. Slide 35
Copyright © 2004 Information Builders, Inc.
Review
Read Internal Matrix from FOCSORT or External Sort
Files
Evaluate COMPUTEs
Apply IF/WHERE TOTAL tests
Secondary sort
Process BY TOTAL
Post Matrix processing
Determine ACROSS column
Process ACROSS-TOTAL
Perform other totals
Format The Line
Write extract file, or Format output using standard
style or STYLESHEET
Copyright 2007, Information Builders. Slide 36
Questions
Thanks for Coming
Copyright 2007, Information Builders. Slide 37