10. Using Report-Writer : Report Setup and Format : How Variables Are Used in a Report
Share this page                  
How Variables Are Used in a Report
For more dynamic report specifications, you can design a report using variables. You can use variables in queries or report formatting specifications. The values of the variables are specified at runtime by:
Assigning initialization values using the .declare statement
Responding to prompts for values
Assigning values using the .let statement
Passing values as parameters on the command line
You are strongly encouraged to define all variables with the .declare statement. 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. By defining variables with .declare, you can use the variables in all of the ways described previously. If a variable has not been defined through the .declare statement, you can assign it a value only by using a command line parameter or by entering a value in response to a runtime prompt. A value cannot be specified for an undeclared variable with a .let statement. In addition, unless the variable has been declared, attempting to pass a parameter with a null value to Report-Writer can produce incorrect results.
During execution of the report, the value assigned to a variable from data retrieval can vary, depending on the placement of the assignment statement in various .header, .footer, or .detail sections.
Note:  A report cannot be run in the background if it contains undeclared variables.
Many Report-Writer statements accept variables, alone or in expressions, to allow users flexibility in determining report specifications.
For information on these specific statements: Report Setup Statements, Page Layout and Control Statements, Report Structure Statements, Column and Block Statements, Text Positioning Statements, Print Statements, and Conditional and Assignment Statements, see the chapter "Report-Writer Statements."
Procedures for using variables are common throughout Report-Writer statements. Exceptions are the .declare and .let statements, which are used to define the variables.
Using variables in the .query statement allows the end user to retrieve data that meets particular needs. For instance, the user can obtain a report on a single employee or on all employees in a specified department by entering the employee name or the department name at runtime.
To create a report with user‑specified variable values, precede the variable with a dollar sign ($) when specifying it in the query. At report runtime, the user can either put the value for the variable on the command line when invoking the report, or respond to a program prompt.
For example, suppose you have a banking database in which you keep a table of customer accounts. In this table you have fields for customer names (custname), customer account numbers (custno), checking account balances (checking), and savings account balances (savings). You want to create two reports. They must be identically formatted, but must present different information; one report must provide checking account balances, and the other must provide savings account balances. To accomplish this task you can write a query like this:
.declare account_type = c10 with prompt
'Please enter "savings" or "checking":'
    select custno, custname,$account_type as val
        from account
The value of the variable account_type is the column name checking or savings rather than a string value.
As Report-Writer generates your report, it prompts you to enter an account type (savings or checking). Your response tells Report-Writer which kind of information it must retrieve with the query. If you respond to the prompt with checking, the completed query looks like this:
select custno, custname, checking as val
from account
For variables declared with the .declare statement, you can create a customized prompt string by using the with prompt clause, or you can specify an initial value for the variable by using the with value clause. For those variables that are undeclared, Report-Writer uses a default prompt string when prompting the user for the variable value.
You can also use variables in titles and other places within the report. For details on using variables in reports, see the .query, .declare, and .let statements in the chapter "Report-Writer Statements."