How-To Document

Based on Oracle Application Express (formerly called HTML DB) version 1.6

After completing this How To, you should be able to:

• Launch Oracle Reports paper reports from Oracle HTML DB
• Build interactive parameter screens for Oracle Reports using Oracle HTML DB

Introduction

Oracle Reports is a professional reporting tool capable of creating rich formatted output, ideal for paper-based layouts.

This document describes how to use HTML DB to create interactive web-based parameter entry screens that can be used to dynamically invoke Oracle Reports. This example will show how to create dynamic list of values, branching, validations, and more.

Software Requirements

• Oracle HTML DB 1.6
• Oracle Reports Services (available as a component of Oracle Application Server Enterprise Edition or Oracle Business Intelligence)
• Oracle Reports Developer (available as a component of Oracle Developer Suite)

Create and Deploy the Oracle Reports Report

You must first create a report using the Oracle Reports Developer tool and then deploy it to the Application Server Reports Services. Useful sources of information are the Getting Starting Guide and the Publishing Reports manual.

For this example, we will use an example report from the Building Reports manual. The example well use is the Invoice Example, but you can work with any other report. This example report as supplied does not use any parameters. Well add 2 new parameters to this report in order to illustrate how to make parameter screens.

To create and deploy the Oracle Reports report:

1. Download the invoice example .rdf from the samples .zip. The invoice example is located in the invoice\invoice.rdf file.
2. This report uses the OE sample schema. This schema is locked by default. You may need to ask your DBA to unlock this schema and reset the password. You will also need to use Oracle HTML DB against the OE schema, so you might want to ask for those privileges as well.
3. Open the invoice.rdf report in Oracle Reports Developer, connect as OE, and test the paper design using the live previewer.

   If you get an error about uncompiled code, you can compile the report by selecting Compile > All ... from the Program menu. The report will look like this:

   

4. Using the Object Navigator, open the Property Inspector for the Q_ORDER Query:

   

5. Click on the ... button beside the SQL Query Statement to edit the SQL Statement, and add a new where clause referencing two new parameters to the query:
   Copy

   
   
                               
   and c1.cust_address.state_province = :p_state
   and (c1.customer_id = :p_customerid or :p_customerid is null)
   
   

   The newly typed text is shown in lowercase letters:

   

6. Click on OK - you should receive a prompt about how the report now has 2 bind variable parameters.
7. Run the report in the paper design previewer again. This time you will be asked to enter a value for the P_STATE parameter. Enter " MI" without the quotes for Michigan, or " IN" for Indiana. Leave the P_CUSTOMERID field empty.
8. Save the report as invoice2.rdf.
9. Deploy the report to the Oracle Application Server Reports Services. Your local administrators may have specific steps to deploying a report, but here are some steps that work with a default setup.
   1. Copy the invoice2.rdf file to $ORACLE_HOME/reports/samples/demo, where $ORACLE_HOME is the oracle home of Reports Services. If using FTP to copy, be sure to transfer the .rdf in binary mode.
10. Test the report. Here is a sample URL to use in your web browser:
    
    Copy

    
    
    http://hostname:port/reports/rwservlet?module=invoice2.rdf&userid=oe/oe@tnsalias&destype=cache&desformat=pdf&p_state=IN
    
    

    Where hostname, port are where Reports Services is running and tnsalias is the TNS alias of the database where the OE schema resides.

    Note: that this example passes the userid parameter on the URL. One can take advantage of the Reports Services $ORACLE_HOME/reports/conf/cgicmd.dat file to hide sensitive parameters like userid. The Publishing Reports to the Web manual discusses this feature.

Create the Oracle HTML DB Application

We are now ready to create parameter entry screens in HTML DB. You must first create an HTML DB application that will own the parameter screens of our reporting system.

To create an Application:

1. Login to HTML DB as a user who privileges to use the OE schema.
2. Click on Create Application.
3. In the Create Application Wizard:
   1. For the Creation Method, select From Scratch and click Next.
   2. Enter a Name for your Application and click Next.
   3. Make sure that the Default Parsing Schema is set to OE. If OE is not available in the drop-down list, ask your Oracle HTML DB Administrator to associate the OE schema with your Workspace.
   4. Select 2 for Number of Pages, and click Next.
   5. Select One Level of Tabs and click Next.
   6. For Page Names, enter Home and Invoice and click Next.
   7. For Tab Names, simply click Next.
   8. Select any Theme and click Next.
   9. Click Create Application.

Create the Home Page

Now that you have created the application, you will create a home starter page for our reporting system.

To create a home page:

1. Under Pages, click on Home.
2. Click on the " +" icon under Regions.
3. Select HTML and click Next.
4. Select HTML again and click Next.
5. For the Title, enter Welcome and click Next.
6. For the HTML Text Region Source, enter " Welcome to the Oracle HTML DB & Oracle Reports How-To" and click Create Region.

Create the Utility Objects

Next, create some utility objects like static substitution strings and list of values.

1. Click on the Application Builder tab.
2. Click on Edit Attributes.
3. Scroll down to the Static Substitution Strings section and enter the following:

   Substitution String: REPORTS_URL

   Substitution Value: http://hostname:port/reports/rwservlet?userid=oe/oe@tnsalias&

   Be sure to replace hostname, port, and tnsalias to the same values which you used when testing the invoice2.rdf report against Reports Services when deploying. As discussed earlier, you can use the Oracle Reports cgicmd.dat file to avoid passing sensitive parameters like userid via a URL.

   

4. Scroll to the top and click Apply Changes.
5. Click on Shared Components.
6. Click on List of Values.
7. Click on Create.
8. Select From Scratch and click on Next.
9. Enter DESFORMAT_LOV as the Name, select Static as the Type, and click Next.
10. Enter the following data:

    Facet Name	Display Value	Return Value
    1	PDF	PDF
    2	HTML	HTMLCSS

    

11. Click Create Static LOV.

Create the Parameter Entry Page

Next, we need to create a page to accept parameters for our Oracle Report.

To create a Parameter Page:

1. Click on the Application Builder tab.
2. Click on the Invoices page.
3. Click on the " +" icon under Regions.
4. Select HTML and click Next.
5. Select HTML again and click Next.
6. For the Title, enter Invoice Report and click Next.
7. Click Create Region.

Create the Parameter Items

Now you will items to allow users to select parameters. You will create drop-down lists for the STATE and CUSTOMER parameters of the report.

1. Click on the " +" icon under Items.
2. Select Select List and click Next.
3. Select Select List with Submit and click Next.
4. Enter P2_STATE as the Name and click Next.
5. For Display Null Option, select Yes.
6. Enter this SQL statement for the List of Values Query and click Next:
   Copy

   
   
   select distinct c.cust_address.state_province display1, c.cust_address.state_province return1
   from customers c
   where exists (select 'x' from orders o where o.customer_id = c.customer_id and sales_rep_id is not null)
   order by 1
   
   

7. Take the defaults for Item Attributes and click Next.
8. Click on Create Item.
9. Click on the " +" icon under Items.
10. Select Select List and click Next.
11. Select Select List and click Next.
12. Enter P2_CUSTOMER as the Name and click Next.
13. For Display Null Option, select Yes.
14. Enter this SQL statement for the List of Values Query and click Next:
    Copy

    
    
    select c.cust_last_name||','||c.cust_first_name display1, c.customer_id return1
    from customers c
    where exists (select 'x' from orders o where o.customer_id = c.customer_id and sales_rep_id is not null)
    and c.cust_address.state_province = :p2_state
    order by 1
    
    

15. Under the Items region, click on P2_CUSTOMER.
16. Scroll down to Conditional Display.
17. For Condition Type, select Value of Item in Expression 1 is NOT NULL.
18. For Expression 1, enter P2_STATE.

    

19. Scroll to the top and click Apply Changes.
20. Click on the " +" icon under Items.
21. Select Select List and click Next.
22. Select Select List and click Next.
23. Enter P2_DESFORMAT as the Name and click Next.
24. Select DESFORMAT_LOV for the Named LOV.
25. For Display Null Option, select No and click Next.
26. For Label, enter Report Format and click Next.
27. Click Create Item.

Create Validation and Branches

Now you will items to allow users to select parameters. You will create drop-down lists for the STATE and CUSTOMER parameters of the report.

1. Click on the " +" icon under Branches.
2. Take the defaults and click Next.
3. For Page, enter 2 and click Next.
4. Click on Create Branch.
5. Click on the " +" icon under Validations.
6. Select Item Level Validation and click Next.
7. Select P2_STATE and click Next.
8. Select Item Not Null and click Next.
9. Take the defaults for Validation Attributes and click Next.
10. Enter "Please select a State" and click Next.
11. Click Create.

Create the Run Report Button

Next, create a button to run the report and a new branch to launch Orale Reports when the button is clicked.

1. Click on the " +" icon under Buttons.
2. Take the defaults and click Next.
3. Select Invoice Report as the Region and click Next.
4. Select Create a button displayed among this regions items and click Next.
5. Enter P2_GO as the Button Name, and click Create Button.
6. Click on the " +" icon under Branches.
7. Take the defaults and click Next.
8. Select URL for Target is a, and for the URL Targer, enter:
   Copy

   
   
   &REPORTS_URL.&module=invoice2.rdf&destype=cache&desformat=&P2_DESFORMAT.&p_state=&P2_STATE.&p_customerid=&P2_CUSTOMER.
   
   

9. Click Next.
10. For the Sequence, enter 5.
11. For When Button Pressed, select P2_GO and click Create Branch.

    

Advanced Techniques

1) Creating Additional Parameter Pages for Additional Reports

A typical reporting system will have multiple reports. One approach would be to create each report's parameter page from scratch as described above. Another approach is to use HTML DB's ability to copy a page. This approach copies over the regions, items, branches, and validations intelligently.

2) Creating a Data Preview Reporting Region with Oracle HTML DB

HTML DB makes it very easy to add a live "preview" region to your parameter page. To do this, add a new region and select the Report (SQL Report) region type. In this region you can use SQL that gives the user a preview of the reports data. In many cases, this will be as easy as copying the SQL from the .rdf file using Oracle Reports Developer. Once pasted into HTML DB, you can refer to the HTML DB parameters by their HTML DB item names.

3) Passing Parameters via Tables

The example above passes parameters like P_STATE and P_CUSTOMERID to the report via a URL. In some scenarios, you might not want to pass parameters this way (for instance, you might be worried about mischievous users who try to edit the parameter values in the URL address field of their browser).

An alternative approach is to use a database table to store the report parameters and simply pass an identifier of the table row which holds the parameters to the report submission. Some of HTML DB's wizards can be helpful in this situation. Here's one approach:

1. To build the parameter table for a report, use HTML DB's Data Workshop's ability to Import Spreadsheet Data. Use Excel and build a simple spreadsheet. Put the names of the report parameters in the first row as the column headings. Put some sample values in the second row. In the example below, I have added some additional tracking columns like REPORT_STATUS, REPORT_SUBMIT, etc. Other typical columns/parameters to consider would be ones like DESFORMAT, DESTYPE, and DESNAME.

   

   You can let the HTML DB wizard create the primary key column (ID) and new sequence/trigger to maintain it.

2. To have your parameter screen store the parameters in your new table, you will want to:
   1. Create a hidden item called P2_ID
   2. Create a Process (conditional on the Submit button) with the following PL/SQL:
      Copy

      
      
      
      insert into params_invoice (REPORT_STATUS,REPORT_SUBMIT,
          P_STATE,P_CUSTOMERID) values
      ('NEW',sysdate,:p2_state,:p2_customer) returning id into :p2_id;
      commit;
      
      

      

   3. Modify the Oracle Reports URL in the Branching item to pass the primary key of the row where the parameters are stored. For instance, it could look like:
      Copy

      
      
      &REPORTS_URL.&module=invoice2.rdf&destype=cache&desformat=&P2_DESFORMAT.&p_param_id=&P2_ID.
      
      

3. Modify the Oracle Reports .rdf file by:
   1. Creating a P_PARAM_ID parameter.
   2. Adding an After Parameter Form Trigger:

      

   3. Adding a After Report Trigger (this trigger is not required, but as shown below is used to record when a report finishes in the parameter table):