more hidden gems of webfocus reporting

Walter F. BloodTechnical DirectorInformation Builders, Inc

More Hidden Gems of WebFOCUS Reporting

1

More Hidden Gems Where We Will Look

… in SettingsWhen the retrieved data values don’t line up correctly Two shades of Missing

… in Retrieval Creating an index for your request

… in Connections Playing with matchesMore is less … work

… in OutputRetaining the original structure of your dataThe any-delimiter file

… in SortingSorting it your way Uneven grouping

2

More Hidden Gems… in Settings

3

When the retrieved data values don’t line up correctly

Two shades of Missing

More Hidden Gems …In Settings - JOINOPT


4

TRAIN ROUTES

TRAIN ROUTES

ORIGIN STATIONS DESTINATION STATIONS

TRAIN ROUTES

Route NumberOrigin StationDest Station

Dest Station

Dest CityDest State

Orig Station

Orig CityOrig State

JOIN TR TO OR - UNIQUE JOIN TR TO DS - UNIQUE



5

TRAIN ROUTES

TRAIN ROUTES


TRAIN ROUTES

TABLE FILE ROUTES PRINT TRAIN_NUM OR_STATION OR_CITY DE_STATION DE_CITY END

TRAIN_NUM OR_STATION OR_CITY DE_STATION DE_CITY --------- ---------- ------- ---------- ------- 101 NYC NEW YORK ATL . 202 BOS BOSTON BLT ATLANTA 303 DET DETROIT BOS BALTIMORE 404 CHI CHICAGO DET BOSTON 505 BOS BOSTON STL DETROIT 505 BOS . STL ST. LOUIS



6

TRAIN ROUTES

TRAIN ROUTES


TRAIN ROUTES

TABLE FILE ROUTES PRINT TRAIN_NUM OR_STATION OR_CITY DE_STATION DE_CITY END

TRAIN_NUM OR_STATION OR_CITY DE_STATION DE_CITY --------- ---------- ------- ---------- ------- 101 NYC NEW YORK ATL ATLANTA202 BOS BOSTON BLT BALTIMORE 303 DET DETROIT BOS BOSTON 404 CHI CHICAGO DET DETROIT505 BOS BOSTON STL ST. LOUIS

SET JOINOPT=GNTINTSET P1170=NEW

More Hidden Gems …In Settings - SHORTPATH

Two shades of Missing In JOINS

7

SET SHORTPATH = FOCUS/SQL

FOCUS – omits host value of missing child regardless of any selection on the child segment

SQL – Inserts null value for missing fields and allows the selection on the child segment to occur. If the selection passes the records are retained.

JOIN with SET ALL=ONLEFT_OUTER JOIN

More Hidden Gems… in Retrieval

8

Creating an Index for your request

More Hidden Gems …In Retrieval - MDI

9

CAR

COUNTRY

MODEL

TABLE FILE CARSUM RETAILCOST …WHERE COUNTRY EQ ‘ENGLAND’WHERE CAR EQ ‘JAGUAR’WHERE MODEL EQ ‘XJ’

Instant access to data Single index, multiple fields External to FOCUS or XFOCUS

files Create as many as needed Index your report selection



10

MASTERNAME=CAR,$ DATANAME= 'CARACX1 FOCUS M‘,$ DATANAME= 'CARACX2 FOCUS M‘,$ DATANAME= 'CARACX3 FOCUS M‘,$ MDILOCATION = CARMDI,

TARGET_OF = ORIGIN,$ DIMENSION = CAR,$ DIMENSION = COUNTRY,$ DIMENSION = MODEL,$ DATANAME = 'CARMDI MDI M'

TABLE FILE CARPRINT BODYTYPE SEATSWHERE CAR EQ 'JAGUAR'WHERE COUNTRY EQ 'ENGLAND'END

Unlike RDBMS clustered index, MDI fields can be in any order and all fields need not be used

ACCESS FILE



11

REBUILD MDINDEX creates new MDIs, or adds new partitions to an existing MDI

Create MDIMake NEW or ADD to existing?

File NameMDI Name

Any WHERE Selection?



12

SET AUTOINDEX=ON turns on and selects the appropriate MDI for any given request.

The segment most involved in the query.

The MDI with the most filtering conditions (IF/WHERE selection tests).

The percent of index dimensions involved in

the request from each MDI. How close the fields being retrieved are to the

target segment.

The size of each MDI.


More Hidden Gems… in Connections

13

Playing with MATCHes

MORE is Less…Work

More Hidden Gems …In Connections - MATCH

Playing with Matches

14

Sort Merge

HOLD

OLD NEW

BYs control selection and merge of records

AFTER MATCH HOLD controls selection of output

More Hidden Gems …In Connections - MATCH

Playing with Matches

15

Holding Patterns

OLD-OR-NEW – All RECORDS, both files (DEFAULT) OLD-AND-NEW—Common RECORDS only OLD-NOT-NEW – RECORDS in OLD missing in NEW

NEW-NOT-OLD - RECORDS in NEW missing in OLD OLD-NOR-NEW - Combo of OLD-NOT-NEW NEW-NOT-OLD OLD – RECORDS in OLD with RECORDS in NEW NEW – RECORDS in NEW with RECORDS in OLD

CONTROL THE MISSING!

More Hidden Gems …In Connections - MORE

MORE is Less…Work

16

Universal Concatenation works with - TABLE

MATCH Universal Concatenation lets you use -

Multiple filesDifferent file formatsWHERE/IF selection on each file

Universal Concatenation requires – Fields must be available in each file

Real fieldDEFINE

Fields must have same format

TABLE FILE ORACLESUM A BBY CWHERE … ON TABLE HOLD AS

MOREFILE FLATWHERE …END

DEFINE FILE FLATA=DB=XEND

More Hidden Gems …In Connections - MORE

MORE is Less…Work

17

TABLE FILE ORACLESUM A BBY CWHERE … ON TABLE HOLD AS

MOREFILE FLATWHERE …END

DEFINE FILE FLATA=DB=XEND

Usage Format

Correspondence

A Type and length equal. I, F, D Type equal.P Type and scale equalDATE (new) Format information (type, length,

components, and order) equalDATE (old) Edit options equal. DATE -TIME Format information (type, length,

components, and order) equal.

Beware duplicate sort keys not grouped or aggregated

More Hidden Gems… in Outputs

18

Retaining the Original Structure of your data

The Any-Delimiter File

More Hidden Gems…in Output - EXTRACT

Most Hold Files remove all structure…

VSAM

FOCUSDB2

FOCUSDB2

VSAM

FOCUSDB2

FOCUSDB2

How can I preserve the original structure?

More Hidden Gems..in Output - EXTRACT

…Or give it totally different structure…

More Hidden Gems …in Output - EXTRACT

EXTRACT – structured hold files that retain the original structure

From

SEG1

SEG4SEG2

SEG5SEG3

VSAM

FOCUSDB2

FOCUSDB2

…and they travel!


A simple case: Report Structure

A

CB

DE

File 1

File 2 File 3

TABLE FILE PRINT A B DIF C EQ 5ON TABLE HOLD END

A B D

Plain HOLD file


A structured case: Report Structure

A

CB

DE

File 1

File 2 File 3

TABLE FILE PRINT A B DIF C EQ 5ON TABLE SET EXTRACT ONON TABLE HOLD AS ABC FORMAT ALPHA END

Structured HOLD file

A

DB

More Hidden Gems…in Output – EXTRACT

FILE=HOLD ,SUFFIX=FIX ,$ SEGNAME=A ,SEGTYPE=S0 ,$ FIELDNAME =RECTYPE ,ALIAS=R ,A1 ,A1 ,$FIELDNAME =FROMA1 ,ALIAS=FA1 ,A10 ,A10 ,$FIELDNAME =FROMA2 ,ALIAS=FA2 ,A5 ,A5 ,$SSEGNAME=B ,SEGTYPE=S0, PARENT=AFIELDNAME =RECTYPE ,ALIAS=1, ,A1 ,A1 ,$FIELDNAME =FROMB1 ,ALIAS=FB1 ,A15 ,A15 ,$

RECTYPE Hold Master generated

Multiple output types• Sequential files with rectypes – alpha or binary• FOCUS databases


Structured HOLD file content1. Determined by structure of and relationships

among the input files

Structured HOLD file content1. Determined by structure of and relationships

among the input files 2. Controlled with the FOCUS request syntax

A

CB

DE

TABLE FILE PRINT A.F1 B.F2 D.F3IF C.F4 EQ 5ON TABLE SET EXTRACT ONON TABLE HOLD AS SHOLD FORMAT ALPHAEND


Content from the structural view1. Entire hierarchy2. Exclude several fields3. Rollups4. Resorting children within parent segments5. Eliminating duplicates6. Restructuring based upon a model


TABLE FILE PRINT *ON TABLE SET EXTRACT ONON TABLE HOLD FORMAT ALPHAEND

A

CB

ED

F

HG

JI

PRINT * Includes every field in every path

ABCDFGIABCDFHJACE

Retains the segment relationships


DEFINE FILEDEFB/I4=IF B.F1 CONTAINS ‘ABC’ THEN 1 ELSE 0; ENDTABLE FILE PRINT *COMPUTE COMPD/I4=IF D.F1 CONTAINS ‘DEF’ THEN 1 ELSE 0;ON TABLE HOLD FORMAT ALPHAON TABLE SET EXTRACT ONEND

A

CB

ED

F

HG

JI

PRINT * Virtual fields

DEFINEs treated like real fields COMPUTEs hold constituents only


TABLE FILE HEADING“Today’s Report - <A.F1 “PRINT *COMPUTE Q2SALES/D12.2=IF D.F1 EQ ‘Q2’ THEN D.F2 ELSE 0;FOOTING“Produced by XYZ Division”WHERE TOTAL Q2SALES GT 100000ON TABLE SET EXTRACT ONON TABLE HOLD FORMAT ALPHAEND

A

CB

ED

F

HG

JI

PRINT * No internal matrix created WHERE TOTAL

ignored Output formatting clauses ignored


TABLE FILE PRINT *D.FLD1 NOPRINTON TABLE SET EXTRACT ONON TABLE HOLD FORMAT FOCUSEND

A

CB

ED

F

HG

JI

<fld1> NOPRINT Excludes specified fields from PRINT *

Verb objects Sorting fields

NOPRINT ignored for COMPUTEs to ensure that results are the same from extract

FLD1


TABLE FILE SUM D.FLD1 WITHIN B.FLD2PRINT D.FLD1ON TABLE SET EXTRACT ONON TABLE HOLD FORMAT ALPHAEND

A

CB

ED

F

HG

JI

SUM <fld> WITHIN <seg.fld> Rollup value placed in WITHIN segment SUM/WITHIN for each rollup level needed PRINT field to include detail Most verb object operators supported –

AVE, FST, LST, CNT, MAX, MIN

More Hidden Gems…in Output – EXTRACT

TABLE FILE PRINT A.FLD1 BY D.FLD2 BY B.FLD3ON TABLE SET EXTRACT ONON TABLE HOLD FORMAT FOCUSEND

A

CB

ED

F

HG

JI

BY <fld> Sort child segment instances within single

parent instance BY fields do not need to be related BY HIGHEST supported Operators requiring sort order will suppress

sort – FST, LST

A

D

B


TABLE FILE PRINT D.FLD1 BY A.FLD2 BY B.FLD3 NODUPESON TABLE SET EXTRACT ONON TABLE HOLD FORMAT ALPHAEND

BY <fld> NODUPES Applied selectively at the segment level Always retains the first instance Immediate descendants of dupe instance

appended to first instance Disabled when rollups – WITHIN – present

A

B

D

A

BB

D


A

CB

ED

From

To

ABDCE

ABDCE

A

CB

ED

A

B

D

D

B

A

Denormalization Normalization Inversion

Model-based Restructuring

Hidden Gems 2…In Output - DFIX

Working with the any-delimiter file

35

"COUNTRY","CAR","MODEL","BODYTYPE","SEATS","DEALER_COST","RETAIL_COST","SALES" "ENGLAND","JAGUAR","V12XKE AUTO","CONVERTIBLE",2,7427,8878,0 "ENGLAND","JAGUAR","XJ12L AUTO","SEDAN",5,11194,13491,12000 "ENGLAND","JENSEN","INTERCEPTOR III","SEDAN",4,14940,17850,0 "ENGLAND","TRIUMPH","TR7","HARDTOP",2,4292,5100,0 "FRANCE","PEUGEOT","504 4 DOOR","SEDAN",5,4631,5610,0 "ITALY","ALFA ROMEO","2000 4 DOOR BERLINA","SEDAN",4,4915,5925,4800 “ITALY","ALFA ROMEO","2000 GT VELOCE","COUPE",2,5660,6820,12400 "ITALY","ALFA ROMEO","2000 SPIDER VELOCE","ROADSTER",2,5660,6820,13000 "ITALY","MASERATI","DORA 2 DOOR","COUPE",2,25000,31500,0 "JAPAN","DATSUN","B210 2 DOOR AUTO","SEDAN",4,2626,3139,43000 "JAPAN","TOYOTA","COROLLA 4 DOOR DIX AUTO","SEDAN",4,2886,3339,3503 "GERMANY","AUDI","100 LS 2 DOOR AUTO","SEDAN",5,5063,5970,7800 "GERMANY","BMW","2002 2 DOOR","SEDAN",5,5800,5940,8950 "GERMANY","BMW","2002 2 DOOR AUTO","SEDAN",4,6000,6355,8900 "GERMANY","BMW","3.0 SI 4 DOOR","SEDAN",5,10000,13752,14000 "GERMANY","BMW","3.0 SI 4 DOOR AUTO","SEDAN",5,11000,14123,18940 strings

HEADER , if desired

Specific delimited file support COM, COMTTAB, TABT

DFIX provides support forAny delimiter – you defineAny enclosure for alphaHEADER if requiredRetain leading and trailing space if required



36


HEADER , if desired

FILENAME=CAR_CSV, SUFFIX=DFIX , DATASET=directory/car_csv.csv, $ SEGMENT=CAR_CSV, SEGTYPE=S0, $ FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A7, ACTUAL=A7BV, $ FIELDNAME=CAR, ALIAS=CAR, USAGE=A10, ACTUAL=A10BV, $ FIELDNAME=MODEL, ALIAS=MODEL, USAGE=A23, ACTUAL=A23BV, $ FIELDNAME=BODYTYPE, ALIAS=BODYTYPE, USAGE=A11, ACTUAL=A11BV, $ FIELDNAME=SEATS, ALIAS=SEATS, USAGE=I1, ACTUAL=A1, $ FIELDNAME=DEALER_COST, ALIAS=DEALER_COST, USAGE=I5, ACTUAL=A5V, $ FIELDNAME=RETAIL_COST, ALIAS=RETAIL_COST, USAGE=I5, ACTUAL=A5V, $ FIELDNAME=SALES, ALIAS=SALES, USAGE=I5, ACTUAL=A5V, $

Generated Synonym



37


HEADER , if desired

Access File contains DFIX information

SEGNAME=CAR_CSV, DELIMITER=',', ENCLOSURE=", HEADER=YES, PRESERVESPACE=YES,$

More Hidden Gems… in Sorting

38

Sorting it Your way

Uneven Grouping

HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH

GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG

FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE

DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD

Hidden Gems 2…In Sorting – ROWS/COLUMNS

Sorting it your way

39

CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC

Sorting is done based on the collation sequence in the codepage in effect on your system

BBBBBBBBBBbBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

BBBBBBBBBBbBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG

DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD

EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE

FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC

HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH


Sorting it your way

40

But what if you have a better way to do it!

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

ACROSS <field> COLUMNS <value> OR <value>…

BY <field> ROWS <value> OVER <value>…


Sorting it your way

41

But what if you have a better way to do it!

ACROSS <field> COLUMNS <value> OR <value>…

BY <field> ROWS <value> OVER <value>…

Implicit selection - only values mentioned are retrieved

Each value can have an AS phrase – relabeling Will affect SQL optimization


Sorting it your way

42

Hidden Gems 2…In Sorting – PLUS OTHERS

Uneven grouping

43

When you only need the top 5 values –

SUM SALESBY HIGHEST 5 SALESREPS

And you want a total for all the other values?

SUM SALESBY HIGHEST 5 SALESREPS PLUS-OTHERS


Uneven grouping

44

TABLE FILE CARPRINT CAR.BODY.DEALER_COST CAR.COMP.CARBY HIGHEST 2 CAR.BODY.SALES PLUS OTHERS AS 'Others'


Uneven grouping

45

TABLE FILE CARPRINT CAR.BODY.DEALER_COST CAR.COMP.CARBY HIGHEST 2 CAR.BODY.SALES PLUS OTHERS AS 'Others'


46

Sorting it Your way

Uneven Grouping

Retaining the Original Structure of your data

The Any-Delimiter File

MORE is Less…Work

Creating an Index for your requestTwo shades of Missing



47

QUESTIONS?

more hidden gems of webfocus reporting

Documents

requestmore hidden gems

newmore hidden gems

ds uniquemore hidden

outer joinmore hidden

det detroit bos boston

bos boston stl detroit

city end train

det detroit bos baltimore