Building and Customizing an Interactive Report in Oracle Application Express 4.2

Overview

Purpose

This tutorial shows you how to build, use, and customize an Interactive Report in Oracle Application Express.

Note: Some screenshots in this tutorial may be slightly different depending on what browser or operating system you use.

Time to Complete

Approximately 60 minutes.

Introduction

Interactive Reporting Regions enable end users to customize reports. Users can alter the layout of report data by choosing the columns they are interested in, applying filters, highlighting, and sorting. They can also define breaks, aggregations, different charts, and their own computations. Users can create multiple variations of the report and save them as named reports and download to various file formats including comma-delimited file (CSV) format, Microsoft Excel (XLS) format, Adobe Portable Document Format (PDF), and Microsoft Word Rich Text Format (RTF). For further information, click on help and search on Interactive Reports.

Another feature shown in this tutorial is Application Date Format. You can define a date format that will be used throughout your application. This date format will be used to alter the NLS_DATE_FORMAT database session setting prior to showing or submitting any page within the application. This format is used by all reports showing dates and is also picked up by form items of type 'Date Picker (use Application Date Format)'. This can be set using:

Application Builder > Application Builder Defaults (under Tasks): These are specific to a Workspace, not a developer, and will be used as the default when using the create application wizard.
Application Builder > Create Application Wizard > Attributes page
Application Builder > Your Application > Shared Components > Edit Globalization Attributes

This tutorial shows the developer how to create and customize an Interactive Report for an end user.

Please keep in mind the following while running this tutorial:

Logging into your Oracle Application Express workspace - Your Oracle Application Express workspace may reside in an on-premise Oracle Database or in Oracle Database Cloud Service (Database Schema).
The login credentials differ depending on where your workspace is located:
Application ID - Screenshots in this tutorial show an Application ID of 111. Your Application ID may be different.
Schema - If you are accessing an Oracle Application Express workspace in Oracle Database Cloud Service (Schema) you have one schema assigned to you with a schema name that you cannot change. If you are accessing the workspace in an on-premise Oracle database, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.

Prerequisites

Before starting this tutorial, you should:

Have access to an Oracle Database 11g or later database, either on-premise or in the Oracle Database Cloud Service (Database Schema).
Have installed Oracle Application Express Release 4.2 into your Oracle Database (for on-premise only).
At least one Oracle Application Express user provisioned. See Creating New User Accounts in Oracle Application Express Administration Guide.

Creating an Application with an Interactive Report

In this topic, you create an application with an Interactive Report. To create an Interactive Report, login to your Oracle Application Express workspace, either on-premise or in Oracle Database Cloud Service (Schema), and perform the following steps:

Select the drop down next to Application Builder and select Database Applications
Click Create >.
Enter an Application Name and click Next >.

Make sure Blank is selected for Page Type, enter Home for Page Name and click Add Page.

Select Report and Form for Page Type, select Home (1) for Subordinate to Page, make sure Implementation is set to Interactive. Click the Drop-Down arrow to select a table in the Table Name field.

Select the DEMO_PRODUCT_INFO table and click Add Page.

Click Next >.

Accept the default and click Next >.

You want to specify the type of authentication used to access this application. Select the List of Values icon for Authentication Scheme and choose Application Express. Also, you can specify a Date Format for the entire application. Click the List of Values icon for Date Format.

Select the 12-JAN-2004 link. Note: By setting the date format when you create the application, the same date format will be used for all report columns and items.

Click Next >.

Some of the themes have been updated in Application Express 4.2. In this tutorial, you want to select Theme 26 and click Next >.

Click Create Application.

Click the Run Application icon.

If the login screens appears, enter the user's Oracle Application Express credentials and click Login.
The Home page is displayed, select the Demo Product Info to view the report.
The Report is displayed. Notice that the date format is set according to the date you specified when you created the application. In the next section, you examine some of the Interactive Report features.

Click the Edit Page link in the developer toolbar.

Adding Images to Your Interactive Report

You can add images to your Interactive Report. In this section, you add a product image for each product. Perform the following steps:

You want to change your query for this interactive report. Under Regions > Body, right-click DEMO_PRODUCT_INFO and select Edit.
Click the Source subtab.

Change the SQL Statement to the following and click Apply Changes.

select p.product_id,
       p.product_name, 
       p.product_description, 
       p.category, 
       p.product_avail, 
       p.list_price,
       (select sum(quantity) from demo_order_items 
           where product_id = p.product_id) units,
       (select max(o.order_timestamp) od from demo_orders o, demo_order_items i 
           where o.order_id = i.order_id and i.product_id = p.product_id) last_date_sold,
       p.PRODUCT_ID image
from demo_product_info p

Click Apply Changes to confirm the report region changes in the query.
You also want to make some changes to the Report Attributes. Under Regions > Body, right-click DEMO_PRODUCT_INFO and select Edit Report Attributes.

You need to define the way in which the blob will be displayed in the report. Select Standard Report Column for Display Type and click List of Values icon.
Select the BLOB Format link.

Enter the following values.

**Field** **Value**

Format Mask **IMAGE**

BLOB Table **DEMO_PRODUCT_INFO**

BLOB Column **PRODUCT_IMAGE**

Primary Key Column 1 **PRODUCT_ID**

MIME Type Column **MIMETYPE**

Filename Column **FILENAME**

BLOB Last Updated Column **IMAGE_LAST_UPDATE**

Content Disposition **Inline**

Click Apply Changes to save the changes you just made.
.Click the Run Page icon.
If the Image column does not get displayed in the report by default, then follow the steps 12-14 as shown below. Also, move the Units and Last Sale columns to the Do Not Display area by following similar procedure(if the columns appear in the Display in Report area by default).

Click Actions and select Select Columns.
Select the Image column and click the Move icon to move the column to the Display in Report area.
Click Apply.
The image column was added to the report with the images displayed.

Manipulating Your Interactive Report

Now that the Interactive Report has been created, you can manipulate the report in many ways. In this section, only some of them are discussed. You can review more on this topic in the OBE- Using Interactive Report Regions with Oracle Application Express 4.2. Perform the following steps:

You can search for values. Enter shoes in the search bar and click Go.
Notice that only the rows where 'shoes' is found are listed. A filter was also created above the report data.
To hide a column, you can select the column header link and then click the hide column icon.

Alternatively, from the Actions pull down, you can select Selected Columns and move the column to the Do Not Display area. Select the Available header and click the Hide icon.
Notice that the column no longer appears in the report.

Note: You can show the column again by clicking Select Columns from the Actions menu.
You can remove a filter by selecting the delete icon next to the filter.

Note that you can also disable a filter by deselecting the check box next to the filter.
To sort on a column, click the header you want to sort on and select the Sort Ascending or Sort Descending icon. Select the Name column and click the Sort Ascending icon.

Alternatively, from the Actions menu, you can select Format > Sort and select multiple sort levels.
The report is now sorted on Name.
You can create a control break on a column. Select the Category header and select the Control Break icon.

Alternatively, from the Actions menu, select Format > Control Break.
The control break was created. Notice the Category column was eliminated in the list and now appears before each breakpoint in the report.
Scroll down to the bottom of the report. Notice that there are 10 rows on this page. You can set the rows per page to be displayed.
Scroll to the top of the page. From the Actions menu, select Rows per Page and select 5.
Scroll to the bottom of the page again and notice that only 5 rows are displayed. In order to go to the next page, you can select the arrow. In this case, scroll to the top of the page again.
You can also highlight a particular cell or row in the report based on some criteria. Click the Actions menu and select Format > Highlight.
You want to highlight the row of a product if its price is higher than $80. Enter Top Tier Products for the Name, make sure Enabled is set to Yes, Highlight Type is set to Row and select [blue] for Background color.

Under Highlight Condition, select Price for the Column, select > for the Operator and 80 for the Expression and click Apply.
Notice that a highlight rule is created at the top of the report and the rows that meet the criteria are highlighted.
You can reorder and add an additional column to the report. From the Actions menu, select Select Columns.
Select Units from the list of Do Not Display columns and select the Move icon.
Select Units from the list and click the Up arrow to move it above the Image column.
You want to remove the Product Id column from the report. Select the Product Id column from the list of Display in Report columns and click Remove icon.
Click Apply.
The Units column is displayed in the report and Product Id column is removed.
You can create a computed column. In this case, you will create a column to show the sales for each product. Click the Actions menu and then select Format > Compute.
Enter Sales for Column Heading and select $5,234.10 for the Format Mask from the drop down list.
From the list of Columns, select Price. Notice that the letter corresponding to the column appears in the computation area.
This is the column alias. Select * from the Keypad area.
From the list of Columns, select Units.
Click Apply to create the new Sales Column.
Notice that the computed Sales column now appears in the report. If you want to move this computed column, from the Actions menu, select Select Columns.
Select the computed column **Sales and select the Up arrow.
Click Apply.
Notice that the Sales column was moved. You can also aggregate a particular column. Click the Actions menu and select Format > Aggregate.
You want to total the Sales column. Select Sum for the Function and **Sales (the ** indicates that the column is a computed column) for the Column. Then click Apply.
You now see total sales for each category. Note: The aggregated column inherits any formatting from the base column.
You can also create a group by report. From the Actions menu, select Format > Group By.

Specify the following and click **Apply**.

**Field** **Value**

Group By Column **Name**

Function **Percent of Total Sum**

Column **Sales

Label **Total Percent of Sales**

Sum **<select check box>**

The Group By Report is displayed. It shows the Total Percent of Sales for each Product. Notice that it only displays 5 rows in the report. You want to change this setting to show all.

From the Actions menu, select Rows Per Page and select All.
All rows are displayed. To return to the Report view, select the View Report icon.
You can save all the customizations you have made to a saved report. Click the Actions menu and select Save Report.
Enter My Report for the Name and click Apply.

Note: Save as Named Report is available to all authenticated end users (when saving reports is enabled for the region). The report settings are displayed as a tab above the report, with the name you provide, and is visible *only* for the APP_USER who saved it. Save as Default Report Settings is only available to developers. These settings are applied to anyone who comes to the page for the first time in a session.
You just created a Private report called My Report. Notice that you now see a drop down list of the reports you have created. This customized view will be available each time you login.
To go back to the other view, select Primary Report from the list of reports.
With the Primary Report selected, select Reset from the Actions menu.
Click Apply.
Note that the report has been reset back to the original version of the report. To go back to the report you formatted, select your Private Report My Report from the list.
The formatted report is displayed. In the next section you will examine some additional customization options. Select the Edit Page link in the developer toolbar.

Customizing Your Interactive Report

As a developer you can change the way the Interactive Report is rendered for all users. Perform the following steps:

Under Regions > Body (3), right-click DEMO_PRODUCT_INFO and select Edit Report Attributes.
Select the Search Bar subtab.
Select Save Public Report and Subscription. Deselect Highlight and select the Download subtab.
Make sure the HTML, Email and PDF check boxes are selected and click Apply Changes.
Click Run.
Select Save Report from the Actions menu.
Enter Product Report by Category for Name and check the Public check box and click Apply.
Select the drop down list of Reports and notice that you now have a public report which can be accessed by anyone. The private report can only be accessed by the person who created it.
From the Actions menu, select Format. Notice that the Highlight option is no longer available because you deselected it from the Search Bar.
From the Actions menu, select Download.
Select the Email format. To send the report via email you can enter an email in the To field and click Send. In this case, you will click Cancel.

Below is a view of the report that will appear in the email message.
To set up an automatic email of the report to be sent on a scheduled basis, select Subscription from the Actions menu.
You only want the subscription to last 1 day. Select 1 for Ending and make sure Day is selected from the drop down list and click Apply.
Your subscription was created. To see the list of subscriptions, select Edit Page in the developer toolbar.
Right-click DEMO_PRODUCT_INFO and select Edit Report Attributes.
In the list of Tasks on the right window, select Manage Subscriptions.
You see your subscription in the list. You want to remove it from the list. Select the check box in front of the subscription and click Delete Checked.
Your subscription was removed. You want to return to your application. Select Application Builder > Database Applications.
Select the View Report icon to show the list view of applications.
Click the Run icon for the Interactive Reports OBE application..
Select the Demo Product Info link.
Select an Edit icon in front of the Bag product.
The form is displayed. This form allows you to change the values for this product. You may not want to allow people to change the product information.

In this case, you can exclude the column that allows you to view this form from the report. Click Cancel.
Select Edit Page from the developer toolbar.
Right-click DEMO_PRODUCT_INFO and select Edit Report Attributes.
Click the Link Column tab.
Select Link to Single Row View from the Link Column drop down list.
Select [Icon 1] for Link Icon.
Click Apply Changes.
Click the Run Page icon.
Note that the edit icon column is no longer displayed. Click the icon.
The single row view is displayed. You can display the next row by clicking the > icon or click Report View to return to the report.

Summary

In this tutorial, you have learned how to:

Create an Application with an interactive report
Add an image to your interactive report
Manipulate and customize your interactive report

Resources

Oracle Application Express homepage on OTN.
Oracle Learning Library
Oracle University

Credits

Lead Curriculum Developers: Veerabhadra Rao Putrevu, Drue Swadner
Original Curriculum Developer: Dimpi Sarmah
Other Contributors: David Peake, Chaitanya Koratamaddi

To navigate this Oracle by Example tutorial, note the following:

Topic List:: Click a topic to navigate to that section.
Expand All Topics:: Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:: Click the button to show or hide the screenshots. By default, all images are displayed.
Print:: Click the button to print the content. The content that is currently displayed or hidden is printed.

To navigate to a particular section in this tutorial, select the topic from the list.

Attribute	Heading	Display Text As
PRODUCT_ID	Product Id	Display as Text (escape special characters)
PRODUCT_NAME	Name	Display as Text (escape special characters)
PRODUCT_DESCRIPTION	Description	Display as Text (escape special characters)
CATEGORY	Category	Display as Text (escape special characters)
PRODUCT_AVAIL	Available	Display as Text (escape special characters)
LIST_PRICE	Price	Display as Text (escape special characters)
UNITS	Units	Display as Text (escape special characters)
LAST_DATE_SOLD	Last Sale	Display as Text (escape special characters)
IMAGE	Image	Display as Text (escape special characters)

Field	Value
Format Mask	IMAGE
BLOB Table	DEMO_PRODUCT_INFO
BLOB Column	PRODUCT_IMAGE
Primary Key Column 1	PRODUCT_ID
MIME Type Column	MIMETYPE
Filename Column	FILENAME
BLOB Last Updated Column	IMAGE_LAST_UPDATE
Content Disposition	Inline

Field	Value
Group By Column	Name
Function	Percent of Total Sum
Column	**Sales
Label	Total Percent of Sales
Sum	<select check box>