Print      Open PDF Version of Online Help

Analytics > Designing an Analysis > Step 1: Defining Criteria in Analytics > Adding Filters to Columns in Analytics

Previous Topic

Next Topic

Adding Filters to Columns in Analytics

In the Criteria tab, you can set up filters for columns in your report. A filter limits the results that appear when a report is run, showing only those results that match the criteria.

A column filter consists of the following elements:

  • A column to filter, such as Account Type.
  • A value to use when applying the filter, such as 10 (a SQL expression or a variable can be used instead of a value when necessary).
  • An operator that determines how the value is applied, such as Less Than.

    For example, if the column contains the quantity sold, the operator is Less Than, and the value is 10, the results include only order quantities where less than 10 units were sold.

A column filter can also have the following characteristics:

  • It can be combined with other column filters to further constrain the results of a report.
  • Filters can be grouped to create complex filters.
  • Its value can be constrained by the results of a previously saved report.
  • A column filter applies only to the column in which it is built. It does not affect other columns.

The filter is translated into a WHERE clause in the SQL SELECT statement. The WHERE clause is used to limit the rows returned to those that fit the specified constraints. Advanced users can enter the SQL for a filter directly.

NOTE: If you selected the option to add new picklist values when importing data, those new picklist values may not show up in the reports for 24 hours. Therefore, you may not be able to use the new picklist values as filters during that time period.

Creating Column Filters

To create a filter without adding the column to the report

  • In the Filters pane, click Create a filter for the current Subject Area icon, select More Columns..., and then select the column from the Select Column dialog.

To create a column filter

  1. In the Criteria tab, select the Filter option in the column where you want to create the filter.
  2. In the New Filter dialog box, select an operator from the Operator drop-down list.

    Use the guidelines shown in the following table when choosing an operator and specifying values. For more information on operators, see Usage Notes on Operators, which appears after this procedure.

    Operator

    Usage Guidelines

    is equal to or is in

    Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results will include only records where the data in the column matches the value in the filter.

    is not equal to or is not in

    Valid for a column that contains text, numbers, or dates. Specify a single value or multiple values. Results will 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 will 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 will 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 will 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 will 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. Results will include records for both the specified values and the values in between them.

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

    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 will include only records where there is data in the column.

    is in top

    Valid for a column that contains text, numbers, or dates. Specify a single value. Results will 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 10 performers.

    is in bottom

    Valid for a column that contains text, numbers, or dates. Specify a single value. Results will 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 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 will include only records where the data in the column contains all 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 will include only records where the data in the column contains at least one 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 will include only records where the data in the column does not contain any of the values in the filter.

    begins with

    Valid for a column that contains text, numbers, or dates. Specify a single value. Results will 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 will 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 percentage symbol (%) as a wild card character. You may specify up to two percent sign characters in the value. Results will 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 percentage symbol (%) as a wild card character. You may specify up to two percent sign characters in the value. Results will 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. Setting a column to is prompted flags it to be filtered by a value passed to it from another report.

    NOTE: Use this option when linking reports together through navigation (see Step 2: Reviewing Results and Creating Layouts). One or more columns in the report you drill into must contain the is prompted filter for that report to display rows limited to the value selected and drilled into from the parent report.
  3. Enter a value in the Value field.

    The Value field has options for entering formulas, dates, variables, and so on. The following steps describe some of these options.

  4. For date columns you can click the calendar icon to the right of the Value field and use the Select Date dialog box to specify a date.
  5. For some columns you can select specific row to appear in the report by clicking the down arrow icon to the right of the Value field and then selecting the rows to include.
  6. For some columns you can use the Search feature (click the search icon to the right of the Value field) and select specific values to include in the analysis. Move your selected values from the Available column into the Selected column using the arrow controls.
  7. To add an SQL expression or a variable (session, repository, or presentation, do the following:
    1. Under the Value field click Add More Options and choose SQL Expression, Session Variable, Repository Variable, or Presentation Variable.

      A new field appears under the Value field.

    2. Enter the SQL expression or variable name into the new field.

      SQL expressions can contain function calls that operate on column values. For more information, see Using Functions in Analytics and Session Variables in Analytics.

      To remove a value, SQL expression, or session variable, click the X icon next to it.

  8. To have this filter constrained by the value of a column in another analysis, see Using Saved Reports as Filters later in this topic.
  9. To convert the filter to SQL, click the Convert this filter to SQL check box.
  10. When you are finished, click OK.

    The filter appears in the Filters area on the Criteria tab or in the Catalog.

Usage Notes on Operators

Some operators do similar, but not exactly the same functions. Consider the similarities and differences between the following operators:

  • is equal to
  • is in
  • contains all
  • contains any
  • is LIKE

Is equal to or is in searches for an exact match between the search criteria and the values in the data base. For example, if the criterion is Pat Lee, then only Pat Lee exactly matches and passes the filter. Because all searches are case sensitive, values such as pat Lee or Pat lee do not pass the filter.

Contains all searches for all the values that match the criteria, and all the values must be in the values to pass the filter, as in an AND search. For example, if the criteria is to search for two values; value = Pat and value =Lee, then Pat Lee and Lee Pat pass the filter, but pat Lee, Pat Smith and Chris Lee do not pass the filter because they do not contain all of the criteria (Pat AND Lee).

Contains any searches for values that match the criteria, but is less strict in that it does not require all the values, as in an OR search. For example, if the criterion is to search for two values; value = Pat, and value = Lee then Pat, Pat Smith, Chris Lee, and Lee all pass the filter because they meet the criteria of Pat OR Lee.

Is LIKE searches for patterns, and as such requires the use of one or two percentage symbols (%) as a wild card character. For example, if the search criteria is value = P%t %ee, then Pat Lee, Pit smee, and Packet trainee all pass the filter, but pat Lee does not.

For optimum search speed, reduce or eliminate the use of pattern matching operators such as Contains All, Contains Any, and Is LIKE. If you want to search by exact values, do not use pattern matching operators instead of exact operators.

Saving Column Filters

You can save a filter as part of an analysis or for reuse in other analyses. If a filter is for a specific analysis and you save the analysis, the filter is saved as part of the analysis and will be applied every time the analysis runs. You can also save the filter explicitly so it can be used in other analyses.

Saved filters and folders containing filters for the subject area appear following the name of the subject area, where applicable.

To save a column filter as part of an analysis

  1. In the Criteria tab, click Save.
  2. In the Save As dialog box, select the location in which to save the analysis and then click OK.

To save a column filter for use in other analyses

  1. In the Criteria tab, in the Filters section, click More options (>>) and then choose Save Filters.
  2. In the Save As dialog box, select a folder in which to save the filter:
    • To save the filter for your personal use, click My Folders.

      Filters saved in My Folders are available only to you.

    • To save the filter for use by others, select the Company Wide Shared Folder.

      Filters saved in a public folder are available to other users that have permission to access the folder.

  3. Enter a name (maximum 512 characters) for the filter.
  4. (Optional) Enter a description for the filter.
  5. Click OK.

To display the properties for a saved filter

  • In the Criteria tab, in the Filters section, select the filter and click the View Saved Filter icon.

Applying Saved Column Filters to Analyses

You can apply a saved column filter to an analysis. You can apply either the contents of the filter or a reference to the filter.

When you apply the contents of a saved column filter, the actual contents of that filter are copied into the Filters area on the Criteria tab. This allows you to manipulate the filter criteria without altering the saved filter. When you apply a reference to a saved filter, the saved filter is referenced only by its name, and you can view but not alter its contents.

To apply a saved column filter to an analysis

  1. In the Criteria tab, in the Catalog pane, navigate to the saved filter.
  2. Select the saved filter and click the Add More Options icon at the top of the Catalog pane.

    The Apply Saved Filter dialog box opens.

  3. Specify the filter options (Clear all existing filters before applying and Apply contents of filter instead of a reference to the filter) and then click OK.

    The filter appears in the Filters area on the Criteria tab.

Editing Column Filters

You can edit a column filter to change its properties.

To edit a column filter

  1. In the Criteria tab, in the Filters section, hover over the Filter and when the menu appears click the Edit icon.

    The Edit Filter dialog box appears.

  2. Make your changes, and then click OK.

Removing Column Filters

You can remove a single filter or all filters from an analysis.

NOTE: If the analysis was saved previously with any filters applied, you must save the analysis again to permanently remove the filters.

To delete a column filter from an analysis

  • In the Criteria tab, in the Filters section, hover over the filter until the menu appears and then click the Delete icon.

Combining Column Filters with Other Column Filters

Combining column filters, also known as parenthetical filtering, allows you to create complex filters without requiring you to know SQL.

You can combine column filters with AND and OR operators. The AND operator means that the criteria specified in each filter must be met. This is the default method for combining column filters. The OR operator means that the criteria specified in at least one of the column filters must be met.

To combine a column filter with other column filters

  1. In the Criteria tab, add at least two column filters to an analysis, or add at least two column filters to a saved filter.

    The filters are listed in the Filters section with an AND operator between them.

  2. To change an AND operator to an OR operator, click it.
  3. As you add column filters, click AND and OR operators to group filters and construct the desired filter combinations.

    Combined filters elements are bounded by boxes.

  4. To duplicate, delete, or ungroup bounded elements, click the Edit Filter Group button and select the appropriate option.
  5. To copy or paste filters, hover over the filter and choose Copy Filter or Paste Filter.

Preventing a Filter from Being Replaced During Navigation and Prompting

You can protect the contents of a filter in a report from being affected during navigation and prompting. A protected filter is always applied to results.

To protect a filter from changing during navigation and prompting

  1. In the Criteria tab, in the Filters section, hover over the filter until the menu appears and click Edit Filter.
  2. In the Edit Filter dialog box, check Protect Filter and click OK.

Using Saved Reports as Filters

Filters can be combined with other filters, as well as be based on the values returned by another report. Any saved report that returns a column of values can be used to filter the selected column in your report.

To create a filter based on the results of another saved report

  1. In the Criteria tab, choose Filter in the column where you want to create the filter.
  2. In the New Filter dialog box, pull down the Operator drop-down menu and choose "is based on results of another analysis".
  3. In the Saved Analysis field, click Browse and navigate to the saved report.
  4. In the Relationship drop-down menu choose an operator.
  5. In the Use Values in Column field, select the column that is to be used for the filter.

    If the saved report contains a column name that matches the column where you are creating the filter, that column name appears first in the list of values in the Use Values in Column field. You can select a different column.

  6. Click OK.

    The filter appears in the Filters section in the Criteria tab.

Editing the SQL for a Column Filter

You can edit the logical SQL WHERE clause to be used as a filter. While generally not necessary, this feature is available for users who need advanced filtering capability. For a comprehensive description of SQL syntax, see a third-party reference book on SQL, a reference manual on SQL from one of the database vendors, or an online reference site. For more general information about using SQL, see Using Functions in Analytics.

To edit the SQL generated for a column filter

  1. In the Filters pane hover over the SQL-generated filter and click the Edit icon.
  2. The Advanced SQL Filter dialog box opens.
  3. Enter your modifications into the text box and then click OK.

    The filter appears in the Filters area on the Criteria tab. When you have chosen this option, editing the filter will always display the SQL that you entered.

Example: Identifying Customers with the Most Sales Volume

The following example reports data on the ten customers with the most sales volume in 2003.

"Close Date"."Calendar Year" = 2003

AND RANK("Opportunity Metrics"."Closed Revenue") <= 10
Published 7/3/2018 Copyright © 2005, 2018, Oracle. All rights reserved. Legal Notices.