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.
• Rows sets the number of records to display per page.
• 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).

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.

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.