Transcript WebFOCUS Debugging Techniques

WebFOCUS Debugging Techniques
Nat Poe
1
Copyright 2007, Information Builders. Slide 1
Introduction
“Anything that can go wrong will go wrong.”
-Murphy’s Law

The strangest errors can have the simplest solutions.

Finding bugs is an acquired skill based on experience.
Copyright 2007, Information Builders. Slide 2
Contents

Incorrect Output

No Report

Relational Database Considerations

Other Considerations
Copyright 2007, Information Builders. Slide 3
Incorrect Output
4
Copyright 2007, Information Builders. Slide 4
Defined Field Problem
Issue 1: A defined field doesn’t seem to calculate the correct value. Only
Non-Sales department employees are to receive a 1% bonus.
Except for Sales
the bonus is 1%
In Sales, there
should be no
bonus
Copyright 2007, Information Builders. Slide 5
Defined Field Problem (continued)
Issue 1: A defined field doesn’t seem to calculate the correct value. Only
Non-Sales department employees are to receive a 1% bonus.
Remove sorting
for detail report
Bonus is the
same from the
record above the
SALES records
Copyright 2007, Information Builders. Slide 6
Defined Field Problem Solution
Issue 1: A defined field doesn’t seem to calculate the correct value. Only
Non-Sales department employees are to receive a 1% bonus.
Solution: Define needs an ELSE condition.
Always code an
ELSE condition
in an IF statement
In Sales, bonus
is now correct
Copyright 2007, Information Builders. Slide 7
Aggregation Problem
Issue 2: Adding a new field to the report changes the results.
Aggregating Salary
by Division
Salary is
different
Copyright 2007, Information Builders. Slide 8
Aggregation Problem (continued)
Issue 2: Adding a new field to the report changes the results.
Target a group and
run detail reports
Some employees are missing
Copyright 2007, Information Builders. Slide 9
Aggregation Problem (continued)
Issue 2: Adding a new field to the report changes the results.
First report used
data from EMPINFO
Second report used
data from TRNHIST
Copyright 2007, Information Builders. Slide 10
Aggregation Problem (continued)
Issue 2: Adding a new field to the report changes the results.
Unspecified defers
to the “ALL” setting
Copyright 2007, Information Builders. Slide 11
Aggregation Problem (continued)
Issue 2: Adding a new field to the report changes the results.
To check the
ALL setting
Copyright 2007, Information Builders. Slide 12
Aggregation Problem (continued)
Issue 2: Adding a new field to the report changes the results.
Salaries of employees
not taking training
were excluded
Copyright 2007, Information Builders. Slide 13
Aggregation Problem Solution
Issue 2: Adding a new field to the report changes the results.
Solution: Either set ALL to ON or declare a left outer JOIN.
Note: Inner and Left Outer
JOINs override the
ALL setting
Copyright 2007, Information Builders. Slide 14
MATCH FILE Problem
Issue 3: Data is appearing with blank values when there should be values.
The procedure is using MATCH FILE.
Blank departments and
names start the report
Copyright 2007, Information Builders. Slide 15
MATCH FILE Problem (continued)
Issue 3: Data is appearing with blank values when there should be values.
The procedure is using MATCH FILE.
Extracting all WE
employees and the
matching courses
Copyright 2007, Information Builders. Slide 16
MATCH FILE Problem (continued)
Issue 3: Data is appearing with blank values when there should be values.
The procedure is using MATCH FILE.
Department and name
listed for first course only
Create a report on CRSHOLD with no sort fields.
Copyright 2007, Information Builders. Slide 17
MATCH FILE Problem (continued)
Issue 3: Data is appearing with blank values when there should be values.
The procedure is using MATCH FILE.
Note: When using PRINT for the old file and new file, MATCH assumes there
are duplicate PIN values in the old and new files, so it will match the first
employee 30 in the old file to the first employee 30 in the new file. It
needs a second old file employee 30 to match it to the second new file
employee 30 and so forth.
Old file
New file
Copyright 2007, Information Builders. Slide 18
MATCH FILE Problem Solution
Issue 3: Data is appearing with blank values when there should be values.
The procedure is using MATCH FILE.
Solution: Use SUM for the old file.
SUM EMPINFO fields instead
MATCH now
replicates the data
Copyright 2007, Information Builders. Slide 19
MATCH FILE Problem Solution (continued)
Issue 3: Data is appearing with blank values when there should be values.
The procedure is using MATCH FILE.
Solution: Use SUM for the old file.
Report is now correct
Copyright 2007, Information Builders. Slide 20
Sort Group Calculation Problem
Issue 4: Calculation for a subfoot is incorrect for some of the groups. The
report is using a RECAP with invisible fields.
Incorrect average
Correct average
Copyright 2007, Information Builders. Slide 21
Sort Group Calculation Problem (continued)
Issue 4: Calculation for a subfoot is incorrect for some of the groups. The
report is using a RECAP with invisible fields.
NOPRINT fields
RECAP uses the sum of
values for a sort group
Copyright 2007, Information Builders. Slide 22
Sort Group Calculation Problem (continued)
Issue 4: Calculation for a subfoot is incorrect for some of the groups. The
report is using a RECAP with invisible fields.
Use LET to
“deactivate”
the NOPRINT
Note: LET translates a word or phrase
into another word or phrase.
Copyright 2007, Information Builders. Slide 23
Sort Group Calculation Problem (continued)
Issue 4: Calculation for a subfoot is incorrect for some of the groups. The
report is using a RECAP with invisible fields.
NOPRINT fields
will now display
RECAP for CA is:
10,730.00 / 10
Copyright 2007, Information Builders. Slide 24
Sort Group Calculation Problem Solution
Issue 4: Calculation for a subfoot is incorrect for some of the groups. The
report is using a RECAP with invisible fields.
Solution: CNTR should be set to 1 for each course.
Note: Delete LET when
finished debugging!
RECAP for CA is:
10,730.00 / 4
Copyright 2007, Information Builders. Slide 25
Sort Group Calculation Problem Solution (continued)
Issue 4: Calculation for a subfoot is incorrect for some of the groups. The
report is using a RECAP with invisible fields.
Solution: CNTR should be set to 1 for each course.
Correct average
Copyright 2007, Information Builders. Slide 26
Incorrect Ratio Problem
Issue 5: Wrong ratio displayed at the end of the report.
Should be between
1.8% and 2.6%
Copyright 2007, Information Builders. Slide 27
Incorrect Ratio Problem (continued)
Issue 5: Wrong ratio displayed at the end of the report.
Percentages
were summed
Copyright 2007, Information Builders. Slide 28
Incorrect Ratio Problem Solution
Issue 5: Wrong ratio displayed at the end of the report.
Solution: Use Summarize instead of Column totals.
Recalculated
the Compute
Copyright 2007, Information Builders. Slide 29
Incorrect Column Total Problem
Issue 6: Percentages don’t add up to 100%.
Where is the other 2%?
Copyright 2007, Information Builders. Slide 30
Incorrect Column Total Problem (continued)
Issue 6: Percentages don’t add up to 100%.
Integer field
Format changed to
D6.1% for PCT of
STAFF_CNTR
Copyright 2007, Information Builders. Slide 31
Incorrect Total Problem (continued)
Issue 6: Percentages don’t add up to 100%.
Note: The percent of total was calculated as
an integer value, which truncates the
decimal portion of a number. The result
is then changed to the decimal format.
Copyright 2007, Information Builders. Slide 32
Incorrect Total Problem Fixed
Issue 6: Percentages don’t add up to 100%.
Solution: Change the format of the counter to a decimal number.
Counter changed to a
floating decimal format
Copyright 2007, Information Builders. Slide 33
Incorrect Output
Review
 Isolate the Problem





When aggregating data, look at some or all of the detail.
Display data without sort fields.
Check the temporary field expression syntax.
Display NOPRINT fields.
A different numeric format could change the results.
 Common Remedies





With IF…THEN…, always code an ELSE too.
To control the handling of short path records, use:
 The SET ALL command for an Unspecified JOIN.
 Declare Inner or Left Outer in the JOIN component.
In MATCH FILE use SUM for the Old file to replicate data in the New file.
Use Summarize for column totals and Recompute for subtotals to
recalculate computed values in summary lines.
Don’t use Integer fields when decimal results are needed in calculations.
Copyright 2007, Information Builders. Slide 34
Incorrect Output
Review (continued)
 Helpful Debugging Commands
To display a setting:
? SET setting
Note: Use ALL. to display the ALL setting.
To “disable” NOPRINT:
LET NOPRINT =
Copyright 2007, Information Builders. Slide 35
No Report
36
Copyright 2007, Information Builders. Slide 36
Multi-path Problem
Issue 7: Path error message occurs when running a report.
Note: Report is sorted by SITE,
FULLNAME, and
COURSESTART.
Error number
Copyright 2007, Information Builders. Slide 37
Multi-path Problem (continued)
Issue 7: Path error message occurs when running a report.
Add an Other
component
Query the
error
Detailed
message
Copyright 2007, Information Builders. Slide 38
Multi-path Problem (continued)
Issue 7: Path error message occurs when running a report.
Delete the Other
component
Note: With the double arrowheads
(-->>), the Reporting Server
assumes multiple sites exist
for each employee with an
independent number of
course start dates for each
employee.
Copyright 2007, Information Builders. Slide 39
Multi-path Problem Solution
Issue 7: Path error message occurs when running a report.
Solution: Since Site is unique, specify a single path.
Changed to a
single path
Copyright 2007, Information Builders. Slide 40
No Data Selected
Issue 8: No data appears in a report when a selection statement is added.
No selection statements
Some states
exceed 5,000
Copyright 2007, Information Builders. Slide 41
No Data Selected (continued)
Issue 8: No data appears in a report when a selection statement is added.
Selection statement added
Copyright 2007, Information Builders. Slide 42
No Data Selected Solution
Issue 8: No data appears in a report when a selection statement is added.
Solution: Screen for aggregated values.
Copyright 2007, Information Builders. Slide 43
Column Notation Problem
Issue 9: Changing a field format loses all data for a report.
No data when the
Avg Salary formats
were changed
Copyright 2007, Information Builders. Slide 44
Column Notation Problem (continued)
Issue 9: Changing a field format loses all data for a report.
Multi-set request
Column notation to avoid
duplicate field names
Note: Report is OK.
Copyright 2007, Information Builders. Slide 45
Column Notation Problem (continued)
Issue 9: Changing a field format loses all data for a report.
Both average salary
formats were changed
to whole numbers
Copyright 2007, Information Builders. Slide 46
Column Notation Problem (continued)
Issue 9: Changing a field format loses all data for a report.
There are now two columns:
C1 – Original Format
C2 – New Format
C3 – Original Format
C4 – New Format
Copyright 2007, Information Builders. Slide 47
Column Notation Problem Solution
Issue 9: Changing a field format loses all data for a report.
Solution: Define fields with the desired format and use them instead.
Unique names for selection
Copyright 2007, Information Builders. Slide 48
Column Notation Problem Solution (continued)
Issue 9: Changing a field format loses all data for a report.
Solution: Define fields with the desired format and use them instead.
Copyright 2007, Information Builders. Slide 49
Including a Non-MR Procedure
Issue 10: A Standard Report in Managed Reporting is trying to include a
procedure on the Report Server.
Web Server
WebFOCUS Client
(Managed Reporting)
WebFOCUS Client can’t find the procedure
Copyright 2007, Information Builders. Slide 50
Including a Non-MR Procedure (continued)
Issue 10: A Standard Report in Managed Reporting is trying to include a
procedure on the Report Server.
Web Server
debug_10 resides
on the WebFOCUS
Client
WebFOCUS Client
(Managed Reporting)
Data Source
debug_01s resides
on the WebFOCUS
Report Server
WebFOCUS
Report Server
Copyright 2007, Information Builders. Slide 51
Including a Non-MR Procedure Solution
Issue 10: A Standard Report in Managed Reporting is trying to include a
procedure on the Report Server.
Solution: Use –MRNOEDIT to pass commands “as is” to the Report Server.
Copyright 2007, Information Builders. Slide 52
No Report
Review

Check to see if there are any error messages.
 Usually fixing the first one clears up subsequent

error messages.
If there are no error messages, check your selection criteria.
 Common Remedies




Don’t use sort fields from independent paths.
Use WHERE TOTAL to screen on aggregated data.
Avoid column notation by creating “shadow” fields.
For Managed Reporting, use –MRNOEDIT to pass commands
directly to the Report Server.
Copyright 2007, Information Builders. Slide 53
No Report
Review (continued)
 Helpful Commands:
To display additional information for an error message
? error_nbr
To pass Managed Report commands to the Report Server
-MRNOEDIT BEGIN
command …
-MRNOEDIT END
Copyright 2007, Information Builders. Slide 54
Relational Database Considerations
55
Copyright 2007, Information Builders. Slide 55
RDBMS Response Time Problem
Issue 11: A report is taking a long time to run. Data is retrieved from a
relational database and aggregated into product categories.
Sorted by length of
Category description
Copyright 2007, Information Builders. Slide 56
RDBMS Response Time Problem (continued)
Issue 11: A report is taking a long time to run. Data is retrieved from a
relational database and aggregated into product categories.
WebFOCUS
aggregated
the data
Copyright 2007, Information Builders. Slide 57
RDBMS Response Time Problem (continued)
Issue 11: A report is taking a long time to run. Data is retrieved from a
relational database and aggregated into product categories.
Text editor to
create tracing
statements
Copyright 2007, Information Builders. Slide 58
RDBMS Response Time Problem (continued)
Issue 11: A report is taking a long time to run. Data is retrieved from a
relational database and aggregated into product categories.
Command
Purpose
SET TRACEOFF=ALL
Turn off all current traces.
SET TRACEON=SQLAGGR…
Show if RDBMS will aggregate the data.
SET TRACEON=STMTRACE…
Show SQL code generated.
SET TRACEUSER=CLIENT
Display traces in a Web page.
SET XRERTIEVAL = OFF
Do not execute the procedure.
Copyright 2007, Information Builders. Slide 59
RDBMS Response Time Problem (continued)
Issue 11: A report is taking a long time to run. Data is retrieved from a
relational database and aggregated into product categories.
WebFOCUS will have
to aggregate the data
Copyright 2007, Information Builders. Slide 60
RDBMS Response Time Problem (continued)
Issue 11: A report is taking a long time to run. Data is retrieved from a
relational database and aggregated into product categories.
CATLEN is used as a sort
field and cannot be
converted to SQL code
Copyright 2007, Information Builders. Slide 61
RDBMS Response Time Problem Solution
Issue 11: A report is taking a long time to run. Data is retrieved from a
relational database and aggregated into product categories.
Solution: Compute CATLEN and use Order Report By (BY TOTAL)
Order report
by column
Copyright 2007, Information Builders. Slide 62
RDBMS Response Time Problem Solution (continued)
Issue 11: A report is taking a long time to run. Data is retrieved from a
relational database and aggregated into product categories.
Solution: Compute CATLEN and use Order Report By (BY TOTAL)
RDBMS will
aggregate the
data
Copyright 2007, Information Builders. Slide 63
RDBMS Response Time Problem Solution (continued)
Issue 11: A report is taking a long time to run. Data is retrieved from a
relational database and aggregated into product categories.
Solution: Compute CATLEN and use Order Report By (BY TOTAL)
RDBMS aggregated
the data
Copyright 2007, Information Builders. Slide 64
Relational Database Considerations
Review

When the RDBMS cannot aggregate the data:
 Individual rows are returned to the WebFOCUS



Report Server from the RDBMS.
WebFOCUS Report Server will have to aggregate the data.
Processing is slowed down.
Common Remedies
 Turn on traces to determine if the RDBMS is to aggregate


the data.
Use SET XRETRIEVAL=OFF to not retrieve data when tracing.
Avoid defining sort fields that will turn off aggregation; if possible,
compute them instead.
For more information, see:
Course 402 – Reporting Concepts and Efficiencies for Relational Databases
Copyright 2007, Information Builders. Slide 65
Other Considerations
66
Copyright 2007, Information Builders. Slide 66
Parameterized Reports
Question: How can I display the value of variables?
Answer A: Use &ECHO and SET XRETRIEVAL
Copyright 2007, Information Builders. Slide 67
Parameterized Reports (continued)
Question: How can I display the value of just the variables?
Answer B: Use -? and -EXIT
Copyright 2007, Information Builders. Slide 68
Parameterized Reports (continued)
Review
To control the display of Dialogue Manager variables:
-SET &ECHO = OFF|ON|ALL;
To display of Dialogue Manager variables:
-? &[&]variable
To control creating a report or not:
SET XRETRIEVAL ON|OFF
To terminate a procedure:
-EXIT
Copyright 2007, Information Builders. Slide 69
Using -EXIT
Note: Except for debugging purposes, avoid using –EXIT:
• ReportCaster may not work properly.
• A procedure that is included with a –EXIT will terminate
the procedure invoking it.
Copyright 2007, Information Builders. Slide 70
Profiles
Question: Why does a procedure work for one user, but not another?
Answer A: Check for Profiles
 Profiles are files that contain commands.


Can contain any valid report server core language command.
Executed every time a procedure is run in WebFOCUS.
 Three types of profiles:



Note: Never assume a setting; establish
Global
it in your procedure.
Group
 Executed after the global profile
 Can override commands found in the global profile
User
 Executed after the group profile

Can override commands in the group profile
 Commands in a procedure override commands in a profile.
Copyright 2007, Information Builders. Slide 71
Application Namespace
Question: Why does a procedure work for one user, but not another?
Answer B: Application Namespace
Application Namespace, commonly referred to as APP PATH, is
the search sequence used to locate the metadata and procedures
needed by the WebFOCUS Report Server.
Copyright 2007, Information Builders. Slide 72
Application Namespace (continued)
Question: How can I display the APP path search order?
Answer: Use APP SHOWPATH
Copyright 2007, Information Builders. Slide 73
WHENCE Command
Question: How do I know from which application the metadata is being
used?
Answer: Use the WHENCE command
EMPINFO.MAS in the
DEBUG application will
be used
To display from which application a file will be utilized:
WHENCE filename FOCEXEC|MASTER|ACCESS|FOCSTYLE|FOCUS
Copyright 2007, Information Builders. Slide 74
Additional Help
Question: What can I do when all else fails?
Answer: WebFOCUS Development Center
http://www.informationbuilders.com/support/wf_dev_center.html
Focal Point is a
free online Q&A
Bulletin Board
Copyright 2007, Information Builders. Slide 75
Review

Debugging is not an exact science.
 Never assume anything.
 Try to isolate the problem and work from there.
 Sometimes the strangest errors have the simplest solutions.
 Look at what is and not what you are expecting.

Debugging is an acquired skill based on experience.
 Learn as much as possible about the product and tools.
 The more you know, the more quickly you will be able solve
problems.
Copyright 2007, Information Builders. Slide 76