Saturday, May 18, 2013

How to use summary filter and AT clause to develop rolling average report much more efficient


Both summary filter and AT clause is confused concept in report development. This document is intended to use an example to explain the power of summary filter and AT clause.


Concepts


Summary filter is used to filter summary data items in a report. The key to understand this concept is to figure out the sequence of calculation and filter. There are two cases for an individual query item:
Case 1: when query item is IN SCOPE, then the query item is calculated first, and then filter is applied. This is natural understanding, such as we already consider summary filter as having in SQL statement.
Case 2: when query item is NOT IN SCOPE, then the query item is filtered out first, and then calculation is applied. This is where you can control the format.
By combining both cases together, summary filters can be very powerful for some reports. If you don’t use summary filter, the solution is to have queries, the base query is to get detail data, the main query is to filter data for base query. We’ll demonstrate this idea below.

AT clause: AT clause is used for running average, or running total. Again, this is a sequence understanding. The FOR and AT clauses come in very handy when calculating summary outside the current scope of a data item. The FOR clause allows us to specify what grouping context the summary function will be evaluated for and the AT clause allows us to define how the values will be grouped and aggregated before a summary function is applied. In other word, the calculation sequence is
1) Group data based AT clause first, and then
2) Summary function will be applied



Requirement

It is a very easy report without rolling average. However, report becomes more difficult as soon as rolling 4 month average is added. The bottom line is that you need data from last year, Dec, Nov and Oct.
We need to satisfy two criteria:
  1. Add last three months to get 4 month average,  and
  2. Keep YTD only to summary current year, while three months from last year will be filtered out.

Applying the summary concept above,
  • 4 month average should be IN SCOPE, calculated, then filtered out
  • YTD should be NOT IN SCOPE, filtered out, then calculated


Implementation

Query

Day of the week:  [Sales (query)].[Time].[Day of the week]
Weekday: [Sales (query)].[Time].[Weekday]
Month key: [Sales (query)].[Time].[Month key]
Month (caption): [Sales (query)].[Time].[Month (caption)]
Revenue: [Sales (query)].[Sales].[Revenue]
4 Month Avg: moving-average  ( [Revenue] , 4 at [Month key])

Filter:
[Sales (query)].[Time].[Year]='2013'
OR
([Sales (query)].[Time].[Month key] between 201210 and 201212)


Summary filter


Layout


SQL generated



Note that this solution will have very good performance, as it generates a sample SQL statement, all summary calculations and layout are resolved at Cognos server.

No comments:

Post a Comment