Interactive Report Help

Owned by Holley Vosburgh
Apr 06, 2023
7 min read

 

Interactive reports enable end users to customize reports. Users can alter the layout of report data by selecting columns, applying filters, highlighting, and sorting. Users can also define breaks, aggregations, charts, group bys, and add their own computations. Users can also set up a subscription so that an HTML version of the report will be emailed to them at a designated interval. Users can create multiple variations of a report and save them as named reports, for either public or private viewing.

The sections that follow summarize ways you can customize an interactive report. To learn more, see "Using Interactive Reports" in Oracle Application Express End User's Guide.

Search Bar

At the top of each report page is a search region. This region (or Search bar) provides the following features:

 

  • Select columns icon enables you to identify which column to search (or all).

  • Text area enables you to enter case insensitive search criteria (wild card characters are implied).

  • Go button executes the search. Hitting the enter key will also execute the search when the cursor is in the search text area.

  • Reports displays alternate default and saved private or public reports.

  • Actions Menu enables you to customize a report. See the sections that follow.

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.

Select Columns

Used to modify the columns displayed. The columns on the right display. 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 **.

Filter

Focuses the report by adding or modifying the WHERE clause on the query. You can filter on a column or by row.

If you filter by column, select a column (it does not need to be one that displays), select a standard Oracle operator (=, !=, not in, between), and enter an expression to compare against. Expressions are case sensitive. Use % as a wild card (for example, STATE_NAME like A%).

If you filter by row, you can create 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).

Rows Per Page

Sets the number of records to display per page.

Format

Format enable you to customize the display of the report. Format contains the following submenu:

  • Sort

  • Control Break

  • Highlight

  • Compute

  • Aggregate

  • Chart

  • Group By

  • Pivot

Sort

Used to change the columns to sort on and determines whether to sort in ascending or descending order. You can also specify how to handle NULLs. The default setting always displays NULLs last or always display them first. The resulting sorting displays to the right of column headings in the report.

Control Break

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.

Highlight

Enables you to define a filter. The rows that meet the filter criteria display as highlighted using the characteristics associated with the filter. Options include:

  • Name is used only for display.

  • Sequence identifies the sequence in which the rules are evaluated.

  • Enabled identifies if a rule is enabled or disabled.

  • Highlight Type identifies whether the row or cell should be highlighted. If Cell is selected, the column referenced in the Highlight Condition is highlighted.

  • Background Color is the new color for the background of the highlighted area.

  • Text Color is the new color for the text in the highlighted area.

  • Highlight Condition defines your filter condition.

Compute

Enables you to add computed columns to your report. These can be mathematical computations (for example, NBR_HOURS/24) or standard Oracle functions applied to existing columns. Some display as examples and others (such as TO_DATE) can also be used). Options include:

 

  • Computation enables you to select a previously defined computation to edit.

  • Column Heading is the column heading for the new column.

  • Format Mask is an Oracle format mask to be applied against the column (for example,S9999).

  • Computation is the computation to be performed. Within the computation, columns are referenced using the aliases displayed.

Below computation, the columns in your query display with their associated alias. Clicking on the column name or alias includes them in the Computation. Next to Columns is a keypad. This keypad functions as a shortcut to commonly used keys. On the far right are Functions.

The following example computation demonstrates how to display total compensation:

CASE WHEN A = 'SALES' THEN B + C ELSE B END

(where A is ORGANIZATION, B is SALARY and C is COMMISSION)

 

Aggregate

Aggregates are mathematical computations performed against a column. Aggregates display after each control break and at the end of the report within the column they are defined. Options include:

 

  • Aggregation enables you to select a previously defined aggregation to edit.

  • Function is the function to be performed (for example, SUM, MIN).

  • Column is used to select the column to apply the mathematical function to. Only numeric columns display.

Chart

You can define one chart per saved report. Once defined, you can switch between the chart and report views using view icons the Search bar. Options include:

 

  • Chart Type identifies the chart type to include. Select from horizontal bar, vertical bar, pie, or line.

  • Label enables you to select the column to be used as the label.

  • Axis Title for Label is the title that displays on the axis associated with the column selected for Label. This is not available for pie chart.

  • Value enables you to select the column to be used as the value. If your function is a COUNT, a Value does not need to be selected.

  • Axis Title for Value is the title that displays on the axis associated with the column selected for Value. This is not available for pie chart.

  • Function is an optional function to be performed on the column selected for Value.

  • Sort allows you to sort your result set.

Group By

You can define one Group By view per saved report. Once defined, you can switch between the group by and report views using view icons on the Search bar. To create a Group By view, you select:

 

  • the columns on which to group

  • the columns to aggregate along with the function to be performed (average, sum, count, etc.)

Pivot

You can define one Pivot view per saved report. Once defined, you can switch between the pivot and report views using view icons on the Search bar. To create a Pivot view, you select:

 

  • the columns on which to pivot

  • the columns to display as rows

  • the columns to aggregate along with the function to be performed (average, sum, count, etc.)

Flashback

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 will differ for each database.

Save Report

Saves the customized report for future use. You provide a name and optional description and can make the report accessible to the public (that is, all users who can access the primary default report). You can save four types of interactive reports:

  • Primary Default (Developer Only). The Primary Default is the report that initially displays. Primary Default reports cannot be renamed or deleted.

  • Alternative Report (Developer Only). Enables developers to create multiple report layouts. Only developers can save, rename, or delete an Alternative Report.

  • Public Report (End user). 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 Report (End user). Only the end user that created the report can view, save, rename or delete the report.

If you save customized reports, a Reports selector displays in the Search bar to the left of the Rows selector (if this feature is enabled).

Reset

Resets the report back to the default settings, removing any customizations that you have made.

Download

Enables the current result set to be downloaded. The download formats differ depending upon your installation and report definition but may include CSV, HTML, Email, XLS, PDF, or RTF.

Column Heading Menu

Clicking on any column heading exposes a column heading menu.Options include:

 

  • Sort Ascending icon sorts the report by the column in ascending order.

  • Sort Descending icon 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, there will be no Hide Column icon.

  • Break Column creates a break group on the column. This pulls the column out of the report as a master record.

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

  • Text Area 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 then select a value from the bottom and the selected value will be created as a filter using '=' (for example, column = 'ABC'). Alternatively, you can click the Flashlight icon and enter a value to be created as a filter with the 'LIKE' modifier (for example, column LIKE '%ABC%').

  • List of Unique Values contains the first 500 unique values that meets your filter criteria. If the column is a date, a list of date ranges is displayed instead. If you select a value, a filter will be created using '=' (for example, column = 'ABC').

Report Settings

If you customize an interactive report, the report settings display below the Search bar and above the report. This area can be collapsed and expanded using the icon on the left.

For each report setting, you can:

  • Edit a setting by clicking the name.

  • Disable/Enable a setting by unchecking or checking the Enable/Disable check box. Use this control to temporarily turn a setting off and on.

  • Remove a setting by clicking the Remove icon.

If you have created a chart, group by or pivot, you can toggle between them and the base report using the Report View, Chart View, Group By View, and Pivot View links shown on the right. If you are viewing the chart, group by or pivot, you can also use the Edit link to edit the settings.