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:

Create the Report and Condition
Turn Off Pagination

Create the Report and Condition

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

To create the report and condition:

Navigate to the Page Definition for page 3, Employees.
Under Regions, click the Create icon.
For Region:
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.
For Display Attributes:
1. Title - Enter Department Details.
2. Region Template - Select No Template.
3. Sequence - Enter 7.
4. Accept the remaining defaults and click Next.

For Source:

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

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

This selection displays the list vertically.
Accept the remaining defaults and click Next.

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

For Conditional Display:
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
4. Click Create Region.

Turn Off Pagination

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

To turn off pagination:

Under Regions, click Report next to Department Details.
Scroll down to Layout and Pagination.
For Pagination Scheme, select - No Pagination Selected.
Click Apply Changes.
Run the page.
From the Department list, select No Department Assigned.

Description of the illustration bldapp_nodptassgn.gif

Note that the employee Kimberley Grant appears. This employee is not currently assigned to a department. Therefore, no department details appear. Also note that the 1 - 1 you see on this form is part of the Employees report, not part of the Department Details region you just edited.
From the Department list, select Marketing.

Description of the illustration bldapp_dptdetail.gif

Note the details under the Department field.