Things You Can Use on Monday

1. Things You Can Use on Monday Doug Hutcherson Technical Account Manager Presented at the OKC User Group Meeting Oct 7, 2010

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 favorite 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 Turn on all the time

6. SET Parameters – CURRSYMB • Controls the currency symbol printed with M and N formats • Options: character, USD, GBP, JPY, EUR, NIS • Default: $ SET CURRSYMB = $ ALSO  ON TABLE SET CURRSYMB GBP 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 Make it a default

8. 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

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

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

11. 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

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

13. 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

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

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

16. 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

17. 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

18. 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

19. Dialogue Manager

20. 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”

21. Dialogue Manager – Some system variables FOCEXEC -TYPE &FOCMODE -TYPE &APPROOT -TYPE &FOCFEXNAME OUTPUT WINNT C:\ibi\apps ggsales_rpt.fex

22. Reporting

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

24. 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 …

25. 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”

26. 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.

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

28. 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

29. 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

30. 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

31. 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''D12M 'Units''D12.2' 'Margin''D12.1%' 'Bonus''D12.2M'); NOPRINT VALUE/FLDFMT BY METRIC END

32. 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

33. Functions

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

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

36. 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

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

38. APP Commands

39. APP Commands • APP commands enable us to work with the applicationnamespace • Control the use of files in the path • Manipulate the path • Interrogate the path • Interrogate the application folders • Operating System agnostic • Works the same everywhere

40. APP Commands – Control and use files • APP HOLD appname • Directs all hold files to a specific app directory • APP HOLD summit_work • All hold files and their MFDs go to summit_work • APP HOLDMETA appname • Directs only the hold file MFDs to a specific app directory • APP HOLDMETA summit_work • MFDs go to summit_work, data goes to temp • APP HOLDDATA appname • Directs only the hold data file to a specific app directory • APP HOLDDATA summit_work • Data goes to summit_work, MFDs go to temp

41. APP Commands – Control and use files (cont) • If the files exists, you may need to filedef it • FILEDEF and DYNAM are operating system specific • APP FI is operating system agnostic • APP FI holdname DISK appname/filename.ext • Works on all platforms • Can also be used in Master Files to specify the data file • DATASET=‘appname/filename.ext’ APP FI WRITEOUT DISK summit_work/writeout.txt FILE=GLACCOUNT ,SUFFIX=FIX ,DATASET='ibisamp/glaccount.ftm'

42. APP Commands – Manipulate the Path • Change the path on the fly • APP PREPENDPATH adds your app to the front of the path • APP APPENDPATH adds your app to the end of the path • Applies to the current process only • Allows you to bring app folders into the path temporarily • summit_work is now the first app folder in the path • summit_work is now the last app folder in the path APP PREPENDPATH summit_work APP APPENDPATH summit_work

43. APP Commands – Manipulate the Path (cont) • By default APP folders are subdirectories of APPROOT Windows  C:\ibi\apps Unix  ibi/apps • APP MAP allows you to assign a logical app name to any available physical directory • Can be placed in the profiles or dynamic in a Focexec • APP MAP appnamephysicalname • Can be used like any other available APP folder • Hold to it, put it in the path, etc. APP MAP share \\robpalmer\share APP MAP summit_temp c:\temp\summit_temp

44. APP Commands – Interrogate the Path • APP SHOWPATH • Shows you the path • Notice the two we just APP MAPped • You see it, but you can’t act on it • Let’s act on it

45. APP Commands – Interrogate the app folders • APP LIST [HOLD] • Lists all real and mapped application folders • HOLD option creates focappl APP LIST HOLD TABLE FILE focappl PRINT * END

46. APP Commands – Interrogate the app folders (cont) • APP QUERY appname [HOLD] • Lists contents of selected application • HOLD option creates focappq APP QUERY summit_work HOLD TABLE FILE focappq PRINT * END

47. Troubleshooting

48. Troubleshooting • Leave a trail • SQL Traces, XRETRIEVAL • &ECHO, &STACK • WHENCE • IB Tech Support as a Search Engine

49. Troubleshooting – SQL Traces • Have a Focexec handy to quickly turn on SQL traces • -INCLUDE trace • Shows Optimization trace and SQL trace trace.fex SET TRACEOFF=ALL SET TRACEON=SQLAGGR//CLIENT SET TRACEON=STMTRACE//CLIENT SET TRACEUSER=CLIENT

50. Troubleshooting – XRETRIEVAL • Controls whether WebFOCUS communicates with thedatabase • Options: ON, OFF • Default: ON • SET XRETRIEVAL = ON -INCLUDE trace TABLE FILE PORTFOLIO SUM AUTHORIZED BY MONTH ON TABLE HOLD END • SET XRETRIEVAL = OFF