More Things You Can Use on Monday

1. More Things You Can Use on Monday Presented by Walter Blood Information Builders

2. More Things You Can Use on Monday Agenda • SET Parameters • Reporting • Dialogue Manager • Functions • Troubleshooting • My Top 5 from Last Year

3. SET Parameters

4. SET Parameters • A few more of my favourite SET parameters • ACROSSLINE • EMPTYCELLS • EMPTYREPORT • FILECOMPRESS • FOCFIRSTPAGE • CNOTATION • OVERFLOWCHAR • HIDENULLACRS • Working with SET Parameters

5. SET Parameters – ACROSSLINE • Controls the display the default line across the report • Options: ON, OFF, SKIP • Default: ON SET ACROSSLINE = ON SET ACROSSLINE = OFF

6. SET Parameters – ACROSSLINE • Controls the display the default line across the report • Options: ON, OFF, SKIP • Default: ON SET ACROSSLINE = SKIP TITLELINEis a synonym forACROSSLINE

7. SET Parameters – EMPTYCELLS • Controls the content of null cells in Excel output • Options: ON, OFF • Default: ON SET EMPTYCELLS = ON SET EMPTYCELLS = OFF

8. SET Parameters – EMPTYCELLS • Works in conjunction with NODATA SET EMPTYCELLS = OFF SET NODATA = 'No Data'

9. SET Parameters – EMPTYREPORT • Controls what is displayed if no data returned • Options: ON, OFF, ANSI • Default: OFF SET EMPTYREPORT = OFF SET EMPTYREPORT = ON SET EMPTYREPORT = ANSI

10. SET Parameters – FILECOMPRESS • Enables the compression of PDF report output • Options: ON, OFF • Default: OFF SET FILECOMPRESS = OFF SET FILECOMPRESS = ON 1.1 MB 244 KB

11. SET Parameters – FOCFIRSTPAGE • Controls the first page number of a report • Options: n • Default: 1 SET FOCFIRSTPAGE = 1 SET FOCFIRSTPAGE = 5

12. SET Parameters – FOCFIRSTPAGE • &FOCNEXTPAGE will tell you what the next page number will be after a report runs • Use with FOCFIRSTPAGE SET FOCFIRSTPAGE = &FOCNEXTPAGE &FOCNEXTPAGE = 5

13. SET Parameters – CNOTATION • Controls how columns are numbered for internal referencing during report processing • Options: ALL, PRINTONLY, EXPLICIT • Default: ALL TABLE FILE GGSALES SUM DOLLARS NOPRINT COMPUTE VARIANCE/D12M = DOLLARS - BUDDOLLARS; BY REGION END

14. SET Parameters – CNOTATION TABLE FILE GGSALES SUM DOLLARS NOPRINT COMPUTE VARIANCE/D12M = DOLLARS - BUDDOLLARS; BY REGION END SET CNOTATION = ALL SET CNOTATION = PRINTONLY SET CNOTATION = EXPLICIT

15. SET Parameters – OVERFLOWCHAR • Controls the character displayed on an overflow • Options: *, any character • Default: * OVERFLOWCHAR = * OVERFLOWCHAR = !

16. SET Parameters – HIDENULLACRS • Suppresses printing of null across columns on a page • Options: ON, OFF • Default: OFF SET HIDENULLACRS= OFF SET HIDENULLACRS= ON

17. SET Parameters – Working with parameters • SET parameter values can be displayed ? SET ? SET ALL ? SET ACROSSLINE

18. SET Parameters – Working with parameters • SET parameter categories can be displayed ? SET CATEGORY REPORT ? SET CATEGORY DATES

19. SET Parameters – Working with parameters • SET parameters can be displayed by category Categories MEMORY DATES SECURITY SINK SEND COMPUTATION MDI EXTERNALSORT FOCCALC ENVIRONMENT WEBFOCUS REPORT GRAPH STYLESHEET RETRIEVAL HOLD PLATFORM MAINFRAME ? SET BY CATEGORY

20. SET Parameters – Working with parameters • SET parameter values can be explained ? SET FOR ACROSSLINE

21. SET Parameters – Working with parameters • SET parameter values can be captured • SET parameter values can be used -? SET ACROSSLINE &SETTING &SETTING = ON -? SET ACROSSLINE &SETTING -IF &SETTING EQ 'ON' THEN GOTO …

22. Reporting

23. Reporting – DEFINE Functions • Subroutines based on WebFOCUS DEFINEs • Take arguments and return results • Can be used with any data file • As a compute, serves a very specific purpose • How do we make it more useable? COMPUTE DOLPERUNIT/D12.2 = DOLLARS / UNITS;

24. Reporting – DEFINE Functions • Create a DEFINE function • Use the function in the COMPUTE DEFINE FUNCTION PERUNIT (VALUE/D12, QTY/I9) PERUNIT/D12.2 = VALUE / QTY ; END COMPUTE DOLPERUNIT/D12.2=PERUNIT(DOLLARS,UNITS);

25. Reporting – DEFINE Functions • Creates a reusable and consistent function COMPUTE DOLPERUNIT/D12.2 = PERUNIT(DOLLARS,UNITS); COMPUTE BUDPERUNIT/D12.2 = PERUNIT(BUDDOLLARS,BUDUNITS);

26. Reporting – DEFINE Functions • Syntax DEFINE FUNCTION name (arg1/fmt1,..., argn/fmtn) [tempvariablea/formata = expressiona;] . . . [tempvariablex/formatx = expressionx;] name/format = [result_expression]; END

27. Reporting – DEFINE Functions • Usage Notes • The number of functions you can define and use in a session is virtually unlimited • Only cleared by DEFINE FUNCTION CLEAR command • Function names can be up to 64 characters. • Argument names are limited to 12 characters • No limit to the number of arguments • Can call other DEFINE functions, but cannot call themselves • If a DEFINE function isn’t available • (FOC03665) Error loading external function '%1'

28. Reporting – FILTER FILE • Automatic filtering in reports and graphs • Can be activated and deactivated as required Define the Filter FILTER FILE GGSALES NAME = MIDWEST WHERE REGION EQ 'Midwest'; END Activate the Filter SET FILTER = MIDWEST IN GGSALES ON

29. Reporting – FILTER FILE FILTER FILE GGSALES NAME = MIDWEST WHERE REGION EQ 'Midwest'; END TABLE FILE GGSALES SUM DOLLARS UNITS BY REGION END SET FILTER = MIDWEST IN GGSALES OFF SET FILTER = MIDWEST IN GGSALES ON

30. Reporting – FILTER FILE SET FILTER = FILTER FILE GGSALES NAME = MIDWEST WHERE REGION EQ 'Midwest'; NAME = NTHEAST WHERE REGION EQ 'Northeast'; NAME = STHEAST WHERE REGION EQ 'Southeast'; NAME = WEST WHERE REGION EQ 'West'; END MIDWEST IN GGSALES ON NTHEAST IN GGSALES ON STHEAST IN GGSALES ON WEST IN GGSALES ON

31. Reporting – FILTER FILE FILTER FILE GGSALES NAME = MIDWEST WHERE REGION EQ 'Midwest'; NAME = COFFEE WHERE PRODUCT CONTAINS 'Coffee'; END SET FILTER = MIDWEST COFFEE IN GGSALES ON

32. Reporting – FILTER FILE • Once activated, will apply to all subsequent reports • Apply variable selections passed in from forms • Enforce row level security • Avoid Dialogue Manager code inside a TABLE FILE -DEFAULT &SETFILTER=OFF SET FILTER = MIDWEST IN GGSALES &SETFILTER TABLE FILE GGSALES SUM DOLLARS UNITS BY REGION BY PRODUCT END TABLE FILE GGSALES SUM DOLLARS UNITS BY REGION BY PRODUCT -IF &SETFILTER EQ 'OFF' THEN - GOTO NO_FILTER; WHERE REGION EQ 'Midwest'; -NO_FILTER END VS

33. Reporting – Hold Files • ON TABLE HOLD directly to an app folder • HOLD AS app/filename • Puts summit.ftm into the baseapp app folder • To capture summit.mas still needs APP HOLDMETA TABLE FILE GGSALES SUM DOLLARS BY REGION ON TABLE HOLD AS baseapp/summit.ftm END

34. Reporting – Hold File Delimiter • Select any character as your column delimiter ON TABLE HOLD AS HDATA FORMAT DFIX DELIMITER '~' Midwest~11400665 Northeast~11392300 Southeast~11710379 West~11652946 SEGNAME=HDATA, DELIMITER=~, HEADER=NO, $ ON TABLE HOLD AS HDATA FORMAT DFIX DELIMITER '~' HEADER YES Region~Dollar Sales Midwest~11400665 Northeast~11392300 Southeast~11710379 West~11652946 SEGNAME=HDATA, DELIMITER=~, HEADER=YES, $

35. Dialogue Manager

36. Dialogue Manager – Testing for Operating System • &FOCMODE • Returns the operating system you are running in • Enables you to take action based on where you are • Values: WINNT, UNIX, etc. -IF &FOCMODE EQ 'UNIX' THEN GOTO UNIX_STUFF - ELSE GOTO WIN_STUFF;

37. Dialogue Manager – .EXISTS • Indicates if an & variable exists (has value) • Can be used to control navigation • 0 = variable does not exist • 1 = variable exists -IF &REGION.EXIST EQ 0 THEN GOTO GET_REGION - ELSE GOTO RUN_REPORT;

38. Dialogue Manager – .EVAL • Resolves & variables in a pre-parse of Dialogue Manager • Enables resolved variables to be used in DM code -SET &REGION = 'West'; -SET &TITLE = 'Report for &REGION'; -SET &REGION = 'West'; -SET &TITLE = 'Report for &REGION.EVAL';

39. Dialogue Manager – .QUOTEDSTRING • Automatically and intelligently puts quotes around & variables TABLE FILE EMPDATE PRINT NAME IF NAME EQ &NAME END TABLE FILE EMPDATE PRINT NAME IF NAME EQ &NAME.QUOTEDSTRING END &NAME = John Smith &NAME = John O’Brian

40. Dialogue Manager – .LENGTH • Returns the length of an & variable value • Can be used for edit checking and to control navigation -SET &REGION = 'West'; -SET &REGION = 'West '; &REGION.LENGTH = 4 &REGION.LENGTH = 10 -SET &REGION = 'West '; -SET &REGION = TRUNCATE(&REGION); &REGION.LENGTH = 4 -IF &REGION.LENGTH LT 10 THEN GOTO SHORT_REGION - ELSE GOTO LONG_REGION;

41. Dialogue Manager – .TYPE • Returns the type of an & variable value • Can be used for edit checking and to control navigation • A = alphanumeric • N = numeric -SET &REGION = 'West'; -SET &REGION = '12345'; &REGION.TYPE = A &REGION.TYPE = N -IF &REGION.TYPE EQ A THEN GOTO ALPHA_REGION - ELSE GOTO NUM_REGION;

42. Dialogue Manager – DEFAULTH • Sets a default value for hidden & variables • Hidden & variables are not prompted for • Autoprompt, MR Publish Utility, HTML Composer, ReportCaster Scheduling -DEFAULT &ORDERDATE=19970101 -DEFAULTH &ORDERDATE=19970101

43. Functions

44. Functions • A few more of my favourite functions • UPCASE • LOCASE • LCWORD • FPRINT

45. Functions – UPCASE • Convert a string to upper case • UPCASE(length, input, outfield) UP_REGION/A11=UPCASE(11,REGION,UP_REGION);

46. Functions – LOCASE • Convert a string to lower case • LOCASE(length, input, outfield) LO_REGION/A11=LOCASE(11,REGION,UP_REGION);

47. Functions – LCWORD • Convert a string to lower case • LCWORD(length, string, outfield) MX_COUNTRY/A10=LCWORD(10, COUNTRY, MX_COUNTRY);

48. Functions – FPRINT • Convert any numeric data to the alpha display you want • FPRINT(infield, format, outfield) ADOLLARS/A20= FPRINT(DOLLARS,'D12M',ADOLLARS); SALES/A30= 'Sales = ' | ADOLLARS;

49. Troubleshooting

50. SQL Tracing - TRACESTAMP • Controls the display of the time stamp on SQL trace • Options: ON, OFF • Default: ON SET TRACESTAMP = ON SET TRACESTAMP = OFF SET TRACESTAMP=OFF SET TRACEOFF=ALL SET TRACEON=SQLAGGR//CLIENT SET TRACEON=STMTRACE//CLIENT SET TRACEUSER=CLIENT