You can define a parameter using the IO Design perspective. For example, the following Actuate SQL query uses the parameters lastname and firstname in the WHERE clause:
WITH ( lastname VARCHAR, firstname VARCHAR )
SELECT lname, fname, address, city, state, zip
FROM customerstable
WHERE (lname = :lastname) AND (fname = :firstname)
If an Actuate SQL query defines a parameter in a WITH clause but does not use the parameter, the query does not return any rows if no value is provided for the parameter when the report runs. For example, the following query does not return any rows if no values are provided for the lastname and firstname parameters when the report runs:
WITH ( lastname VARCHAR, firstname VARCHAR )
SELECT lname, fname, address, city, state, zip
FROM customerstable
How to define a parameter
1 In the graphical information object editor, choose Parameters.
2 In Parameters, click the top empty line, and complete the following tasks:
In Parameter, type the name of the parameter. If a parameter name contains a special character, such as a period (.) or a space, enclose the name in double quotation marks (").
In Data type, select a data type from the drop-down list.
In Default value, type the default value:
If Default value is a string, enclose the string in single quotation marks, as shown in the following example:
'New York City'
If Default value is a time stamp, it must be of the following form:
TIMESTAMP '2001-02-03 12:11:10'
If Default value is a number, use a period (.) as the decimal separator, as shown in the following example:
123456.78
NULL is not a valid parameter value.
To change the order of the parameters, use the up or down arrow.
To use Prompt editor to specify the parameter’s prompt properties, choose Prompt editor, as shown in Figure 3‑41.
To define other parameter properties, such as display name, select the parameter in Parameters, and define the properties in Properties.
Figure 3‑41 Choosing Prompt editor to specify a parameter’s prompt properties
How to delete a parameter
1 In the graphical information object editor, choose Parameters.
2 In Parameters, complete one of the following tasks:
To delete an individual parameter, select the parameter, and choose Remove.
To delete all parameters, choose Remove All.
Specifying a parameter’s prompt properties
Use Prompt editor to specify the properties for a parameter prompt, such as display control type, list of values, and default value. You can specify the parameter values and, if desired, a corresponding set of display values that users can select. You create a list of values by typing the values or by typing an Actuate SQL query that retrieves the values.
The query must meet the following requirements:
The query must retrieve one or two columns from an information object or map, as shown in the following example:
SELECT DISTINCT custID, customName
FROM "MyInformationObject.iob"
ORDER BY 2
SELECT DISTINCT custID, customName
FROM "MyInformationObject.iob" ['CA']
ORDER BY 2
The first column’s data type must match the parameter’s data type.
The query must not contain a WITH clause.
How to specify a parameter’s prompt properties
1 Locate the appropriate parameter in Parameters and choose Prompt editor.
2 On Prompt editor, in Show as, select the method of prompting the user, as shown in Figure 3‑42. If you use a type of display other than a text box, you can specify a list of values for the user to choose.
Figure 3‑42 Selecting the method of prompting the user
You can create a list of values by typing the values and, optionally, the display names, as shown in Figure 3‑43. If you do not provide display names, the values are displayed to the user.
Figure 3‑43 Typing a list of values and display names
You can create an Actuate SQL query that retrieves the values or both the values and the corresponding display names. If the query has two columns, the values in the second column are used as the display names. To use a query to create the list of values, select Dynamic list of values, as shown in Figure 3‑44, and type the query.
Figure 3‑44 Specifying an Actuate SQL query to provide a dynamic list of values
If you select Combo box (editable), Dynamic list of values, and Auto suggest, a drop-down list appears after the user types the number of characters specified in Start Auto suggest after N character(s). The list contains values that begin with the characters the user typed. For example, if the user typed 'Atel and N=4, the list contains the value 'Atelier graphique'. In this case, the query that retrieves the values must select two columns, a value column and a display name column.
3 In Default value, you specify the default value.
4 You also can specify values for the following additional properties:
Conceal value
Do not prompt
Required
When you finish specifying the property values for the prompt, choose OK.
Setting parameter properties
Table 3‑4 lists parameter properties and provides a description of each property.
Table 3‑4 Parameter properties
Parameter property
Can set?
Description
Conceal Value
Yes, in Prompt editor
Visibility of the value that the user provides for this parameter. To conceal the value, set to True. To display the value, set to False. This parameter property applies only to parameters with the varchar data type and the text box display type.
Data Type
Yes, on the Parameters tab
Parameter’s data type.
Default Value
Yes, in Prompt editor
Parameter’s default value. If a parameter does not have a default value, and the Required property is set to False, the parameter takes one of the following values if the user does not provide a value:
0 if the parameter is of type decimal, double, or integer.
Empty string if the parameter is in the varchar data type.
Current date and time if the parameter is in the timestamp data type.
Description
Not used
Not used.
Description Key
Not used
Not used.
Display Control Type
Yes, in Prompt editor
Control type for the parameter. The options are text box, read-only drop-down list, editable drop-down list, or radio buttons.
Display Format
Not used
Not used.
Display Length
Not used
Not used.
Display Name
Yes
Parameter prompt in Report Studio.
Display Name Key
Yes
Key for Display Name property in localization properties file.
Do Not Prompt
Yes, in Prompt editor
Visibility of the parameter to the user. To hide the parameter, set to True. To display the parameter, set to False.
Heading
Not used
Not used.
Heading Key
Not used
Not used.
Help Text
Not used
Not used.
Help Text Key
Not used
Not used.
Horizontal Alignment
Not used
Not used.
Name
Yes, on the Parameters tab
Parameter name.
Parameter Mode
Yes
Setting for parameters in stored procedures and ODA data source queries to specify the input or output type of the parameter. The options are Input, Output, InputAndOutput, or ReturnValue. ReturnValue is used only for stored procedures and is equivalent to Output.
Required
Yes, in Prompt editor
Indicator of whether the parameter is required. To require a value for this parameter, set to True. Otherwise, set to False.
Size
Yes
The size of the parameter if the parameter data type is varchar. Otherwise, not used. Must be set if size is greater than 1300.
Setting source parameters
A source parameter is a parameter that is defined in an information object from which you are building an information object.
You can set a source parameter to one of the following types of values:
A single scalar value
A local parameter in the information object that you are creating
You cannot set a source parameter to a column reference, such as ORDERS.ORDERID, or an Actuate SQL expression.
When you set a source parameter to a local parameter, you can indicate that the local parameter inherits the values of its prompt properties from the source parameter. The available prompt properties are Conceal Value, Default Value, Display Control Type, Do Not Prompt, and Required. If you specify that the local parameter inherits its prompt property values from the source parameter, and prompt property values for the source parameter change, the changes are propagated to the local parameter. For example, if the display control type for the source parameter changes from text box to read-only drop-down list, the display control type for the local parameter also changes from text box to read-only drop‑down list.
If you change a prompt property value for a local parameter, its prompt property values are no longer inherited from the source parameter. For example, if you change the display control type for the local parameter to editable drop-down list, and the display control type for the source parameter later changes to text box, the change is not propagated to the local parameter. To reinstate inheritance, choose Reset in Prompt editor. Choosing Reset returns all property values in the local parameter to inherited values, and the local parameter inherits any future changes to property values in the source parameter.
To set source parameters, use the Parameters page. To define a local parameter and set a source parameter to the local parameter in one step, drag the source parameter from Source parameter, and drop it in Parameter, as shown in Figure 3‑45.
Figure 3‑45 Setting a source parameter for a new, local parameter
How to set a source parameter
1 In the graphical information object editor, choose Parameters.
2 In Parameters, complete the following tasks:
In Source parameter, select the appropriate parameter.
In Value, complete one of the following tasks:
Choose a parameter from the drop-down list. The drop-down list contains the local parameters for the information object you are building.
If Value is a string, enclose the string in single quotation marks, as shown in the following example:
'New York City'
If Value is a time stamp, it must be in the following form:
TIMESTAMP '2001-02-03 12:11:10'
If Value is a number, use a period (.) as the decimal separator, as shown in the following example:
123456.78
If Value is a parameter, precede the parameter name with a colon (:).
Figure 3‑46 Providing a value for a source parameter
Synchronizing source parameters
You must synchronize source parameters when parameters in a source map or information object are added, removed, or reordered, or their data types or other properties change. To synchronize source parameters, choose Compile IO in the graphical information object editor, as shown in Figure 3‑47. Synchronizing source parameters refreshes the list of source parameters on the Parameters page.
In Parameter, type the name of the parameter. If a parameter name contains a special character, such as a period (.) or a space, enclose the name in double quotation marks (").
