Create a Simple Filter

You can create a filter that retrieves specific data, such as the orders for a specific customer.

You can also combine two or more filters into a complex filter, add a model filter, and create a prompt report.

If data rows that you want to exclude are included in your filtered result, clear the Apply the filter to individual values in the data source check box when you specify the filter. This removes rows with nulls that are included because of a join between two non-conforming fact tables in the underlying data. Clearing this option can have an impact on query performance.

Procedure

  1. Open a report in IBM® Cognos® Query Studio.
  2. Click the heading of the item you want to filter.

    Tip: You can also add filters to relational and dimensionally modeled relational data sources directly from the data tree without first adding the data item to the report. To do so, right-click the data item and click Filter for report.

  3. Click the filter button Filter icon on the toolbar.
  4. If filters already exist for the item, do one of the following:
    • To edit a filter, click Modify the existing filter.
    • To create a new filter, click Add a filter to data item.
    • To create a combined filter, click Open the "Combine filters" dialog.
  5. Click the Condition box and choose one of the following options:
    • If you want to select items that will appear in the report, click Show only the following.
    • If you want to select items that will not appear in the report, click Do not show the following (NOT).
  6. Depending on the type of data you are filtering, select the items you want from a list or specify the range of values to include.

    If you specify a range, you can specify whether to include or exclude the From and To values.

    If you are filtering text or alphanumeric values, click Search for values or Type in values.

    Note that these options will not appear unless the data source you are using supports search or type in values.

    If you are working with a dimensional data source and you are filtering a level, there may be duplicate names, such as Paris, Ontario and Paris, France.

  7. If you are filtering on relational or dimensionally modeled relational data sources, choose the Type in values option and click the type of operation you want to perform in the Operation box:
    • To filter on specific values, click Exactly Matches.
    • To filter on values that start with specific letters or numbers, click Starts With.
    • To filter on values that end with specific letters or numbers, click Ends With.
    • To filter on values that contain a specific word or number, click Contains.
    • To filter on SQL patterns, click Matches SQL Pattern.

      When matching SQL patterns, you can use these symbols as placeholders: % (percent) to substitute for zero or more characters, or _ (underscore) to substitute for one single character.

    • To filter on a range of values, click Is In Range.
  8. If you filter a date range on relational or dimensionally modeled relational data sources, click the type of operation you want to perform in the Operation box:
    • To filter on a range between two dates, click Range.
    • To filter on a number of days before today, click Last number of days, then type a number in the Number of days before today box.
  9. If you are filtering a measure, click one of the following options under Apply the filter to:
    • To filter details in the report, click Values in the report.
    • To filter group summaries in the report, click Group summaries in the report, and click the report item you want.
    • To filter on individual values in the data source, click Individual values in the data source.
  10. If you are filtering alphanumeric values, select the Apply the filter to individual values in the data source check box to filter on individual values in the data source.
  11. Under Missing values, click the option you want to use to handle missing values in the report:
    • If you want to include missing values, click Include missing values.
    • If you do not want to include missing values, click Leave out missing values.
    • If you want to only see missing values, click Show only missing values.

      When selected, all other options in the Filter dialog box are disabled.

      If set to (Default), missing values are left out if you defined a range filter. If you did not define a range filter, missing values are included.

  12. Click OK.

    By default, the values of any filters applied appear as an additional subtitle.

    Tip: To remove the filter values subtitle, from the Change Layout menu, click Edit Title Area and then clear the Show filters check box.

Results

You can delete a filtered column but keep the filter itself. Click the column you want to delete and click the delete button Delete icon. The Delete dialog box appears, and you can choose to delete or keep filters and sorting associated with the column. For more information about sorting, see Sort Data.