Oracle^® Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide > Filtering Requests in Oracle BI Answers >

Using Column Filters in an Oracle BI Request

A column filter allows you to constrain a request to obtain results that answer a particular question. Together with the columns you select, a column filter determines what the results will contain. For example, depending on the industry you work in, you can use column filters to find out who the top ten performers are, sales for a particular brand, most profitable customers, and so on. You can create column filters for a particular request, and save them to your personal filter folder (My Filters) or to a shared filter folder if you want other users to be able to use them.

A column filter consists of the following elements:

• A column to filter, such as Order Quantity.
• A value to use when applying the filter, such as 10.

  Advanced users can include SQL expressions, session variables, repository variables (defined in the Oracle BI repository), and presentation variables to define or limit the value.

• An operator that determines how the value is applied, such as Less Than.

  For example, if the column contains the number of units sold, the operator is Less Than, and the value is 10, the results include only order quantities only 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 request.

  Filters can be grouped (a capability called parenthetical filtering) to create complex filters without requiring you to know SQL.

• Its value can be constrained by the results of a previously-saved request from the same subject area.

You can also prevent the filter from being replaced during navigation and prompting.

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

This section provides the procedures for working with column filters in Oracle BI Answers. It contains the following topics:

• Creating a Column Filter in Oracle BI Answers
• Saving a Column Filter in Oracle BI Answers
• Applying a Saved Column Filter to an Oracle BI Request
• Editing a Column Filter in Oracle BI Answers
• Editing the Formula for a Column Filter in Oracle BI Answers
• Removing a Column Filter in Oracle BI Answers
• Combining a Column Filter with Other Column Filters in Oracle BI Answers
• Preventing a Filter from Being Replaced During Navigation and Prompting
• Removing a Column Filter in Oracle BI Answers

Creating a Column Filter in Oracle BI Answers

The following procedure explains how to create a column filter in Oracle BI Answers.

If you do not want the column to display in results, you can hide it. For more information, see Editing the Appearance of Column Contents in Oracle BI Answers.

CAUTION:  If you click your browser's Refresh button before you are done creating a column filter, be aware that the browser will reload all frames and discard your changes.

To create a column filter in Oracle BI Answers

1. In Oracle BI Answers, perform one of the following actions:
   • To create a filter for use with a specific request, display a request to which you want to add a filter, click the Criteria tab, and then click the Filter button for the column that you want to include in the filter.

     TIP:   To add a filter for a column that is not included in request, press and hold down the CTRL key at the Criteria tab and click the column name in the selection pane.

   • To create a filter for use with multiple requests, click the New Saved Filter button at the top of the selection pane, and then select the subject area that contains the column you want to use in the filter.

     When the Saved Filter screen appears, select the column from the Columns area in the selection pane.

2. At the Create/Edit Filter dialog box, choose 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, type it into the Value box or select a value from the right column.

   Use the guidelines shown in the following table when choosing an operator and specifying values.

   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 will 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 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. Result will 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 will include only records where there is no data in the column. Sometimes it may 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 world-wide 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 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 ten 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.
   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.
   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.
   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 percent sign character (%) 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 percent sign character (%) 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. Choosing this operator for a column flags it as ready to be filtered by a dashboard prompt. This means when a dashboard prompt is used, results will include only records where the data in the column that is prompted column matches the user's choices. NOTE:  This operator is required for columns included in dashboard prompts where no prefiltered values are desired.

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 next page, or the double left-arrow paging button ( << ) to go back to the previous page.
   • Click triple right-arrow paging button ( >>> ) to advance to the last page, or the triple 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 box to limit the list of values to appear in the All Choices or Limited Choices list for columns that contain text or numbers.

   For example, suppose you want to see results for the East region. If you type an E into the Match box, the list shows only the values that begin with an E. If you have set other constraints in the filter, the Limited Choices list shows only the choices within those constraints.

7. To add an SQL expression or a system variable (defined in the Oracle BI repository), perform the following actions:
   1. Click the Add button and choose the appropriate option.

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

   2. Type the SQL expression or system variable name into the box.
8. To add a presentation variable, perform the following actions:
   1. Click the Add button, choose Variable, and then the Presentation option.

      A Presentation Variable field and a Default field appears.

   2. Type the presentation variable, using correct syntax.

      For more information about using variables, see the chapter about working with requests in Oracle Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide.

   3. Type a default value (Optional) to be used if no value is returned by the presentation variable.
9. To remove a value, SQL expression, system variable, or presentation variable, click the Delete button next to it.

   To remove all definitions, click the Clear Values button.

10. To have this filter constrained by the value of a column from the same subject area in another request, click the Advanced button and choose the following option:

    Filter based on results of another request

    The Filter on Saved Request dialog box appears. For more information, see Using a Saved Oracle BI Request as a Filter.

11. To convert the filter to SQL, click the Advanced button and choose the following option:

    Convert this filter to SQL

    The Advanced SQL Filter dialog box appears. For more information, see Editing the SQL for a Column Filter in an Oracle BI Request.

12. When you are finished, click OK.

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

    NOTE:  If you accessed the Create/Edit Filter dialog from the Edit Column Formula dialog Column Formula tab, the filter will appear in the Insert Filter dialog. For more information, see Editing the Formula of a Column.

    NOTE:  If you accessed the Create/Edit Filter dialog from the Edit Column Formula dialog Bins tab, the filter will appear in the Bins tab. For more information, see Editing the Formula of a Column.

Saving a Column Filter in Oracle BI Answers

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

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, or if your user name does not have the appropriate permissions, this part of the page is blank.

To save a column filter as part of an Oracle BI request

1. At the Criteria tab in Oracle BI Answers, click the Save Request button.
2. At the Choose Folder dialog box, choose the location in which to save the request.
3. For more information about saving requests, see Saving an Oracle BI Request to a Personal or Shared Folder.

To save a column filter for use in other Oracle BI requests

1. In the Filters area on the Criteria tab, click the Save Filter button for the filter you want to save.
2. At the Choose Folder dialog box, choose 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 Shared Filters.

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

3. To specify a subfolder, perform one of the following actions:
   • Navigate to it.
   • Click Create Folder to create a new subfolder.
   • Type the path in the Folder box.
4. Type a name for the filter.

   The name will appear in the selection pane.

5. (Optional) Type a description for the filter.

   Descriptions are displayed when Oracle BI administrators use the Catalog Manager.

6. Click OK.

   When you click the Refresh Display link in the selection pane, the filter is listed under the Filters folder in either My Filters or in a shared filters folder.

To display the properties for a saved filter in Oracle BI Answers

• Locate the filter in the selection pane and click it.

Applying a Saved Column Filter to an Oracle BI Request

You can apply a saved column filter to a request. 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 Oracle BI request

1. At the Criteria tab in Oracle BI Answers, display a request to which you want to add a saved column filter.
2. In the selection pane, navigate to the appropriate Filters folder and click the saved filter that you want to apply.

   The Apply Saved Filter dialog box appears.

3. Specify your choices, if any, in the Filter Options area.
4. Click OK.

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

Editing a Column Filter in Oracle BI Answers

If your user ID has the appropriate permissions, you can edit a column filter to change its properties.

To edit a column filter in Oracle BI Answers

1. In the Filters area on the Criteria tab in Oracle BI Answers, click the menu button for the filter you want to edit and choose Edit Filter.

   The Create/Edit Filter dialog box appears.

   NOTE:  The Edit Filter option is available only if you have permission to edit the filter.

2. Make your changes, and then click OK.

Editing the Formula for a Column Filter in Oracle BI Answers

If your user ID has the appropriate permissions, you can edit the formula for a column filter.

To edit the column formula for a filter in Oracle BI Answers

1. In the Filters area on the Criteria tab in Oracle BI Answers, click the menu button for the filter whose formula you want to edit and choose the following option:

   Edit Column Formula

2. At the Column Formula tab, type the formula into the Column Formula area.

   For more information, click the help button.

Removing a Column Filter in Oracle BI Answers

You can remove a single filter or all filters from an Oracle BI request.

NOTE:  If the request was saved previously with any filters applied, save the request again to remove the filters.

To remove a column filter from an Oracle BI request

• In the Filters area on the Criteria tab in Oracle BI Answers, perform one of the following actions:
  • To remove a single filter from a request, click the Delete button for the filter.
  • To remove all filters from a request, click the Remove Filters button.

Combining a Column Filter with Other Column Filters in Oracle BI Answers

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.

You can save the multi-column filter. For more information, see Combining a Column Filter with Other Column Filters in Oracle BI Answers.

To combine a column filter with other column filters in Oracle BI Answers

1. At the Criteria tab in Oracle BI Answers, add at least two column filters to a request, or add at least two column filters to a saved filter.

   The filters are listed in the Filters area 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 ellipses button [. . .] and choose 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.

NOTE:  You can protect the contents of a saved filter only by editing the saved filter. However, you cannot protect the Is Prompted operator.

To protect a filter from changing during navigation and prompting

• In the Filters area on the Criteria tab in Oracle BI Answers, click the menu button for the filter you want to protect and select the option Protect Filter.

  A check mark appears next to the option when it is selected.

Related Topic

Using Prompts to Simplify Filtering in an Oracle BI Request