things you can use on monday n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Things You Can Use on Monday PowerPoint Presentation
Download Presentation
Things You Can Use on Monday

Loading in 2 Seconds...

play fullscreen
1 / 78

Things You Can Use on Monday - PowerPoint PPT Presentation


  • 150 Views
  • Updated on

Things You Can Use on Monday. Rob Palmer Solution Director. Things You Can Use on Monday. Agenda SET Parameters Dialogue Manager Reporting Functions Missing Data App Commands Troubleshooting. SET Parameters. SET Parameters. A few of my favourite SET parameters CENT-ZERO CURRSYMB

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

Things You Can Use on Monday


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
    Presentation Transcript
    1. Things You Can Use on Monday Rob Palmer Solution Director

    2. Things You Can Use on Monday Agenda • SET Parameters • Dialogue Manager • Reporting • Functions • Missing Data • App Commands • Troubleshooting

    3. SET Parameters

    4. SET Parameters • A few of my favourite SET parameters • CENT-ZERO • CURRSYMB • COUNTWIDTH • DIRECTHOLD • DUPLICATECOL • BYDISPLAY • DMPRECISION • TARGETFRAME • ACROSSPRT • ASNAMES • HOLDLIST • HOLDATTR

    5. SET Parameters – CENT-ZERO • Controls the display of the 0 to the left of the decimal • Options: ON, OFF • Default: OFF SET CENT-ZERO = OFF SET CENT-ZERO = ON

    6. SET Parameters – CURRSYMB • Controls the currency symbol printed with M and N formats • Options: character, USD, GBP, JPY, EUR, NIS • Default: $ SET CURRSYMB = $ SET CURRSYMB = GBP SET CURRSYMB = EUR

    7. SET Parameters – COUNTWIDTH • Controls the format size of COUNT columns • Options: ON (9), OFF (5) • Default: OFF SET COUNTWIDTH = OFF SET COUNTWIDTH = ON

    8. SET Parameters – DIRECTHOLD • Controls how HOLD FORMAT FOCUS files are loaded • Options: ON (directly), OFF (hold file and Modify) • Default: ON SET DIRECTHOLD = ON SET DIRECTHOLD = OFF READS = 54 READS = 108

    9. SET Parameters – DUPLICATECOL • Controls how outer verb totals are displayed • Options: ON (show as separate column OFF (show in same column) • Default: ON SUM SALES SUM SALES BY COUNTRY SET DUPLICATECOL = ON SET DUPLICATECOL = OFF

    10. SET Parameters – BYDISPLAY • Controls how BY fields are printed • Options: ON, OFF • Default: OFF SET BYDISPLAY = OFF SET BYDISPLAY= ON

    11. SET Parameters – BYDISPLAY • Controls how BY fields are printed • Options: ON, OFF • Default: OFF SET BYDISPLAY = OFF SET BYDISPLAY= ON Excel rows

    12. SET Parameters – DMPRECISION • Controls decimal precision of & variables • Options: OFF, n • Default: OFF • If OFF, everything is an integer • Otherwise, you decide -SET &TEST = 123.45 + 5.43; -TYPE TEST = &TEST SET DMPRECISION = OFF SET DMPRECISION = 1 TEST = 128.88 &TEST = 128 &TEST = 128.9 SET DMPRECISION = 2 &TEST = 128.88

    13. SET Parameters – TARGETFRAME • Controls target frame for drilldown display • Options: framename • Default: current framename • Explicit “TARGET=” in drilldown overrides this setting No Setting

    14. SET Parameters – TARGETFRAME • Controls target frame for drilldown display • Options: framename • Default: current framename • Explicit “TARGET=” in drilldown overrides this setting No Setting SET TARGETFRAME = _blank

    15. SET Parameters – ACROSSPRT • Compress rows in report using PRINT and ACROSS • Options: NORMAL, COMPRESS • Default: NORMAL SET ACROSSPRT= NORMAL SET ACROSSPRT= COMPRESS

    16. SET Parameters – ACROSSPRT (cont) SET ACROSSPRT = NORMAL 4 rows SET ACROSSPRT = COMPRESS Compressedto 2 rows

    17. SET Parameters – ASNAMES, HOLDLIST, HOLDATTR • Affect contents of hold files and MFDs • ASNAMES (enables AS names to be used as column names) • Options: ON, OFF, FOCUS • Default: FOCUS • HOLDLIST (keeps non-printed columns out of hold file) • Options: ALL, PRINTONLY, ALLKEYS • Default: ALL • HOLDATTR (carries field attributes into the hold file) • Options: ON, OFF • Default: OFF

    18. SET Parameters – ASNAMES etc. (cont) All defaulted SET ASNAMES = ON – AS ‘UTILIZATION’ becomes fieldname SET HOLDLIST = PRINTONLY –non-printed columns excluded SET HOLDATTR = ON – TITLEs from original MFD carried forward

    19. SET Parameters – ASNAMES and ACROSS • Affects fieldnames of ACROSS columns TABLE FILE CAR SUM SALES BY COUNTRY ACROSS BODYTYPE ON TABLE HOLD END SET ASNAMES = OFF SET ASNAMES = ON

    20. SET Parameters • We will look at a few other SET parameters but later in the context of how they are used • XRETRIEVAL • HOLDMISS • HNODATA • NODATA

    21. Dialogue Manager

    22. Dialogue Manager – ASIS • In a Dialogue Manager variable blank = 0 = '0' = '0000' • Dialogue Manager variables have no format • ASIS allows us to test the true value of the variable -SET &VAR1 = ''; -SET &VAR2 = 0; &VAR1 EQ &VAR 2 = TRUE but ASIS(&VAR1) EQ ASIS(&VAR 2) = FALSE ASIS(&VAR1) EQ 0 = FALSE ASIS(&VAR2) EQ ‘ ’ = FALSE

    23. Dialogue Manager – Some system variables • &FOCMODE • Returns the operating system you are running in • Enables you to take action based on where you are • Values: WINNT, UNIX, etc. • &APPROOT • Returns the approot directory • You can test on it • You can also parse it apart • &FOCFEXNAME • Returns the name of the currently running Focexec • You can pass this along and know who called “me”

    24. Dialogue Manager – Subscripted & variables • & variables can be numerically subscripted • If we have &TEST, no problem, we refer to &TEST • But what if we want &TEST1, &TEST2 … &TESTn • Maybe we are looping • We can create and use &TEST.&n where n is an integer -REPEAT VAR_LOOP FOR &I FROM 1 TO 10 -SET &TEST.&I='This is variable ' | &I; -VAR_LOOP -? &

    25. Reporting

    26. Reporting • TABLEF and RDBMS • Conditional Joins • Dynamic Formatting

    27. Reporting – TABLEF and RDBMS • WebFOCUS can sort and RDBMS can sort • Make sure one does and make sure both don’t • Verify that RDBMS is sorting • SQL TRACE • ? STATS AGGREGATION DONE …

    28. Reporting – TABLEF and RDBMS (cont) • If RDBMS sorts your request, change TABLE to TABLEF • TABLEF • No sorting done by WebFOCUS • No Internal Matrix created • Report written faster • Some reporting commands require TABLE • Watch for errors and warnings • Use TABLEF whenever possible • May improve performance dramatically • Verify using traces and “? STAT”

    29. Reporting – Conditional Joins • Joins do not have to be based on equality tests • All relational tests are available • JOIN … WHERE … NE • JOIN … WHERE … GT • JOIN … WHERE … LT • etc.

    30. Reporting – Conditional Joins (cont) • In JOIN tool select the JOIN and “Create/Edit Selection”

    31. Reporting – Conditional Joins (cont) • You will be presented with a familiar panel • The Where Expression Builder • The expression will be EQ by default • Amend as required • I changed it to GT

    32. Reporting – Conditional Joins (cont) • We now JOIN based on a GT test • The WHERE clause will show up in your SQL JOIN FILE PORTFOLIO_BASE AT PORTFOLIO_BASE.PORTFOLIO.SIC_CODE TO MULTIPLE FILE INDUSTRY AT INDUSTRY.INDUSTRY.SIC_CODE TAG J0 AS J0 WHERE PORTFOLIO_BASE.PORTFOLIO.SIC_CODE GT J0.INDUSTRY.SIC_CODE; END

    33. Reporting – Dynamic Formatting • By default every value in a column is formatted the same • I5, D12.2, A10 … • But what if the rows require different formats • Some may be dollars, percents, units, etc. • Format each row’s value differently • Create a business rule to set each row’s format

    34. Reporting – Dynamic Formatting (cont) • Calculate the format with a COMPUTE • Reformat the measure referencing the computed column TABLE FILE METRICS SUM COMPUTE FLDFMT/A8 = DECODE METRIC ('Revenue''D12Mb 'Units''D12.2' 'Margin''D12.1%' 'Bonus''D12.2M'); NOPRINT VALUE/FLDFMT BY METRIC END

    35. Reporting – Dynamic Formatting (cont) • Run the report • Every row gets the appropriate format COMPUTE FLDFMT/A8 = DECODE METRIC ('Revenue' 'D12M' 'Units' 'D12.2' 'Margin' 'D12.1%' 'Bonus' 'D12.2M'); NOPRINT VALUE/FLDFMT

    36. Functions

    37. Functions • A few of my favourite functions • STRIP • STRREP • DB_LOOKUP • PUTDDREC

    38. Functions - STRIP • Removes a character from a string • STRIP(length, string, char, outfield) PHONE_DIGITS/A10 = STRIP(12, PHONE, '-', 'A10');

    39. Functions - STRREP • Replace all instances of a string with another string • STRREP (inlength, instring, searchlength, searchstring, replength,   repstring, outlength, outstring) CITY_SHORT/A35 = STRREP (35, CITY, 13, 'United States', 2, 'US', 35, 'A35');

    40. Functions – DB_LOOKUP • Lookup a value in one data source while reading another • No JOIN required • Not a replacement for JOIN – use when appropriate • DB_LOOKUP(look_mf, srcfld1, lookfld1, srcfld2, lookfld2, ..., returnfld) MANAGER/A10 = DB_LOOKUP (FINANCE, REGION, REGION, SALES_MANAGER);

    41. Functions – PUTDDREC • Write data to a flat file • Executed as part of a calculation • PUTDDREC(ddname, dd_len, record_string, record_len, outfield) APP FI WRITEOUT DISK summit_work/writeout.txt TABLE FILE CAR SUM SALES COMPUTE TEXT/A52='Sales for ' | CAR | ' in ' | COUNTRY | ' were ' | EDIT(SALES); COMPUTE WRITE_TEXT/I1=PUTDDREC('WRITEOUT', 8, TEXT, 52, 'I1'); NOPRINT BY COUNTRY BY CAR END

    42. Functions – PUTDDREC (cont) COMPUTE WRITE_TEXT/I1=PUTDDREC('WRITEOUT', 8, TEXT, 52, 'I1'); NOPRINT

    43. Functions – DB_LOOKUP and PUTDDREC • Both functions are executed as part of a calculation • Can be conditionally execute based on IF–THEN–ELSE logic • You decide exactly when and what is done • Based on your test and your data at run time MANAGER/A10 = IF SALES LT 100000 THEN DB_LOOKUP(FINANCE, REGION, REGION, SALES_MANAGER) ELSE ''; COMPUTE WRITE_TEXT/I1=IF REGION EQ 'HR' THEN PUTDDREC('Sales Data N/A', 8, TEXT, 52, 'I1') ELSE PUTDDREC('WRITEOUT', 8, TEXT, 52, 'I1'); NOPRINT

    44. Missing Data

    45. Missing Data • Missing data in WebFOCUS can refer to: • Missing data values or nulls • Missing data segments • We will be looking at ways to handle missing values • In calculations • In HOLD files • On report output

    46. Missing Data – In calculations • Example: • Sales in HR is missing but treated like 0 in the calculation • Is this right? • Results of calculations can be made “missing” based on availability of components PROFIT/D12 = SALES – COSTS;

    47. Missing Data – In calculations (cont) • Calculation: • Supports a result of MISSING (MISSING ON) • Demands that all components be present (NEEDS ALL) • Now HR has profit of MISSING because SALES is MISSING • NEEDS ALL could also be NEEDS SOME • Supported in Dev Studio PROFIT/D12 MISSING ON NEEDS ALL = SALES – COSTS;

    48. Missing Data – In HOLD files • If source data contains nulls, nulls will move to HOLD files • Common with RDBMS data • What happens if MISSING data created by the report TABLE FILE CAR SUM SALES BY COUNTRY ACROSS BODYTYPE ON TABLE HOLD END

    49. Missing Data – In HOLD files (cont) • HOLDMISS • Enables created MISSING data to be held as missing • Options: ON, OFF • Default: OFF TABLE FILE CAR ACROSS BODYTYPE ON TABLE HOLD END SET HOLDMISS = ON

    50. Missing Data – HNODATA vs. NODATA • NODATA • Controls characters printed for missing values • Options: character string • Default: . • HNODATA • Controls characters held for missing alpha values • Options: character string • Default: . • Note: If set, numeric missing values held as blank