Report Studio : Impact of Sumary Filters on reports - Part 1

Report Studio : 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

Report Studio : of Summary Filters on Reports - Part 2

This is in continuation to the post on Impact of Summary Filters on Reports.

Scenario 8: No grouping in report, no scope in summary filter, Rank Data Item's Rollup is set to None
Result: Filtering effect

Scenario 9: Include Sales, Product in Query 1. In Query 2 reference Query 1's Product and Sales and create Rank Data Item in Query 2 and summary filter in Query 2. No scope for summary filter, no grouping in report.

Result: Filtering effect

If you notice the query, the difference between setting Rank Data Item's Rollup to Automatic and None is as shown below:


Automatic -
(rank() over ( at XSUM(Order_details8.Revenue ) order by XSUM(Order_details8.Revenue ) desc nulls last ) < 5)


None -
(rank() over ( at Order_header6.ORDER_NUMBER,Product7.PRODUCT_NAME order by XSUM(Order_details8.Revenue for Order_header6.ORDER_NUMBER,Product7.PRODUCT_NAME ) desc nulls last ) < 5)

Here's my understanding -

Any summary filter works on the principle of After Auto Aggregaion / Rollup Aggregation. Hence Rollup aggregation of Rank is taken into consideration as Rank is used in summary filter. By setting this rollup to None, we are specifiying that no rollup aggregations need to be applied to this data item as well as objects inside of this data item. Hence whatever Rank function was applied inside of the detail query is applied at the rollup level as well.

This is a litle confusing to explain. But hope atleast I was able to throw light on the fact that summary filters are similar to rollup calculations or have an impact on rollup calculations.