How to setup a dynamic default value for an active report (Dimensional based)

Check list of all posts

It is normally requested to setup default value in active report. The difference is that default value in active reports must be static, or a specified string. The challenge here is how to transform a dynamic data into a static value for variable. For the sake of explanation, active report needs to display last three month based on available data. In Cognos sample database, the last available month is July 2013. It is requested to display last three months, which are May 2013, June 2013 and July 2013. From user point of view, report should not display Month – 2, Month -1 and Current Month, it should display real month name May 2013, June 2013 and July 2013.  Certainly, three months will be changed accordingly based on data availability.

This document is intended to demonstrate an idea to dynamically setup variable. The report specification, and active report .mht are attached for review (Cognos 10.2.0). The main challenge of dimensional package is how to get relative month number, as there is no MDX function that can calculate the difference between different periods.  

Report design:

Create a relative Month as variable with default value = 0

The key to turn dynamic month as static value is to create a relative month as the difference between Month – current Month.  In this case, the relative month is 0, -1, -2 with following map:

May 2013   ←→ -2

June 2013  ←→ -1

July  2013 ←→ 0

If we specify default value = 0, then July 2013 will be the default Month.

We can create a query as below to be used for data deck, and other active report items such as button group  

The relative month becomes the value in report. The query is like follows

 

Month	tail(filter(members([Sales (analysis)].[Time].[Time].[Month]), tuple([Revenue], currentMember([Sales (analysis)].[Time].[Time])) is not null), 3)
MonthNumber	cast(substring(caption([Month]),char_length (caption([Month]))-4,5),integer) * 10 + [Month (numeric)]
Current Month	maximum([Month (numeric)] for report)
RelativeMonth	[MonthNumber] - [CurrentMonth]

Display real Month name

As variable vRelativeMonth is a number 0, -1 and -2, you cannot simply display a variable as normal case. We need to display a real month name as below

Same query is used for both data deck and repeater, and there is master detail relationship created based on Month itself. Variable value is passed in data deck via relative Month. Actually data deck performs the map between month name and relative month.

Keep all other queries based on real month for data containers

The approach above is very good, as we can define the real query with real month without consider relative month. Normally, there is a data deck with query with relative month; both real month and relative Month are listed for data deck. Make sure that variable is used to control relative month, then define master detail relationship with real month only