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, otherwise known as a parameterized report. In this exercise, you create a report region based on a SQL query that references the value of form items within the application.
Before you begin, you need to import and install the OEHR Sample Objects application in order to access the necessary sample database objects. See "About Loading Sample Objects".
This section contains the following topics:
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.
Creating an Application
First, you need to create an application using the Create Application Wizard.
To create an application using the Create Application Wizard:
-
On the Workspace home page, click the Application Builder icon.
The Application Builder home page appears.
-
Click Create.
-
Select Create Application and click Next.
-
For Name, specify the following:
-
Name - Enter
Parameterized Report
. -
Application - Accept the default.
-
Create Application - Select From scratch.
-
Schema - Select the schema where you installed the OEHR sample objects.
-
Click Next.
Next, add a blank page.
-
-
Under Add Page, specify the following:
-
Select Page Type - Select Blank.
-
Page Name - Enter
Employees
. -
Click Add Page.
The new page appears in the list at the top of the page.
-
Click Next.
-
-
For Tabs, accept the default, One Level of Tabs, and click Next.
-
For Copy Shared Components from Another Application, accept the default, No, and click Next.
-
For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and click Next.
-
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.
-
Review your selections and click Create.
The Application home page appears.
See Also:
"Managing Themes" in Oracle Application Express Application Builder User's GuideCreating 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
To create a search region:
-
On the Application home page, click 1 - Employees.
The Page Definition for page 1 appears.
-
Under Regions, click the Create icon as shown in Figure 3-2.
-
For Region:
-
Identify the type of region to add to this page - Accept the default, HTML, and click Next.
-
Select the type of HTML region container you wish to create - Accept the default, HTML, and click Next.
-
-
For Display Attributes, specify the following:
-
For Title - Enter
Search
. -
Accept the remaining default values.
-
Click Next.
-
-
Click Create Region.
The Page Definition for page 1 appears. A confirmation message displays at the top of the page: Region created.
Create a Query Region
Next, you need to create a report region based on a SQL query.
See Also:
"Understanding Regions" in Oracle Application Express Application Builder User's GuideTo create a report region based on a SQL query:
-
Under Regions, click the Create icon.
-
For Region, select Report and click Next.
-
For Report Implementation, select SQL Report and click Next.
-
For Display Attributes:
-
Title - Enter the following, making sure to include the trailing period:
Employees &P1_TEXT.
&P1_TEXT
is a substitution string that will determine region title. You create this item in the next section. -
Accept the remaining default values and click Next.
-
-
Enter the following SQL query:
SELECT "OEHR_EMPLOYEES"."EMPLOYEE_ID" "EMPLOYEE_ID", "OEHR_EMPLOYEES"."FIRST_NAME" "FIRST_NAME", "OEHR_EMPLOYEES"."LAST_NAME" "LAST_NAME", "OEHR_EMPLOYEES"."EMAIL" "EMAIL", "OEHR_EMPLOYEES"."PHONE_NUMBER" "PHONE_NUMBER", "OEHR_EMPLOYEES"."HIRE_DATE" "HIRE_DATE", "OEHR_EMPLOYEES"."JOB_ID" "JOB_ID", "OEHR_EMPLOYEES"."SALARY" "SALARY", "OEHR_EMPLOYEES"."COMMISSION_PCT" "COMMISSION_PCT", "OEHR_EMPLOYEES"."MANAGER_ID" "MANAGER_ID", "OEHR_EMPLOYEES"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "#OWNER#"."OEHR_EMPLOYEES" "OEHR_EMPLOYEES" WHERE (lower(first_name) like '%' || lower(:P1_NAME) || '%' OR lower(last_name) like '%' || lower(:P1_NAME) || '%') AND department_id = decode(:P1_DEPT,'%null%',department_id,:P1_DEPT) AND manager_id = decode(:P1_MGR,'%null%',manager_id,:P1_MGR)
The
WHERE
clause forces both the search criteria and value from the database to be lower case. This makes the resulting search case insensitive for first and last names. -
Click Create Region.
The Page Definition for page 1 appears. A confirmation message displays at the top of the page.
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:
See Also:
"Understanding Page-Level Items" in Oracle Application Express Application Builder User's Guide.Create a Search Employee Text Field
To create the text field P1_NAME
:
-
Under Items, click the Create icon.
-
For Item Type, select Text and click Next.
-
For Text Control Display Type, select Text Field and click Next.
-
For Display Position and Name:
-
For Item Name - Enter
P1_NAME
. -
For Sequence - Accept the default.
-
For Region - Select Search.
-
Click Next.
-
-
For Item Attributes:
-
Label - Enter
Search Employee
. -
Accept the defaults.
-
Click Next.
-
-
Accept the defaults and click Next.
-
Click Create Item.
Create a Hidden Text Field
Next, you will create a hidden text field named P1_TEXT
. The value of P1_TEXT
is used as the basis for the region title that displays.
To create the hidden text field P1_TEXT:
-
Under Items, click the Create icon.
-
For Item Type, select Hidden and click Next.
-
For Display Position and Name:
-
For Item Name - Enter
P1_TEXT
. -
For Sequence - Accept the default.
-
For Region - Select Employees &P1_TEXT.
-
Click Next.
-
-
Accept the remaining defaults and click Next.
-
Click Create Item.
Create Department and Manager Select Lists
Next, you need to create two items named P1_DEPT
and P1_MGR
. These items will display as select lists and be based on two named (or shared) lists of values.
A list of values (LOV) is a static or dynamic set of values used to display a page item. To create these items, you first define two dynamic LOVs and then create the items P1_DEPT
andP1_MGR
.
Topics in this section include:
See Also:
"Creating Lists of Values" in Oracle Application Express Application Builder User's GuideCreate a Named LOV for Department
To create a named LOV for department:
-
Under Lists of Values, click the Create icon.
-
For Source, accept the default, From Scratch, and click Next.
-
For Name and Type:
-
Name - Enter
DEPARTMENT
. -
Type - Select Dynamic.
-
Click Next.
-
-
For Query or Static Values, replace the existing text with:
SELECT department_name,department_id FROM oehr_departments
-
Click Create List of Values.
The Page Definition appears.
Create a Named LOV for Manager
To create a named LOV for manager:
-
Under Lists of Values, click the Create icon.
-
For Source, accept the default, From Scratch, and click Next.
-
For Name and Type:
-
Name - Enter
MANAGER
. -
Type - Select Dynamic.
-
Click Next.
-
-
For Query or Static Values, replace the existing text with:
SELECT y.first_name || ' ' || y.last_name d, y.employee_id r FROM oehr_employees y WHERE y.employee_id IN ( SELECT x.manager_id FROM oehr_employees x)
-
Click Create List of Values.
The Page Definition appears.
Create an Item Named P1_DEPT
To create the item named P1_DEPT
:
-
Under Items, click the Create icon.
-
For Item Type, select Select List and click Next.
-
For Select List Control Type, select Select List, and click Next.
-
For Display Position and Name:
-
For Item Name - Enter
P1_DEPT
. -
For Sequence - Accept the default.
-
For Region - Select Search.
-
Click Next.
-
-
For List of Values:
-
Named LOV - Select DEPARTMENT.
-
Null Text - Enter the following:
- All -
-
Accept the remaining defaults and click Next.
-
-
For Item Attributes:
-
Label - Enter
Department
. -
Accept the remaining defaults and click Next.
-
-
Click Create Item.
Create an Item Named P1_MGR
To create the item named P1_MGR
:
-
Under Items, click the Create icon.
-
For Item Type, select Select List and click Next.
-
For Select List Control Type, select Select List and click Next.
-
For Display Position and Name:
-
For Item Name - Enter
P1_MGR
. -
For Sequence - Accept the default.
-
For Region - Select Search.
-
Click Next.
-
-
For List of Values:
-
Named LOV - Select MANAGER.
-
Null Text - Enter the following:
- All -
-
Accept the remaining defaults and click Next.
-
-
For Item Attributes:
-
Label - Enter
Manager
. -
Accept the remaining defaults and click Next.
-
-
Click Create Item.
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:
-
Under Buttons, click the Create icon.
-
For Button Region, select Search and click Next.
-
For Button Position, select Create a button displayed among this region's items and click Next.
-
In Button Name, enter
P1_GO
. -
Accept the remaining defaults and click Create Button.
The Page Definition for page 1 appears.
See Also:
"Creating Buttons" in Oracle Application Express Application Builder User's GuideAdding 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:
-
Under Page Processing, Processes, click the Create icon.
-
For Process Type, select PL/SQL and click Next.
-
For Process Attributes:
-
Name - Enter
get region title info
. -
Accept the remaining defaults and click Next.
-
-
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;
-
Click Create Process.
The Page Definition for page 1 appears.
Running the Page
To run the page:
-
Click the Run Page icon in the upper right corner as shown in Figure 3-3.
-
If prompted to enter a user name and password, enter your workspace user name and password and click Login. See "About Application Authentication".
-
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 follows Description of Figure 3-4 follows](http://docs.oracle.com/cd/E10513_01/doc/appdev.310/e10497/img/rpt_frm_input.gif)
Description of "Figure 3-4 Form Results Being Populated from Select Lists"