0) { obj.className = "imgborder_on"; } } } function hideImage(obj) { if (obj.className.substr(0, 10) == "imgborder_") { obj.src = eyeglass.src; obj.className = "imgborder_off"; } } function showAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { showImage(imgs[i]); } } function hideAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { hideImage(imgs[i]); } } //-->

Using Interactive Report Regions with Oracle Application Express 3.1

Purpose

This tutorial shows you how to use an Interactive Report created using Oracle Application Express 3.1.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Searching for Information in your Report
 Viewing a Record in Your Report
 Manipulating the Interactive Report Using the Actions Menu
 Manipulating the Interactive Report Using Column Headers
 Summary

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

Interactive Reports allow developers to define queries that can be highly customized by end-users.  Users can customize the layout of the data by choosing the columns they are interested in, applying filters, highlighting, and sorting. They can also define breaks, aggregations, computed columns and include a chart of the query results. Users can create multiple variations of the report and save them as named reports, output to comma-delimited files, and print to PDF documents.

An Interactive Report can be customized in three ways:

Search Bar

 Actions Menu
 Column Heading Menu

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 9.2 or above.

2.

Download and Configure Oracle Application Express 3.1 and have access to the Oracle Application Express 3.1 Sample Application.

Back to Topic List

Searching for Information in Your Report

The search bar in Interactive Report Regions contains the following:

The Select Columns icon identifies which column to search (or all).

 The Text Area allows you to enter case insensitive search criteria (no need for wild cards).
 The Rows drop-down list allows you to select the number of records to display per page.
 The Go button executes the search.
 The Actions Menu display the tasks you can perform on the Interactive Report. This is discussed in a later topic.

To examine how the Search bar works, perform the following steps:

1.

Run the Sample Application in Oracle Application Express 3.1. You can get the Application Express URL from your Systems Administrator. Note: Login as the demo user and the password is the all lowercase name of the workspace in which this application is located. (Hint: the workspace name is displayed in the lower left corner of the application home page).

 

2.

Enter jan in the text area and click Go. Note: when the Sample Application is installed, the dates are updated so that the orders are current. As a result, you may have slightly different data in your report. Review the data and enter the first 3 letters of the month that is currently assigned for Albert Lambert. For example, if the month for Albert Lambert is currently June, enter jun in the text area and click Go.

 

3.

The search is not case sensitive, so it finds 'January 2008' in the Order Month. Notice that the filter shows below and the word is no longer in the text area which allows you to create multiple filters.

 

4.

Create another filter. Enter Logan in the text area and click Go.

 

5.

Notice that the list only shows rows that meet both filter criteria.

 

6.

You can remove a filter by clicking the red X next to the filter you want to remove. Click the red X next to the first filter.

 

7.

Remove the other filter by clicking the red X.

 

8.

You can also create a filter for just one column using the select columns icon. Click the Search Columns icon  and select Customer Name.

 

9.

Enter jan in the text area and click Go.

 

10.

This time you receive no hits because the filter was only applied to the Customer Name column. Click the red X next to the jan filter to remove it.

 

11.

You can change the number of rows that are displayed in an Interactive Report. Click the Products tab in the Sample Application.

 

12.

Another Interactive Report is displayed. Notice that only 5 rows are shown. Select 10 from the Rows drop-down list.

 

13.

Click Go.

 

14.

You now see more rows in the report.

 

Back to Topic List

Viewing a Record in Your Report

You can view a record in your Interactive Report Region by performing the following steps:

1.

Switch back to the Home tab to the previous Interactive Report.

 

2.

Select the view icon  to the left of any row in your report.

Note: A single record view icon may not be included in your report. If it is, it might be a different icon (determined by the developer) but will always be the first column shown.

 

3.

A single-record view of that record is shown. Note this may take you to a custom page depending on the application. You can scroll through the records by using the arrow buttons. Click the > button to scroll to the next record.

 

4.

The next record is displayed. The Exclude Null Values check box does not have any affect on this page because all the columns are populated. If one of the columns was null, it would be hidden if the check box was selected.

 

5.

This page is a view only form. This form can have more columns than the interactive report shows. The Displayed columns checkbox reduces this form to just the columns that were displayed on the interactive report. Click the Displayed Columns check box.

 

6.

To return to the Interactive Report, click the Report View button.

 

Back to Topic List

Manipulating the Interactive Report Using the Actions Menu

The Actions Menu contains many tasks that are useful in manipulating your Interactive Report. All Interactive Reporting functions from the Actions Menu are exposed in this example report. For reports that you use within other applications in your environment, the developer can limit the functions available. In addition, the icon to invoke the Actions Menu does not have to be a gear, which is the default. The developer can use a different icon or hide it all together. Perform the following tasks:

Selecting Columns

 Adding a Filter
 Sorting Columns
 Creating Control Breaks
 Highlighting Rows Based on Filter
 Adding Computed Columns
 Aggregating Columns
 Creating a Chart
 Perform a Flashback Query
 Saving a Report
 Resetting Defaults
 Downloading Data in Different Formats

Back to Topic List

Selecting Columns

This action is used to modify the columns displayed. The columns on the right are displayed. The columns on the left are hidden. You can reorder the displayed columns using the arrows on the far right. Computed columns are prefixed with **. Perform the following steps:

1.

Click the Products tab again to return to the Products Interactive Report Page.

 

2.

Click the Actions Menu icon  and select Select Columns.

 

3.

To hide columns from the report display, select a column and click the < to move the columns to the Do Not Display area.

 

4.

To reorder the columns, select a column and click the Up or Down arrow.

 

5.

Click Apply to see the changes to the Report.

 

6.

The changes you made have been applied.

Back to Topic

Adding a Filter

This action is used to add or modify the where clause on the query. You first select a column (it does not need to be one that is displayed), select from a list of standard Oracle operators (=, !=, not in, between), and enter an expression to compare against. The expression is case sensitive and you can use % as a wild card (e.g. STATE_NAME like A%). Perform the following steps:

1.

Click the Actions Menu icon  and select Filter.

 

2.

You want to create a filter where the Category = Video. From the Column drop-down list, select Category.

 

3.

Select Video from the list of expressions. Notice that this list of values is determined automatically based on all the values in the Category column. The list of expressions can show up to 500 unique values.

 

4.

Click Apply to create the filter.

 

5.

The filter has been applied. You can have more than one filter for a report. If you decide that you want to disable a particular filter, you can click the check box to disable the filter.

6.

You can add another filter. Click the Actions Menu icon  and select Filter.

 

7.

Select Price from the list of columns.

 

8.

Select > for the Operator.

 

9.

Enter 100 for the Expression and click Apply.

 

9.

Notice that there are now 2 filters applied. The rows displayed are fewer now because they are only the rows that meet the filter criteria.

 

10.

You can remove each filter by clicking the red X next to the filter you want to remove. Click the red X for both the Price > 100 and Category=Video filters.

 

11.

The filters has been removed.

 

Back to Topic

Sorting Columns

This action is used to change the column(s) to sort on and whether to sort ascending or descending. You can also specify how to handle nulls (use the default setting, always display them first or last). The resulting sort result is displayed to the right of the column heading in the report. Perform the following steps:

1.

Click the Actions Menu icon  and select Sort.

 

2.

You want to sort the report in ascending order by price. From the Column drop-down list select Price.

Note: The null sorting field indicates where null values should appear in the report for the column being sorted. If set to default, nulls will default to the value set in the Direction field for this sort entry. You can also force nulls to always show first or last.

 

3.

Click Apply to sort the report.

 

4.

Notice that the report is now sorted by Price. The sort indicator is located next to the columns that are sorted. The arrow indicates whether it is in ascending or descending order.

To change the sort to descending order, you can click on the sort ascending icon in the header for Price and it will change to sort descending. This is discussed and shown in a later topic.

 

Back to Topic

Creating Control Breaks

This action is used to create a break group on one or several columns. This pulls the columns out of the Interactive Report and displays them as a master record. Perform the following steps:

1.

Click the Actions Menu icon  and select Control Break.

 

2.

You want to create a control break on Category. From the Column drop-down list select Category.

 

3.

Click Apply to create the control break

 

4.

Notice that the control break was created and the Category column was extracted from the report. In this case, the report now shows a list of Products by Category.

 

Back to Topic

Highlighting Columns Based on a Filter

This action is used to highlight specific rows or cells based on a filter. The rows or cells that meet the filter are highlighted using the characteristics associated with the filter. Perform the following steps:

1.

Click the Actions Menu icon  and select Highlight.

 

2.

Enter Only One Customer for the Name and select Cell for the Highlight Type. Notice that you can also highlight for an entire row if the row meets the criteria that was set for the filter.

 

3.

Select [yellow] for the Background Color and select the color icon for the Text Color.

 

4.

Select any color from the pallette.

 

5.

You want to highlight the Customers cell when the value is set to 1. From the Column drop-down list select Customers. Then enter 1 for the Expression and click Apply.

 

6.

The filter has been applied and the cells that meet the criteria are now highlighted.

 

Back to Topic

Adding Computed Columns

This action is used to add computed columns to your report. These can be mathematical computations (e.g. NBR_HOURS/24) or standard Oracle functions applied to existing columns (some have been displayed, for example, others, such as TO_DATE, can also be used). Perform the following steps:

1.

Click the Actions Menu icon  and select Compute.

 

2.

You want to create a computation column that has the calculation Price * 1.05. Enter Price With Tax for the Column Heading and select the down arrow for the format mask and select $5,234.10.

 

3.

Place your cursor in the computation field and click Price from the list of Columns.

 

4.

An F will appear in the Computations area. F appears because column aliases are used for the columns and the alias is displayed in the table on the left. Click * from the Keypad.

 

5.

An * will now appear after the F in the Computations area. Enter 1.05 after the * so that the computation now reads F*1.05. Then click Apply.

 

6.

The new computed column now appears in the report.

 

Back to Topic

Aggregating Columns

This action is used to aggregate columns in your report. Aggregates are mathematical computations performed against a column. Aggregates are displayed after each control break and at the end of the report within the column they are defined. Perform the following steps:

1.

Click the Actions Menu icon  and select Aggregate.

 

2.

You want to add an aggregate that is a sum of Price. Select Sum from the list of Functions.

 

3.

Select Price from the list of Columns. Note: Only base columns can be used in aggregates, not computed columns.

 

4.

Click Apply.

 

5.

The aggregate now appears in the report. Notice that the sum is shown for Price per control break.

 

Back to Topic

Creating a Chart

This action is used to create a chart based upon the data contained within your report. You can include one chart per Interactive Report. Once defined, you can switch between the chart and report views using links below the search bar. Perform the following steps:

1.

Click the Actions Menu icon  and select Chart.

 

2.

You want to create a pie chart that shows how many products there are per category. Select the Pie chart type

 

3.

Select Category for the Label.

 

4.

Select Count for the Function. Note: You do not select a column for Value in this case because you are doing a count. For other functions, you would select a Value column

 

5.

For pie charts, you can select a sort order, you can sort by label descending or you can sort on categories within the pie. For the function, you will not select a Sort Value. Click Apply.

 

6.

The pie chart is displayed. You can edit the chart or switch back to the report. Click View Report.

 

Back to Topic

Perform a Flashback Query

This action is used to view the data as it existed at a previous point in time. The default amount of time that you can flashback is 3 hours (or 180 minutes) but the actual amount will differ per database. Perform the following steps:

1.

In order to show a flashback query, you need to make a change to the database first. Click Create Product.

 

2.

Enter iPOD for the Product Name, 4GB Memory for the Description, select Audio from the Category list, make sure Y is select for Available and 250 is entered for the Price. then click Create.

 

3.

The iPOD product is now displayed in the report.

 

4.

Click the Actions Menu icon  and select Flashback.

 

5.

You want to show the data as of 5 minutes ago. Enter 5 in the field and click Apply.

 

6.

The flashback was applied and you do not see iPOD in the list because it was created less than 5 minutes ago.

 

Back to Topic

Saving a Report

This action is used to save the customized report for future use. You provide a name and an optional description. When navigating between pages in an application and then returning to the Products tab, your changes (filters, control breaks, etc.) will still be available. If you logout, however, your changes will not be saved unless you have saved the report. You can save multiple versions of a report and each will appear as a separate tab. Perform the following steps:

1.

Click the Actions Menu icon  and select Save Report .

 

2.

Enter a name for the report and click Apply.

 

3.

A tab with the name of your report was created and the details (filters, control breaks, sorts, etc.) have been saved. You can also view the working report. Click Working Report.

 

4.

The working report is now displayed. You can make any changes to this report and it will not be reflected in the saved report.

 

Back to Topic

Resetting Defaults

This action is used to reset the report back to the default settings and remove any customizations that you have made. Perform the following steps:

1.

Click the Actions Menu icon  and select Reset.

 

2.

Click Apply to confirm that you want the reset to be performed.

 

3.

All the filters, control breaks, etc. have been lost and the report has been reset. Notice that you still have a tab for your Saved Report. Click your saved report tab.

 

4.

The Saved Report changes are still entact.

 

Back to Topic

Downloading Data in Different Formats

This action is used to download the current result set. The download formats will differ depending upon your installation and report definition but may include CSV, XLS, PDF, or RTF. Perform the following steps:

1.

Click the Actions Menu icon  and select Download.

 

2.

Select a format to download, in this case CSV.

 

3.

A CSV file was created with all the data.

 

Back to Topic

Manipulating the Interactive Report Using the Column Heading Menu

Clicking any column heading exposes a column heading menu. Some of the functions on the Column Heading Menu can also be done using the Actions Menu. The Column Heading Menu contains the following functions:

Sorting Columns

 Hiding a Column
 Creating a Control Break on a Column
 Displaying Column Information
 Creating a Filter

Back to Topic List

Sorting Columns

You can sort on a particular column from the column header. Perform the following steps:

1.

Selec the Working Report tab.

 

2.

Select the Category Header and select the sort ascending icon  .

 

3.

Notice that the sort ascending icon is now next to the Category header indicating that this is the column that the report is currently sorted on. Note that the sort descending icon works the same way as the sort ascending icon  .

 

Back to Topic

Hiding a Column

You can hide a particular column from the column header. Perform the following steps:

1.

Select the Sales Header and select the Hide Column icon  .

 

2.

Notice that the Sales column no longer appears in the report. Note: To get the column back, use the Select Columns option from the Actions menu.

 

Back to Topic

Creating a Control Break on a Column

You can break a particular column from the column header. When control break is created, the column becomes a master record for the report. Perform the following steps:

1.

Select the Category Header and select the Control Break icon  .

 

2.

The report now has a control break on the Category column.

 

Back to Topic

Displaying Column Information

You can display Column Information about a particular column from the column header. The Column Information icon will only appear if there is help text defined for the column. This is defined by the developer who created the report. Perform the following steps:

1.

Select the Name Header and select the Column Information icon  .

 

2.

The Column Information is displayed.

 

Back to Topic

Creating a Filter

The Text Area on the column header is used to enter case insensitive search criteria (no need for wild cards). Entering a value reduces the list of values at the bottom of the menu. You can select a value from the bottom and the selected value creates a filter using '=' (e.g. column = 'ABC'). Perform the following steps:

1.

Select the Name Header and enter the word player in the text area. Notice that the list of values changes as you type. Select the value you want to create a filter for.

 

2.

Notice that the filter is created.

 

Back to Topic

Summary

In this tutorial, you learned how to:

 View a record in your report
 Search for information in your report
 Manipulate the interactive report using the actions menu
 Manipulate the interactive report using column headers

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document