Customizing Interactive Reports

About the Search Bar

A search bar displays at the top of every interactive report and may include the following features:

• Select Columns icon - Resembles a magnifying glass. Click this icon to narrow your search to specific columns. To search all columns, select All Columns.

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

• Go button - Executes a search.

• Reports - Displays alternate default and saved private or public reports.

• View icons - Switches between icon, report and detail view of the default report (if enabled). May also include Chart and Group By View (if defined).

• Actions menu - Clicking the icon displays the Actions menu. Use this menu to customize an interactive report. See "Using the Actions Menu".

Using the Select Columns Icon

A Select Columns icon displays to the left of the search bar. Clicking this icon displays a listing of all columns in the current report.

To search specific columns:

1. Click the Select Columns icon and select a column.

   
   Description of the illustration ir_select_col_rpt.gif
   
   

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

   
   Description of the illustration ir_select_col.gif
   
   

   Notice in this example the filter Row text contains 'Edward' displays above the report.

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

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

Using the 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.

The Actions menu contains the following options:

• Select Columns specifies which columns to display and in what order. See "Selecting Columns to Display".

• Filter focuses the report by adding or modifying the WHERE clause on the query. See "Adding a Filter".

• Rows Per Page determines how many rows display in the current report. See "Specifying Rows Per Page".

• Format contains the following submenu:

  • Sort - Changes the columns to sort on and determines whether to sort in ascending or descending order. See "Selecting Columns and Sort Order".

  • Control Break - Creates a break group on one or several columns. This pulls the columns out of the interactive report and displays them as a master record. See "Creating a Control Break".

  • Highlight - Defines a filter that highlights the rows that meet the filter criteria. See "Adding Highlighting".

  • Compute - Enables users to add computed columns to a report. See "Computing Columns".

  • Aggregate - Enables users to perform mathematical computations against a column. See "Aggregating a Column".

  • Chart - Displays the report data as a chart. See "Creating a Chart from the Actions Menu".

  • Group By - Enables users to group the result set by one or more columns and perform mathematical computations against columns. See "Grouping Columns".

• Flashback - Enables you to view the data as it existed at a previous point in time. See "Executing a Flashback Query".

• Save Report - Saves the interactive report. Depending upon their user credentials, users can save four different types of reports. See "Saving a Report".

• Reset - Enables users to reset the report back to the default report settings. See "Resetting a Report".

• Help - Provides descriptions of how to customize interactive reports.

• Download - Enables users to download a report. Available download formats depend upon your installation and report definition. See "Downloading a Report".

• Subscription - Enables users to send themselves an interactive report by email. See "Subscribing to Emailed Reports"

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 region appears.

   
   Description of the illustration ir_select_column.gif
   
   

2. Select the columns you wish to move. Click the center arrows to move a column from the Display in Report box to the Do Not Display box. 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.

Adding a Filter

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 (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%
  

• 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.

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.

   
   Description of the illustration ir_row_per_page.gif
   
   

Selecting Columns and Sort Order

You can specify which columns to display and the 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 display them first.

To sort by column:

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

   The Sort region appears.

   
   Description of the illustration ir_sort.gif
   
   

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.

Creating a Control Break

You can create a break group of one or several columns by selecting Control Break from the Actions, Format submenu. 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 region appears.

   
   Description of the illustration ir_ctrl_break.gif
   
   

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

3. Click Apply.

   One or more break groups display in Report Settings area above the report.

4. To expand the break group, click the plus (+) sign.

5. To enable the Control Break filter, select the Enable/Disable Filter check box.

6. To delete the filter, click Remove Filter.

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 region appears.

   
   Description of the illustration ir_highlight.gif
   
   

2. Enter the following information:

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

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

   3. Enabled - Select Yes.

   4. Highlight Type - Select Cell or Row. If Cell is selected, the column referenced in the Highlight Condition is highlighted.

   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.

3. Click Apply.

   
   Description of the illustration ir_highlight_rpt.gif
   
   

   Note a new highlight appears in the Report Settings area above the report.

4. To revise the highlight:

   1. Click the highlight name (in this example, Top Customer).

   2. Edit your selections and click Apply.

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

6. To delete the highlight, click Remove Highlight.

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. Note that some display as examples and others (such as TO_DATE) can also be used).

To create a computation:

1. Click the Actions menu and select Compute.

   The Compute region appears.

2. In the Compute region:

   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, S9999).

      Next, create the computation.

   4. Create the computation:

      • Columns - Select a column or alias.

      • Keypad - Select a shortcut for commonly used keys.

      • Functions - Select the appropriate function.

      The following example, creates a new Commission column. This column calculates at 10% sales commission using the formula C*.10 where C is the order total.

      
      Description of the illustration ir_compute.gif
      
      

3. Click Apply.

   The revised report appears containing a new Commission column.

   
   Description of the illustration ir_compute_eg.gif
   
   

Aggregating a Column

You can define a mathematical computation against a column (or aggregate) 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 in which they are defined.

To aggregate columns:

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

   The Aggregate region appears.

2. In the Aggregate region:

   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 the illustration ir_aggreg.gif
      
      

      This example creates a sum or Order Total column.

3. Click Apply.

   The computation appears at the bottom of the report.

   
   Description of the illustration ir_aggreg_rpt.gif
   
   

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

Creating a Chart from the Actions Menu

To create a chart, select Chart on the Actions, Format menu. 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.

To create a chart:

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

   The Chart region appears.

   
   Description of the illustration ir_chart.gif
   
   

2. In the Chart region, specify the following:

   1. Chart Type - Select the type of chart you wish 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 title to be displayed 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.

      Click Apply and a View Chart icon appears in Search bar.

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.

To use Group By:

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

   The Group By region appears.

   
   Description of the illustration ir_group_by.gif
   
   

2. From Group by Column, select the columns to display.

3. To include a computation, select the function, column, label, and format mask.

4. To specify sorting, select a column, sort direction, and preferences for NULL values.

   A Group By icon appears in the Search bar.

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

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.

Saving a Report

Both developers and end users can save an interactive reports. However, only a developer can save the report that initially displays, the Primary Default, or create an Alternative 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.

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

Topics

• Configuration Dependencies

• Saving a Primary or Alternative Interactive Report

• Saving a Public or Private Interactive Report

• Editing or Deleting Primary or Alternative Reports

• About Exporting Interactive Reports

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.

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). See "Configuring Download Options on the Actions Menu"

To download a report:

1. Click the Actions menu and select Download.

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

Subscribing to Emailed Reports

End users can receive updated versions of a report by subscribing to it. To subscribe to a report, click Subscription on the Actions menu.

To utilize Subscription:

• An Oracle Application Express administrator must configure email at the Instance level. See "Configuring Mail" in Oracle Application Express Administration Guide

• The application developer must check the Subscription check box on the Interactive Report Attributes page. See "Customizing the Interactive Report Search Bar"

To receive updated report results by email:

1. Click the Actions menu and select Subscription.

   The Add Subscription region appears.

2. Under Add Subscription:

   1. Email Address - Enter the email address to receive the report.

   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 a end date and time. Select a day, week, month, or year.

   6. Click Apply.

Using the Column Heading Menu

Clicking a column heading exposes the Column Heading menu.

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 - Used to enter a 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. 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. 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.