Cognos Report Studio: Filters

 Understanding Detail and Summary Filters

There are two main types of filters you can add to your report in Report Studio – Detail filters and Summary filters. Detail filters will limit rows within the report. Summary Filters will limit groups that have been defined within the report.

Detail Filters

Detail filters will limit your data by each row of data returned by the database. If you add a detail filter using the filter icon on the toolbar while working on the report, Cognos will add a filter to the detail object in the query section.

There are two main properties of a detail filter - Application and Usage.

Usage will determine whether this filter is required (the user has no choice but to use this filter), optional (the user can decide to use or ignore this filter), or disabled (the filter is in the report specification but not being used). Disabled will primarily be used during the testing period of a report. You can turn the filter on and off without actually deleting it.

Aggregation determines when the filter is going to be applied. You can apply the filter before or after aggregation. Before aggregation will limit the data at the most detailed level of data available in the database.

For example, look at the two tables above. The table on the left is not summarized. It contains multiple records for each product type. The table on the right is summarized so only one row per Product type will be shown. Applying a filter before aggregation will apply the limit to the table on the left. Applying it after aggregation will apply the filter to the table on the right. 

If we were to apply the limit quantity > 5,000,000 before aggregation, we are only going to get the individual records that meet the criteria. The second and third records for Knives Product type do not have values of more than 5,000,000 so they do not get kept in the results set. If we were to apply the same filter after aggregation, we will get all records for that account, because Cognos is summarizing the data first.

Summary Filters

Summary filters are going to limit your data based on fields that you have grouped in your report. If you add a Summary filter using the filter icon on the toolbar while working on the report, Cognos will add a filter to the cube object in the query section.

The Summary Filter does not have the Before/After aggregation property that the detail filter had. Instead, it has a property for Level. Level allows you to choose at which level of summary you would like to apply the filter.

In our example, a list report has been grouped by Product Line and Product Type

If we add a Summary Filter, we have two different levels to which we can apply this filter. We can either limit on any Product Line that has a total Quantity > 5,000,000 or any Product Type that has a total Quantity > 5,000,000.