3 How to Create a Parameterized Report

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 entering an employee ID in the Search Employee field, or by making a selection from two select lists. 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 an Application

First, you need to create an application using the Create Application Wizard.

To create an application using the Create Application Wizard:

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

   The Application Builder home page appears.

2. Click Create.

3. Select Create Application and click Next.

4. For Name, specify the following:

   1. Name - Enter Parameterized Report.

   2. Application - Accept the default.

   3. Create Application - Select From scratch.

   4. Schema - Select the schema where you installed the OEHR sample objects.

   5. Click Next.

      Next, add a blank page.

5. Under Add Page, specify the following:

   1. Select Page Type - Select Blank.

   2. Page Name - Enter Employees.

   3. Click Add Page.

      The new page appears in the list at the top of the page.

   4. Click Next.

6. For Tabs, accept the default, One Level of Tabs, and click Next.

7. For Copy Shared Components from Another Application, accept the default, No, and click Next.

8. For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and click Next.

9. For User Interface, select Theme 18 and click Next.

   A theme is collection of templates that define the layout and style of an application. You can change a theme at any time.

10. Review your selections and click Create.

    The Application home page appears.

Creating Regions

Next, you need to create regions. A region is an area on a page that serves as a container for content. For this exercise you need to create two regions: a Search region to contain search criteria items and a Query region that displays the resulting report.

Topics in this section include:

• Create a Search Region

• Create a Query Region

Adding Form Items

An item is part of an HTML region. An item can be a text field, text area, password, select list, check box, and so on. The previous SQL query references the following items: P1_NAME, P1_DEPT, P1_MGR, and P1_TEXT. Next, you need to create these items.

Topics in this section include:

• Create a Search Employee Text Field

• Create a Hidden Text Field

• Create Department and Manager Select Lists

Adding a Button to Submit the Page

Why do you need to submit the page? Once the user enters search criteria, the page needs to be submitted so that the query will be rerun against that criteria. To submit the page, you add a button.

To add a button to submit the page:

1. Under Buttons, click the Create icon.

2. For Button Region, select Search 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 P1_GO.

5. Accept the remaining defaults and click Create Button.

   The Page Definition for page 1 appears.

Adding an Onload Process

Next, you create a process that sets the value for the hidden item P1_TEXT. The value of P1_TEXT determines the region title that displays. By adding this process, the region title displays as:

Employees in department_name

If you choose to not add this process, the region header simply displays as Employees.

To add an onload process:

1. Under Page Processing, Processes, click the Create icon.

2. For Process Type, select PL/SQL and click Next.

3. For Process Attributes:

   1. Name - Enter get region title info.

   2. Accept the remaining defaults and click Next.

4. For Process, enter the following SQL query:

   DECLARE
      l_dept varchar2(100);
      l_mgr varchar2(100);
   BEGIN
   :P1_TEXT := null;
   
   IF :P1_DEPT != '%null%' 
      THEN SELECT department_name 
             INTO l_dept 
             FROM oehr_departments 
            WHERE department_id = :P1_DEPT;
           :P1_TEXT := :P1_TEXT || ' in Department ' || l_dept;
   END IF;
   
   IF :P1_MGR != '%null%'
      THEN SELECT first_name || ' ' || last_name 
             INTO l_mgr 
             FROM oehr_employees 
            WHERE employee_id = :P1_MGR;
           :P1_TEXT := :P1_TEXT ||' reporting to ' || l_mgr;
   END IF;
   END;
   
   

5. Click Create Process.

The Page Definition for page 1 appears.

Running 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 user name and password, enter your workspace user name and password and click Login. See "About Application Authentication".

3. When the Employees page appears, make a selection from the Department or Manager lists and click Go.

An Employees report appears as shown in Figure 3-4.

Figure 3-4 Form Results Being Populated from Select Lists

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