Oracle® Database Express Edition 2 Day Plus Application Express Developer Guide Release 2.1 Part Number B25310-01 |
|
|
View PDF |
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:
Application Builder installs with a number of demonstration applications. In this exercise you create a drilldown report using the tables that install with the demonstration application, Sample Application.
To see if Sample Application is installed:
Log in to the Database Home Page using your database username and password.
Click the down arrow on the right side of the Application Builder icon.
From the menu, select Demonstrations.
The Demonstration Applications page appears, displaying links to demonstration applications.
Locate Sample Application and check the Status column:
If the Status column displays Installed, return to the Database 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:
On the Database Home Page, click the Application Builder icon.
The Application Builder home page appears.
Click Create.
Select Create Application and click Next.
Specify the page name.
For Name, enter Drilldown Reports
.
For Application, accept the default.
For Create Application, select From scratch.
For Schema, accept the default.
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.
Add the first blank page:
Under Select Page Type, select Blank as shown in Figure 3-1.
In Page Name, enter Orders
.
Click Add Page.
Add the second blank page:
Under Select Page Type, select Blank.
In Page Name, enter Order Items.
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 your application contains three pages:
1 - Orders
2 - Order Items
101 - Login
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 home page, click Create Page.
Select the page type Report and click Next.
Select Wizard Report and click Next.
For Page Attributes:
For Page, select 1 Orders.
In Page Title and Region Title, enter Orders
.
For Region Template, accept the default.
Click Next.
For Tabs, accept the default, Do not use tabs, and click Next.
For Tables and Columns:
For Table/View Owner, select the default.
For Table/View, select DEMO_ORDERS.
The columns in the DEMO_ORDERS table appear.
From the Available Columns list, press CTRL and move the following columns to the Displayed Columns list as shown in Figure 3-2:
ORDER_ID, ORDER_TOTAL, ORDER_TIMESTAMP
Next, create a join with the DEMO_CUSTOMERS
table to display the customer name. First, select a new table.
From the Table/View list, select DEMO_CUSTOMERS.
The columns in the DEMO_CUSTOMERS table appear.
From the Available Columns list, select CUST_LAST_NAME and move it to the Display Columns list.
Click Next.
For Join Conditions, accept the join by clicking 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 database user account credentials.
As shown in Figure 3-3, a report on the DEMO_ORDERS
table appears.
Click Edit Application on the Developer toolbar to return to Application Builder.
The Application home page appears.
To create a report on the DEMO_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:
For Page, select 2 Order Items
.
In Page Title and Region Title, enter Order Items
.
For Region Template, accept the default.
Click Next.
For Tables and Columns:
For Table/View Owner, accept the default.
For Table/View, select DEMO_ORDER_ITEMS.
The columns in the DEMO_ORDER_ITEMS table appear.
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 DEMO_PRODUCT_INFO
table to display the product name.
For Show Only Related Tables, select No.
Then, select a new table.
From the Table/View list, select DEMO_PRODUCT_INFO.
The columns in the DEMO_PRODUCT_INFO
table appear.
From the Available Columns list, select PRODUCT_NAME and move it to the Display Columns list.
Click Next.
For Join Conditions:
Under the first Column, select DEMO_ORDER_ITEMS.PRODUCT_ID.
Under the second Column, select DEMO_PRODUCT_INFO.PRODUCT_ID.
Click Next.
For Report Options, accept the defaults and click Next. Click Create Report Page.
Click Run Page.
As shown in Figure 3-4, a report on the DEMO_ORDER_ITEMS
table appears.
Figure 3-4 Report on DEMO_ORDER_ITEMS Table
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. Click Edit Page 2 on the Developer toolbar.
The Page Definition appears.
Under Items, click the Create icon as shown in Figure 3-5.
For Item Type, select Hidden and click Next.
For Display Position and Name:
In Item Name, enter P2_ORDER_ID
.
In Sequence, accept the default.
For Region, select Order Items.
Click Next.
Click Create Item.
To add a condition to the DEMO_ORDER_ITEMS report:
Under Regions, select Order Items as shown in Figure 3-6.
Select the Query Definition tab.
Click Modify Join Conditions.
On the Modify Join Conditions page:
For first Column, select DEMO_ORDER_ITEMS.ORDER_ID
. Note that you may need to click Search to view available columns.
Enter the following condition in the second Column field:
= :P2_ORDER_ID
Click Apply Changes.
To modify the region title of the DEMO_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.
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, Orders. On the Page Definition, 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 Order Items.
Next, populate the P2_ORDER_ID hidden item on page 2 with the clicked ORDER_ID.
From Item 1 Name, select P2_ORDER_ID.
From Item 1 Value, select #ORDER_ID#.
For Link Text, select #ORDER_ID#.
Your Column Link attributes should resemble Figure 3-7.
Figure 3-7 Column Link Attributes for the Order Id column
Scroll back 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 3-8, you can link to page 2 by clicking on an Order Id.
Figure 3-8 DEMO_ORDERS Report with LInk to Page 2