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

Check list of all posts

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.