Thursday, February 11, 2010

Impact of Sumary Filters on reports - Part 1

Consider the following columns in your report: Product, Sales and Rank - Rank(Sales).

To display only top 5 products you create a summary filter where Rank < 5.

Scenario 1: No grouping in report, no scope in summary filter
Result: No filtering effect

Scenario 2: Product grouping in report, no scope in summary filter
Result: No filtering effect

Scenario 3: Rank grouping in report, no scope in summary filter
Result: Filtering effect

Scenario 4: Product grouping in report, Product scope in summary filter
Result: Filtering effect, validation warning

Scenario 5: Rank grouping in report, Rank scope in summary filter
Result: Filtering effect, validation warning

Scenario 6: Product Grouping in report, Rank expression changed to include for Product, no scope in summary filter
Result : Filtering Effect

Scenario 7: Product Grouping in report, Rank expression changed to include for Product, A summary calculation - max - maximum(Rank) included for Product Grouping level, summary filter changed to max < 5, set scope of this filter to Product then only those groups that have maximmum ranks of 5 are displayed. Not setting scope will result in no data.

A summary filter should hence be used only when groups need to be filtered out otherwise an after aggregation filter should be used.

4 comments:

Anonymous said...

Good information about summary filter.
winson.

Zephyr said...

Thank you all for the wonderful comments.

As always, your thoughts and comments are appreciated.

Unknown said...

Very good and informative blog.............

radha said...

Nice post . Keep sharing
Cognos TM1 online training Bangalore