1 / 73

More Things You Can Use on Monday

More Things You Can Use on Monday. Walter F. Blood Technical Director . More Things You Can Use on Monday. Agenda SET Parameters Reporting Dialogue Manager Functions Troubleshooting My Top 5 from Last Year. SET Parameters. SET Parameters. A few more of my favourite SET parameters

druce
Download Presentation

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

More Related