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)
|
RelativeMonth
|
[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
A very good idea Mr Yin and I am trying to something similar with an Active Report where I would like to select a month from a data drop down list to activate charts / lists on a data deck where the chosen month from the drop down becomes the maximum for the objects on the data deck, which then display previous periods. I am having real difficulty doing this and I am using a Relational based package. many thanks
ReplyDeleteKevin