../E11946-02.epub /> ../E11946-02.mobi />

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:

This section contains the following topics:

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:

  1. "Creating the Initial Application": Create an application that includes a Home page and a basic Departments report.

  2. "Using an Interactive Report": Run and use the Interactive Report.

  3. "Modifying the Departments Report": Expand the report to pull in data from other tables.

  4. "Adding an Employees Report and Form": Build an Employee form and report based on the OEHR_EMPLOYEES table.

  5. "Editing the Employees Report": Add calculated fields to the Employees report.

  6. "Adding a Link on the Home Page to the Employees Report": Add a link on the Home page.

  7. "Adding a Department Column to the Employees Reports": Modify the Employees report to enable the user to select a department.

  8. "Linking a Column Value to Another Page": Add a link on the Departments report that navigates the user to the Employees report.

  9. "Adding a Conditional Department Details Report": Add a report as a region that displays department details.

  10. "Adding Your Company Name": Add your company name so that it appears on all pages in your application.

  11. "Switching Themes to Change the User Interface": Switch the theme for your application.

Creating the Initial Application

This first 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. Interactive reports should be selected when you want to provide user customizations such as filters, sorting, compilation and so on. If you have limited need for this functionality, you are best served by using a classic report.

    See also:

    For more information on the difference between these two report types, see Chapter 3. How to Create a Parameterize Report in the Oracle Application Express Advanced Tutorials.

To create your initial application:

  1. Log in to Oracle Application Express.

    For instructions, see "Logging In to the Hosted Demo Environment" or "Logging In to Your Local Instance".

  2. On the Workspace home page, click Application Builder.

  3. Click the Create button.

    The Create Application Wizard appears.

    Description of cr_app.gif follows
    Description of the illustration cr_app.gif

    Note that each page within a wizard displays the title of the wizard (for example, Create Application). Each wizard page also displays a series of blocks on the left, representing the sequence of steps. The highlighted block indicates the step you are performing.

    Description of bldap_wiz_steplst.gif follows
    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.

  4. For Method, accept the default, Create Application, and click Next.

  5. For Name:

    1. Name - Enter AnyCo Corp.

    2. Application - accept the default ID. The system provides a unique ID for each application you create in this workspace.

    3. Create Application - Accept the default, From scratch.

    4. Schema - Select the schema that contains the objects for this tutorial (for example, TWODAYPLUS).

    5. Click Next.

    Next, add a page to your application.

  6. For Pages, specify the following in the Add Page section:

    1. Select Page Type - Accept the default, Blank.

      Tip:

      Explanatory information for each option appears on the right side of the page. This page-level help appears throughout the application.
    2. Page Name - Enter Home.

    3. Click Add Page.

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

    Description of cr_app_add_pg.gif follows
    Description of the illustration cr_app_add_pg.gif

    Next, add a report page on the OEHR_DEPARTMENTS table.

  7. 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.

      Notice that 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.

  8. Click the Oehr_Departments link.

  9. Under Page Definition, change the Page Name to Departments and click Apply Changes.

  10. In the Create Application section, click Next.

    After adding your initial pages, define some application-level settings.

  11. For Tabs, select No Tabs and click Next.

  12. For Shared Components, accept the default, No, and click Next.

  13. For Attributes, make these changes:

    1. Authentication Scheme - Accept the default, Application Express.

      Note that 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. Language - Accept the default, English (United States) (en-us)

    3. User Language Preference Derived From - Accept the default, Use Application Primary Language.

    4. Date Format - Select the up arrow to select the 12-JAN-04 format from the list of Date formats.

    5. Click Next.

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

  15. For Confirm, verify the information and click Create.

    The two pages you created, Home and Departments, appear as icons by default.

    Note:

    To view the pages in a list instead of as icons, from the View list select Details and click Go. Your view setting is retained from session to session.

    Because these steps use the default setting, switch back to Icons view before continuing.

    Notice that Oracle Application Express adds a Login page automatically to your application.

    Description of bldap_cr_app.gif follows
    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.

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.

To preview your application:

  1. 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, simply enter your workspace user name and password and then click Login.

  2. The first time you run an application you are prompted to enter a user name and password. To continue, simply enter your workspace user name and password and then 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 bldap_home.gif follows
    Description of the illustration bldap_home.gif

    Notice that a link to the Departments page appears on the Home page because you set up that hierarchy.

    Also notice the Developer toolbar at the bottom of the page. These links appear when you run an application within the development environment. The Developer toolbar offers a quick way to edit the current page, create a page, region, or page control, view session state, or toggle in and out of Debug mode.

  3. Click the Departments link.

    The Departments report appears.

    Description of bldap_dept.gif follows
    Description of the illustration bldap_dept.gif

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:

  1. Click the Edit Page 2 link on the Developer toolbar (at the bottom of the page).

    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?".

  2. Under Page Rendering, locate the Regions section.

    Description of bldap_reg.gif follows
    Description of the illustration bldap_reg.gif

  3. Click Departments.

  4. 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
    
  5. Click Apply Changes.

  6. Click Apply Changes to confirm.

  7. Under the Regions section, click the Interactive Report link.

  8. Click Edit icon next to Number of Employees.

  9. Under Column Definition, select center for Column Alignment.

  10. Click Apply Changes.

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:

  1. Click the Run Page icon in the upper right corner.

    Description of runpg_iconrow.gif follows
    Description of the illustration runpg_iconrow.gif

    The revised Departments report appears.

    Description of bldap_deptexp.gif follows
    Description of the illustration bldap_deptexp.gif

  2. To add the new columns, click the Actions menu. The list of Action options appears.

    Description of bldap_addcolumn.gif follows
    Description of the illustration bldap_addcolumn.gif

    Note that the Manager Id and Location Id columns are no longer available. Also note that the three new columns are not displayed on the revised report: Number of Employees, Manager Name, and Location. The following steps are required to add these columns to the display and reorder them.

  3. Select Select Columns.

    The columns that were added by the new SQL, Location and Manager Name, are in the Do Not Display box.

  4. Click the Move All icon to add these three columns to the Display in Report box. The Move All icon looks like the following figure. Description of bldap_moveall.gif follows
    Description of the illustration bldap_moveall.gif

  5. Use the up and down arrows to the right of the Display in Report to put the columns in this order:

    Description of bldap_reorder.gif follows
    Description of the illustration bldap_reorder.gif

  6. Click Apply.

    The Departments page appears and includes the new columns.

    Description of bldap_deptnew.gif follows
    Description of the illustration bldap_deptnew.gif

Using an Interactive Report

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

See Also:

For an overview of all Interactive Report components see How to Create a Parameterized Report in the Oracle Application Express Advanced Tutorials.

Topics included in this section:

You already 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 some of the various search, sort, highlight and reset features to manipulate the Departments interactive report.

This tutorial only touches on some of the most prominent functionality available with interactive reports. For complete details refer to the Oracle Application Express Advanced Tutorials and Oracle Application Express Application Builder User's Guide.

Save Report Settings as the Default

If the report settings are not saved as the default, the columns added to the display in the previous steps are not displayed when the application is restarted or when the report is reset.

To save current report settings as the default:

  1. Select the Actions menu.

  2. Select Save Report.

    The Save Report settings appear.

  3. Select As Default Report Settings from the Save drop down.

    Description of bldap_default.gif follows
    Description of the illustration bldap_default.gif

  4. Click Apply.

    The report will show all columns when the user selects the Reset option from the Actions menu or when the page is reset by a Reset Interactive Report command, RIR.

    See Also:

    For a description of how to save as a report as a Named Report see How to Create a Parameterized Report in the Oracle Application Express Advanced Tutorials.

Create a Filter

This exercise shows you how a user can add a filter to the Departments report. This example creates a filter to show all departments with the exception of departments with an Id of 60.

There is more than one way 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:

  1. Select the Actions menu.

  2. Select Filter.

    The filter settings display. Note the user 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 bldap_filter.gif follows
    Description of the illustration bldap_filter.gif

  3. From the Operator drop down, select the not equals operator:

    !=
    
  4. Select 60 from the Expression drop down.

  5. Click Apply.

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

    Description of bldap_dept60.gif follows
    Description of the illustration bldap_dept60.gif

    Notice the filter is shown at the top of the report. You can edit, disable, or delete this filter. To edit, click on the filter name. To disable, deselect the check box. To delete, click on the Delete icon that looks like an X over the filter icon.

  6. Deselect the check box next to the Department Id!=60 filter to disable it.

    The report shows data for all Department Ids.

Change the Sort Order

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

To change the sort order:

  1. Select the Actions menu.

  2. Select Sort.

    The sort settings are displayed. Note 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.

  3. Select Department Name for the first column entry.

    Description of bldap_actionsort.gif follows
    Description of the illustration bldap_actionsort.gif

  4. Click Apply.

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

    Description of bldap_ascend.gif follows
    Description of the illustration bldap_ascend.gif

    Notice the up arrow icon after the Department Name that indicates the column has been sorted in ascending order. You can click on this arrow to resort the column.

Add a Controlled break

Another Actions menu option the user can select to reformat the report, is the controlled break. This allows the user to delineate data into clearly visible groups.

To add a controlled break:

  1. Click the Location column heading.

    The column heading menu appears.

  2. Click the Control Break icon as shown below.

    Note this same option is also available from the Actions menu.

    Description of bldap_ctlbrk.gif follows
    Description of the illustration bldap_ctlbrk.gif

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

    Description of bldap_locbrk.gif follows
    Description of the illustration bldap_locbrk.gif

    Note the Location control break report setting has been added under the Department Id!=60 filter.

Display the Single Row View

The user can display details for a row by selecting the Single Row icon.

To display the single row view:

  1. Locate the Single Row View icon for the first row.

    Description of bldap_singlerow.gif follows
    Description of the illustration bldap_singlerow.gif

  2. Select the Single Row View icon for the first row.

    The details page for the first row appears.

    Description of bldap_details.gif follows
    Description of the illustration bldap_details.gif

Reset Report Settings

The user can revert the report settings back 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:

To return to the default settings:

  1. Select the Departments breadcrumb.

  2. Select the Actions menu.

  3. Select Reset.

  4. Click Apply.

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

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:

  1. Click the Application ID link on the Developer toolbar (at the bottom of the page).

    The Application home page appears.

  2. Click Create Page.

  3. 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.

  4. For Table/View Owner, accept the default and click Next. This specifies the schema owner.

  5. For Table/View Name, select OEHR_EMPLOYEES and click Next.

  6. For Define Report Page, make these changes:

    1. Implementation - Select Interactive.

    2. Page Name - Change to Employees.

    3. Region Title - Change to Employees.

    4. Breadcrumb - Select Breadcrumb.

      The Create Breadcrumb Entry section appears.

    5. For Select Parent Entry, click the Home link.

      Home appears in the Parent Entry field.

    6. Click Next.

  7. For Define Report Page, accept the default, Do not use tabs, and click Next.

  8. For Define Report Page, press the Ctrl key, select the following columns, and click Next:

    • EMPLOYEE_ID

    • FIRST_NAME

    • LAST_NAME

    • HIRE_DATE

    • SALARY

    • COMMISSION_PCT

    The columns you selected appear on the Report page when the page is run.

  9. For Define Report Page, accept the defaults and click Next.

    Note that the default Edit Link image resembles a small piece of paper and pencil.

    Next, define the page and region information for the form.

  10. For Define Form Page, edit the following:

    1. Page Name - Enter Create/Edit Employee.

    2. Region Title - Enter Create/Edit Employee.

    3. Entry Name - Enter Create/Edit Employee.

    4. Click Next.

  11. In Primary Key, accept the defaults and click Next.

  12. In Define the source for the primary key columns, accept the default, Existing trigger, and click Next.

    Your table, OEHR_EMPLOYEES, already has a trigger that populates the primary key.

  13. In Select Column(s), select all columns and click Next.

    These columns appear in the Create/Edit Employee form when the page is run.

  14. In Identify Process Options, accept the defaults and click Next.

    These selections enable users to add, update, and create employee records.

  15. For Confirm, verify the information and click Finish.

Previewing the Pages

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

To preview the pages:

  1. Click the Run Page icon.

    Description of runpg_afterwiz.gif follows
    Description of the illustration runpg_afterwiz.gif

    The Employees report appears.

    Description of bldap_emprpt.gif follows
    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 allow 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. Since you created a form based on a table with a report, the wizard automatically linked the report to the form. If you create just a report, as is done in the previous Creating the Initial Application section, 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 five 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 and continue with these steps.

  2. To review the form you also created, click the Edit icon in an Employee row.

    The Create/Edit Employee form appears.

    Description of bldap_empeditfrm.gif follows
    Description of the illustration bldap_empeditfrm.gif

    Note the following about the Create/Edit Employee form:

    • The form includes Cancel, Delete, and Apply Changes buttons.

    • The Hire Date field displays a calendar, which is included because the data type of the underlying column is DATE.

    • Fields labeled in red require entries.

Editing the Employees Report

In this exercise, you create a new column titled Remuneration to display employees' salaries in the Employees report.

Topics in this section include:

Create a Function

First, create a function to calculate employees' salaries.

To create a function:

  1. Click the Home link on the Developer toolbar.

  2. Click SQL Workshop and then SQL Commands.

    SQL Commands provides a window where you can run PL/SQL against the database.

  3. In 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.

  4. Click Run.

    The Results section displays this message:

    Function created.
    

Add a Column

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

To add a column:

  1. Navigate to the Page Definition for page 3:

    1. Click the Application Builder tab.

    2. Select the AnyCo Corp application.

    3. Click 3 - Employees.

      The Page Definition for page 3, Employees, appears.

  2. Under Regions, click the Employees link (next to Interactive Report).

    Description of bldap_emprjn.gif follows
    Description of the illustration bldap_emprjn.gif

    The Region Definition appears.

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

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

  5. Click Apply Changes to confirm.

Previewing the Page

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

Description of bldap_emprpt.gif follows
Description of the illustration bldap_emprpt.gif

To display the Remuneration column:

  1. Select the Actions menu.

  2. Select the Select Columns action.

  3. Move the Remuneration column from the Do Not Display box to the Display in Report box.

    Description of bldap_addrem.gif follows
    Description of the illustration bldap_addrem.gif

  4. Click Apply.

    The Employees Report is displayed and includes the Remuneration column.

    Description of bldap_empfrmrem.gif follows
    Description of the illustration bldap_empfrmrem.gif

Changing the Column Format

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

To change the column format:

  1. Click Edit Page 3 on the Developer toolbar (at the bottom of the page).

    The Page Definition appears.

  2. Under Regions, click Interactive Report.

    The Interactive Report Attributes page appears.

  3. Locate the Column Attributes section.

    Next, change the format of the value in the Salary and Remuneration columns.

  4. To edit the Salary format:

    1. Click the Edit icon next to SALARY.

    2. For Number/Date Format, select $5,234.10 from the list.

      Notice that the appropriate format mask appears in the field.

    3. Click Apply Changes.

  5. Repeat the previous step for REMUNERATION.

  6. Click the Run Page 3 icon in the upper right corner to preview the page.

    Notice that the numbers in the Salary and Remuneration columns now appear with the dollar sign, include commas and decimals, and are correctly aligned.

    Description of bldap_emprptnum2.gif follows
    Description of the illustration bldap_emprptnum2.gif

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:

  1. Navigate to the Page Definition for the Home page, page 1:

    1. Click Application in the Developer toolbar (at the bottom of the page).

    2. On the Application home page, click Home.

  2. Under Regions, click the List link (next to Navigation).

  3. On the List Entries page, click the Create List Entry button on the right side of the page.

  4. On the Create/Edit List Entry page, edit the following:

    1. Sequence - Change to 20.

    2. List Entry Label - Enter Employees.

    3. Page - Enter 3.

  5. Click Create.

  6. Run the page.

    Description of bldap_homeemp.gif follows
    Description of the illustration bldap_homeemp.gif

    Notice that your Home page now includes a link to Employees.

  7. Click Employees to test the link. The Employees report appears.

Adding a Department Column to the Employees Reports

In this exercise, you link the Employees and Departments reports. When you complete this section, the Employees report will have 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 display. The report settings are saved as the default.

Topics in this section include:

Add a Column

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

To add a column:

  1. Click on Edit Page 3 in the Developer toolbar.

  2. Under Regions, click Employees and scroll down to Source.

  3. Replace Region Source with the following SQL:

    SELECT 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
    
  4. Click Apply Changes.

  5. Click Apply Changes to confirm.

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

Display Column in the Report

When you first run this report, use the Select Columns action to add the Department Name column to the display. Once 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:

  1. Click Run Page 3 icon.

  2. Select the Actions menu and select Select Columns from the list.

  3. Move the Department Name from the Do Not Display box to the last entry in the Display in Report box. Description of bldap_deptcol.gif follows
    Description of the illustration bldap_deptcol.gif

  4. Click Apply.

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

    Description of bldap_empdept.gif follows
    Description of the illustration bldap_empdept.gif

    Next, make these report settings the default.

Save Report Settings as the Default

This section describes how to save the current report settings as the default. Report settings are any setting made through the Actions menu including: selected columns, filters, sorting, control breaks, highlights, computed columns, aggregate columns, charts, flashback.

In this example, the default settings include the display of all columns with no additional Actions menu settings such as filters, sorting, highlights and so on in the report. Once the default settings are saved, these columns are the default display in this report:

  • Employee Id

  • First Name

  • Last Name

  • Hire Date

  • Salary

  • Commission Pct

  • Remuneration

  • Department Name

To save the current report settings as the default:

  1. Run the Employees report page.

    The current report default does not include the Department Name column when the report is displayed.

  2. Click the Actions menu.

  3. Select Save Report from the list.

  4. For Save, select As Default Report Settings from the list.

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

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 included in this section:

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:

  1. Navigate to the Page Definition for the Departments page, page 2.

  2. Under Regions, click Interactive Report.

    The Interactive Report Attributes page appears.

  3. Locate the Column Attributes section.

  4. In the Column Attributes section, click the Edit icon next to Number of Employees.

  5. Scroll down to the Column Link section.

  6. Under Column Link, make these changes:

    1. Link Text - Select [Number of Employees]

    2. Page - Enter 3.

    3. Select the Reset Pagination check box.

    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.

      Note:

      If you want to reset the interactive report to the default report settings instead of clear the report, replace CIR with RIR. The last saved default report settings are applied to the interactive report.
    5. Item 1, Name - Enter IR_DEPARTMENT_ID.

      By prefixing the item name with IR_, the URL item will be associated with an interactive report column rather than an item on the page.

    6. Item 1, Value - Select #Department ID#.

      The Column Link section shows all the options you selected.

      Description of bldap_setnumlink.gif follows
      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, the Employees report.

      • On the Employees report, the selected department is in focus.

      • The Employees report is cleared. This means 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.

      Note that in the Column Attributes section, the Number of Employees row contains a check mark in the Link column, reflecting the change you just made.

Run the Departments Report

Now it's time to 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.

  1. Click the Run Page 2 icon.

    The Departments report is displayed. Note that end users can now drill down on the data in the Number of Employees column.

    Description of bldap_numemplink.gif follows
    Description of the illustration bldap_numemplink.gif

  2. In the Number of Employees column, click the 3 for the Executive row.

    All employees that belong to the Executive department appear in the Employees report.

    Description of bldap_emplink.gif follows
    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. Notice the Clear Interactive Report command is included at the end of the URL in your browser, as shown in this example:

    f?p=9257:3:3672543193352902::NO:RP,3,CIR:IR_DEPARTMENT_ID:90
    

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. To show this, 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:

  1. Click the Actions menu and select Highlight.

    The Highlight options appear.

  2. For Highlight options make these changes:

    1. Name - Enter Neena

    2. Background Color - Select [yellow]

    3. Text Color - Select [blue]

    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 bldap_high.gif follows
    Description of the illustration bldap_high.gif

  3. Click the Home breadcrumb.

  4. Click Departments link.

  5. Click Number of Employees in the Executive row.

    The Employees report is displayed with the highlighting cleared, due to the CIR command specified in the column link.

Adding a Conditional Department Details Report

In this exercise, you add a report as a region that displays department details. You also create a condition so that the report displays department information only if you select a department. If you select No Department Assigned, the Department Details report does not appear.

Topics in this section include:

Creating the Report and Condition

First, create the report and set it to conditionally display.

To create the report and condition:

  1. Navigate to the Page Definition for page 3, Employees.

  2. Under Regions, click the Create icon.

  3. For Region, make these changes:

    1. Identify the type of region to add to this page - Select Report and click Next.

    2. Report Implementation - Accept the default, SQL Report, and click Next.

  4. For Display Attributes, make these changes:

    1. Title - Enter Department Details.

    2. Region Template - Select No Template.

    3. Sequence - Enter 7.

    4. Accept the remaining defaults and click Next.

  5. For Source, make this change:

    1. Enter the following query:

      SELECT count(e2.employee_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, 
              oehr_employees e2
          WHERE d.manager_id    = e.employee_id 
          AND d.location_id   = l.location_id 
          AND d.department_id = e2.department_id
          AND l.country_id    = c.country_id 
          AND nvl(d.department_id,'-1') = nvl(:P3_DEPARTMENT_ID,'-1')
          GROUP BY substr(e.first_name,1,1)||'. '||e.last_name,  c.country_name
      
    2. Click Next.

    Next, you create a condition that does not display department details when the user selects the No Department Assigned option.

  6. For Report Attributes, make these changes:

    1. Report Template - Select default: vertical report, look 1 (include null columns).

      This selection displays the list vertically.

    2. Accept the remaining defaults and click Next.

  7. For Conditional Display, make these changes:

    1. Condition Type - Select Value of Item in Expression 1 != Expression 2.

      Note the exclamation point (!) included after Expression 1 in the option to select.

    2. Expression 1 - Enter:

      P3_DEPARTMENT_ID
      
    3. Expression 2 - Enter:

      -1

      The -1 indicates no Department has been assigned.

    4. Click Create Region.

  8. Under Regions, click Create icon.

  9. Select HTML and click Next.

  10. Select HTML and click Next.

  11. For Create Region, make these changes:

    1. Title - Enter Department

    2. Region Template - Select No Template

    3. Sequence - Enter 5

    4. Click Create.

Add a List of Values

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

To create a list of values:

  1. Click Shared Components icon at the top right next to the Run Page 3 icon.

  2. Under User Interface, select Lists of Values.

  3. Click the Create button.

  4. Accept the default, From Scratch, and click Next.

  5. For Name, enter DEPARTMENTS and select Dynamic.

  6. Click Next.

  7. For the Query, enter the following SQL:

    SELECT department_name d, department_id v
        FROM oehr_departments
        ORDER BY d
    
  8. Click Create List of Values.

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:

  1. Navigate to the Page Definition for page 3, Employees.

  2. Under Items, click the Create icon.

  3. Select Select List and click Next.

  4. Select Select List with Submit and click Next.

  5. For Create Item, make these changes:

    1. Item Name - Enter P3_DEPARTMENT_ID

    2. Sequence - Enter 10

    3. Region - Select Department (1) 5

    4. Click Next.

  6. For Identify List of Values, make these changes:

    1. Named LOV - Select DEPARTMENTS

    2. Display Null Option - Select Yes

    3. Null Text - Enter - No Department Assigned -

    4. Null Value - Enter -1

    5. Click Next.

  7. For Label, enter Department and click Next.

  8. For Item Source Value, enter -1

  9. Click Create Item.

Add a Branch

You need to add a branch that returns to the Employees page 3 when the user chooses a department from the Department list of values.

  1. Under Branches, click the Create icon.

  2. Accept defaults and click Next.

  3. For Page, enter 3.

  4. Select reset pagination for this page and click Next.

  5. Accept defaults and click Create Branch.

Change Employees Report Query

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

To change the query:

  1. Under Regions, click the Employees link.

  2. Enter the following SQL for Region Source:

    SELECT  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'))
    
  3. Click Apply Changes.

Change Column Link on Department Report

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

To change the column link for Number of Employees:

  1. Click the Application ID breadcrumb.

  2. Click 2 - Departments.

  3. Under Regions, click the Interactive Report link.

  4. Click the Edit icon for Number of Employees.

  5. Under Column Link, enter P3_DEPARTMENT_ID for Item 1.

  6. Click Apply Changes.

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:

  1. Click the Application ID breadcrumb.

  2. Click 3 - Employees.

  3. Under Regions, click Report (next to Department Details).

  4. Scroll down to Layout and Pagination.

  5. For Pagination Scheme, select - No Pagination Selected -.

  6. Click Apply Changes.

  7. Run the page.

  8. To remove the Department id = 90 filter, select the Remove Filter icon next to the filter.

  9. From the Department list, select No Department Assigned.

    Description of bldapp_nodptassgn.gif follows
    Description of the illustration bldapp_nodptassgn.gif

    Employee Kimberely Grant appears. This employee is not currently assigned to a department. Therefore, no department details appear. The 1 - 1 you see on this form is part of the Employees report, not part of the Department Details region you just edited.

  10. From the Department list, select Marketing.

    Description of bldapp_dptdetail.gif follows
    Description of the illustration bldapp_dptdetail.gif

    Note the details under Department.

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:

  1. Navigate to the Application home page. Click the Application link on the Developer toolbar (at the bottom of the page).

  2. Click Shared Components.

  3. Under Application, click Definition.

    The Edit Application Definition page appears.

  4. Under Logo:

    1. Logo Type - Select Text.

    2. Logo - Enter AnyCo Corp.

    3. Logo Attributes - Select Black Text from the pop up list.

  5. Click Apply Changes.

  6. Click Application link on Developer toolbar.

  7. Click Run Application 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.

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:

  1. Click the Application link on the Developer toolbar (at the bottom of the page).

  2. On the Application home page, click Shared Components.

  3. Under User Interface, click Themes.

    Description of bldap_sharcomp.gif follows
    Description of the illustration bldap_sharcomp.gif

  4. On the Themes page, click Create.

    The Create Theme Wizard appears.

  5. For Method, accept the default, From the Repository, and click Next.

  6. For Identify Theme, select Theme 11 and click Next.

  7. For Confirm, click Create.

  8. Click Switch Theme.

  9. For Identify Theme, accept the default, 11. Round Green, and click Next.

  10. 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.

  11. For Confirm Switch, click Switch Theme.

  12. Run the page.

    Description of bldap_newtheme2.gif follows
    Description of the illustration bldap_newtheme2.gif

    The color scheme and display layouts are changed in all your pages.