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

Check list of all posts

Both summary filter and AT clause are difficult to understand in report development. This document is intended to use an example to explain the power of both 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 at first, and then filtered. This is natural understanding when comparing with SQL statement with having after group by.

Case 2: when query item is NOT IN SCOPE, then the query item is filtered out at first, and then calculated. This is where you can control the sequence.
 

By combining both cases together, summary filters can be very powerful for some reports. If you don’t use summary filter, then you need to create a 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, even they do not display on report.
 

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

Please download report specification for review

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.