Add filters to refine data - IBM Cognos

To create reports that meet clients’ expectations, provide accurate information, and avoid unpredictable results, advanced business users, report authors, and analysts must understand how to use filters. Also, they must understand the differences when a filter is applied in a report that uses dimensional data source as opposed to relational data sources. This section describes the behavior differences between the two data source types.

Filter reports for relational data sources

Consider the following points when filtering reports for relational data sources.

Summary and detailed filters

When creating a report, which is based on a relational data source, users can filter the data that is retrieved in the queries and apply the filter before or after auto aggregation. To apply a filter before or after auto aggregation, open the Edit Filters dialog box, and make the appropriate selection in the Application section

Creating a filter in a report that is based on a relational data source

Creating a filter in a report that is based on a relational data source

Combined filters

A complex filter is a combination of two or more filters creating AND or OR logic. With this feature, advanced business users can create advanced filtering expressions easily.

Complex filtering expression created using Combined Filter feature

Complex filtering expression created using Combined Filter feature

Filtering features

You can use the Filter menu to create filter expressions easily. For example, the following list shows several commonly used filter expressions:

  • Include x, y and Exclude x, y: Focus on data that is based on the selection, or exclude the selection from the results.
  • Greater and Lower: Retrieve data that is Lower than(<), Lower than (<) or Equal (=), Greater than (>), or Greater than (>) or Equal (=) a specific value.
  • Between x and y and Not Between x and y: Retrieve data that is between or not between selected values.
  • Include Null and Exclude Null: Include or exclude null values for the selected column.
  • Filter features menu
    Filter features menu

Filter reports for dimensional data sources

Use filters to remove unwanted data from reports. Data is retrieved from the database only if it meets the filter criteria. When working with dimensional data sources, you can filter only by members and measures. When creating a report, which is based on a dimensional data source, users can focus the data that is retrieved in the queries using several options:

  • Specifying members instead of data items during report development
  • Applying filters within a set
  • Using the filter function
  • Using context filters
  • Using the Explore menu features

Using any other filtering options available on IBM Cognos Business Insight Advanced can cause unpredictable results.

Focus reports using members

You can specify the members who you want to see in a report using the View Member Tree on the Insertable Objects pane toolbar.

To specify a member, the user clicks one or more members of a dimension and drags them to the report object (list, crosstab, or chart).

Inserting members in a crosstab

Inserting members in a crosstab

View Member Tree option:Using the View Member Tree on the Insertable Objects pane toolbar is a quick option to focus the data that the users want to see. If the users know what they want to see and will always use the same set, they need to choose this option.

Create Sets for Members option: If the Create Sets for Members option is enabled, a new set is created for the selection of members, allowing the user to summarize and create calculations with the data.

Applying filters to members within a set

Filters on dimensional reports need to be applied to a Set of members. Filtering the members in a Set is not the same as relational detail or summary filters. To apply filters in a Set:

  1. Click the Set that you want to filter.
  2. On the top toolbar, click Explore.
  3. Click Filter Set.

Figure shows the Set Filter Condition window. In this window, you can select the kind of filter that you want to apply.

Applying filters to members within a set

You have the following options for filtering:

  • Caption: Filter by the member caption value, which is indexed data
  • Property:Filter by a descriptive data value, which is not indexed data
  • Intersection: Filter by an intersection of members and metrics (tuple) that you Define After applying a filter, you can verify that the filter logic was applied or you can change the logic that was applied by clicking Explorer Edit Set

Visualizing the Set Definition

Visualizing the Set Definition

Context filters

When working with dimensional data, you can use context filters, or slicer filters, to focus your report on a particular view of the data quickly. For example, the following crosstab contains product lines in the rows, years in the columns, and revenue as the measure. We want to filter the values to show us the revenue for only web orders from Asia Pacific. To change the context, you drag Asia Pacific and web from the source tree to the Context filter section of the overview area. The crosstab then shows the revenue for only Asia Pacific and web. Changing the context changes only the values that appear. It does not limit or change the items in the rows or columns. The members that are used as the context filter appear in the report header when you run the report. Any summary values in the report are recomputed to reflect the results that are returned by the context filter.

Context filters: Context filters differ from other filters. When you filter data, members that do not meet the filter criteria are removed from the report. A context filter does not remove members from a report. Instead, their values are filtered, and you see blank cells.

Guideline:When creating context filters, use only members from hierarchies that are not already projected on an edge of the crosstab, and use only one member per hierarchy.

Explore features

IBM Cognos Business Insight Advanced also provides several ways to filter dimensional data using the Explore button:

  • Top or Bottom filters: Focus data on the items of greatest significance to your business question (for example, Top 5 Sales’ Performers or Bottom 10 Clients’ Revenue).
  • Exclude and Include Member: Exclude members from current set or initial set
  • Drill down and drill up: Display parents or children of the selected valued in the dimension hierarchy If the set definition has more than one level, for instance, consider a crosstab with a Top 3 filter applied.

Crosstab with a Top 3 filter applied

Crosstab with a Top 3 filter applied

If you exclude a member from the initial set, the crosstab applies the Top 3 filter again and excludes the selected member.

Exclude a member from the initial set

Exclude a member from the initial set

If you exclude a member from the current set, the Top 3 filter is kept, and the crosstab shows only two values on the edge on which the Exclude logic was applied.

Exclude a member from the current set

Exclude a member from the current set

Using custom filters

When report developers need to create complicated logic for filtering in a dimensional data source, they can create customized expressions to filter members on reports. For example, Lynn Cope, who is a Professional Report Author, wants to filter a crosstab to show only the Product lines that have Revenue greater than USD5,000,000 (this condition hides Outdoor Protection from the results). To implement this logic, Lynn must replace the default row or column with a new expression.

Figure shows the report.

Initial result for the report: Revenue by Product line for 2007

Initial result for the report: Revenue by Product line for 2007

Lynn follows these steps:

  1. In the Insertable Objects pane, on the Toolbox tab, drag Query Calculation to the same place as Product line (Rows area),
  2. In the Name field, type Filtered Product line, select Other expression, and then click OK.
  3. In the Expression Definition section, type the following expression: filter ([Sales (analysis)].[Products].[Products].[Product line], [Revenue] > 5000000)
  4. Click OK.
  5. Creating a filter expression

    Creating a filter expression

    After performing these steps, Lynn notices that the Outdoor Protection product line is
    removed from the results

    Final result for the report: Revenue by Product line for 2007

    Final result for the report: Revenue by Product line for 2007

Suppress data

When a user works with dimensional data sources and creates an analysis nesting dimensions (in a crosstab or chart), the user notices that there are many rows with no values on the report. When working with dimensional data sources, IBM Cognos returns all the members of the hierarchy, even if they do not have values for the metrics.

To avoid this scenario, IBM Cognos Business Insight Advanced provides the Suppress data feature that hides all the rows or columns (or both) that do not have data for the intersections,

Report without the Suppress data feature applied

Report without the Suppress data feature applied

Report with the Suppress data feature applied

Report with the Suppress data feature applied

The rows with all null values are removed.

Example

Lynn Cope, an Advanced Business User for the Great Outdoors company, wants to create a report to show data for the Camping Equipment Product line, for 2007 and 2006, and for GO Americas only.

To create this report:

  1. In IBM Cognos Connection, launch Business Insight Advanced. The Select a package window opens.
  2. Navigate to Cognos Public Folders Samples Models.
  3. Click GO Data Warehouse (analysis) to create a new report that is based on this package.
  4. Click Create New to create a new report, and then click the Crosstab icon.
  5. Suppress feature:When using the Suppress feature, calculations are always
    performed before the suppression.

  6. In the Insertable Objects pane, navigate to Go Data Warehouse (analysis) Sales and Marketing (analysis) Sales Products
  7. Drag Camping Equipment under Products to the Rows area.
  8. Drag Time under the Sales folder to the Columns area.
  9. Drag Revenue under Sales fact to the Measures area.
  10. Initial report showing the Revenue for all Camping Equipment members by year

    Initial report showing the Revenue for all Camping Equipment members by year

  11. Click 2006, press Ctrl, and click 2007.
  12. On the top toolbar, click the Filters icon.
  13. Click Include 2006, 2007

Filtering a report based on a selection

Filtering a report based on a selection

After performing these steps, the report is displayed with the Revenue totals for 2006 and 2007 grouped by Camping Equipment product types Lynn wants to filter this report to show information for GO Americas only.

Revenue by Camping Equipment product types, 2006 and 2007 years only

Revenue by Camping Equipment product types, 2006 and 2007 years only

Lynn decides to slice the report for GO Americas, by creating a Context filter,

Final report filtered for GO Americas scenario

Final report filtered for GO Americas scenario


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Cognos Topics