1 / 47

More Hidden Gems of WebFOCUS Reporting

More Hidden Gems of WebFOCUS Reporting. Walter F. Blood Technical Director Information Builders, Inc. More Hidden Gems Where We Will Look. … in Settings When the retrieved data values don’t line up correctly Two shades of Missing … in Retrieval Creating an index for your request

mira
Download Presentation

More Hidden Gems of WebFOCUS Reporting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. More Hidden Gems of WebFOCUS Reporting Walter F. Blood Technical Director Information Builders, Inc

  2. More Hidden Gems Where We Will Look … in Settings • When 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 matches • More is less … work … in Output • Retaining the original structure of your data • The any-delimiter file … in Sorting • Sorting it your way • Uneven grouping

  3. More Hidden Gems… in Settings When the retrieved data values don’t line up correctly Two shades of Missing

  4. More Hidden Gems …In Settings - JOINOPT When the retrieved data values don’t line up correctly Route Number Origin Station Dest Station TRAIN ROUTES JOIN TR TO OR - UNIQUE JOIN TR TO DS - UNIQUE ORIGIN STATIONS DESTINATION STATIONS Orig Station Dest Station Dest City Dest State Orig City Orig State TRAIN ROUTES TRAIN ROUTES

  5. More Hidden Gems …In Settings - JOINOPT When the retrieved data values don’t line up correctly TRAIN ROUTES TABLE FILE ROUTES PRINT TRAIN_NUM OR_STATION OR_CITY DE_STATION DE_CITY END ORIGIN STATIONS DESTINATION STATIONS 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 TRAIN ROUTES TRAIN ROUTES

  6. More Hidden Gems …In Settings - JOINOPT When the retrieved data values don’t line up correctly TRAIN ROUTES TABLE FILE ROUTES PRINT TRAIN_NUM OR_STATION OR_CITY DE_STATION DE_CITY END SET JOINOPT=GNTINT SET P1170=NEW ORIGIN STATIONS DESTINATION STATIONS TRAIN_NUM OR_STATION OR_CITY DE_STATION DE_CITY --------- ---------- ------- ---------- ------- 101 NYC NEW YORK ATL ATLANTA 202 BOS BOSTON BLT BALTIMORE 303 DET DETROIT BOS BOSTON 404 CHI CHICAGO DET DETROIT 505 BOS BOSTON STL ST. LOUIS TRAIN ROUTES TRAIN ROUTES

  7. More Hidden Gems …In Settings - SHORTPATH Two shades of Missing In JOINS JOIN with SET ALL=ON LEFT_OUTER JOIN 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.

  8. More Hidden Gems… in Retrieval Creating an Index for your request

  9. More Hidden Gems …In Retrieval - MDI Creating an Index for your request CAR • Instant access to data • Single index, multiple fields • External to FOCUS or XFOCUS files • Create as many as needed • Index your report selection MODEL COUNTRY TABLE FILE CAR SUM RETAILCOST … WHERE COUNTRY EQ ‘ENGLAND’ WHERE CAR EQ ‘JAGUAR’ WHERE MODEL EQ ‘XJ’

  10. More Hidden Gems …In Retrieval - MDI Creating an Index for your request MDI provides virtually instant access to the data you need • 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' ACCESS FILE TABLE FILE CAR PRINT BODYTYPE SEATS WHERE CAR EQ 'JAGUAR' WHERE COUNTRY EQ 'ENGLAND' END MDI – single index multiple fields Unlike RDBMS clustered index, MDI fields can be in any order and all fields need not be used MDIs can have more than 3 dimensions REBUILD MDINDEX creates new MDIs, or adds new partitions to an existing MDI

  11. More Hidden Gems …In Retrieval - MDI Creating an Index for your request REBUILD MDINDEX creates new MDIs, or adds new partitions to an existing MDI Create MDI Make NEW or ADD to existing? File Name MDI Name Any WHERE Selection?

  12. More Hidden Gems …In Retrieval - MDI Creating an Index for your request 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.

  13. More Hidden Gems… in Connections Playing with MATCHes MORE is Less…Work

  14. More Hidden Gems …In Connections - MATCH Playing with Matches Sort Merge NEW OLD BYs control selection and merge of records AFTER MATCH HOLD controls selection of output HOLD

  15. More Hidden Gems …In Connections - MATCH Playing with Matches Holding Patterns CONTROL THE MISSING! • 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

  16. More Hidden Gems …In Connections - MORE MORE is Less…Work DEFINE FILE FLAT A=D B=X END • Universal Concatenation works with - • TABLE • MATCH • Universal Concatenation lets you use - • Multiple files • Different file formats • WHERE/IF selection on each file • Universal Concatenation requires – • Fields must be available in each file • Real field • DEFINE • Fields must have same format TABLE FILE ORACLE SUM A B BY C WHERE … ON TABLE HOLD AS MORE FILE FLAT WHERE … END

  17. More Hidden Gems …In Connections - MORE MORE is Less…Work DEFINE FILE FLAT A=D B=X END TABLE FILE ORACLE SUM A B BY C WHERE … ON TABLE HOLD AS MORE FILE FLAT WHERE … END Beware duplicate sort keys not grouped or aggregated

  18. More Hidden Gems… in Outputs Retaining the Original Structure of your data The Any-Delimiter File

  19. VSAM DB2 FOCUS DB2 FOCUS More Hidden Gems…in Output - EXTRACT Most Hold Files remove all structure…

  20. …Or give it totally different structure… VSAM DB2 FOCUS DB2 FOCUS More Hidden Gems..in Output - EXTRACT How can I preserve the original structure?

  21. SEG1 VSAM SEG2 DB2 SEG4 FOCUS SEG3 DB2 SEG5 FOCUS More Hidden Gems …in Output - EXTRACT EXTRACT – structured hold files that retain the original structure From …and they travel!

  22. File 1 File 2 File 3 A Plain HOLD file E B C A B D D More Hidden Gems…in Output - EXTRACT A simple case: Report Structure TABLE FILE PRINT A B D IF C EQ 5 ON TABLE HOLD END

  23. File 1 Structured HOLD file File 2 File 3 A E B C A D B D More Hidden Gems…in Output - EXTRACT A structured case: Report Structure TABLE FILE PRINT A B D IF C EQ 5 ON TABLE SET EXTRACT ON ON TABLE HOLD AS ABC FORMAT ALPHA END

  24. More Hidden Gems…in Output – EXTRACT RECTYPE Hold Master generated 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=A FIELDNAME =RECTYPE ,ALIAS=1, ,A1 ,A1 ,$ FIELDNAME =FROMB1 ,ALIAS=FB1 ,A15 ,A15 ,$ • Multiple output types • Sequential files with rectypes – alpha or binary • FOCUS databases

  25. A B C E D More Hidden Gems…in Output - EXTRACT • Structured HOLD file content • Determined by structure of and relationships among the input files • Structured HOLD file content • Determined by structure of and relationships among the input files • Controlled with the FOCUS request syntax TABLE FILE PRINT A.F1B.F2D.F3 IF C.F4 EQ 5 ON TABLE SET EXTRACT ON ON TABLE HOLD AS SHOLD FORMAT ALPHA END

  26. More Hidden Gems…in Output - EXTRACT • Content from the structural view • Entire hierarchy • Exclude several fields • Rollups • Resorting children within parent segments • Eliminating duplicates • Restructuring based upon a model

  27. A F B G C H D I E J More Hidden Gems…in Output - EXTRACT TABLE FILE PRINT * ON TABLE SET EXTRACT ON ON TABLE HOLD FORMAT ALPHA END • PRINT * • Includes every field in every path • ABCDFGI • ABCDFHJ • ACE • Retains the segment relationships

  28. A F B G C H D I E J More Hidden Gems…in Output - EXTRACT DEFINE FILE DEFB/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 ALPHA ON TABLE SET EXTRACT ON END • PRINT * • Virtual fields • DEFINEs treated like real fields • COMPUTEs hold constituents only

  29. A F B G C H D I E J More Hidden Gems…in Output - EXTRACT 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 100000 ON TABLE SET EXTRACT ON ON TABLE HOLD FORMAT ALPHA END • PRINT * • No internal matrix created WHERE TOTAL ignored • Output formatting clauses ignored

  30. A F B G C H FLD1 D I E J More Hidden Gems…in Output - EXTRACT TABLE FILE PRINT * D.FLD1NOPRINT ON TABLE SET EXTRACT ON ON TABLE HOLD FORMAT FOCUS END • <fld1>NOPRINT • Excludes specified fields from PRINT * • Verb objects • Sorting fields • NOPRINT ignored for COMPUTEs to ensure that results are the same from extract

  31. A F B G C H D I E J More Hidden Gems…in Output - EXTRACT TABLE FILE SUM D.FLD1 WITHIN B.FLD2 PRINT D.FLD1 ON TABLE SET EXTRACT ON ON TABLE HOLD FORMAT ALPHA END • 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

  32. A F D B G C H B A D I E J More Hidden Gems…in Output – EXTRACT TABLE FILE PRINT A.FLD1 BY D.FLD2 BY B.FLD3 ON TABLE SET EXTRACT ON ON TABLE HOLD FORMAT FOCUS END • 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

  33. A A B B B D D More Hidden Gems…in Output - EXTRACT TABLE FILE PRINT D.FLD1 BY A.FLD2 BY B.FLD3 NODUPES ON TABLE SET EXTRACT ON ON TABLE HOLD FORMAT ALPHA END • 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

  34. D A A A B B A D B B C C D D E E More Hidden Gems…in Output - EXTRACT From Model-based Restructuring A B D C E To A B D C E Denormalization Normalization Inversion

  35. Hidden Gems 2…In Output - DFIX Working with the any-delimiter file • Specific delimited file support • COM, COMT • TAB, TABT • DFIX provides support for • Any delimiter – you define • Any enclosure for alpha • HEADER if required • Retain leading and trailing space if required "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

  36. Hidden Gems 2…In Output - DFIX Working with the any-delimiter file Generated Synonym 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, $ "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

  37. Hidden Gems 2…In Output - DFIX Working with the any-delimiter file Access File contains DFIX information SEGNAME=CAR_CSV, DELIMITER=',', ENCLOSURE=", HEADER=YES, PRESERVESPACE=YES,$ "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

  38. More Hidden Gems… in Sorting Sorting it Your way Uneven Grouping

  39. Hidden Gems 2…In Sorting – ROWS/COLUMNS Sorting it your way Sorting is done based on the collation sequence in the codepage in effect on your system AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBbBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH

  40. Hidden Gems 2…In Sorting – ROWS/COLUMNS Sorting it your way But what if you have a better way to do it! BY <field> ROWS <value> OVER <value>… AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG ACROSS <field> COLUMNS <value> OR <value>… BBBBBBBBBBbBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

  41. Hidden Gems 2…In Sorting – ROWS/COLUMNS Sorting it your way BY <field> ROWS <value> OVER <value>… But what if you have a better way to do it! ACROSS <field> COLUMNS <value> OR <value>… • Implicit selection - only values mentioned are retrieved • Each value can have an AS phrase – relabeling • Will affect SQL optimization

  42. Hidden Gems 2…In Sorting – ROWS/COLUMNS Sorting it your way

  43. Hidden Gems 2…In Sorting – PLUS OTHERS Uneven grouping When you only need the top 5 values – SUM SALES BY HIGHEST 5 SALESREPS And you want a total for all the other values? SUM SALES BY HIGHEST 5 SALESREPS PLUS-OTHERS

  44. Hidden Gems 2…In Sorting – PLUS OTHERS Uneven grouping TABLE FILE CAR PRINT CAR.BODY.DEALER_COST CAR.COMP.CAR BY HIGHEST 2 CAR.BODY.SALES PLUS OTHERS AS 'Others'

  45. Hidden Gems 2…In Sorting – PLUS OTHERS Uneven grouping TABLE FILE CAR PRINT CAR.BODY.DEALER_COST CAR.COMP.CAR BY HIGHEST 2 CAR.BODY.SALES PLUS OTHERS AS 'Others'

  46. More Hidden Gems… in Sorting When the retrieved data values don’t line up correctly Two shades of Missing Creating an Index for your request MORE is Less…Work Retaining the Original Structure of your data The Any-Delimiter File Sorting it Your way Uneven Grouping

  47. More Hidden Gems… in Sorting QUESTIONS?

More Related