Print      Open PDF Version of Online Help

Reports > Step 1: Defining Criteria > Adding Filters to Columns

Previous Topic

Next Topic

Adding Filters to Columns

In Oracle CRM On Demand Answers, in the Define Criteria page, you can set up filters for columns in your report. A filter limits the results that appear when a report is run. Oracle CRM On Demand Answers shows 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 from the same subject area 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 from the same subject area.
  • 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

You can create a filter on any column in your reports. If you do not want the column to display in results, you can hide it.

SHOW ME

CAUTION: If you click your browser's Refresh button before you have finished creating a column filter, the browser reloads the page and discards your changes.

To create a filter without adding the column to the report

  • Control-click the column.

To create a column filter

  1. In Oracle CRM On Demand Answers, in the Define Criteria page, click the New Filter button in the column where you want to create the filter.
  2. In the Create/Edit Filter dialog box, select an operator from the Operator drop-down list.

    The choices for operators and values appear in the left column. Depending on the type of column you select, additional options may appear in the right column, such as calendar buttons for specifying a date range, or a text-matching box for limiting long lists of values.

  3. To specify a value, enter it in the Value box, or click All Choices to view the available values in the column, and select a value from the list.

    Multiple values can be added by clicking Add and selecting Value after entering data for the current value field.

    NOTE: To use All Choices/Limited Choices on a Control-Click filter, you need to select at least one column in the Analysis. The selected column does not need to be the one to which you are adding the filter.

    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: 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.
  4. Use the paging controls to navigate the choices when there are many choices for the column:
    • Click on a specific page number to navigate to that page.
    • Click the double right-arrow paging button ( >> ) to advance to the last page or the double left-arrow paging button ( << ) to go back to the first page.
  5. Use the calendar buttons to specify the date range for columns that contain dates.

    To specify a single date, specify the same date for the beginning and ending date.

  6. Use the Match drop-down list to view the available values to use in a filter. If desired, specify criteria to constrain the values to be returned (this is not mandatory). Then click the All Choices link, and select a value from the list returned.

    For example, suppose you want to see results for the East region. If you enter an E into the text box and select "begins with" from the Match drop-down list, the list shows only the values that begin with an E.

  7. To add an SQL expression or a session variable, do the following:
    1. Click the Add button and select the appropriate option.

      The label on the Value box changes to reflect your selection.

    2. Enter the SQL expression or session variable name into the box.

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

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

    To remove all definitions, click the Clear Values button.

  9. To have this filter constrained by the value of a column from the same subject area in another analysis, click the Advanced button and select the Filter based on results of another request option.

    The Filter on Saved Request dialog box appears.

  10. To convert the filter to SQL, click the Advanced button and select the Convert this filter to SQL option.

    The Advanced SQL Filter dialog box appears.

  11. When you are finished, click OK.

    The filter appears in the Filters area on the Define Criteria page or on the Saved Filters page.

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 criteria 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 criteria 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. If there are no saved filters for the subject area, this part of the page is blank.

To save a column filter as part of an analysis

  1. In the Define Criteria page, click Save.
  2. In the Save Analysis 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 Define Criteria page, in the Filters section, click Save Filter.
  2. In the Save Filter dialog box, select a folder in which to save the filter:
    • To save the filter for your personal use, click My Filters.

      Filters saved in My Filters are available only to you.

    • To save the filter for use by others, click Public Filters.

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

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

To display the properties for a saved filter

  • In the Define Criteria page, in the Filters section, click the Filter Options icon next to the filter whose properties you want to display, and then select the Edit Filter option.

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 Define Criteria page. 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 Define Criteria page, in the Filters section, click Open Saved Filter.
  2. Navigate to the appropriate Filters folder, select the saved filter that you want to apply, and then click OK.
  3. In the Apply Saved Filter dialog box, in the Filter Options section, specify your choices, if any.
  4. Click OK.

    The filter appears in the Filters area on the Define Criteria page.

Editing Column Filters

You can edit a column filter to change its properties.

To edit a column filter

  1. In the Define Criteria page, in the Filters section, click the Filter Options button for the filter you want to edit, and then select Edit Filter.

    The Create/Edit Filter dialog box appears.

  2. Make your changes, and then click OK.

Editing the Formula for a Column Filter

You can edit the formula for a column filter. The edits that you make apply only to the filter's use of the column; a formula can contain calls to functions to perform more advanced calculations. See Using Functions in Analyses.

To edit the column formula for a filter

  1. In the Define Criteria page, in the Filters section, click the Filter Options button for the filter, and then select Edit Column Formula.
  2. Click the Column Formula tab.
  3. Type the formula into the Column Formula area.
  4. Click OK to save your changes.

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, save the analysis again to remove the filters.

To remove a column filter from an analysis

  • In the Define Criteria page, in the Filters section, click the X 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 Define Criteria page, 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 cut, copy, or ungroup bounded elements, click the Edit Filter Group button and select the appropriate option.

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

  • In the Define Criteria page, in the Filters section, click the Filter Options button for the filter, and then select Protect Filter.

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 Define Criteria page, click the New Filter button in the column where you want to create the filter.
  2. In the Create/Edit Filter dialog box, click Advanced, and then select the Filter based on results of another request option.
  3. In the Filter on Saved Request dialog box, in the Relationship field, select the appropriate relationship between the results and the column to be filtered.
  4. Click Browse and navigate to the saved report.
  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 Define Criteria page.

    NOTE: If you later want to edit the filter, you can edit only the information shown in the Filter on Saved Request dialog box.

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 in Oracle CRM On Demand Answers, see Using Functions in Analyses.

To edit the SQL generated for a column filter

  1. While in the Create/Edit Filter dialog box, click Advanced, and then select the Convert this filter to SQL option.
  2. Enter your modifications into the text box and then click OK.

    The filter appears in the Filters area on the Define Criteria page. Once 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 1/9/2017 Copyright © 2005, 2017, Oracle. All rights reserved. Legal Notices.