In this section, you will learn how to modify an HTML Parameter Form in Reports Builder to populate the list of values (LOV) you created with values from a data source. You will use JSP tags for Oracle Reports to enable the Parameter Form to access elements from a data model.
The steps in this section will show you how to create a simple data model for the Parameter Form.
In Reports Builder, open the HTML file you created,
In the Object Navigator, double-click the icon next to the Data Model node to display the Data Model view.
In the Data Model view that displays, click the SQL Query tool in the tool palette, then click in an open area of the Data Model view to display the SQL Query Statement dialog box.
In the SQL Query Statement field, enter the following
select department_name, department_id from departments order by department_name
You you can enter this query in any of the following ways:
Copy and paste the code from the provided text file called
simplejsppf_code.txt into the SQL Query Statement field.
Click Query Builder to build the query without entering any code manually.
Type the code in the SQL Query Statement field.
Click Connect, then type the connection information for the Human Resources sample schema (HR).
Click OK. Your data model should look like this:
Save the report as
In this section, you will learn how to modify the Web source to pull data into the existing list of values (LOV) in your Parameter Form. This data will rely on the data model you created in the previous section. We will also examine the code to explain how each element operates.
Click the Web Source button in the toolbar to display the Web Source view.
In the Web Source view, look for the following code:
<select name="p_department" size="1"> <option value="1">a</option> </select>
In the above code, the LOV returns a static value. The display name is "a" and the value is "1".
Since the LOV is currently static, we need to change this HTML element to dynamically retrieve data based on our data model.
In the Web Source view, modify the above code so that it looks like the following:
<select name=" p_department"> <rw:foreach id="fn" src="G_department_name"> <option value="<rw:field id="f_deptId" src="department_id"/>"><rw:field id="deptname1" src="department_name"/></option> </rw:foreach> </select>
You can either type the code manually or copy and paste it from the provided file called
Save your report.
By using JSP tags for Oracle Reports in the above code, we retrieve data into the Parameter Form's LOV by basing the parameters on fields in the data model. Let us examine each element:
<option>: The display name of the LOV is replaced by the field
department_name from the data model. When the user displays the Parameter Form, the department name will display in the list.
<rw:field>: This element accesses each element of the
<rw:foreach>: This element iterates through the results based on the
g_department_name group in the data model.
src: This parameter is used in both
<rw:field> elements. For the
src must match the group name of the data model (
G_department_name). For the
<rw: field> element, it must match the field that is referred (
id: This parameter can be any value, but it must be unique. This parameter also applies to both
For more infomation on these JSP elements and their parameters, refer to the Oracle Reports online Help.
By making these modifications to the code, we have replaced the return value attribute of the LOV with the field
department_id, based on the data model we created in the previous section. If we now choose a department name from the list of values, its related department ID is returned. Note that the return value is not displayed.
Now that we have modified and examined our Web source, let us view the Parameter Form in a Web browser.
Click the Run Web Layout button in the toolbar.
If Netscape 7.0 is your default browser, and the browser does not display, set the registry key
HKEY_CURRENT_USERS\Software\Oracle\Toolkit\Tkbrowser to the default browser location. Ensure that the
BrowserName and the
BrowserPath keys reflect the correct values. For example:
The Parameter Form displays in your Web browser, and should look like the following:
In the modified Parameter Form, notice how the list of values for the Department has changed from "a" to "Accounting."
Click the Department list and notice how the list is now populated with department names. Although you can click Run Report, nothing will happen because we have not yet defined an action for it.