Skip Headers
Oracle® HTML DB 2 Day Developer
Release 2.0
Part Number B16376-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Master Index
Master Index		 Go to Feedback page
Feedback
Go to previous page
Previous		 Go to next page
Next
View PDF

3 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 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:

About Sample Application

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:

  1. Log in to Oracle HTML DB.

  2. Click the down arrow on the right side of the Application Builder icon.

  3. From the menu, select Demonstrations.

    The Demonstration Applications page appears, displaying links to the following applications:

    • Sample Application offers a working demonstration that highlights basic design concepts

    • Collection Showcase demonstrates shopping cart concepts

    • Web Services serves an example of how you can use Web Services

    • Presidential Inaugural Addresses demonstrates Oracle Text

  4. Locate Sample Application and check the Status column:

    1. If the Status column displays Installed, return to the Workspace home page.

    2. If the Status column displays Not Installed, select Install in the Action column.

    3. Follow the on-screen instructions.

Creating a New Application

First, create a new application.

To create an application:

  1. From the Workspace home page, click the Application Builder icon.

  2. From the Application Builder home page, click Create.

  3. Select Create Application and click Next.

  4. Specify the page name.

    1. For Name, enter Drilldown Reports.

    2. For Application, accept the default ID.

    3. For Create Application, select From scratch.

    4. 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 will add two blank pages.

  5. Add the first blank page:

    1. Under Select Page Type, select Blank and click Add Page.

      The page appears in the list at the top of the page.

    2. Select the Name Page 1.

    3. In Page Name, enter Orders and click Apply Changes.

  6. Add the second blank page:

    1. Under Select Page Type, select Blank and click Add Page.

      The page appears in the list at the top of the page.

    2. Select the Name Page 2.

    3. In Page Name, enter Order Items and click Apply Changes.

  7. Click Next.

  8. For Tabs, accept the default, One Level of Tabs, and click Next.

  9. For Copy Shared Components from Another Application, accept the default, No, and click Next.

  10. For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preferences Derived From and click Next.

  11. For User Interface, select Theme 2 and click Next.

  12. Review your selections and click Create.

Creating Reports for DEMO_ORDERS and DEMO_ORDER_ITEMS

Next, you need to create reports for the DEMO_ORDERS and the DEMO_ORDER_ITEMS tables.

Topics in this section include:

Create a Report for DEMO_ORDERS

To create a report on the DEMO_ORDERS table:

  1. On the Application home page, click Create Page.

  2. For the page type, select Report and click Next.

  3. For Page, select Wizard Report and click Next.

  4. For Page Attributes:

    1. For Page, select 1 Orders.

    2. In Page Title and Region Title, enter Orders.

    3. Click Next.

  5. For Tabs, accept the default, Do not use tabs, and click Next.

  6. For Tables and Columns:

    1. For Table/View Owner, select the appropriate schema.

    2. For Table/View, select DEMO_ORDERS.

    3. From the Available Columns list, press CTRL and 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.

    4. From the Table/View list, select DEMO_CUSTOMERS.

    5. From the Available Columns list, select CUST_LAST_NAME and move it to the Display Columns list.

    6. Click Next.

  7. For Join Conditions, accept the join by clicking Next.

  8. For Report Options, accept the defaults and click Next. Click Create Report Page.

  9. Select Edit Application from the Developer Toolbar to return to Application Builder.

Create a Report for DEMO_ORDER_ITEMS

To create a report on the DEMO_ORDER_ITEMS table:

  1. On the Application home page, click Create Page.

  2. For Page, select Report and click Next.

  3. On Create Page, select Wizard Report and click Next.

  4. For Page Attributes:

    1. For Page, select 2 Order Items.

    2. In Page Title and Region Title, enter Order Items.

    3. Click Next.

  5. For Tabs, accept the default Do not use tabs and click Next.

  6. For Tables and Columns:

    1. For Table/View Owner, select the appropriate schema and click Next.

    2. For Table/View, select DEMO_ORDER_ITEMS.

    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 DEMO_PRODUCT_INFO table to display the product name.

    4. For Show Only Related Tables, select No.

    5. From the Table/View list, select DEMO_PRODUCT_INFO.

    6. From the Available Columns list, select PRODUCT_NAME and move it to the Display Columns list.

    7. Click Next.

  7. For Report Options, accept the defaults and click Next. Click Create Report Page.

  8. Run by page by clicking Run Page.

    As shown inFigure 3-1, a report on the DEMO_ORDER_ITEMS table appears.

    Figure 3-1 Report on DEMO_ORDER_ITEMS Table

    Description of drill_demo_order.gif follows
    Description of the illustration drill_demo_order.gif

Customizing the DEMO_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 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:

Add an Item to Hold the Value of ORDER_ID

To create an item to hold the value of ORDER_ID:

  1. Navigate to the Page Definition of page 2 by selecting Edit Page 2 from the Developer Toolbar.

  2. Under Items, click the Create icon.

  3. For Item Type, select Hidden and click Next.

  4. For Display Position and Name:

    1. For the Item Name, enter P2_ORDER_ID.

    2. For Region, select Order Items.

    3. Click Next.

  5. Click Create Item.

Add a Condition

To add a condition to the DEMO_ORDER_ITEMS report:

  1. Under Regions, select Order Items.

  2. Select the Query Definition tab.

  3. Click Add/Modify Conditions.

  4. On the Conditions page:

    1. From the Columns list, select ORDER_ID [DEMO_ORDER_ITEMS].

    2. Enter the following in the Conditions field:

      = :P2_ORDER_ID

  5. Click Apply Changes.

Modify the Region Title

To modify the region title of the DEMO_ORDER_ITEMS report:

  1. Under Regions, click Order Items.

  2. In Title replace this existing text with the following:

    Order Items for Order # &P2_ORDER_ID.

  3. Click Apply Changes.

Linking the DEMO_ORDERS Report to the DEMO_ORDER_ITEMS Report

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:

  1. Navigate to the Page Definition of page 1, Orders. Enter 1 in the Page field and click Go.

  2. Under Regions, select Orders.

  3. Click the Report Attributes tab.

  4. Click the Edit icon adjacent to ORDER_ID.

  5. Scroll down to Column Link:

    1. In the Page field, select 2 Order Items.

    2. From the Item 1 Name list, select P2_ORDER_ID.

    3. From the Item 1 Value list, select #ORDER_ID#.

    4. From the Link Text list, select #ORDER_ID#.

  6. Click Apply Changes.

  7. Click the Run Page icon in the upper right corner of the page.

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

    Figure 3-2 DEMO_ORDERS Report with LInk to Page 2

    Description of drill_link.gif follows
    Description of the illustration drill_link.gif

Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Master Index
Master Index		 Go to Feedback page
Feedback