41 Building a Simple Parameter Form for a JSP-based Web Report

In this chapter, you will learn about JSP-based Web reports that include a simple Parameter Form. To build a Parameter Form for a paper report, refer to the Oracle Reports online Help. By following the steps in this chapter, you can generate the report output shown in Figure 41-1 and Figure 41-2.

Figure 41-1 JSP Parameter Form

Description of "Figure 41-1 JSP Parameter Form"

Note:

The JSP Parameter Form in this image enables the user to choose from a list of departments, connect to a database, then run a report based on the selected parameters.

Figure 41-2 JSP-based Web report based on a user parameter

Description of "Figure 41-2 JSP-based Web report based on a user parameter"

Note:

The JSP-based Web report in this image displays a graph and a tabular report based on the department selected in the JSP Parameter Form. To see how this report was built, refer to the Oracle Reports Tutorial.

Concepts

In this example, you will build an HTML Parameter Form using JavaServer Pages (JSPs) for a JSP-based Web report. For conceptual information about JSPs and Parameter Forms for Web reports, refer to Section 2.2.1, "About JavaServer Pages (JSPs) and servlets" and Section 1.9.4, "About Parameter Forms for Web reports".
This example shows you how to build a very simple Parameter Form. If this Parameter Form does not suit your needs, refer to a more advanced example on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (http://www.oracle.com/technology/products/reports/index.html), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Examples, and click Search. In the list of examples that displays, click Building a JSP-based Web Report with a Parameter Form.

Example Scenario

In this example, you have an existing JSP-based Web report that shows a bar graph of employee salaries per department, as well as a tabular report that shows the employee details. Your customers want to see the employee information for a specific department at runtime, so that they do not have to read the data for all departments. The steps in this example show you how to add a JSP Parameter Form to this JSP-based Web report.

This example uses the resulting report from the Oracle Reports Tutorial. If you want to learn how to build the Web report that we use in this example, follow the steps in the tutorial.

As you build this example report, you will:

Create a Parameter Form in HTML using a text or HTML editor.
Modify the HTML Parameter Form in Reports Builder using the Data Model and Web Source views, and save as a JSP.
Set up the target report using the Data Model view to modify the query for the target report to accept user parameters.
Deploy the JSP Parameter Form and the target report.

To see a sample JSP-based Web report that includes a simple Parameter Form, open the examples folder named SimpleJSPPF, then open results\emprev_param.jsp. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 41-1.

Table 41-1 Example report files

File	Description
`Examples\SimpleJSPPF\source\paramform.html`	An example HTML Parameter Form, which contains an example List of Values, a field, and a button.
`Examples\SimpleJSPPF\results\paramform.jsp`	This JSP-based Web report contains the modifications to the Parameter Form you will make in Reports Builder.
`Examples\SimpleJSPPF\results\emprev_final.jsp`	The source Web report that will become the target report for the Parameter Form.
`Examples\SimpleJSPPF\results\emprev_param.jsp`	The final JSP-based Web report with a JSP Parameter Form.

Note:

If you completed the exercises in the Oracle Reports Tutorial, you can also use the emprevb_your_initials.jsp file you created at the end of Chapter 6.

41.1 Prerequisites for this example

To build the example in this chapter, you must have the example files we have provided (see "Example Scenario", above), as well as access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.

41.2 Create a Parameter Form in HTML

The steps in this section will show you how to build a simple Parameter Form using plain HTML. You will then modify this HTML Parameter Form in Reports Builder so that you can call the Parameter Form from your JSP-based Web report.

If you do not want to create your own HTML file, you can open the sample HTML file we have provided in the Source directory, called paramform.html, then view the source code.

To create a simple Parameter Form in HTML:

In a text editor or HTML editor, create an HTML page that contains a form. The form should contain a list of values, a field, and a button. The code for this form can look something like the following:

<form name="form1" method="post" action="">
    <h2>Parameter Form</h2>
    <p>Choose a department from the list, then click the <b>Run Report</b>
button to show salary details for each employee in that department.</p>
    <select name="p_department" size="1">
      <option value="1">a</option>
    </select>
    <br>
    <input type="text" name="userid" value="hr/hr@db-connect">
	<br>
    <input type="submit" name="Submit" value="Run Report">
</form>

Note:

Although you can use the above code, you will need to change the userid value to reflect the connection information for your data source. You can also copy and paste the HTML code from the provided file, called simplejsppf_code.txt in the SimpleJSPPF/scripts directory, then modify it in a text or HTML editor.

Save the HTML file as paramform_your_initials.html. When you display this HTML file in a Web browser, it should look similar to the following image:

Figure 41-3 Sample HTML Parameter Form

Description of "Figure 41-3 Sample HTML Parameter Form"

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

41.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:

In Reports Builder, open the HTML file you created, paramform_your_initials.html.
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 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.

Click Connect, then type the connection information for the Human Resources sample schema (HR).
Click OK. Your data model should look like this:

Figure 41-4 Data Model view of the Parameter Form

Description of "Figure 41-4 Data Model view of the Parameter Form"
Save the report as paramform_your_initials.jsp.

41.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:

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

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.

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.

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

Click the Run Web Layout button in the toolbar.

Notes:

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.
If Run Web Layout fails with an error related to the major.minor version, you will need to update the environment variables CLASSPATH or REPORTS_CLASSPATH, as follows: remove the 9.0.x ORACLE_HOME specification in the oc4j.jar file path (the oc4j.jar file path should specify only the 10g Release 2 (10.1.2) ORACLE_HOME).

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

Figure 41-5 Parameter form with values

Description of "Figure 41-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."
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.

41.4 Set up the target report

Now that you have set up the parameters, the next step is to set up the target report to accept the parameters. Then, we will define the action for your Parameter Form. When a user clicks Run Report, the target report will be run based on the Department and User ID parameters.

The target report we use in this section is the sample report for the Oracle Reports Tutorial. If you completed the exercises in the Oracle Reports Tutorial and created emprevb.jsp, you can use that report in this section. Otherwise, you can use the example file we have provided, called emprev_final.jsp. We will not show the steps to build this report in this section.

For more information on building the sample JSP-based Web report, refer to the Oracle Reports Tutorial.

To set up the target report:

In Reports Builder, open the file we have provided called emprev_final.jsp.
In the Object Navigator, double-click the icon next to the Data Model node.
In the Data Model view that displays, double-click the query (Q_1) to display the SQL Query Statement dialog box.

Find the final line of the code:

WHERE (EMPLOYEES.MANAGER_ID = EMPLOYEES_A1.EMPLOYEE_ID) 
AND EMPLOYEES.DEPARTMENT_ID = 100

Change the second WHERE clause so that the last line looks like this:
```
WHERE (EMPLOYEES.MANAGER_ID = EMPLOYEES_A1.EMPLOYEE_ID) 
AND EMPLOYEES.DEPARTMENT_ID = :P_DEPARTMENT
```
Tip:
The code we have changed is in bold text.
Click OK.
A note displays that indicates a bind parameter has been created. Click OK.
Save your report as emprev_param_your_initials.jsp.

41.5 Deploy the JSP Parameter Form and the target report

To deploy the JSP Parameter Form and the target JSP-based Web report, you must copy paramform_your_initials.jsp and emprev_final_your_initials.jsp to the deployment directory of your Application Server. For testing purposes, however, you can use the OC4J instance shipped with the Oracle Developer Suite. Once you have placed the target report in the desired directory, you can then modify the Parameter Form to point to the report location.

For more information on deploying a JSP-based Web report, refer to the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page ().

To set up and deploy the JSP Parameter Form and the target report:

Copy the JSP Parameter Form and the Web report (paramform_your_initials.jsp and emprev_param_your_initials.jsp) into the following directory:
```
ORACLE_HOME\reports\j2ee\reports_ids\web
```
Note:
ORACLE_HOME is the directory in which Reports Builder is installed.
In Reports Builder, open the file: ORACLE_HOME\reports\j2ee\reports_ids\web\paramform_your_initials.jsp.

In the Web Source view, modify the action for the form so that when the user clicks Run Report, the emprev_param_your_initials.jsp report executes based on the selected parameters. The code should look like this:

<form name="form1" method="post" action="/reports/rwservlet">
    <input type="hidden" name="report" value="reportname.rdf">
    <input type="hidden" name="destype" value="destype">
    <input type="hidden" name="desformat" value="desformat">
    <input type="hidden" name="userid" value="db_connect_string">
    <input type="submit" name="Submit" value="Submit">
</form>

Notes:

In the above code, the action attribute assumes that the report is located in the directory we specified in the above steps.
The Parameter Form developed in this chapter can also be used to execute a paper report instead of a Web report. To do this, use the following code:
```
<form name="form1" method="post"
action="/reports/rwservlet?report=report
_name.jsp&destype=destype&desformat=desformat">
```
For more information on deploying JSP-based Web reports and paper reports, refer to the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://www.oracle.com/technology/documentation/reports.html).

Save your report.
Start your OC4J instance.
- On Windows, you can do either of the following:
  - From the Start menu, choose Programs > Oracle Developer Suite - ORACLE_HOME > Reports Developer > Start OC4J Instance
  - From the command line, execute:
```
IDS_HOME\j2ee\DevSuite\startinst.bat
```
- On UNIX, start the shell script:
```
 IDS_HOME/j2ee/DevSuite/startinst.sh
```
Tip:
Once the containers for J2EE are initialized, the OC4J instance has been started.

In a Web browser, type the URL for the Parameter Form:

http://computer name:port/reports/paramform_your_initials.jsp?userid=userid/password@database name

In our example, we would use:

http://mycomputer-pc:8888/reports/paramform_your_initials.jsp?userid=hr/hr@orcl

Note:

The connect string you type in the URL is for the database you used to create the data model in Section 41.3.1, "Create a data model manually for the Parameter Form". For the purposes of this example, we have used plain text to pass the connect string. You can find more information about security in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://www.oracle.com/technology/documentation/reports.html).

If the Single Sign-On login page displays, enter your Single Sign-On user name and password. For more information about OracleAS Single Sign-On, see the Oracle Application Server Reports Services Publishing Reports to the Web manual.
When the Parameter Form displays, choose a department from the list. For example, choose Finance.
In the Login ID field, type the connect string for the database schema that the emprev_param_your_initials.jsp report uses. For example, hr/hr@orcl:

Figure 41-6 JSP Parameter Form with selections

Description of "Figure 41-6 JSP Parameter Form with selections"
Click Run Report.

The employee salary report displays with a graph at the top, and should look like this:

Figure 41-7 Sample employee report based on user parameters

Description of "Figure 41-7 Sample employee report based on user parameters"

Note:

If you are not sure whether your report appears as it should, try using the files we have provided, paramform.jsp and emprev_param.jsp and follow all the steps in this section to deploy the Parameter Form and the Web report.

41.6 Summary

Congratulations! You have created a JSP Parameter Form for an existing Web report. You now know how to:

create a simple Parameter Form in HTML.
add a data model to a simple Parameter Form in Reports Builder.
modify an existing JSP-based Web report to accept user parameters.
test and deploy a JSP-based Web report with a JSP Parameter Form using OC4J.

For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".