3 How to Create a Parameterized Report

In an Oracle Application Express application, a report is the formatted result of a SQL query. You can generate reports in three ways:

• Running a built-in wizard

• Defining a report region based on a SQL query

• Creating a report region based on a PL/SQL function returning a SQL query

This tutorial illustrates how to create a report in which the results depend on the form input, or a parameterized report. In this exercise, you create a report region based on a SQL query that references the value of a form item within the application.

For this tutorial, you use one of the demonstration applications, Sample Application, which is installed as part of the Application Builder installation. Verify that it is installed before you begin the tutorial. See "Checking the Sample Application Installation".

This section contains the following topics:

• Sample Report Utilizing a Form Input

• Creating a New Page

• Creating the Query Region

• Adding an Item

• Adding a Button to Submit the Page

Sample Report Utilizing a Form Input

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

Figure 3-1 Sample Report

Description of "Figure 3-1 Sample Report"

Creating a New Page

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

To create a new page:

1. On Workspace home page, click the Application Builder icon.

2. Click the Sample Application icon.

   The Application home page appears.

3. Click the Create Page button.

4. For Page, select Blank Page and click Next.

5. For Page Number, enter 700 and click Next.

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

7. For Tabs, accept the default, No, and click Next.

8. Review your selections and click Finish.

9. On the Success page, click the Edit Page icon.

   The Page Definition for page 700 appears.

Creating the Query Region

Next, you need to create a report.

To create a query region to contain the report:

1. Under Regions, click the Create icon as shown in Figure 3-2.

   Figure 3-2 Create Icon

   
   Description of "Figure 3-2 Create Icon"
   
   

2. Select Report and click Next.

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

4. For Display Attributes:

   1. For Title, enter Ordered Products.

   2. Accept the remaining default values and click Next.

5. Enter the 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.

   The Page Definition for page 700 appears with a confirmation message at the top.

Adding an Item

The previous SQL query references an item named P700_SHOW. Next, you need to create this item. An item is part of an HTML form. An item can be a text field, text area, password, select list, check box, and so on.

See Also:

"Creating Items" in Oracle Database Application Express User's Guide.

To create the select list P700_SHOW:

1. Under Items, click the Create icon.

2. For Item Type, select Select List and click Next.

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

4. For Item Name, enter P700_SHOW, accept the remaining defaults, and click Next.

5. For List of Values:

   1. For Named LOV, select CATEGORIES.

   2. In Null Text, enter:

      - All Categories -
      
      

   3. For Null Value, enter:

      ALL
      
      

   4. Click Next.

6. 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 to add a button.

To add a button to submit the page:

1. Under Buttons, click the Create icon.

2. For Button Region, select Ordered Products and click Next.

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

4. In Button Name, enter P700_GO.

5. Accept the remaining defaults and click Create Button.

   The Page Definition for page 700 appears.

See Also:

"Creating Buttons" in Oracle Database Application Express User's Guide

Run the Page

When you run the new page you just created, you are prompted to log in to the Sample Application. Sample Application accepts either demo or admin for the username and your workspace name for the password. Therefore, before running the page, scroll down and note your current workspace name. It appears at the bottom of the page.

To run the page:

1. Click the Run Page icon in the upper right corner as shown in Figure 3-3.

   Figure 3-3 Run Page Icon

   
   Description of "Figure 3-3 Run Page Icon"
   
   

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 your workspace in lowercase letters.

   3. Click Login.

      Sample Application appears.

   4. To navigate to page 700 again, click Edit Page 1 on the Developer toolbar at the bottom of the page.

      The Page Definition appears.

   5. In the Page field, enter 700 and click Go.

   6. Click the Run Page icon.

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

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

Figure 3-4 Form Results Being Populated from a Select List

Description of "Figure 3-4 Form Results Being Populated from a Select List"