Linking the Employees and Departments Reports

In this exercise, you link the Employees and Departments reports. To perform this task, you create a region and item on the Employees page to enable the user to select a department. You then modify the Employees report to only display the employees that belong to the selected department.

Topics:

Creating a Region
Creating an Item
Linking the Item to the Report
Creating a Branch

Creating a Region

First, create a region on the Employees page to hold the Department select list.

To create a region:

Navigate to the Page Definition for the Employees page, page 3.
In the Regions section, click the Create icon.
For Region:
1. Identify the type of region to add to this page - Accept the default, HTML, and click Next.
2. Select the type of HTML region container you want to create - Accept the default, HTML, and click Next.
For Display Attributes:
1. Title - Enter Department.
2. Region Template - Select No Template.
  
  This option adds a region without a region title.
3. Sequence - Change to 5 so that this region displays above Employees.
4. Accept the remaining defaults and click Next.
Click Create Region.

Note the Regions section now lists a Department region of type HTML.

Description of the illustration reg_sect_depthtml.gif

Creating an Item

Next, create an item in the Department regions. The item is a select list using the Department list of values.

To create an item:

Under Items, click the Create icon.
For Item Type:
1. Select Item Type - Select Select List and click Next.
2. Select List Control Type - Select Select List with Submit and click Next.
  
  This option refreshes the page whenever a change is made to the select list.
For Display Position and Name:
1. Item Name - Change to P3_DEPARTMENT_ID.
2. Sequence - Accept the default, 10.
3. Region - Select Department (1) 5.
4. Click Next.
For List of Values:
1. Named LOV - Select DEPARTMENTS.
  
  Departments appears in the select list because you created it as a shared component, which enables you to use it in other pages.
2. Display Null Option - Accept the default, Yes.
3. Null Text - Enter the following:
```
- No Department Assigned -
```
4. Null Value - Enter the following:
```
-1
```
  Entering a null value makes it easier for you to write your query and to default your value to that entry. In this case, whenever a user selects - No Department Assigned -, the session state for this item is set to -1, and you can use that value in a query.
5. Click Next.
For Item Attributes, change the Label to Department and click Next.
For Source:
1. Default - Enter -1.
2. Accept the remaining defaults.
3. Click Create Item.

Linking the Item to the Report

At this point, you have created the item, but it is not linked to the report. To link it to the report, you must edit the Region Source and add a WHERE clause.

To link the item to the report:

Under Regions, click the Employees link (next to Report).
Scroll down to Source.
In Region Source, add the following after the existing code:
```
WHERE (DEPARTMENT_ID = :P3_DEPARTMENT_ID or  (DEPARTMENT_ID is null and nvl(:P3_DEPARTMENT_ID,'1') = '-1'))
```
This WHERE clause changes the query to display only those employees that belong to the selected department.
Click Apply Changes.

Creating a Branch

When a page is submitted, the branches defined for the page determine which page to display next. Because you want this page to redisplay when a user submits it, create a branch to the same page.

To create a branch:

Under Page Processing, Branches, click the Create icon.

Description of the illustration pgproc_branch.gif
For Point and Type, accept the defaults and click Next.
For Target:
1. Page - Enter 3.
2. Select the reset pagination for this page check box.
  
  When you select the reset pagination option, the application displays the first set of data meeting a user's query. Otherwise, if the user was on the third page of data and then selected another department, the user would see the third page of data for the revised query.
3. Click Next.
Click Create Branch.
Run the page.
From the Department select list, select Accounting.

Note that the list displays only those employees belonging to that department.

Description of the illustration bldap_deptfilter.gif