Troubleshooting Report-Writer
Parameter Substitution Troubleshooting
Report-Writer parameter substitution problems commonly fall into the following areas:
• Failure to declare variables
• Failure to always precede variable references with a dollar sign ($)
• Special characters in the variable string
• Embedded quotes in the variable string
Always declare all variables. Although Report-Writer recognizes any name preceded by a dollar sign ($) as a variable, undeclared variables assume default types and characteristics that are often incompatible with their intended use. If a variable has not been declared, you can assign it a value only by using a command line parameter or by entering a value in response to a run‑time prompt. You cannot specify a value for an undeclared variable with a .let statement. Also, unless the variable has been declared, attempting to pass a parameter with a null value to Report-Writer can produce incorrect results.
Always specify variable instances by including the leading dollar sign ($). If the dollar sign is not present, and the variable has the same name as an identifier, such as a column or table name, Report-Writer assumes the variable is an identifier.
Check to make sure you have dereferenced any special characters in the variable's value string, such as embedded dollar sign ($), SQL or QUEL wild cards, and so forth. Special characters in a variable's value string can cause unexpected results. They can be stripped and/or processed by your system's command line processor, or misinterpreted by Report-Writer. Some characters require multiple dereferencing, if the character is meaningful to both the native system's command line processor and Report-Writer.
Check to make sure you have correctly handled quotes in the variable string. Embedded quotes in the variable's value string present problems similar to those of special characters, and you must handle them in the same manner. Because of the potential confusion with delimited identifiers, we strongly advise you to always use single rather than double quotes to surround a variable value string. This greatly reduces the number of double quotes that must be escaped and standardizes the dereferencing of quotes, because string constants must always be delimited by single quotes.
To determine a variable's actual substituted value, use a test report such as this:
.NAME check_variable
.DECLARE string_variable = varchar(32) with value 'Hi!'
.QUERY
SELECT count(object_id)
FROM ii_reports
.DETAIL
.PRINTLN $string_variable
This report prints the value of the variable as it can be used by Report-Writer. Date and format template variables behave similarly to string constants. Numeric variables behave in a much simpler manner, because they cannot contain characters that can be confused with comment or string delimiters. The above example works equally well for parameters specified on the command line and variables for which the user is prompted.
An alternate method of determining if the parameter value string is the cause of the failure is to save the report specification with the sreport command. If this fails, then the problem lies in the existing syntax. If this succeeds, then the problem most likely results from the effects of substituting the parameter value string.
The .setup, .cleanup, and .query statements differ from other statements in that parameter substitution can occur within quoted strings. For example, suppose your query contained the following where clause:
WHERE object_name = '$match_name'
If you substitute the value "ABC" for $match_name, the resulting where clause would be:
WHERE object_name='ABC'
Other statements, such as .println, treat a variable within a quoted string as a constant, as shown by the following results: