Transcript Title of Your Presentation

More Things You Can Use on Monday
Walter F. Blood
Technical Director
Copyright 2010, Information Builders. Slide 1
More Things You Can Use on Monday
Agenda
 SET Parameters
 Reporting
 Dialogue Manager
 Functions
 Troubleshooting
 My Top 5 from Last Year
Copyright 2010, Information Builders. Slide 2
SET Parameters
Copyright 2010, Information Builders. Slide 3
SET Parameters
 A few more of my favourite SET parameters
 ACROSSLINE
 EMPTYCELLS
 EMPTYREPORT
 FILECOMPRESS
 FOCFIRSTPAGE
 CNOTATION
 OVERFLOWCHAR
 HIDENULLACRS
 Working with SET Parameters
Copyright 2010, Information Builders. Slide 4
SET Parameters – ACROSSLINE
 Controls the display the default line across the report
 Options: ON, OFF, SKIP
 Default: ON
SET ACROSSLINE = ON
SET ACROSSLINE = OFF
Copyright 2010, Information Builders. Slide 5
SET Parameters – ACROSSLINE
 Controls the display the default line across the report
 Options: ON, OFF, SKIP
 Default: ON
SET ACROSSLINE = SKIP
TITLELINE
is a synonym for
ACROSSLINE
Copyright 2010, Information Builders. Slide 6
SET Parameters – EMPTYCELLS
 Controls the content of null cells in Excel output
 Options: ON, OFF
 Default: ON
SET EMPTYCELLS = ON
SET EMPTYCELLS = OFF
SET Parameters – EMPTYCELLS
 Works in conjunction with NODATA
SET EMPTYCELLS = OFF
SET NODATA = 'No Data'
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
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
SET Parameters – FOCFIRSTPAGE
 Controls the first page number of a report
 Options: n
 Default: 1
SET FOCFIRSTPAGE = 1
SET FOCFIRSTPAGE = 5
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
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
SET Parameters – CNOTATION
TABLE FILE GGSALES
SUM DOLLARS NOPRINT
COMPUTE VARIANCE/D12M = DOLLARS - BUDDOLLARS;
BY REGION
END
SET CNOTATION = ALL
REGION
DOLLARS
BUDDOLLARS
C1
C2
VARIANCE
C3
SET CNOTATION = PRINTONLY
REGION
DOLLARS
BUDDOLLARS
VARIANCE
C1
SET CNOTATION = EXPLICIT
REGION
DOLLARS
C1
BUDDOLLARS
VARIANCE
C2
SET Parameters – OVERFLOWCHAR
 Controls the character displayed on an overflow
 Options: *, any character
 Default: *
OVERFLOWCHAR = *
OVERFLOWCHAR = !
SET Parameters – HIDENULLACRS
 Suppresses printing of null across columns on a page
 Options: ON, OFF
 Default: OFF SET HIDENULLACRS = OFF
SET HIDENULLACRS = ON
SET Parameters – Working with parameters
 SET parameter values can be displayed
? SET
? SET ALL
? SET ACROSSLINE
SET Parameters – Working with parameters
 SET parameter categories can be displayed
? SET CATEGORY REPORT
? SET CATEGORY DATES
SET Parameters – Working with parameters
 SET parameters can be displayed by category
? SET BY CATEGORY
Categories
MEMORY
DATES
SECURITY
SINK
SEND
COMPUTATION
MDI
EXTERNALSORT
FOCCALC
ENVIRONMENT
WEBFOCUS
REPORT
GRAPH
STYLESHEET
RETRIEVAL
HOLD
PLATFORM
MAINFRAME
SET Parameters – Working with parameters
 SET parameter values can be explained
? SET FOR ACROSSLINE
SET Parameters – Working with parameters
 SET parameter values can be captured
-? SET ACROSSLINE &SETTING
&SETTING = ON
 SET parameter values can be used
-? SET ACROSSLINE &SETTING
-IF &SETTING EQ 'ON' THEN GOTO …
Reporting
Copyright 2010, Information Builders. Slide 22
Reporting – DEFINE Functions
 Subroutines based on WebFOCUS DEFINEs
 Take arguments and return results
 Can be used with any data file
COMPUTE DOLPERUNIT/D12.2 = DOLLARS / UNITS;
 As a compute, serves a very specific purpose
 How do we make it more useable?
Reporting – DEFINE Functions
 Create a DEFINE function
DEFINE FUNCTION PERUNIT (VALUE/D12, QTY/I9)
PERUNIT/D12.2 = VALUE / QTY ;
END
 Use the function in the COMPUTE
COMPUTE DOLPERUNIT/D12.2=PERUNIT(DOLLARS,UNITS);
Reporting – DEFINE Functions
 Creates a reusable and consistent function
COMPUTE DOLPERUNIT/D12.2 = PERUNIT(DOLLARS,UNITS);
COMPUTE BUDPERUNIT/D12.2 = PERUNIT(BUDDOLLARS,BUDUNITS);
Reporting – DEFINE Functions
 Syntax
DEFINE FUNCTION name (arg1/fmt1,..., argn/fmtn)
[tempvariablea/formata = expressiona;]
.
.
.
[tempvariablex/formatx = expressionx;]
name/format = [result_expression];
END
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'
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
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
Reporting – FILTER FILE
FILTER FILE GGSALES
NAME = MIDWEST
WHERE REGION EQ 'Midwest';
NAME = NTHEAST
WHERE REGION EQ 'Northeast';
SET FILTER =
MIDWEST IN GGSALES ON
NTHEAST IN GGSALES ON
NAME = STHEAST
WHERE REGION EQ 'Southeast';
NAME = WEST
WHERE REGION EQ 'West';
STHEAST IN GGSALES ON
END
WEST IN GGSALES ON
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
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
TABLE FILE GGSALES
SUM DOLLARS UNITS
SUM DOLLARS UNITS
BY REGION
BY REGION
BY PRODUCT
VS
BY PRODUCT
END
-IF &SETFILTER EQ 'OFF' THEN
GOTO NO_FILTER;
WHERE REGION EQ 'Midwest';
-NO_FILTER
END
Reporting – Hold Files
 ON TABLE HOLD directly to an app folder
 HOLD AS app/filename
TABLE FILE GGSALES
SUM DOLLARS
BY REGION
ON TABLE HOLD AS baseapp/summit.ftm
END
 Puts summit.ftm into the baseapp app folder
 To capture summit.mas still needs APP HOLDMETA
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, $
Dialogue Manager
Copyright 2010, Information Builders. Slide 35
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;
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;
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';
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
Dialogue Manager – .LENGTH
 Returns the length of an & variable value
 Can be used for edit checking and to control navigation
-SET &REGION = 'West';
&REGION.LENGTH = 4
-SET &REGION = 'West
';
&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;
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';
&REGION.TYPE = A
-SET &REGION = '12345';
&REGION.TYPE = N
-IF &REGION.TYPE EQ A THEN GOTO ALPHA_REGION
ELSE GOTO NUM_REGION;
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
Functions
Copyright 2010, Information Builders. Slide 43
Functions
 A few more of my favourite functions
 UPCASE
 LOCASE
 LCWORD
 FPRINT
Functions – UPCASE
 Convert a string to upper case
 UPCASE(length, input, outfield)
UP_REGION/A11=UPCASE(11,REGION,UP_REGION);
Functions – LOCASE
 Convert a string to lower case
 LOCASE(length, input, outfield)
LO_REGION/A11=LOCASE(11,REGION,UP_REGION);
Functions – LCWORD
 Convert a string to lower case
 LCWORD(length, string, outfield)
MX_COUNTRY/A10=LCWORD(10, COUNTRY, MX_COUNTRY);
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;
Troubleshooting
Copyright 2010, Information Builders. Slide 49
SQL Tracing - TRACESTAMP
 Controls the display of the time stamp on SQL trace
 Options: ON, OFF
 Default: ON
SET TRACESTAMP = ON
SET
SET
SET
SET
SET
SET TRACESTAMP = OFF
TRACESTAMP=OFF
TRACEOFF=ALL
TRACEON=SQLAGGR//CLIENT
TRACEON=STMTRACE//CLIENT
TRACEUSER=CLIENT
Copyright 2010, Information Builders. Slide 50
Developer Studio – Comments
CTRL-M
CTRL-R
Copyright 2010, Information Builders. Slide 51
Developer Studio – I forgot to press Caps Lock
Have you ever typed this …
And wanted to type this …
Copyright 2007, Information Builders. Slide 52
Developer Studio – I forgot to press Caps Lock
+
=
Copyright 2007, Information Builders. Slide 53
Who, What, Where am I
 What’s my site code?
? SITECODE
 What release am I running?
? RELEASE
Copyright 2007, Information Builders. Slide 54
My Top 5 from Last Year
Copyright 2010, Information Builders. Slide 55
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
TEST = 128.88
&TEST = 128
SET DMPRECISION = 1
&TEST = 128.9
SET DMPRECISION = 2
&TEST = 128.88
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
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);
DEFINE Functions and DB_LOOKUP
 Currency conversion
 Consistent conversion based on standard rates
 No JOIN to an exchange rate table required
Sales Data
Month
Country
Sales
Currency
2011/05
Germany
$85,657.14
EUR
2011/04
Germany
$30,286.37
EUR
2011/03
Germany
$25,578.56
EUR
Exchange Rates
Month
Currency
Rate
2011/05
EUR
1.45359
2011/04
EUR
1.44484
2011/03
EUR
1.39992
DEFINE Functions and DB_LOOKUP
 Create a DEFINE Function to convert any value
DEFINE FUNCTION CONVERT
(MONTH/A6, CURRENCY/A3, VALUE/D15.2)
RATE/D15.6 = DB_LOOKUP (XCHG_RATE,
MONTH, RATE_MONTH,
CURRENCY, CURRENCY,
RATE);
CONVERT/D12.2 = VALUE * RATE;
END
DEFINE Functions and DB_LOOKUP
 Use the function in a report
TABLE FILE SALES
SUM SALES
COMPUTE USD_SALES/D12.2=
CONVERT(SALES_MONTH, CURRENCY, SALES);
BY COUNTRY
BY SALES_MONTH
IF COUNTRY EQ 'GERMANY'
END
Missing Data – In calculations
 Example:
PROFIT/D12 = SALES – COSTS;
Department
Sales
Costs
Profit
East
100
90
10
HR
.
90
-90
West
0
90
-90
 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
Missing Data – In calculations (cont)
PROFIT/D12 MISSING ON NEEDS ALL = SALES – COSTS;
Department
Sales
Costs
Profit
East
100
90
10
HR
.
90
.
West
0
90
-90
 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
 Supported in Dev Studio
Troubleshooting – WHENCE
 Shows where a selected module is being pulled from
 Can search for Focexecs, Master Files and Access Files
 WHENCE focexec FOCEXEC
 WHENCE masterfile MASTER
 WHENCE accessfile ACCESS
 Returns physical location of the module
 Verify you are accessing and using the one you think you are
WHENCE controlchart FOCEXEC
WHENCE car MASTER
WHENCE portfolio ACCESS
IB Tech Support as a Search Engine
Copyright 2010, Information Builders. Slide 65
IB Tech Support as a Search Engine
 Go to:
http://www.microsoft.com/windows/ie/searchguide/en-en/default.mspx
IB Tech Support as a Search Engine
 Complete the form on the right to
URL for search provider
Descriptive Name
IB Tech Support as a Search Engine
 To find the required URL:
 Open a new browser window or tab
 Access the IB Enterprise Content Library
 http://ecl.informationbuilders.com/ecl/search
 Search for TEST (upper case)
 Copy the URL in the address bar
 Paste the URL into the form
IB Tech Support as a Search Engine
 After filling out the form, press Install
 Press Add on the
confirmation panel
IB Tech Support as a Search Engine
 Your all set
 Click on the Search Providers drop
down
 IB Tech Support is now listed
 Select it and enter search criteria
IB Tech Support as a Search Engine
 Your search results are presented using Magnify
 Category tree on the left enable further filtering
 Select Tabular View to see results as an Active Report
IB Tech Support as a Search Engine
 Select Tabular View to see results as an Active Report
Thank You!