A drill down report is a type of report that displays summary data with links to related detail data in a second report.
This tutorial describes how to create a report on the DEMO_ORDERS
table with links to drill down detail data in the DEMO_ORDER_ITEMS
table. Both tables are installed with the demonstration application, Sample Application.
This section contains the following topics:
Oracle HTML DB installs with a number of demonstration applications. To complete this exercise you must install the demonstration application, Sample Application.
To see if Sample Application is installed:
Log in to Oracle HTML DB.
From the Workspace Administration list, select Review Demonstration Applications.
Locate Sample Application and check the Status column:
If the Status column displays Installed, return to the Workspace home page.
If the Status column displays Not Installed, select Install in the Action column.
Follow the on-screen instructions.
First, create a new application.
To create an application:
From the Application Builder home page, click Create Application.
On Select Creation Method, select From Scratch and click Next.
On Create Application:
For Name, enter Drilldown Reports
.
From Pages, select 2.
Click Next.
For Tabs, accept the default One Level of Tabs and click Next.
For Page Names:
For Page 1, enter Orders.
For Page 2, enter Order Items.
Click Next.
For Page Tab Name, accept the defaults and click Next.
On Theme, select Theme 2 and click Next.
Review your selections and click Create Application.
Next, you need to create reports for the DEMO_ORDERS
and the DEMO_ORDER_ITEMS
tables.
Topics in this section include:
To create a report on the DEMO_ORDERS
table:
On the Application Builder home page, click Create Page.
Select Page with Component and click Next.
On Select Component Type, select Report and click Next.
On Create Page, select Easy Report and click Next.
On Identify Page Attributes:
For Page, enter 1.
In Page Title and Region Title, enter Orders
.
Click Next.
On Identify Tabs, accept the default Do not use tabs and click Next.
On Identify Tabls(s) and Columns:
For Table/View Owner, select the appropriate schema.
For Table/View, select DEMO_ORDERS.
From the Available Columns list, move the following columns to the Displayed Columns list:
ORDER_ID, ORDER_TOTAL, ORDER_TIMESTAMP
Next, create a join with the DEMO_CUSTOMERS
table to display the customer name.
From the Table/View list, select DEMO_CUSTOMERS.
From the Available Columns list, select CUST_LAST_NAME and move it to the Display Columns list.
Click Next.
On Join Conditions, accept the join by clicking Next.
On Identify Report Options, accept the defaults and click Next. Click Create Report Page.
Run by page by clicking Run Page. If prompted for a user name and password, enter your workspace credentials in lowercase letters.
As shown in Figure 3-1, a report on the DEMO_ORDERS
table appears.
Select Edit Application from the Developer Toolbar to return to Application Builder.
To create a report on the DEMO_ORDER_ITEMS
table:
On the Application Builder home page, click Create Page.
Select Page with Component and click Next.
On Select Component Type, select Report and click Next.
On Create Page, select Easy Report and click Next.
On Identify Page Attributes:
For Page, enter 2.
In Page Title and Region Title, enter Order Items
.
Click Next.
On Identify Tabs, accept the default Do not use tabs and click Next.
On Identify Tabls(s) and Columns:
For Table/View Owner, select the appropriate schema and click Next.
For Table/View, select DEMO_ORDER_ITEMS.
From the Available Columns list, move the following columns to the Displayed Columns list:
ORDER_ITEM_ID, ORDER_ID, UNIT_PRICE, QUANTITY
Next, create a join with the DEMO_PRODUCT_INFO
table to display the product name.
For Show Only Related Tables, select No.
From the Table/View list, select DEMO_PRODUCT_INFO.
From the Available Columns list, select PRODUCT_NAME and move it to the Display Columns list.
Click Next.
On Identify Report Options, accept the defaults and click Next. Click Create Report Page.
Run by page by clicking Run Page.
As shown inFigure 3-2, a report on the DEMO_ORDER_ITEMS
table appears.
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 condition 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:
To create an item to hold the value of ORDER_ID:
Navigate to the Page Definition of page 2. Select Edit Page from the Developer Toolbar.
Under Items, click the Create icon.
On Choose Item Type, select Hidden and click Next.
For the Item Name, enter P2_ORDER_ID
and click Next.
Click Create Item.
To add a condition to the DEMO_ORDER_ITEMS report:
Under Regions, select Order Items.
Select the Query Definition tab.
Click Add/Modify Conditions.
On the Conditions page:
From the Columns list, select ORDER_ID [DEMO_ORDER_ITEMS].
Enter the following in the Conditions field:
= :P2_ORDER_ID
Click Apply Changes.
Lastly, you link the DEMO_ORDERS report to the DEMO_ORDER_ITEMS report. To accomplish this, you edit the attributes of the ORDER_ID
column on the DEMO_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 Orders report to the Order Items report:
Navigate to the Page Definition of page 1. Enter 1
in the Page field and click Go.
Under Regions, select Orders.
Click the Report Attributes tab.
Click the Edit icon adjacent to ORDER_ID.
Scroll down to Column Link:
In the Page field, select 2.
From the Item 1 Name list, select P2_ORDER_ID.
From the Item 1 Value list, select #ORDER_ID#.
From the Link Text list, select #ORDER_ID#.
Click Apply Changes.
Click the Run Page icon.
As shown in Figure 3-3, you can link to page 2 by clicking on an Order Id.