4 Building Your Application

In this tutorial, you create a Human Resources (HR) application for a fictitious company called AnyCo Corp. This section shows you how to build the application, modify it, and preview it at different points during the development process.

Before you begin this section:

Read "Getting Started with Oracle Application Express".
Ensure that you have installed the database objects as described in "Installing the Sample Objects".

Topics:

About the Application You Build in This Tutorial
Creating the Initial Application
Using an Interactive Report
Modifying the Departments Report
Adding an Employees Report and Form
Editing the Employees Report
Adding a Link on the Home Page to the Employees Report
Adding a Department Column to the Employees Report
Linking a Column Value to Another Page
Adding a List of Values to the Employees Report
Adding Your Company Name
Switching Themes to Change the User Interface

4.1 About the Application You Build in This Tutorial

This tutorial guides you through the development of a Human Resources (HR) application for a fictitious company called AnyCo Corp. The application enables users to view and modify employee information stored in the OEHR_DEPARTMENTS and OEHR_EMPLOYEES tables.

When building the application, you perform these tasks:

Creating the Initial Application: Create an application that includes a Home page and a basic Departments report.
Modifying the Departments Report: Expand the report to pull in data from other tables.
Using an Interactive Report: Run and use the Interactive Report.
Adding an Employees Report and Form: Build an Employee form and report based on the OEHR_EMPLOYEES table.
Editing the Employees Report: Add calculated fields to the Employees report.
Adding a Link on the Home Page to the Employees Report: Add a link on the Home page.
Adding a Department Column to the Employees Report: Modify the Employees report to enable the user to select a department.
Linking a Column Value to Another Page: Add a link on the Departments report that navigates the user to the Employees report.
Adding a List of Values to the Employees Report: Add a report as a region that displays department details.
Adding Your Company Name: Add your company name so that it appears on all pages in your application.
Switching Themes to Change the User Interface: Switch the theme for your application.

4.2 Creating the Initial Application

This exercise guides you through creating your initial application. Using the Create Application Wizard, you create two pages and specify basic application functionality.

The pages created in this section include:

Home - This page is the first page displayed when the application is run. This page contains a link to the Departments report.
Departments - This page is an interactive report of all departments.

There are two types of reports, interactive and classic. Interactive is the default when creating applications, converting forms, and creating report regions. Select Interactive when you want to provide user customizations such as filters, sorting, compilation, and so on. If you have limited need for this functionality, use a classic report.

To create your initial application:

Log in to Oracle Application Express.

For instructions, see "Logging In To the Hosted Demo Environment" or "Logging In To Your Local Instance".
On the Workspace home page, click Application Builder.
Click Create.

The Create Application Wizard appears.

Description of the illustration cr_app.gif

Each page within a wizard displays the title of the wizard (for example, Create Application). Each wizard page also displays a line at the top, representing the sequence of steps. The highlighted circle indicates the step you are performing.

Description of the illustration bldap_wiz_steplst.gif

To orient you, each step in these exercises generally begins with the block label. For example, the first step in the Create Application Wizard highlights the word Method. Therefore, the next step orients you by beginning with the words For Method.
In the Create Application Wizard, select Database and click Next.

The Create Wizard shows that you are on the Name step.
For Name:
1. Application - Accept the default ID. The system provides a unique ID for each application you create in this workspace.
2. Name - Enter AnyCo Corp
3. Schema - Select the schema that contains the objects for this tutorial (for example, TWODAYPLUS).
4. Create Options - Select Include Home Page.
5. User Interface - Accept the default, Desktop.
  
  Tip:
  When you move your cursor over an item that has Help, the mouse changes to a question mark. Clicking the item then displays Help in another window.
6. Click Next.
The Create Wizard shows that you are on the Pages step.

The Home page is now listed in the top area. The Add Page area appears so that you can continue adding pages to your initial application.

Description of the illustration cr_app_add_pg.gif

Next, add a report page on the OEHR_DEPARTMENTS table.
In the Add Page section, specify the following:
1. Select Page Type - Select Report.
2. Subordinate to Page - Select Home (1).
  
  This sets up the hierarchy among the pages in your application.
3. Page Source - Accept the default, Table.
4. Table Name - Select OEHR_DEPARTMENTS.
  
  The list shows all tables and views in the schema associated with the application.
5. Implementation - Accept the default, Interactive.
6. Click Add Page.
  
  The pages listed in the Create Application section reflect the hierarchy of the pages in your application.
  
  Next, change the default page name, OEHR_DEPARTMENTS, to Departments.
Click the link Oehr Departments.

The New Page Definition window appears.
Under Page Definition, change the Page Name to Departments and click Apply Changes.

The New Page Definition window closes.

You might have to refresh the page to see that the page name has changed to Departments.
On the Create Application page, click Next.

Next, define some application-level settings.
For Shared Components, accept the default, No, and click Next.
For Attributes, make these changes:
1. Authentication Scheme - Accept the default, Application Express.
  
  Application Express is the default Authentication Scheme. The authentication scheme option enables you to control users' access to the application using Application Express authentication or another type of authentication.
2. Tabs - Select No Tabs.
3. Language - Accept the default, English (en).
4. User Language Preference Derived From - Accept the default, Application Primary Language.
5. Date Format - Click the up arrow and select 12-JAN-04.
6. Date Time Format - Click the up arrow and select 12-JAN-2004 1430.
7. Timestamp Format - Click the up arrow and select 12-JAN-2004 1430.
8. Timestamp Time Zone Format - Click the up arrow and select 12-JAN-2004 1430.
9. Click Next.
The Create Wizard shows that you are on the User Interface Theme step.
For Show, select All Themes.
For Select a theme, select Blue Builder (Theme 2) and click Next.

The Create Wizard shows that you are on the Confirm step.
Verify the information and click Create Application.

This message appears: "Application Created Successfully."

A report at the bottom of the page displays the two pages you created, Home and Departments and a Login page that Oracle Application Express adds automatically to your application.

Note:
To view the pages in Icon view, click the View Icons icon. Your view setting is retained from session to session.
Because these steps use the default setting, switch back to Report view before continuing.

Description of the illustration bldap_cr_app.gif

Congratulations! You created your first application using Oracle Application Express. Next, preview the application to see the application the way it would appear to end users.

4.2.1 Previewing Your Application

You preview your application by running it. Running the application displays the rendered version of the application as end users would see it.

As you create pages, you can view them by running the page individually or by running the entire application. When you run a page or application, the Application Express engine dynamically renders it into viewable HTML based on data stored in the database.

You can run an application in the following ways:

To run the application, click the icon Run Application.

Description of the illustration run_ico_green.gif
To run a specific page, click the icon Run Page.

Description of the illustration run_ico_sm.gif

To preview your application:

On the Application home page, click Run Application.
The first time you run an application you are prompted to enter a user name and password. To continue, enter your workspace user name and password and click Login.

You can run the application using your Application Express login credentials because you chose Application Express Authentication when creating the application. For more information, see "Establishing User Identity Through Authentication" in Oracle Application Express Application Builder User's Guide.

The Home page of your application appears.

Description of the illustration bldap_home.gif

The Home page has a link to the Departments page because you set up that hierarchy.

The Home page has the Developer toolbar at the bottom because you ran the application in the development environment. The Developer toolbar offers a quick way to edit the current page, create a page, region or page control, view the session state, or toggle in and out of Debug mode.
Click the link Departments.

The Departments report appears.

Description of the illustration bldap_dept.gif

4.3 Modifying the Departments Report

When you run the Create Application Wizard, the wizard creates forms and reports based on the selections you make. Behind the scenes, Application Express writes a query for you to extract the appropriate data from the database.

In this exercise, you modify the query for the Departments report to include information from the OEHR_EMPLOYEES table. Three columns from the OEHR_EMPLOYEES table, Number of Employees, Manager Name and Location, are added to the Departments report. The Manager Name column replaces the Manager Id column and the Location column replaces the Location Id column.

To modify the Departments report:

On the Developer toolbar (at the bottom of the page), click Edit Page 2.

The Page Definition for Page 2 appears.

A page is the basic building block of an application. Pages contain user interface elements such as tabs, lists, buttons, items, and regions. To see the definition of each page belonging to your application, you use the Page Definition page. For more information, see "What Is Application Builder?".
Under Page Rendering, locate Regions.
Expand Regions and locate Departments.

Description of the illustration bldap_deptment.gif
Right-click Departments and select Edit.

Tip:
You can also double-click Departments.

The Region Definition appears.

Scroll down to Source, and replace the default Region Source script with the following:

SELECT d.DEPARTMENT_ID,
    d.DEPARTMENT_NAME,
    (select count(*)from oehr_employees where department_id = d.department_id)  "Number of Employees",
    substr(e.first_name,1,1)||'.'||e.last_name "Manager Name",
    c.COUNTRY_NAME "Location"
FROM OEHR_DEPARTMENTS d,
   OEHR_EMPLOYEES e,
   OEHR_LOCATIONS l,
   OEHR_COUNTRIES c
WHERE d.LOCATION_ID=l.LOCATION_ID
    AND l.COUNTRY_ID=c.COUNTRY_ID
    AND d.DEPARTMENT_ID=e.department_id
    AND d.manager_id=e.employee_id
    AND instr(upper(d.department_name),upper(nvl(:P2_REPORT_SEARCH,d.department_name)))>0

Click Apply Changes.
To confirm, click Apply Changes.

This message appears: "Interactive Report query changed successfully."
Right-click Departments and select Edit Report Attributes.
Next to Number of Employees, click the icon Edit.
Under Column Definition, for Column Alignment, select center.

This message appears: "Actions Processed."
Click Apply Changes.

This message appears: "Actions Processed."

4.3.1 Run the Report to View Added Columns

To see how the page looks to end users, run the page. When you run a page, the Application Express engine dynamically renders and processes the page to display viewable HTML.

To run the page:

In the upper right corner, click the icon Run Page.

Description of the illustration runpg_iconrow.gif

The revised Departments report appears.

Description of the illustration bldap_deptexp.gif

The Manager Id and Location Id columns are not available. Also, the three new columns do not display on the revised report: Number of Employees, Manager Name, and Location.
To add the new columns, click the menu A ctions.

The list of Action options appears.

Description of the illustration bldap_addcolumn.gif
From the Actions menu, select Select Columns.

The columns that were added by the new SQL query are in the Do Not Display box.
To add these three columns to the Display in Report box, click the icon Move All, which resembles two forward arrows.
Description of the illustration bldap_moveall.gif
Verify that the columns are in the following order. If they are not, click up and down arrows to the right of the Display in Report list to change the order:
- Department Id
- Department Name
- Number Of Employees
- Manager Name
- Location
Description of the illustration bldap_reorder.gif
Click Apply.

The Departments page appears and includes the new columns.

Description of the illustration bldap_deptnew.gif

4.4 Using an Interactive Report

This exercise guides you through some commonly used report options available to users when they run interactive reports. These options enable users to perform certain tasks quickly.

Topics:

Save Report Settings as the Default
Create a Filter
Change the Sort Order
Add a Controlled Break
Display the Single Row View
Reset Report Settings

You used the Select Column option from the Actions menu to add columns to the report display. In this section, you run the application and use search, sort, highlight, and reset features to manipulate the Departments interactive report.

This tutorial only shows the most prominent functionality available with interactive reports. For complete details, see "Customizing Interactive Reports" in Oracle Application Express Application Builder User's Guide.

4.4.1 Save Report Settings as the Default

Both developers and end users can save an interactive report. However, only a developer can save the Primary Default report that initially displays.

There are four types of interactive reports:

Primary Default (Developer Only). The Primary Default is the report that initially displays. Primary Default reports cannot be renamed or deleted.
Alternative Report (Developer Only). Enables developers to create multiple report layouts. Only developers can save, rename, or delete an Alternative Report.
Public Report (End user). Can be saved, renamed, or deleted by the end user who created it. Other users can view and save the layout as another report.
Private Report (End user). Only the end user that created the report can view, save, rename, or delete the report.

To save current report settings as the default:

Click the menu Actions.
Select Save Report.

The Save Report settings appear.

Description of the illustration bldap_default.gif
For Save, select As Default Report Settings.

The Default Report Type settings appear.
Select Primary and click Apply.

The report shows all columns when the user selects the Reset option from the menu Actions or when the page is reset by the command Reset Interactive Report (RIR).

See Also:
For a description of how to save a report as a Named Report see "Customizing Interactive Reports" in the Oracle Application Express Application Builder User's Guide.

4.4.2 Create a Filter

This exercise shows you how to add a filter to the Departments report. This example creates a filter to show all departments except the department with Id 60.

There are multiple ways that a user can create a filter. The user can add a filter from the:

Search Bar
Actions Menu
Column Heading

The Actions menu offers more options than the others. For a quick filter using the equals operator, the Search Bar or Column Heading are the quickest methods. For anything else, use the Filter option from the Actions menu.

To create a filter for all departments not equal to 60:

Click the menu Actions.
Select Filter.

The filter settings display. You can also click the column heading link to create a filter for that column. Creating a filter from the column heading menu, however, is limited to the equal operator.

Description of the illustration bldap_filter.gif
From Operator, select the not equals operator (!=).
From Expression, select 60.
Click Apply.

The Departments report is displayed with the Department_Id!=60 filter applied. All departments, except for the IT department with a Department Id of 60, are included in the report.

Description of the illustration bldap_dept60.gif

The filter displays at the top of the report. You can edit, disable, or delete this filter. To edit, click the filter name. To disable, deselect the check box. To delete, click the Delete icon that looks like an X over the filter icon.
Deselect the check box next to the Department Id!=60 filter to disable it.

The report shows data for all Department Ids.

4.4.3 Change the Sort Order

In this exercise, you learn how to reorder the data for each column.

To change the sort order:

Click the menu Actions.
Select Format and then Sort.

The sort settings are displayed. This option is also available for each column by clicking the column heading. The Actions menu is quicker when changing the sort order for multiple columns.
For the first column entry, select Department Name.

Description of the illustration bldap_actionsort.gif
Click Apply.

The report shows the Department Name column in ascending alphabetical order.

Description of the illustration bldap_ascend.gif

The Up Arrow icon after the Department Name indicates that the column has been sorted in ascending order. You can click this arrow to re-sort the column.

4.4.4 Add a Controlled Break

Another way to reformat a report is to create a controlled break. A controlled break enables the user to delineate data into clearly visible groups.

To add a controlled break:

Click the column heading Location.

The column heading menu appears.
Click the icon Control Break (shown below).

This option is also available from the menu Actions, Format.

Description of the illustration bldap_ctlbrk.gif

The report is reformatted to show a break after each group of rows that have the same location.

Description of the illustration bldap_locbrk.gif

The Location control break report setting displays under the Search bar.

4.4.5 Display the Single Row View

You can also display details for a row by selecting the Edit icon.

To display a single row view:

In the first row, locate the Edit icon .

Description of the illustration bldap_singlerow.gif
In the first row, select the Edit icon.

The details page for the first row appears.

Description of the illustration bldap_details.gif

4.4.6 Reset Report Settings

You can revert the report settings to the saved default settings. When an interactive report is reset, the default settings are applied to the report. An interactive report can be reset by the user and by the developer under the following conditions:

A user can reset an interactive report if the Reset option is available from the Actions menu. This option is enabled or disabled by the developer. For further details, see "Customizing Interactive Reports" in the Oracle Application Express Application Builder User's Guide.
A developer can specify that an interactive report be reset when it is called from another page. The Reset Interactive Report command, RIR, is used for this purpose. For further details, see "Customizing Interactive Reports as a Developer" in the Oracle Application Express Application Builder User's Guide.

To return to the default settings:

Click Report View.
Click the Actions menu and select Reset.
When prompted to restore the default settings, click Apply.

The report goes back to the default report settings saved in the previous section.

4.5 Adding an Employees Report and Form

In this exercise, you add a report to display employee information and a form to create, update, and delete employee information.

To add an employee report and form:

On the Developer toolbar (at the bottom of the page), click the link Application ID.

The Application home page appears.
Click Create Page.
For Page:
1. Select Form and click Next.
2. Select Form on a Table with Report.
  
  This option creates two pages, a report and form, based on a single table or view.
3. Click Next.
For Report Page, make these changes:
1. Page Name - Change to Employees.
2. Region Title - Change to Employees.
3. Region Template - Select Reports Region.
4. Breadcrumb - Select Breadcrumb.
  
  The Create Breadcrumb Entry section appears.
5. For Select Parent Entry, click the link Home.
  
  Home appears in the Parent Entry field.
6. Click Next.
For Data Source page:
1. Table/View Owner - Accept the default, TWODAYPLUS.
2. Table/View Name - Select OEHR_EMPLOYEES (table).
3. Click Next.
For Tabs Page, accept the default, Do not use tabs, and click Next.
For Report Columns page:
1. Adjust the selected columns so that these columns display in the right box:
  - EMPLOYEE_ID
  - FIRST_NAME
  - LAST_NAME
  - HIRE_DATE
  - SALARY
  - COMMISSION_PCT
2. Click Next.
You are still on the Report Columns step.
Accept the defaults for the link image and click Next.

Next, define the page and region information for the form.
For Form Page, make these changes:
1. Page Name - Enter Create/Edit Employee
2. Region Title - Enter Create/Edit Employee
3. Click Next.
For Primary Key Type, accept the default, Managed by Database (ROWID), and click Next.
In Select Column(s), move all columns from the left box to the right box and click Next.

These columns appear in the Create/Edit Employee form when the page is run.
For Identify Process Options, accept the defaults and click Next.

These selections enable users to add, update, and create employee records.
For Confirm, verify the information and click Create.

This message appears: "The Form and Report have been created successfully."

4.5.1 Previewing the Pages

To preview the two pages you created, run the current page. From the Employees report, you can then preview the form.

To preview the pages:

Click Run Page.

Description of the illustration runpg_afterwiz.gif

The Employees report appears.

Description of the illustration bldap_emprpt.gif

Note the following about the Employees report:
- The Search Bar with the Actions menu appears at the top. For more information, see "Using an Interactive Report".
- The column headings are links that enable the user to quickly perform various sorting, filtering, and reformatting operations. For more information, see "Using an Interactive Report".
- The Edit icon you selected appears in each row. You can click the Edit icon to update the employee record. Because you created a form based on a table with a report, the wizard automatically linked the report to the form. If you create only a report, as described in the previous section, "Creating the Initial Application", the wizard automatically links the report to the single row view.
- The Create button appears in the upper right corner. You can click the Create button to add an employee to the underlying table.
- The Employees report includes the columns you specified. When you selected the columns and applied the changes, Oracle Application Express created the appropriate SQL code in the background to produce this result.
Your data might appear in a different order. If so, ignore this difference.
To review the form you also created, click the Edit icon in an Employee row.

The Create/Edit Employee form appears. For example, if you click the Edit icon for the employee Steven King:

Description of the illustration bldap_empeditfrm.gif

Note the following about the Create/Edit Employee form:
- The Hire Date field displays a calendar, which is included because the data type of the underlying column is DATE.
- Fields with red asterisks are mandatory and cannot be blank or null.

4.6 Editing the Employees Report

In this exercise, you create a new column entitled Remuneration to display employee salaries in the Employees report.

Topics:

Create a Function
Add a Column
Previewing the Page
Changing the Column Format

4.6.1 Create a Function

First, create a function to calculate employees' salaries.

To create a function:

On the Developer toolbar, click Home.
Click SQL Workshop and then SQL Commands.

SQL Commands provides a window where you can run SQL commands (including those that create PL/SQL subprograms).

In the window (the SQL editor), enter the following script:

CREATE OR REPLACE FUNCTION calc_remuneration(
    salary IN number, commission_pct IN number)
RETURN NUMBER IS
BEGIN
    RETURN ((salary*12) + (salary * 12 * nvl(commission_pct,0)));
END;

When copying and pasting this SQL, make sure there is not an empty line before the first statement, CREATE OR REPLACE FUNCTION.

Click Run.

The Results section displays this message:
```
Function created.
```

4.6.2 Add a Column

Next, add a column to display the results of the employees' salary calculations.

To add a column:

Navigate to the Page Definition for the Employees page:
1. Click the tab Application Builder.
2. Select the application AnyCo Corp.
3. Click the Employees page.
  
  The Page Definition for the Employees page appears.
Under Regions, locate Employees.

Description of the illustration bldap_emprjn.gif
Right-click Employees and select Edit.

The Region Definition appears.

Scroll down to Source, and replace the existing code with the following:

SELECT "ROWID", 
    "EMPLOYEE_ID", 
    "FIRST_NAME",
    "LAST_NAME",
    "HIRE_DATE",
    "SALARY",
    "COMMISSION_PCT",
    calc_remuneration(salary, commission_pct) REMUNERATION
FROM "#OWNER#"."OEHR_EMPLOYEES"

Click Apply Changes.
To confirm, click Apply Changes.

This message appears: "Interactive report query changed successfully."

4.6.3 Previewing the Page

To preview the page, in the upper right corner, click the icon Run Page. The Remuneration column does not appear. You must add the column by using the Select Column action.

Description of the illustration bldap_emprpt.gif

To display the Remuneration column:

Click the Actions menu.
Select the action Select Columns.
Move the Remuneration column from the Do Not Display box to the Display in Report box.

Description of the illustration bldap_addrem.gif
Click Apply.

The Employees Report is displayed and includes the Remuneration column.

Description of the illustration bldap_empfrmrem.gif

4.6.4 Changing the Column Format

Next, change the format of the columns that contain numeric values.

To change the column format:

On the Developer toolbar (at the bottom of the page), click Edit Page 3.

The Page Definition appears.
Under Regions, right-click Employees and select Edit Report Attributes.

The Interactive Report Attributes page appears.
Locate the section Column Attributes.

Next, change the format of the value in the Salary and Remuneration columns.
To edit the Salary format:
1. Next to SALARY, click the icon Edit.
2. For Number/Date Format, from the context menu, select $5,234.10.
  
  The appropriate format mask appears in the field.
3. Click Apply Changes.
  
  This message appears: "Action Processed."
Repeat the previous step for REMUNERATION.
To preview the page, in the upper right corner, click the Run Page icon.

The numbers in the Salary and Remuneration columns now appear with the dollar sign and include commas and decimals.

Description of the illustration bldap_emprptnum2.gif

4.7 Adding a Link on the Home Page to the Employees Report

In this exercise, you add a link on the Home page to take users to the Employees report. This link appears in the Navigation region on the left side of the Home page.

To add a link on the Home page to the Employees report:

Navigate to the Page Definition for the Home page:
1. On the Developer toolbar (at the bottom of the page), click the link Application ID.
2. On the Application home page, click Home.
Under Regions, right-click Navigation and select Edit List.
On the List Details page, click Create List Entry.
On the Create/Edit page, make the following changes:
1. List Entry Label - Enter Employees
2. Page - From the context menu, select Employees.
Click Create List Entry.

This message appears: "Action Processed."
Run the page.

Description of the illustration bldap_homeemp.gif

Your Home page now includes a link to Employees.
To test the link, click Employees.

The Employees report appears.

4.8 Adding a Department Column to the Employees Report

In this exercise, you link the Employees and Departments reports. When you complete this section, the Employees report has a Departments column showing which department each employee belongs to.

To perform this task, you create a column on the Employees page to enable the user to select a department. You then run the Employees report and add the Departments column to the report. The report settings are saved as the default.

Topics:

Add a Column
Display the Department Name Column in the Report
Save Report Settings as the Default

4.8.1 Add a Column

In order to filter the Employees report by department, add a Department column to the report.

To add a column:

In the Developer toolbar, click Edit Page 3.
Under Regions, right-click Employees and select Edit.

The Region Definition appears.
Scroll down to Source.

Replace Region Source with the following SQL:

SELECT e.ROWID,
    e.EMPLOYEE_ID,
    e.FIRST_NAME,
    e.LAST_NAME,
    e.HIRE_DATE,
    e.SALARY,
    e.COMMISSION_PCT,
    calc_remuneration(salary, commission_pct) REMUNERATION,
    e.DEPARTMENT_ID,
    d.DEPARTMENT_NAME
FROM OEHR_EMPLOYEES e,
    OEHR_DEPARTMENTS d
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID

Click Apply Changes.
To confirm, click Apply Changes.

This message appears: "Interactive Report query changed successfully."

The next section explains how to add the column to the report display and how to use this column to filter employee reports.

4.8.2 Display the Department Name Column in the Report

When you first run this report, use the Select Columns action to add the Department Name column to the display. After this column is visible on the report, make this report the default and filter by department.

To add the Department Name column to the report:

Click the icon Run Page.
Click the Actions menu and select Select Columns.
Move Department Name from the Do Not Display box to the last entry in the Display in Report box.

Description of the illustration bldap_deptcol.gif
Click Apply.

The Employees Report is displayed and includes the Department Name column.

Description of the illustration bldap_empdept.gif

Next, make these report settings the default.

4.8.3 Save Report Settings as the Default

This section explains how to save the current report settings as the Primary Default report. Both developers and end users can save interactive reports. However, only a developer can save the report that initially displays, called the Primary Default report.

To save the current report settings as the default:

Run the report page Employees.
Click the Actions menu and select Save Report.
For Save, select As Default Report Settings.
Accept the Default Report Type setting, Primary, and click Apply.

Note:
If a reset is performed on this page, these default report settings are used. The Department Name column is displayed by default.

4.9 Linking a Column Value to Another Page

In this exercise, you add a link to the Number of Employees column on the Departments report that navigates the user to the Employees report. It also sets the focus to the selected department. The column link includes a Clear Interactive Report command, CIR, to clear the Employees report, when shown, of any filters, control breaks, highlights, aggregates, computed columns, chart settings, and flashback settings.

Topics:

Change Column Value to a Link
Run the Departments Report
Create a Highlight to Test Clear Interactive Report Command

4.9.1 Change Column Value to a Link

To make this change, you edit the report attributes on the Departments report to change the value for the Number of Employees column to a link.

To change the column value to a link:

On the Developer toolbar, click Application ID.
Click Departments.
Under Regions, right-click Departments and select Edit Report Attributes.

The Report Attributes page appears.
Locate the section Column Attributes.
In the Column Attributes section, next to Number of Employees, click the Edit icon.
Scroll down to the section Column Link.
Under Column Link, make these changes:
1. Link Text - Select #Number of Employees#.
2. Page - From the context menu, select Employees.
  
  In the Page field, the number of the Employees page, 3, appears.
3. Select Reset Pagination.
4. Clear Cache - Enter:
```
3,CIR
```
  The 3 means the cache for page 3 is cleared and the string CIR clears the Employees interactive report of any filters, control breaks, highlights, aggregates, computed columns, chart settings or flashback settings when the report is displayed.
5. Item 1, Name - Enter IR_DEPARTMENT_ID
  
  The prefix IR_ associates the URL item with an interactive report column rather than an item on the page.
6. Item 1, Value - From the context menu, select #DEPARTMENT_ ID#.
  
  The Column Link section shows the options you selected.
  
  Description of the illustration bldap_setnumlink.gif
  
  In summary, these options enable the following behavior when the end user clicks the link:
  - The end user is navigated to page 3 of the Employees report.
  - On the Employees report, the selected department is in focus.
  - The Employees report is cleared—any filters, control breaks, highlights, aggregates, computed columns, chart settings, or flashback settings are removed.
  - The user is returned to the first set of employee records when the user selects another department.
7. Click Apply Changes.
  
  This message appears: "Action Processed."
  
  In the Column Attributes section, the Number of Employees row contains a check mark in the Link column, reflecting the change you made.

4.9.2 Run the Departments Report

Now, run the report and test the Number of Employees column link from the Departments report. The Employees report is displayed, showing all employees that belong to the selected department.

Click the icon Run Page.

The Departments report is displayed. End users can now drill down on the data in the Number of Employees column by clicking the numbers in that column.

Description of the illustration bldap_numemplink.gif
In the Executive row, in the Number of Employees column, click 3.

Description of the illustration bldap_emplink.gif

The Department Id filter is displayed because this report is filtered by the Department Id value for the Executive department, 90, passed to this report from the Departments report. The Clear Interactive Report command is included at the end of the URL in your browser, as shown in this example:
```
f?p=100625:3:13870074215647::NO:RP,3,CIR:IR_DEPARTMENT_ID:90
```

4.9.3 Create a Highlight to Test Clear Interactive Report Command

This exercise demonstrates how to use the CIR command to clear a highlight before displaying the Employees report. You highlight any rows containing the first name of Neena, then display the Employees report from the Number of Employees column on the Departments report.

To highlight rows in the Employees report:

Click the Actions menu and select Format and then Highlight.

The Highlight options appear.
For Highlight options:
1. Name - Enter Neena
2. Background Color - Select [yellow]
3. Text Color - Select [red]
4. Column - Select First Name
5. Operator - Select =.
6. Expression - Select Neena
7. Click Apply.
The Employees report appears with the row for Neena highlighted.

Description of the illustration bldap_high.gif
Next, reset the report.
1. Click the breadcrumb Home.
2. Click link Departments.
3. In the Executive row, click Number of Employees.
  
  The Employees report is displayed with the highlighting cleared, due to the CIR command specified in the column link.
To the right of DepartmentID = 90, click the icon Remove Filter.
On the Developer toolbar, click Application ID.

4.10 Adding a List of Values to the Employees Report

In this exercise, you add a list of values to the Employees page to display employees by department. You also change the Employees report query, edit the column link on the Department report, and turn off pagination.

Topics:

Add a List of Values
Add an Item to Display List of Values
Change Employees Report Query
Change Column Link on Department Report
Turning Off Pagination

4.10.1 Add a List of Values

In this section, you create a list of values to select which department details to view. This is a dynamic list that queries the OEHR_DEPARTMENTS table for a list of the department names.

To create a list of values:

Click Shared Components.
Under User Interface, select Lists of Values.
Click Create.
Accept the default, From Scratch, and click Next.
For Name and Type:
1. Name - Enter DEPARTMENTS
2. Type - Select Dynamic.
3. Click Next.

For the Query, enter:

SELECT department_name d, department_id v
    FROM oehr_departments
    ORDER BY d

Click Create List of Values.

This message appears: "Dynamic list of values created."

4.10.2 Add an Item to Display List of Values

Now, you create an item in the Department Details region to display the list of departments.

To add an item:

Navigate to the Page Definition for the Employees page.
Under Region, right-click Employees and select Create Page Item.
Select Select List and click Next.
For Display Position and Name:
1. Item Name - Enter P3_DEPARTMENT_ID
2. Sequence - Enter 10
3. Region - Select Employee (10)
4. Click Next.
For Item Attributes:
1. Label - Enter Department
2. Accept the remaining defaults and click Next.
For Settings:
1. Value Required - Select No.
2. Page Action when Value Changed - Select Redirect and Set Value.
3. Click Next.
For List of Values, make these changes:
1. Named LOV - Select DEPARTMENTS
2. Display Null Value - Select Yes
3. Null Display Value - Enter:
```
- No Department Assigned -
```
4. Null Return Value - Enter:
```
-1
```
5. Click Next.
Click Create Item.

This message appears: "Item P3_DEPARTMENT_ID created."

4.10.3 Change Employees Report Query

Now, you change the Employees report query to display employees that belong to the selected department.

To change the query:

Under Regions, right-click Employees and click Edit.

For Region Source, enter:

SELECT e.ROWID,
        e.EMPLOYEE_ID,
        e.FIRST_NAME,
        e.LAST_NAME,
        e.HIRE_DATE,
        e.SALARY,
        e.COMMISSION_PCT,
        calc_remuneration(salary, commission_pct) REMUNERATION,
        e.DEPARTMENT_ID,
        d.DEPARTMENT_NAME
  FROM  OEHR_EMPLOYEES e,
        OEHR_DEPARTMENTS d
 WHERE  e.DEPARTMENT_ID = d.DEPARTMENT_ID(+) AND 
        (e.DEPARTMENT_ID = :P3_DEPARTMENT_ID or 
        (e.DEPARTMENT_ID is null and nvl(:P3_DEPARTMENT_ID,'-1') = '-1'))

Click Apply Changes.

This message appears: "Region Updated."

4.10.4 Change Column Link on Department Report

Because the Employees query has changed, the Number of Employees column link must initialize the P3_DEPARTMENT_ID item to the value of DEPARTMENT_ID.

To change the column link for Number of Employees:

Click the breadcrumb Application ID.
Click Departments.
Under Regions, right-click Departments and select Edit Report Attributes.
For Number of Employees, click the icon Edit.
Scroll down to Column Link.
For Item 1 Name, select P3_DEPARTMENT_ID.
Click Apply Changes.

This message appears: "Action Processed."

4.10.5 Turning Off Pagination

Next, turn off pagination for the Department Details report, because only one record (the department details report) displays at a time. If pagination is on, the region that displays the department details includes the information row 1 - 1.

To turn off pagination:

Click the breadcrumb Application ID.
Click Employees.
Under Regions, right-click Employees and select Edit Report Attributes.
Scroll down to Pagination.
For Pagination Type, select - No Pagination Selected -.
Click Apply Changes.

This message appears: "Action Processed."
Click the Run Page icon.
From the Department list, select No Department Assigned.

Description of the illustration bldap_nodptassgn.gif

The row for employee Kimberely Grant appears. This employee is not currently assigned to a department.

Tip:
If no records initially display, reload the page and try again.

4.11 Adding Your Company Name

You can add a graphical logo or text to your application so that it displays on each page. Your page template determines the display location.

In this exercise, you add your company name to your application.

To add your company name to your application:

Navigate to the Application home page: On the Developer toolbar (at the bottom of the page), click Application ID.
In the upper right corner, click Edit Application Properties.

The Edit Application Definition page appears.
Click User Interface.
Under Logo:
1. Logo Type - Select Text.
2. Logo - Enter AnyCo Corp
3. Logo Attributes - Select Black Text.
This message appears: "Application id_number processed."
Click Apply Changes.
Click Home.
Click the Run Page icon.

Description of bldap_compname.gif follows

Description of the illustration bldap_compname.gif

The company name now appears on each page in your application.

4.12 Switching Themes to Change the User Interface

In this exercise, you switch the current theme to another one for your application. A theme is a collection of templates that defines the application user interface. Each theme contains templates for every type of application component and page control, including individual pages, regions, reports, lists, labels, menus, buttons, and list of values.

Oracle Application Express separates presentation (or user interface themes) from the application logic. You can design your application in one theme, change to another supplied theme, or create and use your own custom theme. By separating the application logic (such as queries, processes, and branches) from the HTML rendering, your application can take advantage of the latest designs and other technological advances without an application rewrite. Each theme provides a complete set of templates that accommodate every user interface pattern that may be needed in an application.

To switch the theme:

On the Developer toolbar (at the bottom of the page), click Application ID.
On the Application home page, click Shared Components.
Under User Interface, click Themes.

Description of the illustration bldap_sharcomp.gif
On the Themes page, click Create.

The Create Wizard appears.
For Method, accept the default, From the Repository, and click Next.
For User Interface, accept the default, Desktop, and click Next.
For Identify Theme, select Cloudy (Theme 24) and click Next.
Click Create.

This message appears: "Theme created."
Click Switch Theme.
For Identify Theme:
- For Currently Active Theme, select 2. Builder Blue.
- For Switch to Theme, select 24. Cloudy.
Click Next.
For Verify Compatibility, review the information, accept the defaults, and click Next.

The wizard verifies that corresponding templates exist in the selected theme for all templates used in your current application. If a template is missing, it warns you with a message in the Status column. For this tutorial, you can ignore this message because Oracle Application Express maps missing region templates to existing ones.
For Confirm Switch, click Switch Theme.
Click the icon Run Page.

Description of the illustration bldap_newtheme2.gif

The design and layout are changed in all your pages.