Building and Customizing an Interactive Report in Application Express 4.1

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

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

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

Time to Complete

Approximately 60 minutes

Overview

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:

  1. 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.
  2. Application Builder > Create Application Wizard > Attributes page
  3. 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.

Prerequisites

Before you perform this tutorial, you should:

Creating an Application with an Interactive Report

To create an Application with an Interactive Report , perform the following steps:

.

Enter the following URL to log in to Oracle Application Express.

http://localhost:8080/apex

 

.

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>

 

.

Select the drop down next to Application Builder and select Database Applications.

 

.

Click Create.

 

.

Select Database and click Next.

 

.

Select From Scratch and click Next.

 

.

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 up arrow to select a table.

 

.

Select the DEMO_PRODUCT_INFO table.

 

.

Click Add Page.

 

.

Click Next.

 

.

Accept the default to create One Level of Tabs and click Next.

 

.

Click Next.

 

.

You want to accept the default authentication scheme. You can specify a Date Format for the entire application. Click the up arrow 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.1. In this tutorial, you want to select Theme 1 and click Next.

 

.

Click Create.

 

.

Click the Run Application icon.

 

.

Login with the username/password you use when you login to Application Express and click Login.

 

.

The Home page is displayed, Select the Demo Product Info link 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.

 

.

Change the fields below that are in bold and select the edit link for the IMAGE field.

Attribute Heading Display Text As
PRODUCT_ID Product Id Hidden
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)

 

.

You need to define the way in which the blob will be displayed in the report. Select the BLOB Download Format Mask link under the Number / Date Format field.

 

.

Enter the following values and click Apply.

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

 

.

Click Apply Changes to save the changes you just made.

 

.

Click Run.

 

.

You want to add the Image column to the report. 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.1. 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.

 

.

Click Apply.

 

.

The additional column is displayed.

 

.

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.

 

.

Select the Run Page icon.

 

.

Note that the edit icon column is no longer displayed.

 

.

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:

Resources


Hardware and Software Engineered to Work Together Copyright © 2011, Oracle and/or its affiliates. All rights reserved