Wednesday, February 17, 2010

Impact 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.

No comments: