42.3 Modify the HTML Parameter Form in Reports Builder

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.

42.3.1 Create a data model manually for the Parameter Form

The steps in this section will show you how to create a simple data model for the Parameter Form.

To create a data model:

  1. In Reports Builder, open the HTML file you created, paramform_your_initials.html.

  2. In the Object Navigator, double-click the icon next to the Data Model node to display the Data Model view.

  3. 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.

  4. In the SQL Query Statement field, enter the following SELECT statement:

    select department_name, department_id
    from departments
    order by department_name
    

    Note:

    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.

  5. Click Connect, then type the connection information for the Human Resources sample schema (HR).

  6. Click OK. Your data model should look like this:

    Figure 42-4 Data Model view of the Parameter Form

    Description of Figure 42-4 follows
    Description of "Figure 42-4 Data Model view of the Parameter Form"

  7. Save the report as paramform_your_initials.jsp.

42.3.2 Create a dynamic LOV in the Parameter Form

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.

To modify the LOV in the Parameter Form in Reports Builder:

  1. Click the Web Source button in the toolbar to display the Web Source view.

  2. In the Web Source view, look for the following code:

    <select name="p_department" size="1">
    <option value="1">a</option>
    </select>
    

    Note:

    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.

  3. 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>
    

    Note:

    You can either type the code manually or copy and paste it from the provided file called simplejsppf_code.txt.

  4. Save your report.

Examine the JSP elements in the code:

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 g_department_name group.

  • <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:foreach> and <rw:field> elements. For the <rw:foreach> element, 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 (department_name).

  • id: This parameter can be any value, but it must be unique. This parameter also applies to both <rw:foreach> and <rw:field> elements.

Note:

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.

42.3.3 Run the Parameter Form report to the Web

Now that we have modified and examined our Web source, let us view the Parameter Form in a Web browser.

  1. Click the Run Web Layout button in the toolbar.

    Note:

    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: BrowserName=Netscape 7; BrowserPath=C:\Program Files\Netscape\Netscape\Netscp.exe.

  2. The Parameter Form displays in your Web browser, and should look like the following:

    Figure 42-5 Parameter form with values

    Description of Figure 42-5 follows
    Description of "Figure 42-5 Parameter form with values"

    Note:

    In the modified Parameter Form, notice how the list of values for the Department has changed from "a" to "Accounting."

  3. 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.