Creating a filter based on one data item

Use the Create Custom Filter option to create a simple filter based on one data item.

About this task

If you are viewing a report in the interactive viewer, some options, such as Prompt for values when report is run in viewer, are not available.

The filter criteria that you set depends on the type of item that you are filtering on. The types are string, numeric, and date.

Tip: You can also define filters in Query explorer. Click the Queries icon , and click a query. For more information, see Add a query to a relational report.

1. On the canvas, select the item in the report that you want to filter on, and in the on-demand toolbar, click the Filters icon , and then click Create Custom Filter.

   If more than one query is defined in the report, you must first select a data container object that is linked to a query.

   The Filter condition window opens.

If the item you selected is a string, then do the following steps:

2. On the Individual tab, you can select values to keep from a list of available values or manually enter items:
   1. Under Available items, select all values in the list, or select individual values.
      The list of available items is created from the values in the data source.
      The items you select appear under Selected items.
   2. Under Selected items, click either Keep these values or Exclude these values.
   3. To input your own values, click Manually input items.
      This option is handy if you want to use values in a delimited text file. You can copy values from the file and paste them in to the window.
   4. In the drop-down list, select the delimiter that separates the items.
      You can choose comma, new line, space, or a plus sign as a delimiter.
   5. Copy the values from the file and paste them in to the window and then click Add.
      The items appear under Selected items.
   6. Under Advanced settings, you can include missing values for the following conditions:

      null

      If you want to include an item that has no value, select this check box.

      empty

      If you want to include an item with a length of zero, select this check box. That is, "".

      whitespace

      If you want to include an item that contains only spaces, select this check box. For example, " ".

3. On the Condition tab, you can select a condition and then enter the value to match in the condition:
   1. Select one of the conditions in the drop-down list.
      You can choose contains, starts with, or matches SQL pattern.
   2. Click Input condition, type the condition, and press Enter.
      The condition appears in the list.
   3. By default, Case sensitive filtering is applied to characters in the string. You can optionally change the setting to Case insensitive.
   4. Under Advanced settings, you can include missing values for the following conditions:

      null

      If you want to include an item that has no value, select this check box.

      empty

      If you want to include an item with a length of zero, select this check box. That is, "".

      whitespace

      If you want to include an item that contains only spaces, select this check box. For example, " ".

If the item you selected is numeric, you can define a range of values or individual values:

4. On the Range tab, you can define a range of values:
   1. Under Add a condition, select either Keep these values or Exclude these values.
   2. Select a comparison statement from the drop-down list.
      Depending on the comparison statement you select, you might have to select one value or From and To values.
5. On the Individual tab, you can manually input values:
   1. Click Manually input items.
      This option is handy if you want to use values in a delimited text file. You can copy values from the file and paste them in to the window.

      The Ignore empty items option can be used to either include or remove the empty values that were copied from the file. By default, the empty values are removed.

   2. In the drop-down list, select the delimiter that separates the items.
      You can choose comma, new line, space, or a plus sign as a delimiter.
   3. Copy the values from the file and paste them in to the window and then click Add.
      The items appear under Selected items.
   4. Under Selected items, click either Keep these values or Exclude these values.
6. Under Advanced settings, you can include missing null values by selecting the null check box.
   A column that doesn't have a value is null.

If the item you selected is a date, then do the following steps:

7. On the Range tab, you can define a range of values:
   1. Under Add a condition, select either Keep these values or Exclude these values.
   2. Select a comparison statement from the drop-down list.
      Depending on the comparison statement you select, you might have to select one value or from and to values.
8. On the Individual tab, you can select dates to keep or exclude from a list of available values or manually enter dates:
   The list of available items is created from the values in the data source.
   1. Under Available items, select all dates in the list, or select individual dates.
      The items you select appear under Selected items.
   2. Under Selected items, click either Keep these values or Exclude these values.
   3. To input your own dates, click Manually input items.
      This option is handy if you want to use values in a delimited text file. You can copy values from the file and paste them in to the window.
   4. In the drop-down list, select the delimiter that separates the dates.
      You can choose comma, new line, space, or a plus sign as a delimiter.
   5. Copy the dates from the file and paste them in to the window and then click Add.
      The dates appear under Keep these values or Exclude these values.
9. Under Advanced settings, you can include missing null values by selecting the null check box.
   A column that doesn't have a value is null.

For all types of filters, there are some common settings you can define.

10. Click Settings and then do the following steps:
    1. To prompt for filter values when the report is run, select Prompt for values when report is run in viewer, and type a name for the prompt.
       Tip: If you leave the Prompt for values check box selected (the default) and run a report that contains the same parameter name that appears in your My parameters pane, you are not prompted to provide a value for the parameter. The system automatically uses the default values that are set for the users role. Even if the parameter on the report is optional, the default value for the global parameter is passed.
    2. To allow the filter criteria to be changed in the viewer, select Filter can be changed in the viewer.
       Note: Report consumers can change the filter only if the report is fully interactive.

       Note: Low-level filters, such as ones defined in the expression editor, cannot be changed in the viewer.
    3. Select one of the following items:
       • Individual values in the data source
       • Aggregated values in the data source
11. Click OK when you're finished defining the filter conditions.