3 Using Interactive Reports

An Oracle Application Express database application is a collection of pages linked together using tabs, buttons, or hypertext links. This section describes how to use Oracle Application Express interactive reports.

See Also:

"Developing Reports" in Oracle Application Express Application Builder User's Guide

3.1 What is an Interactive Report?

Oracle Application Express includes two main report types, an interactive report and a classic report. The main difference between these two report types is that interactive reports enable the user to customize the appearance of the data through searching, filtering, sorting, column selection, highlighting, and other data manipulations.

About Interactive Reports

The following is an example of an interactive report in the packaged application, Sample Reporting.

Description of rpt_ir_new.png follows
Description of the illustration rpt_ir_new.png

Interactive reports enable end users to create highly customized reports. Users can alter the report layout by hiding or exposing specific columns and applying filters, highlighting, and sorting. They can also define breaks, aggregations, charts, group data, and add their own computations. Once customized, the report can be saved as either a private or public report. Most interactive reports include a search bar, Actions menu, Column Heading menu, and edit icons in the first column of each row.

In contrast, classic reports support general keyword search capability, the ability to specify the number of rows that display, and basic column sorting.

See Also:

"Utilizing Packaged Applications" in Oracle Application Express Application Builder User's Guide

3.2 Running an Interactive Report

An interactive report is contained in a page within a database application. To run an interactive report you must go to the database application URL, log in using your account credentials (that is, your username and password), and then navigate to the report page.

To run an interactive report:

  1. Click the supplied URL, or enter it in your browser's Address field.

    A Login page appears.

  2. On the Login page:

    1. Username - Enter your username.

    2. Password - Enter your password.

    3. Click Log In.

    Tip:

    The language that displays differs depending upon who creates the application. For Oracle-supplied applications, including packaged applications and the Oracle Application Express development environment, Sign In/Sign out displays. For applications you or your developers create, Log In/Log Out displays.

    The interactive report appears.

3.3 Using the Search Bar

Description of ir_search_bar.png follows
Description of the illustration ir_search_bar.png

A search bar displays at the top of most interactive reports and may include the following features:

  • Select columns to search icon - Resembles a magnifying glass. Click this icon to narrow your search to specific columns. To search all columns, select All Columns. See "Using the Select Columns To Search Icon."

  • Text area - Enter case insensitive search criteria (wildcard characters are implied) and then click Go.

  • Go button - Executes a search.

  • View Report - Displays alternate default and saved private, or public reports. See "Saving an Interactive Report."

  • View icons - Switches between an icon, report and detail view of the default report (if enabled). May also include Chart and Group By View (if defined). Icons do not display be default, but must be configured by the developer.

  • Actions menu - Use the Actions menu to customize an interactive report. See "Customizing an Interactive Report Using the Actions Menu."

Tip:

Developers can customize what displays on the Search bar. To learn more, see "Customizing the Interactive Search Bar" in Oracle Application Express Application Builder User's Guide.

3.4 Using the Select Columns To Search Icon

The Select columns to search icon displays to the left of the search bar. Click this icon to display a listing of all columns in the current report.

To search specific columns:

  1. Click the Select columns to search icon and select a column.

  2. Enter keywords in the Text area and click Go.

  3. To disable the filter, select the Enable/Disable Filter check box.

  4. To delete the filter, click the Remove Filter icon.

See Also:

"Selecting Columns to Display"

3.5 Using the Column Heading Menu

Clicking a column heading in an interactive report exposes the Column Heading menu. Positioning the cursor over each icon displays a tooltip that describes its function.

Description of ir_col_head_menu.png follows
Description of the illustration ir_col_head_menu.png

Column Heading menu options include:

  • Sort Ascending - Sorts the report by the column in ascending order.

  • Sort Descending - Sorts the report by the column in descending order.

  • Hide Column - Hides the column. Not all columns can be hidden. If a column cannot be hidden, the Hide Column icon does not display. To show a hidden column, select Reset from the Actions menu.

  • Control Break - Creates a break group on the column. This pulls the column out of the report as a master record. See "Creating a Control Break."

  • Column Information - Displays help text about the column, if available.

  • Filter - Enter a case insensitive search criteria. Entering a value reduces the list of values at the bottom of the menu. You can then select a value from the bottom. The selected value will be created as a filter using either the equal sign (=) or contains depending on the List of Values Column Filter Type.

3.6 Using Interactive Report Filters

When you customize an interactive report, a filter displays between the search bar and the report. The following illustration shows a report with two filters Project = 'Bug Tracker' and Status = 'On-Hold'. By default, filters display in the Report Settings area above the report. You can show or hide the filter details by clicking the arrow to the left of the filter name.

Description of rpt_hide_filter.png follows
Description of the illustration rpt_hide_filter.png

When you expand a filter and view the details, you can edit it as follows:

  • Enable/Disable - To enable and disable a filter, select and deselect the check box to the left of the filter name.

  • Filter Edit - To edit a filter, click the filter name.

  • Remove Filter - To remove a filter, select the Remove Filter icon to the right of the filter name.

Description of rpt_show_filter.png follows
Description of the illustration rpt_show_filter.png

3.7 Customizing an Interactive Report Using the Actions Menu

You can customize an interactive report by selecting options on the Actions menu.

Tip:

Not all options described in this section are available on every Actions menu. Developers can customize what options appear. To learn more, see "Customizing the Interactive Search Bar" in Oracle Application Express Application Builder User's Guide.

3.7.1 Actions Menu

The Actions menu appears to the right of the Go button on the Search bar. Use this menu to customize an interactive report.

Description of ir_action_menu.png follows
Description of the illustration ir_action_menu.png

The Actions menu contains the following options:

3.7.2 Selecting Columns to Display

To customize a report to include specific columns, select Select Columns on the Actions menu.

To use the Select Columns option:

  1. Click the Actions menu and select Select Columns.

    The Select Columns dialog appears.

  2. Select the columns you want to move. Click the center arrows to move a column from Display in Report to Do Not Display. To select multiple columns at once, press and hold the CTRL key.

  3. To change the order of the columns, click the Top, Up, Down, and Bottom arrows on the right.

  4. Click Apply.

    A revised report appears.

See Also:

"Resetting a Report"

3.7.3 Adding a Filter

This section describes how to create row and column filters on an interactive report.

3.7.3.1 About Creating Filters

You can create a filter on an interactive report by adding or modifying the WHERE clause on the query. You can create two types of filters:

  • Column - Creates a custom column filter. Select a column, select a standard Oracle operator (=, !=, not in, between), and enter an expression to compare against. Expressions are case sensitive. Use the percent sign (%) as a wildcard. Note that the selected column does not need to be one that currently displays. For example:

    STATE_NAME like A%

  • Row - Creates a custom row filter. This filter creates a complex WHERE clauses using column aliases and any Oracle functions or operators. For example:

     G = 'VA' or G = 'CT'

    Where G is the alias for CUSTOMER_STATE.

3.7.3.2 Adding, Editing, and Removing a Column Filter

To add a column filter:

  1. Click the Actions menu and select Filter.

    The Filter dialog appears.

  2. For Filter Type, select Column.

  3. In the Filter region, specify a column, an operator, and an expression and click Apply.

    Description of add_col_filter.png follows
    Description of the illustration add_col_filter.png

    Notice the filter that displays in the Report Settings area above the report. You can show or hide the filter details by clicking the arrow to the left of the filter name.

  4. To revise the filter:

    1. Click the filter name (in this example, Project = 'Discussion Forum').

    2. Edit your selections and click Apply.

  5. To disable the filter, select the Enable/Disable Filter check box.

  6. To delete the filter, click Remove Filter.

3.7.3.3 Adding, Editing, and Removing a Row Filter

To add a row filter:

  1. Click the Actions menu and select Filter.

    The Filter dialog appears.

  2. For Filter Type, select Row.

  3. In the Filter dialog:

    1. Name - Enter a name that describes this filter.

    2. Filter Expression - Enter an expression. Select a column and function or operator at the bottom of the region. For example, I >=2500 displays projects costing more than $5000.

    3. Click Apply.

    Notice the filter that displays in the Report Settings area above the report. You can show or hide the filter details by clicking the arrow to the left of the filter name.

  4. To revise the filter:

    1. Click the filter name.

    2. Edit your selections and click Apply.

  5. To disable the filter, select the Enable/Disable Filter check box.

  6. To delete the filter, click Remove Filter.

3.7.4 Specifying Rows Per Page

You can specify the number of rows that display on a page by selecting Rows Per Page on the Actions menu.

To specify the number of rows that display:

  1. Click the Actions menu and select Rows Per Page.

  2. From the submenu, select a number.

3.7.5 Selecting Column Sort Order

You can specify column display sort order (ascending or descending) by selecting Sort on the Format submenu. You can also specify how to handle NULL values. Using the default setting always displays NULL values last or always displays them first.

To sort by column:

  1. Click the Actions menu and select Format and then Sort.

    The Sort dialog appears.

  2. Select a column, the sort direction (Ascending or Descending), and Null Sorting behavior (Default, Nulls Always Last, or Nulls Always First).

  3. Click Apply.

3.7.6 Creating a Control Break

You can create a break group of one or several columns by selecting Actions, Format, and Control Break. Creating a break group pulls the columns out of the interactive report and displays them as a master record.

To create a break group:

  1. Click the Actions menu and select Format and then Control Break.

    The Control Break dialog appears.

  2. Select a column and then a status (Enable or Disable).

  3. Click Apply.

    A revised report displays.

    Description of ir_ctrl_break_rpt.png follows
    Description of the illustration ir_ctrl_break_rpt.png

    Note the defined filter displays in the Report Settings area above the report.

  4. Click the left arrow to expand the filter.

  5. To disable the Control Break filter, deselect the Enable/Disable Filter check box. To activate a disabled filter, select the Enable/Disable Filter check box again.

  6. To delete the filter, click Remove Control Break.

3.7.7 Adding Highlighting

You can customize the display to highlight specific rows in a report by selecting Highlight on the Actions, Format submenu.

To add highlighting:

  1. Click the Actions menu and select Format and then Highlight.

    The Highlight dialog appears.

  2. Edit the following information:

    1. Name - Enter a name that describes this filter.

    2. Sequence - Enter a numeric value to identify the sequence in which highlighting rules are evaluated.

    3. Enabled - Select Yes.

    4. Highlight Type - Select Cell or Row.

    5. Background Color - Select a new color for the background of the highlighted area.

    6. Text Color - Select a new color for the text in the highlighted area.

    7. Highlight Condition - Select a column, an operator, and expression.

      Description of ir_highlight2.png follows
      Description of the illustration ir_highlight2.png

    8. Click Apply.

      Description of ir_highlight_rpt.png follows
      Description of the illustration ir_highlight_rpt.png

      Note the highlight On-Hold Projects displays in the Report Settings area above the report. You can show or hide the filter details by clicking the arrow to the left of the filter name.

  3. To revise the highlight, click the highlight name and make the following edits:

    1. Background Color - Select yellow.

    2. Text Color - Select red.

    3. Click Apply.

  4. To disable the highlight, select the Enable/Disable check box.

  5. To delete the highlight, click Remove Highlight.

3.7.8 Computing Columns

You can add computations to columns by selecting Compute from the Actions, Format submenu. These computations can be mathematical computations (for example, NBR_HOURS/24) or standard Oracle functions applied to existing columns.

To create a computation:

  1. Click the Actions menu and select Format and then Compute.

    The Compute dialog appears.

  2. In the Compute dialog:

    1. Computation - Select New Computation.

    2. Column Heading - Enter the name of the new column to be created.

    3. Format Mask - Select an Oracle format mask to be applied to the new column. (for example, $5,234.10).

    4. Create the computation:

      • Columns - Select a column or alias.

      • Keypad - Select a shortcut for commonly used keys.

      • Functions - Select the appropriate function.

      In the following example, a new column compares the actual cost to the budgeted amount, using the formula I - H, where I is the budgeted amount and H is the cost.

  3. Click Apply.

    The revised report appears containing a new Cost Analysis column.

    Description of ir_compute_eg.png follows
    Description of the illustration ir_compute_eg.png

3.7.8.1 Deleting a Computation

To delete a computation:

  1. Click the Actions menu and select Format and then Compute.

    The Compute dialog appears.

  2. From Computation, select the computation.

    The computation appears.

  3. Click Delete.

3.7.9 Defining an Aggregation Against a Column

This section describes how to define an aggregation against a column by selecting Aggregate from the Actions, Format menu. Aggregates are displayed after each control break and at the end of the report within the column for which they are defined.

3.7.9.1 Creating a Aggregation Against a Column

To create an aggregation against a column:

  1. Click the Actions menu and select Format and then Aggregate.

    The Aggregate dialog appears.

  2. In the Aggregate dialog:

    1. Aggregation - Select New Aggregation.

    2. Function - Select one of the following: Sum; Average, Count, Count Distinct, Minimum, Maximum, or Median.

    3. Column - Select a column.

      Description of ir_aggreg.png follows
      Description of the illustration ir_aggreg.png

      This example creates a sum of the Cost column.

    4. Click Apply.

      The computation appears at the bottom of the last page of the report.

      Description of ir_aggreg_rpt.png follows
      Description of the illustration ir_aggreg_rpt.png

      In this example, the aggregate shows the sum of all amounts in the Cost column.

3.7.9.2 Removing a Column Aggregation

To remove column aggregation:

  1. Click the Actions menu and select Format and then Aggregate.

    The Aggregate dialog appears.

  2. From Aggregation, select a previously defined aggregation.

  3. Click Delete.

3.7.10 Creating a Chart from the Actions Menu

You can create a chart by selecting Actions, Format, and Chart. You can create one chart for each interactive report. Once defined, you can switch between the chart and report views using links on the Search bar.

3.7.10.1 Creating a Chart

To create a chart:

  1. Click the Actions menu and select Format and then Chart.

    The Chart dialog appears.

  2. In the Chart dialog, specify the appropriate options.

    The following attributes are for bar charts:

    1. Chart Type - Select the type of chart you want to create (horizontal bar, vertical bar, pie, or line.).

    2. Label - Select the column to be used as the label.

    3. Axis for Title for Label - Enter the title to display on the axis associated with the column selected for Label (not available for pie chart).

    4. Value - Select the column to be used as the Value. If your function is a COUNT, a Value does not need to be selected.

    5. Axis Title for Value - Enter the title to display on the axis associated with the column selected for Value (not available for pie chart).

    6. Function - (Optional) Select a function to be performed on the column selected for Value.

    7. Sort - Select a sorting method.

  3. Click Apply.

    The chart appears.

    Tip:

    The Search bar now contains two icons: View Report and View Chart. Click these icons to toggle between chart and report views.

3.7.10.2 Editing a Chart

To edit a chart:

  1. While viewing a report:

    1. Click the Actions menu and select Format and then Chart.

      The Chart dialog appears.

    2. Edit your selections and click Apply.

  2. While viewing a chart:

    1. Click Edit Chart.

    2. Edit your selections and click Apply.

3.7.10.3 Deleting a Chart

To delete a chart:

  1. While viewing a report:

    1. Click the Actions menu and select Format and then Chart.

      The Chart dialog appears.

    2. Click Delete.

  2. While viewing a chart:

    Tip:

    You can also click the Remove Chart icon to the right of the Edit Chart filter.

    1. Click Edit Chart.

    2. Click Delete.

3.7.11 Grouping Columns

Group By enables users to group the result set by one or more columns and perform mathematical computations against the columns. Once users define the group by, they can switch between the group by and report views using the View Icon on the Search bar.

3.7.11.1 Creating a Group By

To use Group By:

  1. Click the Actions menu and select Format and then Group By.

    The Group By dialog appears.

  2. In the Group by dialog:

    1. Select a column to display. To add additional columns, click Add Group By Column.

    2. Select the function, column, label, and format mask. To create a sum, click the Sum check box.To add another function, click Add Function.

      Description of ir_group_by2.png follows
      Description of the illustration ir_group_by2.png

    3. Click Apply.

      Description of ir_group_by_rpt.png follows
      Description of the illustration ir_group_by_rpt.png

      A Group By icon appears to the left of the Actions menu. The resulting report displays the Project, Task Name, and Budget columns. Additionally, a new column, Total Cost, displays on the right side.

3.7.11.2 Editing a Group By

To edit a group by:

  1. Click Edit Group By.

    The Group By dialog appears.

  2. Edit the attributes.

  3. To remove a previously defined Group by Column, select the default setting, Select Group By Column.

  4. Click Apply.

3.7.11.3 Selecting a Group By Sort Order

You can specify group by column sort order (ascending or descending) by either clicking on the group by column heading or selecting Group By Sort on the Format submenu. You can also specify how to handle NULL values. Using the default setting always displays NULL values last or always displays them first.

To sort a group by column:

  1. Access a Group By view. See "Creating a Group By."

  2. Click the Actions menu and select Format and then Group By Sort.

    Tip:

    The Group By Sort menu is only visible when you are viewing Group By view.

    The Group By Sort dialog appears.

  3. Select a column, the sort direction (Ascending or Descending), and Null Sorting behavior (Default, Nulls Always Last, or Nulls Always First).

  4. Click Apply.

3.7.11.4 Deleting a Group By

To delete a group by:

  1. Click Edit Group By.

    The Group By dialog appears.

  2. Click Delete.

Tip:

You can also click the Remove Group By icon to the right of the Edit Group By filter.

3.7.12 Creating Pivot Report

Pivot report transposes rows into columns to generate results in a crosstab format. End users select pivot columns and rows and then provide the functions to be represented in the pivot report. Once created, pivot reports display a new icon in the search bar.

3.7.12.1 Creating a Pivot Report

To create a pivot report:

  1. Click the Actions menu and select Format and then Pivot.

    The Pivot dialog appears.

  2. In the Pivot dialog:

    1. Pivot Columns - Select the columns to display (for example, Status). To add additional columns, click Add Pivot Column.

    2. Row Columns - Select the rows to display (for example, Project). To add additional columns, click Add Row Column.

    3. Computation:

      • Select a function, column, label, and format mask.

      • To create a sum, click the Sum check box (optional).

    4. Click Apply.

    The following example shows a Pivot report that displays the number of closed, on-hold, open, and pending tasks associated with each project.

    Description of ir_pivot.png follows
    Description of the illustration ir_pivot.png

3.7.12.2 Editing a Pivot Report

To edit a pivot report:

  1. Click the Edit Pivot filter.

    The Pivot dialog appears.

  2. Edit the attributes.

  3. To remove a previously defined column or row, select the default setting, Select Pivot Column and Select Row Column.

  4. Click Apply.

3.7.12.3 Deleting a Pivot Report

To delete a pivot report:

  1. Click the Edit Pivot filter.

    The Pivot dialog appears.

  2. Click Delete.

Tip:

You can also click the Remove Pivot icon to the right of the Edit Pivot filter.

3.7.13 Executing a Flashback Query

You can execute a flashback query by selecting Flashback from the Actions menu. A flashback query enables you 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 is different for each database.

3.7.13.1 Creating a Flashback Query

To execute a flashback query:

  1. Click the Actions menu and select Flashback.

  2. In the Flashback region, enter the number of minutes.

  3. Click Apply.

3.7.13.2 Editing a Flashback Query

To edit a flashback query:

  1. Click flashback query filter.

    The Flashback dialog appears.

  2. Edit minute ago attribute.

  3. Click Apply.

3.7.13.3 Deleting a Flashback Query

To delete a flashback query:

  1. Click the flashback query filter.

    The Flashback dialog appears.

  2. Click Delete.

Tip:

You can also click the Remove Flashback icon to the right of the flashback query filter.

3.7.14 Saving an Interactive Report

This section explains the ways in which end users can save an interactive report. As an end user, you can save a private or public interactive report. Note, however, that only the user who creates a private report can view, save, rename, or delete it.

3.7.14.1 About the Report List

The following illustration shows the Reports list on the Search bar of an interactive report.

Description of ir_reports_list.png follows
Description of the illustration ir_reports_list.png

This example shows two reports:

  • Default - Primary Report. This is the initial report created by the application developer. Default, Primary reports cannot be renamed or deleted.

  • Private - 1. Open Projects. This is a Private report. Only the user who creates a private report can view, save, rename, or delete it.

3.7.14.2 About Configuration Dependencies

The ability to save an interactive report is configurable by the application developer who creates the interactive report. To learn more, see "Customizing the Search Bar" and "Saving an Interactive Report" in Oracle Application Express Application Builder User's Guide.

3.7.14.3 Saving a Public or Private Interactive Report

End users can save an interactive report and classify it as being either:

  • Public. The report can be saved, renamed, or deleted by the end user who created it. Other users can view and save the layout as another report.

  • Private. Only the end user that created the report can view, save, rename or delete the report.

To save a public or private interactive report:

  1. Go to the page containing the interactive report you want to save.

  2. Customize the report (for example, hide columns, add filters, and so on). See "Customizing an Interactive Report Using the Actions Menu."

  3. Click the Actions menu and select Save Report.

    The Save Report dialog appears.

    Description of ir_save_rpt.png follows
    Description of the illustration ir_save_rpt.png

  4. In Save Report:

    1. Save - Select either As Named Report or As Default Report Settings option.

      Tip:

      This option is available for only developers.

    2. Name - Enter a name for the report.

    3. Description - Enter an optional description.

    4. Public - Select this check box to make the report viewable to all users. Deselect this check box to make the report private.

      Tip:

      The ability to save an interactive report as Public is determined by your application developer. See "About Configuration Dependencies."

    5. Click Apply.

3.7.14.4 Renaming a Public or Private Interactive Report

To rename a public or private interactive report:

  1. Run the report as a developer.

  2. Select a public or private interactive report to rename.

  3. Edit the attributes (for example, enter a new name).

  4. Click Apply.

3.7.14.5 Deleting a Public or Private Interactive Report

To delete a public or private interactive report:

  1. Run the report as a developer.

  2. Click the Remove Report icon next to the report name link.

    Description of ir_delete.png follows
    Description of the illustration ir_delete.png

  3. Click Apply.

3.7.15 Resetting a Report

You can reset a report back to the default settings by selecting Reset from the Actions menu. Resetting a report removes any customizations you have made.

To reset a report:

  1. Click the Actions menu and select Reset.

  2. Click Apply.

    Tip:

    You can also reset a report by clicking the Reset Report button in the Search Bar.

3.7.16 Downloading a Report

You can download an interactive report back by selecting Download from the Actions menu. Available download formats depend upon your installation and report definition. Supported formats include comma-delimited file (CSV) format, HTML, Email, Microsoft Excel (XLS) format, Adobe Portable Document Format (PDF), and Microsoft Word Rich Text Format (RTF).

Tip:

The ability to download an interactive report is configurable by your application developer. To learn more about configuring download options, see "Configuring Download Options on the Actions Menu" in Oracle Application Express Application Builder User's Guide.

To download a report:

  1. Click the Actions menu and select Download.

  2. Select a report download format and follow the provided instructions.

3.7.17 Subscribing to Report Updates by Email

End users can receive updated versions of a report by subscribing to it. This section describes how report subscriptions work.

See Also:

"Managing Interactive Report Subscriptions" in Oracle Application Express Administration Guide

3.7.17.1 How Report Subscriptions Work

You can subscribe to a report by clicking Subscription on the Actions menu. Emails sent from an interactive report contain a system generated email signature that cannot be overwritten that identifies who originated the email.

To use Subscription:

  • An Oracle Application Express administrator must configure email at the Instance level.

  • The application developer configure enable the Subscription check box on the Interactive Report Attributes page.

See Also:

"Customizing the Interactive Report Search Bar" in Oracle Application Express Application Builder User's Guide

3.7.17.2 Subscribing to Updated Report Results

To receive updated report results by email:

  1. Click the Actions menu and select Subscription.

    The Subscription dialog appears.

  2. Under Subscription:

    1. Email Address - Enter the email addresses to receive the report. To include multiple email addresses, separate each email address with a comma.

    2. Subject - Enter text to display in the email subject line.

    3. Frequency - Select the interval at which the report is sent.

    4. Starting From - Select a start date and time.

    5. Ending - Select an end date and time. Select a day, week, month, or year.

    6. Click Apply.

Note:

Emails sent from a subscription include a system generated email signature indicating who created the subscription. This signature cannot be removed.