1 / 78

Things You Can Use on Monday

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

rhys
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. 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. 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 DISPLAY= ON

  11. SET Parameters – BYDISPLAY • Controls how BY fields are printed • Options: ON, OFF • Default: OFF SET BYDISPLAY = OFF SET DISPLAY= 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 • XRETIREVAL • 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 • &&OSTYPE • 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 • &FOCEXECNAME • 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 • SQLTOPTTF • Automatically turns TABLE to TABLEF, if possible • Options: ON, OFF • Default: ON • 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 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

More Related