How-To Document

Extend an Easy Report

Date: 25-Nov-2003
Based on Oracle Application Express (formerly called HTML DB) version 1.5

After completing this How-To, you should be able to understand:

  • adding search capability to a report created with the Easy Report Wizard
  • adding complex and compound conditions to a report created with the Easy Report Wizard

Table of Contents

 

Introduction

The Easy Report wizard allows you to create a report by choosing columns from tables instead of composing a SQL statement. After creating the report with the wizard, it may become necessary to extend the report by adding search capability or complex query conditions. This document aims to provide instructions for extending reports created using the Easy Report Wizard.


Software Requirements

 

Create the Easy Report

To create an Easy Report on the EMP table:

  • Navigate to the Application Builder.
  • Choose an application and click Go.
  • Click the Wizards tab.
  • Under Reports, click Easy Report.
  • In the Easy Report Wizard:
    • Enter a page, a region title, a page title and choose a region template.
    • Specify tab options.
    • Choose all columns in the EMP table, see figure 1.
    • Optionally choose a report template, or accept the default.
    • Click Create Report Page.

Easy report on employee table

Figure 1: Easy Report on EMP Table

 

Extend Easy Report with Search Capability

To add search capability, first add a text field item for the search criterion and a go button. Next, add a condition to the Easy Report to consider the search criterion. In this example, you will make the ENAME column searchable.

To add the search text field item:

  • Navigate to the Page Definition of the page you created the Easy Report on the EMP table.
  • Under Items, click Create.
  • In the Create Item Wizard:
    • Enter SEARCH as the item name.
    • Ensure the item is displayed as a Text Field.
    • Click Create Item.

To add the Go button:

  • Under Buttons, click Create.
  • In the Create Button Wizard:
    • Choose Create a button displayed among this region's items.
    • Enter GO as the button name.
    • Uncheck Beginning on New Field.
    • Click Create Button.

Running the page should look similar to the following figure.

Adding Search Field and Go Button

Figure 2: Adding a Search Field and Go Button

To add the condition to the Easy Report:

  • Navigate to the Page Definition of the page you created the Easy Report on the EMP table.
  • Under Regions, click RPT: next to the name of the Easy Report.
  • Click Query Definition tab.
  • Click Add/Modify Conditions.
  • Choose ENAME from any of the column select lists.
  • Enter like '%'||upper(:search)||'%'in the conditions text box next to ENAME you chose in the select list. (See figure 3.)
  • Click Apply Changes.

Adding a Condition to the Easy Report

Figure 3: Adding a Condition to the Easy Report

Running the report page now should allow you to search by ENAME.

Adding Complex Conditions to the Easy Report

The report is now constrained by the user input for ENAME, but you may have the need for more complex criteria. For example, you may wish to only show records that match the search criterion, and are in departments 10 and 20, but you always want to show rows where the total compensation is over 2500. To accomplish this, you add another condition in the "AND" column, and one in the "OR" column.

To add a complex condition as described above:

  • Navigate to the Page Definition of the page you created the Easy Report on the EMP table.
  • Under Regions, click RPT: next to the name of the Easy Report.
  • Click Query Definition tab.
  • Click Add/Modify Conditions.
  • Do not choose a column from the list and enter deptno in (10,20) in any text box underneath the first condition on ENAME. The choose column feature on the conditions page only applies to >, <, =, !=, ><, "like" and "between" comparison operators. (See figure 4.)
  • In any text field in a column to the right of the first two conditions, enter sal + nvl(comm,0) > 2500. Conditions listed vertically are joined with an "AND" operator, and conditions listed horizontally are joined with an "OR" operator. (See figure 4.)
  • Click Preview Condition to preview the condition before applying. (See figure 4.)
  • Click Apply Changes.

Adding a Condition to the Easy Report

Figure 4: Adding Complex Conditions to the Easy Report

The report page should now always show employees whose compensation is greater than 2500, and employees that match the search criterion that work in department 10 or 20.

Additional Resources

Learn more about customizing an Easy Report, such as adding hit-highlighting and pagination in the following documents.

Customizing HTML DB Reports (Column headings and column formatting)
Customizing HTML DB Reports - Report Pagination

Discuss this how-to in the OTN HTML DB Discussion Forum.