Sunday, April 7, 2013

How to dynamically set up default value, or initialize parameters in Cognos report

While it is easy to setup static value for prompt, it is very difficult to set up dynamic value, assume that the value is determined by a query. This document is intended to share you 9 different ways.



  1. Use java script
  2. Use hidden prompt page
  3. Use hidden singleton master
  4. Use hard coded value
  5. Use “hidden” query
  6. Use calculation from framework manger
  7. Use relative time category value for power cube
  8. Setup a dynamic value for active reports (Relational based)
  9. Setup a dynamic value for active reports (Dimensional based)


For the sake of explanation, we assume that the sales report is generated for last 60 days by default. The report is using Cognos 10.2 sample database with sample sales package. All methods will generate the same result below



1. Use java script


Use java script to do prompt and setup default parameter

Please check this article http://www.ibm.com/developerworks/library/ba-pp-reporting-scripting_techniques-page634/index.html  for detail.  This document describes a technique to set dynamic default values for date prompts using a combination of prompt macros and JavaScript.

This solution is good for the situation where value can be easily specified by JavaScript. If the value can be only specified by a query. Then this method won’t work anymore.


2. Use hidden prompt page


Use a hidden prompt page to specify default values based on query, and then pass value to real prompt page, and then use it in real report.  Please check report specification (version 10.2) for detail


Page structure below



Step1 create Prompt page Hidden


Query:    HiddenPromptQry


Startdate





Enddate



Query result



Prompt page Hidden




Make page hidden
Startdate value prompt


Select prompt value

<script language="javascript">

function setDefaultVaule()
{
var form = getFormWarpRequest();
form._oLstChoicesStartdate.options[2].selected = true;
form._oLstChoicesEnddate.options[2].selected = true;
setTimeout("promptButtonNext();",100);
}

setDefaultVaule()

</script>


Step2 Create Prompt page show
Two date prompt using two parameters Startdate and EndDate



Step3  Create Page 1

MainQry


Step 4 test
Same result


3. Use hidden singleton master

Use singleton as master query for whole report and the pass data to the main report. Please check report specification (version 10.2) for detail

Step 1: Create two queries, Masterqry and MainQry
MasterQry, which is the same query for prompt of the last method

MainQry

Step 2: Create Page1, with singleton as master and list as detail



4. Use hard coded value

Use hard coded values such as “last 90 Days” in the list, then handles this default in query. Please check report specification (version 10.2) for detail



Step 1: Create Prompt page, with static values


Step 2: Create Page1

Step 3 test

5. Use “hidden” query

Dynamically setup default values for prompts without using Javascript. This method is to use a “hidden” query to specify the start date, then hardcoded as 1990-01-01 as used value to pass to main page. Then the main page build the logic to handle the deault.  Please check report specification (version 10.2) for detail



Step 1 Create Prompt page

Query StartDate

Query Last90Day

Query PromptStartdate



Prompt page


Step 2 Create page 1
Main query


Step 3 test
Prompt show

Report


6. Use calculation from framework manger

Use calculation from framework manger, where the date logic is implemented in parameters map. Please check ex post How to simplify and centralize date logic in ETL and Report  for detail   



7. Use relative time category value for power cube


This idea is from IBM Cognos Proven Practices: Using Relative Time Categories within Report Studio Prompts. Please see prompt below
Use value: [great_outdoors_sales_en].[Years].[Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month] Display value: 'Current Month' Default: [great_outdoors_sales_en].[Years].[Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month]

The current Month is static statement in Prompt, however, the value itself is dynamically decided from power cube. this idea can be directly used in report as well, such as expression below is to turn current month as a real member from full time hierarchy:
linkmember( firstchild( [great_outdoors_sales_en].[Years].[Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month] ) , [great_outdoors_sales_en].[Years].[Years].[Month] )



8. Setup default values for active reports (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)
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



9. Setup default values for active reports (dimensional based)


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.

3 comments:

  1. Very useful article!

    ReplyDelete
  2. Very helpful, Thank you very much! :)

    ReplyDelete
  3. Awesome! Great help! Thanks!

    ReplyDelete