More Things You Can Use on Monday - PowerPoint PPT Presentation

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

play fullscreen
1 / 73
More Things You Can Use on Monday
372 Views
Download Presentation
druce
Download Presentation

More Things You Can Use on Monday

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. More Things You Can Use on Monday Walter F. Blood Technical Director

  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