Dynamically Selecting Filters Before Auto Aggregation & After Aggregation in Cognos

Add a filter expression to focus a report and minimize processing time by excluding unwanted data.

Filter before aggregation

• If the filter is applied before an aggregation, then non aggregated records (Detail records, applies a filter to individual row’s data.) are filtered.

• Detail filters are applied to the data source. They are boolean expressions used to exclude database rows based on non-aggregated values or to exclude lower level groups based on leaf aggregates.

• Detail filter is applied to the whole body of the report.

Procedure:

• Add the following items to the list container

• Product line

• Product type

• Quantity

• Define group on the product line

• Define total on quantity

• Select quantity data item

• From menu bar, select data

• Select filters

• In the operator list box, select >

• In the value text box, type 75000 and Click on OK

• From menu bar select data

• Select filters

• Select Edit filters

• Select Detail filters

• Select Filter before auto aggregation radio button

• Save and run the report

Filter Before Aggregation:

Filter after aggregation:

If the filter is applied after an aggregation, then  (Summary records) are filtered.

Summary filters are boolean expressions, used to exclude specific groups based on the group aggregates. They are based on summary calculations.

                                    Frequently Asked Cognos Interview Questions & Answers

Summary filter is applied to only at footer level.

• Add the following items to the list container

• Product line

• Product type

• Quantity

• Define group on the product line

• Define total on quantity

• Select quantity data item

• From menu bar, select data

• Select filters

• Select custom filter

• In the operator list box, select >

• In the value text box, type 50000 and Click on OK

• From menu bar select data

• Select filters

• Select Edit filters

• Select Detail filters

• Select Filter after auto aggregation radio button

• Save and run the report

Filter After Aggregation: