4.11 Work with Parameters and the Parameter Form

This section provides procedures for the following tasks that you may perform as you work with parameters and the Parameter Form:

Using a pre-defined system parameter
Creating a user parameter
Creating a list of values (LOV) for a parameter
Validating a parameter value at runtime
Creating a default Parameter Form
Selecting parameters to include in the Runtime Parameter Form
Displaying the Parameter Form at runtime
Adding more pages to the Runtime Parameter Form
Passing parameters to reports running in batch mode
Creating an HTML Parameter Form header using PL/SQL
Creating an HTML Parameter Form footer using PL/SQL
Creating HTML Parameter Form input or select events
Changing HTML Parameter Form input to uppercase

4.11.1 Using a pre-defined system parameter

To use a system parameter:

In the Object Navigator, expand the Data Model node, then expand the System Parameters node.
Double-click the properties icon for the desired parameter to display the Property Inspector.
Under the Parameter node, set the Initial Value property, if required.
To validate the parameter's value at runtime, set the Validation Trigger property by clicking the ... button to display the PL/SQL Editor and define the PL/SQL to be triggered at runtime.

4.11.2 Creating a user parameter

Note:

Oracle Reports Builder automatically creates a user parameter when you use a bind parameter reference in a query.

To create a user parameter:

In the Object Navigator, expand the Data Model node, then click the User Parameters node.
Click the Create button in the toolbar.
Double-click the properties icon for the new parameter to display the Property Inspector.
Under the General Information node, replace the Name property with the desired parameter name.
Under the Parameter node, set the Initial Value and List of Values properties, if required.
To validate the parameter's value at runtime, set the Validation Trigger property by clicking property field to display the PL/SQL Editor and define the PL/SQL to be triggered at runtime.

4.11.3 Creating a list of values (LOV) for a parameter

Tip:

If you define a parameter in a template, you must apply the template to a report in order to select that parameter for the Runtime Parameter Form.

To create a LOV for a parameter:

In the Object Navigator, expand the Data Model node, then the User Parameters node.
Double-click the properties icon for the parameter for which you want to create a LOV to display the Property Inspector.
Under the Parameter node, double-click the List of Values property field to display the Parameter List of Values dialog box.
Select the type of list that you want to create:
- For Static Values, type a value in the Value text box and click Add. Repeat for each value you want to add. (Click Remove to delete items from the list)
- For SELECT Statement, type a query to populate the list of values. You can select more than one column to display in the LOV, where the first column contains the value to be assigned to the parameter. The LOV displays columns in the order specified in the query.
If you want the parameter value to be restricted to only those in the LOV, select the Restrict List to Predetermined Values check box. To display a combo box that allows users to edit values or type a different value in the Runtime Parameter Form, clear the check box.
If you do not want the first column (which contains the parameter value) of your query displayed in the LOV, select the Hide First Column check box. If there is no need to preserve the confidentiality of the first column, clear the check box.

Caution:
If you send the report output to an HTML file, either from Oracle Reports Builder or running it in your Web browser, the value of the first column will be visible in the HTML source, even if Hide First Column is selected.
If you run the report from a Web browser and the list of values is unrestricted, the HTML Parameter Form will display a text field instead of a combo box, and a list of static values that you can copy and paste into the text field. In this case, the first column will always be shown in the Parameter Form, even if Hide First Column is selected.
Click OK.

4.11.4 Validating a parameter value at runtime

To validate a parameter value at runtime:

In the Object Navigator, expand the Data Model node, then the System Parameters or User Parameters node.
Double-click the PL/SQL icon for the parameter for which you want to add a PL/SQL validation trigger.
In the PL/SQL Editor, define the PL/SQL to be triggered at runtime.

4.11.5 Creating a default Parameter Form

To create a default Parameter Form:

Choose Tools > Parameter Form Builder.
Click OK to display the Paper Parameter Form view with the default Parameter Form.

4.11.6 Selecting parameters to include in the Runtime Parameter Form

To select parameters to include in the Runtime Parameter Form:

Choose Tools > Parameter Form Builder.
In the Parameter Form Builder, click the parameters you want to include in the Runtime Parameter Form.
Modify the parameter labels as desired.
Click OK to display the Paper Parameter Form view.

4.11.7 Displaying the Parameter Form at runtime

To display the Runtime Parameter Form when you run your report:

Choose Edit > Preferences to display the Preferences dialog box.
On the Runtime Settings page, make sure that the Parameter Form check box is selected.

Note:
The Parameter Form can be used only for paper reports. If you display your paper-based report on the Web, you can create an HTML Parameter Form by adding HTML header and footer tags (using either the Before Form Value property and After Form Value property or the SRW.SET_BEFORE_FORM_HTML and SRW.SET_AFTER_FORM_HTML procedures). However, if you design a JSP-based Web report with a Parameter Form in Oracle Reports Builder, be aware that Web reports that use JSPs do not support the display of the Runtime Parameter Form at runtime. See Section 1.9.4, "About Parameter Forms for Web reports".

4.11.8 Adding more pages to the Runtime Parameter Form

To add more pages to the Runtime Parameter Form:

In the Object Navigator, double-click the properties icon next to the report name.
In the Property Inspector, under the Parameter Form Window node, set the Number of Pages property as desired.

4.11.9 Passing parameters to reports running in batch mode

To pass parameters (for example, data ranges) to reports running in batch mode:

Use bind variables in your query to restrict the query, and use the command line parameters to pass the values to the query. For example:

Query:

 SELECT * FROM EMP WHERE HIREDATE BETWEEN
 :FROM_DATE AND :END_DATE

Runtime:

RWRUN REP1 SCOTT/TIGER FROM_DATE='12-JUN-92' 
END_DATE='24-JUN-92'

4.11.10 Creating an HTML Parameter Form header using PL/SQL

See Section 4.6.10.2.5, "Creating an HTML Parameter Form header using PL/SQL"

4.11.11 Creating an HTML Parameter Form footer using PL/SQL

See Section 4.6.10.2.6, "Creating an HTML Parameter Form footer using PL/SQL"

4.11.12 Creating HTML Parameter Form input or select events

To create an HTML Parameter Form field with input or select events:

In the Object Navigator, double click the view icon next to the Paper Parameter Form node to display the Paper Parameter Form view.
Create or edit a Parameter Form field (see Section 4.9.1.1, "Creating a field object").
Double-click the field object to display the Property Inspector.
Under Web Settings, set the Additional Attributes (HTML) property to a valid JavaScript event handler.

Note:
In some cases, for example, when raising messages it may be necessary to type JavaScript code in the Before Form trigger.

To insert the JavaScript code in the Before Form trigger:

In the Object Navigator, double-click the properties icon next to the report name to display the Property Inspector.
Under Report Escapes, set the Before Form Type property to Text (if you will type the Javascript) or File (if you will import the JavaScript from a file).
Set the Before Form Value property by clicking the ... button to either type JavaScript in the dialog box or select an HTML file with the JavaScript to import.

Example 1: Data input validation

This example shows how to set Parameter Form fields for input validation when the report is run through with the Web. Doing so will raise a message whenever an end user enters invalid data in the Parameter Form field.

In the Paper Parameter Form view, create a Parameter Form field called PF_DEPTNO.
Double-click the field object to display the Property Inspector, and set the following properties:
- Under Parameter Form Field, set the Source property to DEPTNO.
- Under Web Settings, set the Additional Attributes (HTML) property to the following JavaScript event handler:
```
onChange="checkIt(this.form)"
```

In the Object Navigator, click (the properties icon) next to your report name to display the Property Inspector, and set the following properties:

Under Report Escapes, set the Before Form Type property to Text.
Set the Before Form Value property the following JavaScript code:

<SCRIPT LANGUAGE = "JavaScript">
function isNumber(inputStr){
   for (var i = 0; i < inputStr.length; i++) {
      var oneChar = inputStr.charAt(i)      
      if (oneChar < "0" || oneChar > "9") {
         alert("Please enter a numeric value.")
         return false
         }
     }
    return true
   }
function checkIt(form) {
   inputStr = form.DEPTNO.value
   if (isNumber(inputStr)) {
      // statements if true
      } 
      else {
         form.numeric.focus()
         form.numeric.select()
      }
   }
</SCRIPT>

At runtime, if the end user enters the department name in the Runtime Parameter Form rather than the department number when running the report through the Web, the following message is raised:

Please enter a numeric value.

Example 2: Select validation

This example shows you how to set Parameter Form fields for select validation when the report is run through the Web. Doing so will raise a message whenever an end user selects Printer from the DESTYPE list of values in the Runtime Parameter Form.

In the Paper Parameter Form view, create a Parameter Form field called PF_DESTYPE.
Double-click the field object to display the Property Inspector, and set the following properties:
- Under Parameter Form Field, set the Source property to DESTYPE.
- Under Web Settings, set the Additional Attributes (HTML) property to the following JavaScript event handler:
```
onChange="isPrinter(this.form)"
```
In the Object Navigator, click (the properties icon) next to your report name to display the Property Inspector, and set the following properties:
- Under Report Escapes, set the Before Form Type property to Text.
- Set the Before Form Value property the following JavaScript code:
```
<SCRIPT LANGUAGE = "JavaScript">
function isPrinter(form) {
  if( form.DESTYPE.options[form.DESTYPE.selectedIndex].value 
       == 'Printer')
    alert("Please be sure that your print is installed and running.")
    return true}
   }
</SCRIPT>
```

At runtime, if the end user selects PRINTER from a list of values in the DESTYPE field, the following message is raised:

Please be sure that your print is installed and running.

4.11.13 Changing HTML Parameter Form input to uppercase

To change data input values default to uppercase upon entry in a Parameter Form field:

Choose Tools > Parameter Form Builder.
In the Parameter Form Builder, create a Parameter Form field called PF_DESFORMAT, with a source of DESFORMAT.
Choose Tools > Property Inspector.
In the Property Inspector, under Web Settings, set the Additional Attributes (HTML) property to:
```
onChange="this.value=this.value.toUpperCase()"
```

Example: Default input to uppercase

This example specifies that data input values default to uppercase upon entry in a Parameter Form field.

In the Paper Parameter Form view, create a Parameter Form field called PF_DESTYPE.
Double-click the field object to display the Property Inspector, and set the following properties:
- Under Parameter Form Field, set the Source property to DESFORMAT.
- Under Web Settings, set the Additional Attributes (HTML) property to the following JavaScript event handler:
```
onChange="this.value=this.value.toUpperCase()"
```

At runtime, if the end user enters pdf as the destination format, the value will change to uppercase (that is, PDF) in the Runtime Parameter Form when running the report through the Web.