Things You Can Use on Monday

1. Things You Can Use on Monday Noreen Redden Information Builders

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