New Filter dialog

Use this dialog to create or edit a column filter, to specify a condition under which an action link is to be enabled, or to specify a condition for applying graph formatting.

For more information, see:

This dialog is also displayed as:

Edit Condition dialog
Edit Filter dialog
New Condition dialog

Components

Note:

When this dialog is displayed as the New Condition dialog or the Edit Condition dialog, not all components are available.

Column

Displays the name of the column for the filter.

Edit Formula

Use this button to display the "Edit Column Formula dialog" where you can modify the column's formula for use with the filter that you are creating.

Data Time Zone

Use this button to select the time zone.

Operator

Use this list to select the operator to apply to the value specified in the Value. For guidelines, see "Operators."

Note that, if your repository is configured for double columns, and you are creating the filter on a display column and select the contains all, contains any, does not contain, begins with, ends with, is LIKE (pattern match), or is not LIKE (pattern match) operator, then filtering is always done by display values, not code values. For more information on double columns, see "Understanding the Double Column Feature."

Value

Use this field to specify one or more values to use when applying the filter or condition. You can:

Type values.
Select values from the list box or calendar.

The number of values that display in this field is determined by the MaxDropDownValues configuration setting. The administrator can modify this setting. For information about this configuration setting, see "Manually Changing Presentation Settings" in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

To search for specific values from the list box, click Search in the list box. The "Select Values dialog" is displayed, where you can search for and select values.

If the analysis has other filters, the following options appear to the right of Search:
- Limited Values — Use this option to limit the values by any other filters that are associated with the analysis.
- All Values — Use this option to list all values, without limiting the values by any other filters that are associated with the analysis.

If your repository is configured for double columns, and you are creating the filter on a display column, then by default you specify display values. However, if your organization allows the display of code values, then you can specify code values rather than display values, but only if you use one of the following operators:

is equal to / is in
is not equal to / is not in
is between

To specify code values, select the Select by Code Column box.

Note:

If you create or modify a filter or condition using code values and, subsequently, the code column for that value is removed from the Presentation layer using the Administration Tool, then filtering by code values remains in effect (although the code values are not visible). When you edit the filter or condition, the code value (for example, 1) is converted to the equivalent display value (for example, Excellent).

For more information on double columns, see "Understanding the Double Column Feature."

Select by Code Column

Available only if your repository is configured for double columns, you are creating the filter on a display column whose code column is exposed in the repository Presentation layer, and you selected either the is equal to / is in, is not equal to / is not in, or is between operator.

Use this box to allow code values to be specified in the Value field. When this box is selected, the list box displays both the code values and the display values (for example, 1 - Beverages) for selection and the Filter by box is selected but disabled. When this box is deselected, the list box displays only the display values (for example, Beverages).

Filter by Code Column

Available only if your repository is configured for double columns and you selected either the is equal to / is in, is not equal to / is not in, or is between operator.

Use this box to specify whether the column is to be filtered by code values or display values. Select this box to filter by code values. Deselect this box to filter by display values.

Saved Analysis

Available if you selected the is based on the results of another analysis operator.

Use this field to specify the saved analysis whose results are to be the basis for the filter.

Relationship

Available if you selected is based on the results of another analysis in the Operator field.

Use this list to select the appropriate operator to determine the analysis' column results that set the filter.

Use values in column

This field displays if you selected the is based on the results of another analysis operator.

Use this list to view a matching column name in the analysis, if there is one. To use another column, then select it instead. The values in this column set the filter.

Add More Options

Available for all operators except is prompted or the is based on the results of another analysis.

Click this button to add a SQL expression or a session, repository, or presentation variable to the filter. You can add one or more of these options. When you select an option, a corresponding field is displayed where you specify either a SQL expression or the name of the variable.

When this dialog is displayed as the New Conditions dialog from the "Column Properties dialog: Conditional Format tab" or as the Edit Condition dialog from the "Edit Action Link dialog," you can add only a presentation variable.

For more information on variables, see "Using Variables."

SQL Expression

Available if you selected SQL Expression from the Add More Options list.

Enter or paste a SQL expression.

Session Variable

Available if you selected Session Variable from the Add More Options list.

Enter the name of the session variable to use, for example USER.

Repository Variable

Available if you selected Repository Variable from the Add More Options list.

Enter the name of the repository variable to use, for example prime_begin.

Variable Expr.

Available if you selected Presentation Variable from the Add More Options list.

Enter the name of the presentation variable to use, for example MyFavoriteRegion.

(default)

Available if you selected Presentation Variable from the Add More Options list.

(Optional) Specify a default value to be used if no value is returned by the presentation variable.

Delete

Click this button to delete the corresponding value and field.

Clear All

Click this button to remove all values and codes. If you added a SQL expression or variable to the filter, then clicking the Clear Values button deletes all related values and fields.

Protect Filter

Select this option to protect the filter's value from being overridden by either a prompt value or when the user navigates to another analysis. When the user navigates from one analysis to another analysis, any prompt values that were specified in the first analysis transfer to the second analysis.

Convert this filter to SQL

Select this option to convert the filter that you have built to a SQL WHERE clause that you can edit manually. Note that after you convert a filter to SQL code, you can view and edit the filter item as SQL code in the "Advanced SQL Filter dialog," only. You can no longer view and edit the filter in the Edit Filter dialog.

Operators

Use the guidelines shown in Table E-4 when choosing an operator and specifying the required values. The operator list from which you can choose is populated based on the function you are performing (for example, creating a filter or creating a dashboard prompt) and the type of column that you selected.

Table E-4 Guidelines for Choosing an Operator When Creating a Column Filter

Operator	Usage Guidelines
is equal to / is in	Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column matches the value in the filter.
is not equal to / is not in	Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column does not match the value in the filter.
is less than	Valid for a column that contains numbers or dates. Specify a single value. Results include only records where the data in the column is less than the value in the filter.
is greater than	Valid for a column that contains numbers or dates. Specify a single value. Results include only records where the data in the column is greater than the value in the filter.
is less than or equal to	Valid for a column that contains numbers or dates. Specify a single value or multiple values. Results include only records where the data in the column is less than or the same as the value in the filter.
is greater than or equal to	Valid for a column that contains numbers or dates. Specify a single value or multiple values. Results include only records where the data in the column is greater than or the same as the value in the filter.
is between	Valid for a column that contains numbers or dates. Specify two values. Result include only records where the data in the column is between the two values in the filter.
is null	Valid for a column that contains text, numbers, or dates. Do not specify a value. The operator tests only for the absence of data in the column. Results include only records where there is no data in the column. Sometimes it might be useful to know whether any data is present, and using the is null operator is a way to test for that condition. For example, suppose your business has a worldwide address book and you want to extract the United States addresses only. You could do this by checking for the presence or absence of data in the "State" field. This field should be unpopulated (null) for non-United States addresses and populated (not null) for United States addresses. You can obtain a list of United States addresses without the need to check the column for a specific value.
is not null	Valid for a column that contains text, numbers, or dates. Do not specify a value. The operator tests only for the presence of data in the column. Results include only records where there is data in the column.
is ranked first	Valid for a column that contains text or dates. Specify a single value. Results include only the first n records, where n is a whole number specified as the value in the filter. This operator is for ranked results. For example, you could use this operator to obtain a list that contains the first ten brand names in alphabetical order.
is ranked last	Valid for a column that contains text or dates. Specify a single value. Results include only the last n records, where n is a whole number specified as the value in the filter. This operator is for ranked results. For example, you could use this operator to obtain a list of the dates of the last ten sales transactions.
is in top	Valid for a column that contains numbers. Specify a single value. Results include only the first n records, where n is a whole number specified as the value in the filter. This operator is for ranked results. For example, you could use this operator to obtain a list of the top ten sales in dollars.
is in bottom	Valid for a column that contains numbers. Specify a single value. Results include only the last n records, where n is a whole number specified as the value in the filter. This operator is for ranked results. For example, you could use this operator to obtain a list of the customers reporting the fewest numbers of problems.
contains all	Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column contains all of the values in the filter.
does not contain	Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column does not contain any of the values in the filter.
contains any	Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results include only records where the data in the column contains at least one of the values in the filter.
begins with	Valid for a column that contains text, numbers, or dates. Specify a single value. Results include only records where the data in the column begins with the value in the filter.
ends with	Valid for a column that contains text, numbers, or dates. Specify a single value. Results include only records where the data in the column ends with the value in the filter.
is LIKE (pattern match)	Valid for a column that contains text. Specify a single value or multiple values. Requires the use of a percent sign character (%) as a wildcard character. You might specify up to two percent sign characters in the value. Results include only records where the data in the column matches the pattern value in the filter.
is not LIKE (pattern match)	Valid for a column that contains text. Specify a single value or multiple values. Requires the use of a percent sign character (%) as a wildcard character. You might specify up to two percent sign characters in the value. Results include only records where the data in the column does not match the pattern value in the filter.
is prompted	Valid for a column that contains text, numbers, or dates. Choosing this operator for a filter's column flags it as ready to be filtered by a prompt. This means when a prompt is used, results include only records where the data in the column that is prompted matches the user's choices. Note: This operator is required for columns included in prompts where no prefiltered values are desired.
is based on the results of another analysis	Valid for a column that contains text, numbers, or dates. This operator constrains the filter by using the value of a column from the same subject area in another saved request.
prompt user	Valid for a column that contains text, numbers, or dates. Choosing this operator enables the user to select the dashboard or analysis' prompt operator at runtime.