Utilizing Advanced Interactive Report Region Techniques

Purpose

This tutorial shows you how to use some advanced techniques with Interactive Report Regions in Oracle Application Express 3.1.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Resetting your Interactive Report Manually
 Creating a Declarative Filter
 Creating Derived Columns
 Summary
 Related Information

Viewing Screenshots

 Place the cursor over this icon to load and view all screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

In this tutorial, you explore some of the more advanced tasks related to Interactive Report Regions which is a new feature in Oracle Application Express 3.1.

To get more information about the 3.1 New Features, click here.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Perform the Building and Customizing an Interactive Report tutorial.

2.

From SQL Workshop > SQL Commands, execute the following statement:

update oehr_employees
   set job_id='AD_PRES'
 where employee_id=104

Note: The application numbers and page numbers may differ slightly from the screenshots.

Back to Topic List

Resetting your Interactive Report Manually

As a developer, you have the ability to include or exclude the Reset function, Actions Menu or Search Bar all together. Without the Reset function on the Actions Menu, your end-users will not have the ability to reset their report after they have customized it using the Column Heading menu. To allow for resetting, you can programmatically provide a reset button.

In this topic, you will first create an Interactive Report, then exclude the Search bar, and lastly, create a reset button. Perform the following steps:

1.

From your Application page, click Create Page.

 

2.

Click Report and click Next.

 

3.

Click Interactive Report and click Next.

 

4.

Enter Department List for the Page Name and Region Name. Select Breadcrumb for Breadcrumb and click Next.

 

5.

Accept the default and click Next.

 

6.

Enter the following SQL and click Next.

                               
select d.department_name,
 (select count(*) from oehr_employees where department_id = d.department_id) Employees,
 d.department_id 
from oehr_departments d
                            

 

7.

Click Finish.

 

8.

Your interactive report page was created successfully. Before you run the page, you need to make some changes. Click Edit Page.

 

9.

Under Regions, select the Interactive Report link.

 

10.

Click the Search Bar tab.

 

11.

Under Include in Actions menu, Select No for Include Search Bar and click Apply Changes.

 

12.

You will now create a button to perform the reset function. Under Buttons, click the Create icon.

 

13.

Select the Department List region and click Next.

 

14.

Accept the default Create a button in a region position and click Next.

 

15.

Enter Reset for the button name and select the Redirect to URL without submitting page and click Next.

 

16.

Accept the default and click Next.

 

17.

Click [Top] for Position and select Left for Alignment and click Next.

 

18.

To reset an Interactive Report, you send a clear cache request of 'RIR'. This resets the Interactive Report on whichever page is called. Enter the current page for Page. For Clear Cache, and enter 2,RIR and click Create Button.

Note: The RIR string is equivalent to the end user choosing the Reset option from the interactive report actions menu on the target page and causes the report to be returned to the default report settings specified by the developer.

 

19.

Your button was created successfully. Click Run Page.

 

20.

Your report is displayed. Select the Employees header and click the sort descending icon.

 

21.

Your report is now ordered by Employees, in descending order. From the column heading menu, you can only change to ascending or sort by another column. If your end user wants to remove the sort entirely, they will need to use the Reset button. Click Reset.

 

22 .

Notice that your report has been reset back to its original state.

 

Back to Topic List

Creating a Declarative Filter

You can define declarative filters on Interactive Reports by passing filter criteria using the URL item values. This allows you to define complex query criteria that can be translated into filters or drill downs from a report to an interactive report. To create a drill down from one interactive report to another perform the following steps:

1.

Navigate to the Application page. Click Create Page.

 

2.

Click Report and click Next.

 

3.

Click Interactive Report and click Next.

 

4.

Enter Employees by Department for the Page Name and Region Name. Select Breadcrumb for Breadcrumb. Enter Employees by Department for Entry Name. Select the Department List link for the Parent Entry and click Next.

 

5.

Accept the default and click Next.

 

6.

Enter the following SQL and click Next.

                               
select     "OEHR_EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
                                
"OEHR_EMPLOYEES"."LAST_NAME" as "LAST_NAME",
"OEHR_EMPLOYEES"."EMAIL" as "EMAIL",
"OEHR_EMPLOYEES"."PHONE_NUMBER" as "PHONE_NUMBER",
"OEHR_EMPLOYEES"."HIRE_DATE" as "HIRE_DATE",
"OEHR_EMPLOYEES"."JOB_ID" as "JOB_ID",
"OEHR_EMPLOYEES"."SALARY" as "SALARY",
"OEHR_EMPLOYEES"."DEPARTMENT_ID"
from "OEHR_EMPLOYEES" "OEHR_EMPLOYEES"

 

7.

Click Finish.

 

8.

Your interactive report page was created successfully. Before you run the page, you need to make some changes. Click Edit Page.

 

9.

Under Regions, select the Interactive Report link.

 

10.

Select the Show All tab. You need to provide a Heading Name that will be used as the Filter name when this report is called and a DEPARTMENT_ID is specified (even though the DEPARTMENT_ID columns will not be displayed). Change the heading for DEPARTMENT_ID to Specified Department and select Hidden for Display Text As and click Apply Changes.

Note that the column does not need to be hidden in order to have a value passed to it. If a value is passed to a non-hidden column, the resulting filter can be edited. If a value is passed to a hidden column, the resulting filter can only be disabled or deleted, but not edited (i.e. and your end user cannot change the filter to view a different department).

 

11.

Now you need to create a link between the Department List page and this page and pass the Department ID. Select your Application breadcrumb.

 

12.

This page was created in the previous topic of this tutorial. Select the Department List page.

 

13.

Under Regions, select the Interactive Report link.

 

14.

Select Hidden for DEPARTMENT_ID, select Display as Text (escape special characters) for the EMPLOYEES column, and click the Edit icon for the EMPLOYEES column.

 

15.

Click the Link tab.

 

16.

You need to define a link that will display the number of employees per department, call the new page you just created, clear the cache on that page and reset the interactive report region on that page to remove any previously set filters that might interfere with the filter being passed. In addition, you need to pass in a value for the hidden DEPARTMENT_ID. To reference a column within an interactive report, you preface the column alias with 'IR_'.

Select [EMPLOYEES] for Link Text, enter the 3 for Page (which is the page you just created) and enter 3,RIR in the Clear Cache field. Enter IR_DEPARTMENT_ID in the Item 1 Name field and select #DEPARTMENT_ID# for Value. Then click Apply Changes.

Note: By prefixing the item name with IR_, in this example IR_DEPARTMENT_ID, the URL item will be associated with an interactive report column rather than an item on a page.

 

17.

Click Run Page.

 

18.

The Employees value now has a link. Click one of the numbers.

 

19.

The Employees by Department Interactive report is now shown and there is a filter created on Department. Notice that you cannot click to edit the filter (because it was created on a hidden column) but you can disable the filter by deselecting the check box.

 

20.

All the records are now displayed because the filter has been disabled. Click Edit Page in the developer toolbar.

 

Back to Topic List

Creating Derived Columns

In this section, you add a derived column to your query. Specifically, you use a case statement to determine whether an employee is underpaid based on the job they perform. You then use the value of this derived column to create a highlight rule to highlight all underpaid employees. Perform the following steps:

1.

Navigate to the Employees by Department page (which is the page you were last on from the previous section). Under Regions, select the Employees by Department link.

 

2.

In the Region Definition tab click the Source tab.

 

3.

Change the source to the following query and click Apply Changes .

Note that the CASE statement in this SQL statement checks to see if the employee's salary is less than the minimum salary for their job. If so, the value of the derived column, UnderPaid_Yes_No, is set to Yes, otherwise, it is set to No. The derived column is not displayed initially but it is available for use with many actions on the Actions menu such as a Filter and Highlight.

                               
select (case when e.salary < j.min_salary then 'Yes'
                                
else 'No' end) UnderPaid_Yes_No,
e.first_name,
e.last_name,
e.email,
e.phone_number,
e.hire_date,
e.job_id,
e.salary,
e.department_id
from oehr_employees e,
oehr_jobs j
where e.job_id = j.job_id

 

4.

The additional columns will be added to your report. Click Apply Changes.

 

5.

Click Run Page.

 

6.

Your report is displayed. If the new derived columns appear on the report at runtime use the Actions menu > Select Columns action to change the columns to Do not Display and then save the report as the default report. To show the employees who are underpaid, select the Actions menu and click Highlight.

 

7.

Enter Underpaid Employees for the Name and select [yellow] for Background Color. Select UnderPaid Yes No for Column and enter Yes for Expression and click Apply.

 

8.

Notice that the employees who are underpaid are now highlighted in yellow.

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Reset your Interactive Report Manually
 Create a declarative filter
 Create a derived predefined filter

Back to Topic List

Related Information

To learn more about Application Express, you can review the following:

Related Documentation

Description

Online Help

To access this help, click the Help button displayed in the top right corner of Application Express. Search on topics of interest.

Release Notes

These notes contain important information not included in the Oracle Application Express documentation.

Installation Guide

This guide explains how to install and configure Oracle Application Express.

2 Day + Developer’s Guide

This guide shows you how to set up a development environment or access a hosted demonstration environment to use with this guide. It then walks you through building an initial application, modifying it, and previewing it.

User’s Guide

This guide describes how to use the Oracle Application Express development environment to build and deploy database-centric Web applications. Oracle Application Express turns a single Oracle database into a shared service by enabling multiple workgroups to build and access applications as if they were running in separate databases.

Advanced Tutorial

Tutorials with step-by-step instructions that explain how to create a variety of application components and entire applications using the Oracle Application Express development environment.

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document