2 How to Create a Parameterized Report

Sample Report Utilizing a Form Input

Figure 2-1 is an example of a form in which the report results are based on user input. In this example, this user populates the form by making a selection from the Show list. The easiest way to create this type of report in Oracle HTML DB is to define a report region based on a SQL query.

Figure 2-1 Sample Report

Description of the illustration rpt_input_eg.gif

About Sample Application

Oracle HTML DB installs with a number of demonstration applications. In this exercise you will create a tabular form within the demonstration application, Sample Application.

To see if Sample Application is installed:

1. Log in to Oracle HTML DB.

2. From the Workspace Administration list, select Review Demonstration Applications.

3. Locate Sample Application and check the Status column:

   1. If the Status column displays Installed, return to the Workspace home page.

   2. If the Status column displays Not Installed, select Install in the Action column.

   3. Follow the on-screen instructions.

Creating a New Page

First, you create a new blank page and within Sample Application.

To create a new page:

1. Navigate to the Workspace home page.

2. From the Available Applications list, select Sample Application.

3. Click the Create Page button.

4. On Create new Page, select Blank Page and click Next.

5. For Page, enter 700 and click Next.

6. For Name, enter Ordered Products and click Next.

7. On Identify Tabs, accept the default No and click Next.

8. Review your selections and click Finish.

9. On the Success Page, click Edit Page.

   The Page Definition for page 700 appears.

Creating the Query Region

Next, you need to create a report.

To create a the query region:

1. Under Regions, click the Create icon.

2. On Identify Region Type, select Report and click Next.

3. For Report Implementation, select SQL Report and click Next.

4. On Identify Region Attributes:

   1. For Title, enter Ordered Products.

   2. Accept the remaining default values and click Next.

5. Enter following SQL query:

   SELECT p.category,
          p.product_name,
          i.quantity  FROM demo_product_info p,
          demo_order_items i
   WHERE p.product_id = i.product_id
     AND ( p.category = :P700_SHOW or :P700_SHOW = 'ALL' )
   
   

6. Click Create Region.

Adding an Item

The previous SQL query references an item named P700_SHOW.

To create the select list P700_SHOW:

1. Under Items, click the Create icon.

2. On Choose Item Type, select Select List and click Next.

3. For Pick Select List Control Type, accept the default of Select List and click Next.

4. For Item Name, enter P700_SHOW and click Next.

5. On Identify List of Values:

   1. For Named LOV, select CATEGORIES.

   2. For Null Text, enter:

      - All Categories -
      
      

   3. For Null Value, enter:

      ALL
      
      

   4. Click Next.

6. Identify Item Attributes, accept the defaults and click Next.

7. Click Create Item.

Adding a Button to Submit the Page

For the report to be driven by the Product Category select list (the form input), you need to submit the page. To accomplish this, you need add a button.

To add a button to submit the page:

1. Under Buttons, click the Create icon.

2. For Select a region, select Ordered Products and click Next.

3. On Identify Button Position, select Create a button displayed among this region's items and click Next.

4. For Button Name, enter P700_GO.

5. Click Create Button.

Run the Page

To run the page:

1. Click Run Page.

2. If prompted to enter a username and password:

   1. For User Name, enter either demo or admin.

   2. For Password, enter the name of the current workspace using all lowercase letters.

   3. Click Login.

   4. Navigate to page 700:

      • Select Edit Page from the Developer Toolbar.

      • In Page, enter 700 and click Go.

      • Click the Run Page icon.

3. When the Order Products page appears, select Computer from the Show menu and click Go.

As shown in Figure 2-2, notice that making a selection from the Show menu populates the form.

Figure 2-2 Form Results Being Populated from a Select List

Description of the illustration rpt_frm_input.gif