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.

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 the illustration reg_sect.gif
   
   

3. Click Departments.

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

   SELECT d.department_id           "Department ID",
       d.department_name            "Department Name",
       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 instr(upper(d.department_name),upper(nvl(:P2_REPORT_SEARCH,d.department_name))) > 0
       GROUP BY d.department_id,
       d.department_name,
       substr(e.first_name,1,1)||'. '||e.last_name,  c.country_name
   

5. Click Apply Changes.

Running the Page

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:

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

  
  Description of the illustration runpg_iconrow.gif
  
  

The revised Departments report appears.

Description of the illustration bldap_deptexp.gif

Note that the revised report has three additional columns: Number of Employees, Manager Name, and Location. Also note that your data might appear in a different order. You can ignore this difference and continue with the steps.