4 How to Create a Drill Down Report

A drill down report is a type of report that displays summary data with links to related detail data in other reports.

In this tutorial, you create a report on the OEHR_ORDERS table that contains links to drill down to additional data in the OEHR_ORDER_ITEMS table. Additionally, you also learn how to change the format of a column by editing column attributes.

Before you begin, you need to import and install the OEHR Sample Objects application in order to access the necessary sample database objects. See "About Loading Sample Objects".

This section contains the following topics:

Creating a New Application
Creating Reports for OEHR_ORDERS and OEHR_ORDER_ITEMS
Customizing the ORDER_ITEMS Report
Linking the ORDERS Report to the ORDER_ITEMS Report

Creating a New Application

First, create a new application.

To create an application:

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

The Application Builder home page appears.
Click Create.
Select Create Application and click Next.
For Name, specify the following:
1. Name - Enter Drilldown Reports.
2. Application - Accept the default.
3. For Create Application - Accept the default, From scratch.
4. For Schema - Select the schema where you installed the OEHR sample objects.
5. Click Next.
  
  Next, you need to add pages. You have the option of adding a blank page, a report, a form, a tabular form, or a report and form. For this exercise, you add two blank pages.
First, add the first blank page. Under Add Page:
1. Select Page Type - Accept the default, Blank, as shown in Figure 4-1.
  
  Figure 4-1 Add Page
  
  Description of "Figure 4-1 Add Page"
2. Page Name - Enter Orders.
3. Click Add Page.
Add the second blank page. Under Add Page:
1. Select Page Type - Select Blank.
2. Subordinate to Page - Accept the default.
3. Page Name - Enter Order Items.
4. Click Add Page.
The two new pages appear at the top of the page.
Click Next.
For Tabs, accept the default, One Level of Tabs, and click Next.
For Copy Shared Components from Another Application, accept the default, No, and click Next.
For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and click Next.
For User Interface, select Theme 2 and click Next.
Review your selections and click Create.

The Application home page appears. Note that your application contains three pages:
- 1 - Orders
- 2 - Order Items
- 101 - Login

Creating Reports for OEHR_ORDERS and OEHR_ORDER_ITEMS

Next, you need to create reports for the OEHR_ORDERS and the OEHR_ORDER_ITEMS tables.

Topics in this section include:

Create a Report for OEHR_ORDERS
Create a Report for OEHR_ORDER_ITEMS

Create a Report for OEHR_ORDERS

To create a report on the OEHR_ORDERS table:

On the Application home page, click Create Page.
For page type, select Report and click Next.
Select Wizard Report and click Next.
For Page Attributes:
1. Page Number - Enter 1.
2. Page Title - Enter Orders.
3. Region Title - Enter Orders.
4. For Region Template, accept the default.
5. For Breadcrumb, accept the default.
6. Click Next.
For Tables and Columns:
1. For Table/View Owner, select the default.
2. For Table/View, select OEHR_ORDERS.
  
  The columns in the OEHR_ORDERS table appear.
3. From the Available Columns list, press Ctrl to select and move the following columns to the Displayed Columns list as shown in Figure 4-2:
```
ORDER_ID, ORDER_TOTAL, ORDER_DATE
```
  Figure 4-2 Selected Columns
  
  Description of "Figure 4-2 Selected Columns"
  
  Next, create a join with the OEHR_CUSTOMERS table to display the customer name. First, select the table.
4. From the Table/View list, select OEHR_CUSTOMERS.
  
  The columns in the OEHR_CUSTOMERS table appear.
5. From the Available Columns list, select CUST_LAST_NAME and move it to the Displayed Columns list.
6. Click Next.
For Join Conditions, accept the defaults and click Next.
For Report Options, accept the defaults and click Next.Click Create Report Page.
Run the page by clicking the Run Page icon. If prompted for a user name and password, enter your workspace user name and password and click Login. See "About Application Authentication".

As shown in Figure 4-3, a report on the ORDERS table appears.

Figure 4-3 Report on OEHR_ORDERS Table

Description of "Figure 4-3 Report on OEHR_ORDERS Table"

Note the report displays four columns: Order Id, Order Date, Cust Last Name, and Order Total. Also notice the format of the Order Date and Order Total Columns. Also note that your data might appear in a different order. You can ignore this difference and continue with the steps.

Next, you will change the format of these two columns.

Change the Format of Order Date Column

To change the format of the Order Date column:

Click Edit Page 1 on the Developer toolbar.

The Page Definition for page 1 appears.
Under Regions, click the Report link as shown in Figure 4-4.

Figure 4-4 Report Link

Description of "Figure 4-4 Report Link"

The Report Attributes page appears.
Click the Edit icon next to ORDER_DATE. The Edit icon resembles a small page with a pencil on top of it.

The Column Attributes page appears.
Locate the section Column Formatting.
From Number/Date Format, select the date format DD-MON-YYYY (for example, 12-JAN-2004).
Click Apply Changes.

The Report Attributes page appears.

Next, change the format of the Order Total Column.

Change the Format of Order Total Column

To change the format of the Order Total column:

Click the Edit icon next to ORDER_TOTAL.

The Column Attributes page appears.
Locate the section Column Formatting.
From Number/Date Format, select the number format that includes a dollar sign (for example, $5, 234.10).
Click Apply Changes.
Run the page by clicking the Run Page icon in the upper right corner as shown in Figure 4-5.

Figure 4-5 Run Page Icon

Description of "Figure 4-5 Run Page Icon"

As shown in Figure 4-6, the revised report on the OEHR_ORDERS table appears.

Figure 4-6 Report on OEHR_ORDERS Table with New Column Formats

Description of "Figure 4-6 Report on OEHR_ORDERS Table with New Column Formats"
Click Application on the Developer toolbar to return to the Application home page.

Create a Report for OEHR_ORDER_ITEMS

To create a report on the OEHR_ORDER_ITEMS table:

On the Application home page, click Create Page.
For Page, select Report and click Next.
On Create Page, select Wizard Report and click Next.
For Page Attributes:
1. Page Number - Enter 2.
2. Page Title - Enter Order Items.
3. Region Title - Enter Order Items.
4. For Region Template, accept the default.
5. For Breadcrumb, accept the default.
6. Click Next.
For Tables and Columns:
1. Table/View Owner - Accept the default.
2. Table/View - Select OEHR_ORDER_ITEMS.
  
  The columns in the OEHR_ORDER_ITEMS table appear.
3. From the Available Columns list, press Ctrl and move the following columns to the Displayed Columns list:
```
ORDER_ITEM_ID, ORDER_ID, UNIT_PRICE, QUANTITY
```
  Next, create a join with the OEHR_PRODUCT_INFORMATION table to display the product name.
4. For Show Only Related Tables, select No.
  
  Next, select the table.
5. From the Table/View list, select OEHR_PRODUCT_INFORMATION.
  
  The columns in the OEHR_PRODUCT_INFORMATION table appear.
6. From the Available Columns list, select PRODUCT_NAME and move it to the Displayed Columns list.
7. Click Next.
For Join Conditions, accept the defaults and click Next.
For Report Options, accept the defaults and click Next. Click Create Report Page.
Click Run Page.

As shown in Figure 4-7, a report on the OEHR_ORDER_ITEMS table appears.

Figure 4-7 Report on OEHR_ORDER_ITEMS Table

Description of "Figure 4-7 Report on OEHR_ORDER_ITEMS Table"

Customizing the ORDER_ITEMS Report

Next, you need to customize the Order Items page. In this exercise, you add an item to hold the value of the ORDER_ID, add a WHERE clause that constrains the report by the value of ORDER_ID item, and modify the Region Title to note which order is being viewed.

Topics in this section include:

Add an Item to Hold the Value of ORDER_ID
Add a Where Clause to Restrict the Report
Modify the Region Title

Add an Item to Hold the Value of ORDER_ID

To create an item to hold the value of ORDER_ID:

Click Edit Page 2 on the Developer toolbar.

The Page Definition appears.
Under Items, click the Create icon as shown in Figure 4-8.

Figure 4-8 Create Icon

Description of "Figure 4-8 Create Icon "
For Item Type, select Hidden and click Next.
For Display Position and Name:
1. Item Name - Enter P2_ORDER_ID.
2. Sequence - Accept the default.
3. Region - Select Order Items.
4. Click Next.
Click Create Item.

Add a Where Clause to Restrict the Report

Next, you add a WHERE clause that constrains the report by the value of ORDER_ID item.

To add a WHERE to the ORDER_ITEMS report:

Under Regions, select Order Items as shown in Figure 4-9.

Figure 4-9 Order Items

Description of "Figure 4-9 Order Items"
Click the Query Definition tab.
Click Modify Join Conditions.
On the Modify Join Conditions page:
1. For the first Column, select OEHR_ORDER_ITEMS.ORDER_ID. Note that you may need to click Search to view available columns.
2. In the second Column field, replace the existing text with the following condition:
```
:P2_ORDER_ID
```
Click Apply Changes.

Modify the Region Title

To modify the region title of the ORDER_ITEMS report:

Under Regions, click Order Items.
In Title, replace the existing text with the following:
```
Order Items for Order # &P2_ORDER_ID.
```
Click Apply Changes.

Linking the ORDERS Report to the ORDER_ITEMS Report

Lastly, you link the OEHR_ORDERS report to the OEHR_ORDER_ITEMS report. To accomplish this, you must edit the attributes of the ORDER_ID column on the OEHR_ORDERS report and create a link. The link will populate the P2_ORDER_ID hidden item on page 2 with the clicked ORDER_ID.

To create a link from the ORDER_ID column on the OEHR_ORDERS report to the OEHR_ORDER_ITEMS report:

On the Page Definition, enter 1 in the Page field in the Navigation bar and click Go.
Under Regions, click Orders.
Click the Report Attributes tab.
Click the Edit icon next to ORDER_ID.
Scroll down to Column Link.
1. In the Page field, select 2 Order Items.
  
  Next, populate the P2_ORDER_ID hidden item on page 2 with the clicked ORDER_ID.
2. From Item 1 Name, select P2_ORDER_ID.
3. From Item 1 Value, select #ORDER_ID#.
4. For Link Text, select #ORDER_ID#.
Your Column Link attributes should resemble Figure 4-10.

Figure 4-10 Column Link Attributes for the ORDER_ID Column

Description of "Figure 4-10 Column Link Attributes for the ORDER_ID Column"
Scroll to the top of the page and click Apply Changes.
Click the Run Page icon in the upper right corner of the page.

As shown in Figure 4-11, you can link to page 2 by clicking an Order Id.

Figure 4-11 OEHR_ORDERS Report with Link to Page 2

Description of "Figure 4-11 OEHR_ORDERS Report with Link to Page 2"