Tempest-GEMS includes interactive reports that enable the user to customize the appearance of the data through searching, filtering, sorting, column selection, highlighting, and other data manipulations.

This article is designed to help you:

  • conduct better searches by defining your search criteria using columns and filters

  • create customized reports using highlighting, computations

  • create charts, groups, pivot reports, and flashback queries

  • save, download, and print reports

 

Using the Search Bar


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. 
  • Text area - Enter text and then click Go.
  • Go button - Executes a search.

 

Actions menu

Use the Actions menu to customize an interactive report. 

Select Search Columns

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:

  • Click the Select columns to search icon and select a column.
  • Enter keywords in the Text area and click Go.
  • To disable the filter, select the Enable/Disable Filter check box.
  • To delete the filter, click the Remove Filter icon.

 

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.



 

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: Control Break
  • Column Information - Displays help text about the column, if available.
  • Filter - Enter case-insensitive search criteria. 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.

Interactive Report Filters

When customizing an interactive report, a filter displays between the search bar and the report. The following illustration shows a report with the filter Description contains 'emergency' and demonstrates the non-case sensitive nature of the filter.

 



By default, filters display in the Report Settings area above the report. Show or hide the filter details by clicking the arrow to the left of the filter name.

 


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

 


Actions Menu

Customize an interactive report by selecting options on 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
  • Filter focuses the report by adding or modifying the filter 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 Column 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: 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: Aggregations
  • Chart - Displays the report data as a chart. See: Charts
  • Group By - Enables users to group the result set by one or more columns and perform mathematical computations against columns. See: Grouping
  • Pivot - Enables users to define a Pivot report. See: Pivot Reports
  • Flashback - Enables users to view the data as it existed at a previous point in time. See: Flashback Query
  • Save Report - Saves the interactive report. Depending upon user credentials, users can save different types of reports. See: Saving an Interactive Report
  • Reset - Resets the report back to the default report settings.
  • Help - Provides descriptions of how to customize interactive reports.
  • Download - Downloads a report. See: Downloading a Report

Select Columns

  1. To customize a report to include specific columns, click Select Columns on the Actions menu.
  2. The Select Columns dialog appears.
  3. 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 CTRLkey.
  4. To change the order of the columns, click the Top, Up, Down, and Bottom arrows on the right.
  5. Click Apply. A revised report appears.

Filters

Creating Filters

Create a filter on an interactive report by adding or modifying the WHERE clause on the query.

Create two types of filters:

Column - Creates a custom column filter. Select a column, select an 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:

 S = 'VA' or S = 'CT'

Where S is the alias for PHYSICAL_STATE.

 

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.


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

To revise the filter:

  1. Click the filter name (in this example, Physical State = 'VA').
  2. Edit your selections and click Apply.

To disable the filter:

  1. Select the Enable/Disable Filter check box.

To delete the filter:

  1. Click Remove Filter.

 

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:

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

5. 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 $2500.

6. Click Apply.

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

To revise the filter:

  1. Click the filter name
  2. Edit selections and click Apply.

To disable the filter:

  1. Select the Enable/Disable Filter check box.

To delete the filter:

  1. Click Remove Filter.

Specifying Rows Per Page

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.

Selecting Column Sort Order

Specify column display sort order (ascending or descending) by selecting Sort on the Format submenu. 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.

Control Break

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


  • Note the defined filter displays in the Report Settings area above the report.
  • Click the left arrow to expand the filter.

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.

To delete the filter:

  • Click Remove Control Break.

Highlighting

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:

  • Name - Enter a name that describes this filter.
  • Sequence - Enter a numeric value to identify the sequence in which highlighting rules are evaluated.
  • Enabled - Select Yes.
  • Highlight Type - Select Cell or Row.
  • Background Color - Select a new color for the background of the highlighted area.
  • Text Color - Select a new color for the text in the highlighted area.
  • Highlight Condition - Select a column, an operator, and expression.

 


  • Click Apply.

 



Note the highlight Unreimbursed Grants displays in the Report Settings area above the report. Show or hide the filter details by clicking the arrow to the left of the filter name.

  • To revise the highlight, click the highlight name.
  • To disable the highlight, select the Enable/Disable check box.
  • To delete the highlight, click Remove Highlight.

 

Computing Columns

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:

  • Computation - Select New Computation.
  • Column Heading - Enter the name of the new column to be created.
  • Format Mask - Select an Oracle format mask to be applied to the new column. (for example, $5,234.10).
  • Create the computation:
  • Columns - Select a column or alias.
  • Keypad - Select a shortcut for commonly used keys.
  • Functions - Select the appropriate function.

 


  • Click Apply. The revised report appears containing a new “Vendor Capitalized” column.

 


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.

Aggregation

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.

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:

Aggregation - Select New Aggregation.

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

4. Column - Select a column.

 


This example creates a sum of the Transaction Value column.

5. Click Apply.

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


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

 

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.

 

Chart

Create a chart by selecting Actions, Format, and Chart. Create one chart for each interactive report. Once defined, Switch between the chart and report views using links on the Search bar.

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:

  • Chart Type - Select the type of chart to create (horizontal bar, vertical bar, pie, or line.).
  • Label - Select the column to be used as the label.
  • 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).
  • Value - Select the column to be used as the Value. If the function is a COUNT, a Value does not need to be selected.
  • Axis Title for Value - Enter the title to display on the axis associated with the column selected for Value (not available for pie chart).
  • Function - (Optional) Select a function to be performed on the column selected for Value.
  • Sort - Select a sorting method.

3. Click Apply. The chart appears.

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

 

Editing a Chart

To edit a chart:

While viewing a report:

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

The Chart dialog appears.

2. Edit selections and click Apply.

While viewing a chart:

1. Click Edit Chart.

2. Edit selections and click Apply.

 

Deleting a Chart

To delete a chart:

While viewing a report:

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

The Chart dialog appears.

2. Click Delete.

While viewing a chart:

1. Click the Remove Chart icon to the right of the Edit Chart filter.

2. Click Edit Chart.

3. Click Delete.

 

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.

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:

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

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

 


c. Click Apply.

 


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.

 

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.

 

Selecting a Group By Sort Order

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

The Group By Sort menu is only visible when 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.

 

Deleting a Group By

To delete a group by:

1. Click Edit Group By.

The Group By dialog appears.

2. Click Delete.

3. Remove Group By

4. Click the Remove Group By icon to the right of the Edit Group By filter.

 

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.

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:

  • Pivot Columns - Select the columns to display (for example, Tx Paid). To add additional columns, click Add Pivot Column.
  • Row Columns - Select the rows to display (for example, the transaction #). 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.


 

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.

 

Deleting a Pivot Report

To delete a pivot report:

1. Click the Edit Pivot filter.

The Pivot dialog appears.

2. Click Delete.

3. Remove Pivot 

4. Click the Remove Pivot icon to the right of the Edit Pivot filter.

 

Flashback Query

Execute a flashback query by selecting Flashback from the Actions menu. A flashback query enables viewing the data as it existed at a previous point in time. The maximum amount of time allowed for flashback is 3 hours (or 180 minutes).

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.

 

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.

 

Deleting a Flashback Query

To delete a flashback query:

1. Click the flashback query filter.

The Flashback dialog appears.

2. Click Delete.

 

Remove Flashback

1. Click the Remove Flashback icon to the right of the flashback query filter.

 

Saving an Interactive Report

An end user, can save a private or public interactive report. Note that only the user who creates a private report can view, save, rename, or delete it.

To save a public or private interactive report:

1. Go to the page containing the interactive report.

2. Customize the report (for example, hide columns, add filters, and so on). See: Actions Menu

3. Click the Actions menu and select Save Report.

4. The Save Report dialog appears.

 


5. In Save Report:

  • Name - Enter a name for the report.
  • Description - Enter an optional description.
  • Public - Select this check box to make the report viewable to all users. Deselect this check box to make the report private. Click Apply.

 

Downloading a Report

Download an interactive report back by selecting Download from the Actions menu. 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).

To download a report:

1. Click the Actions menu and select Download.

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