Saturday, November 15, 2014

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

Normally, it is requested to setup default value in active report. The 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, June and July. From user point of view, report should not display Month – 2, Month -1 and Current Month, it should display real month name May, June and July.  Certainly, three months will be changed accordingly based on data availability.

This document is intended to demonstrate an idea to dynamically setup active report variable. The sample report specification, and active report .mht are attached for review (Cognos 10.2.0)

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   ←→ -2
June  ←→ -1
July ←→ 0
If we specify default value = 0, then July will be the default Month.
We can create a query as below to be used for data deck, and all active report items such as button group will use this query as well

The relative month becomes the value in report. The query is like follows
Current Month
maximum([Month (numeric)] for report)
[Month (numeric)] - [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

No comments:

Post a Comment