IBM Cognos Proven Practices: Auto Cascading Prompts without Page Refresh in IBM Cognos Report Studio

Nature of Document: Tip or Technique; Product(s): IBM Cognos Report Studio; Area of Interest: Reporting

This document provides technique to implement auto-cascading of Value prompts without having to do a page refresh.

Amit Shivde, Systems Analyst, Cognizant Technology Solutions

Amit Shivde is an systems analyst with Cognizant Technology Solutions. He has been working in Data Warehousing projects for past 3.5 years. He has worked extensively with Cognos tool for complex reporting and providing solutions to clients as per their business requirements.



Smit Modi, Senior System Analyst, Cognizant Technology Solutions

Smit Modi is a solution-focused technology specialist with over 4 years of experience in IBM Cognos BI Suite. He is very competent in the application of BI strategies and techniques to practical situations. Smit has worked extensively in the Cognos BI product suite with relational data sources. He has worked in different business units including Banking, Financial Services and Pharma.



10 December 2012 (First published 22 August 2011)

Also available in Chinese

Introduction

Purpose

This document provides technique to implement auto-cascading of Value prompts without having to do a page refresh.

Applicability

This technique has been tested in the IBM Cognos 8.2, IBM Cognos 8.4.0, IBM Cognos 8.4.1 and IBM Cognos 10.1 Report Studio versions. This technique has been implemented using JavaScript.

Exclusions and Exceptions

This technique works perfectly in Internet Explorer version 5.0 and above. This will not work with Firefox.

This technique requires the use of undocumented and unsupported capabilities in IBM Cognos BI. There may be a risk associated with this technique in that support for these capabilities may change or be dropped entirely in some future release.


Auto-cascading prompt

Cascading of prompt values are typically required for reporting applications in order to limit the data scope and reduce chances of getting no data on report output. Native IBM Cognos functionality does provide capability to auto-cascade on a ‘single select value prompt’ or with ‘Re-prompt button on multi-select value prompt’ where the entire prompt selection page would refresh for each prompt selection. Hence, if the numbers of prompts are significant, this approach of entire page refresh becomes very time consuming and annoying for end users.

Steps to implement auto cascading without page refresh:

  1. Add the required value prompts on the prompt page which are needed for the cascaded. In the current example, Product Line, Product Type and Product Name prompts have been added. Product Type is cascaded on Product Line and Product Name is cascaded on Product Line and Product Type. Each of these prompt boxes has its own query.
    Report Studio prompt page with Product Line, Product Type and Product Name
    Report Studio prompt page with Product Line, Product Type and Product Name
  2. Give names to each of these prompts. In this example, Prompt Names will start with "PN_". Giving unique name to each of the prompt is essential to access these prompts using JavaScript. Product Line prompt has been named as PN_ProductLine, Product Type named as PN_ProductType and Product Name named as PN_Product. The names are entered under Miscellaneous → Name in the Value Prompt properties.
  3. Create a new query, which will include the concatenation of values for all prompt query items. In this example, Q_Prompt query has been created with a new data item expression called “Concat String” which will concate all three prompt values as string. Please make sure while concatenating use different identifier between each value.
    The expression is the following:
    '!' || [gosales_goretailers].[Products].[Product line] || '~'|| 
    [gosales_goretailers].[Products].[Product type] || '#' || 
    [gosales_goretailers].[Products].[Product name] || '^'
    Data Item Expression window
    Data Item Expression window
    Note: In case of different use and display value, display value should be used for concatenation.
  4. On the prompt page, insert one HTML item after the prompts, and in this example, call it 'fW Script' then include the following code:
    <script>
    var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : 
       document.forms["formWarpRequest"]);
    if ( !fW || fW == undefined) { fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : 
       formWarpRequest_NS_ );}
    </script>
  5. On the prompt page, insert another HTML item next to first HTML item. Call it ‘GetAllPromptNames’ then insert following code:
    <script>
    //------------------------------------get All Prompt Names----------------------
    function getAllPromptNames()
    {
    
    var aPromptNames = new Array();
    
    for (var i in window) {
    if ( (""+i).match(/^listBox/) ) {
    var sName = i.replace( /^listBox/, "" );
    aPromptNames.push (sName);
    }
    else if ( (""+i).match(/^radioList/) ){
    
    var sName = i;
    aPromptNames.push (sName);
    }
    else if ( (""+i).match(/^checkBoxList/) ){
    
    var sName = i;
    aPromptNames.push (sName);
    }
    }
    return aPromptNames;
    //-----------------------------------to avoid Memory Leak----
    	i=null;
    	sName=null
    
    }
    //------------------------------------get All Prompt Names----------------------
    
    // hiding select All/deselect All 
    for(j=0;j<document.links.length;j++)
    {
    y=document.links[j];
    if (y.id.indexOf("SELECT") != -1) document.getElementById(y.id).style.display='None';
    }
    </script>
  6. On the prompt page, insert third HTML item next to previously inserted HTML items and insert following code in that:
    <script>
    String.prototype.trim = function() {
    	return this.replace(/^\s+|\s+$/g,"");
    }
     fW._oLstChoicesPN_ProductLine.onchange = function ()
          {cascadePrompts("ProductLine","ProductType,Product")};
     fW._oLstChoicesPN_ProductType.onchange = function ()
          {cascadePrompts("ProductType","Product")};
    
    //Check if the prompts being cascaded are present 
    // on page or removed as per report requirement
    function validatePrompts(removearr)
    {
    promptsonpage=getAllPromptNames();
    	var flag ;
    	newarr = new Array();
    	var i=0;
    	for(var j =0 ; j < removearr.length ; j++ )
    		{
    			flag = false;
    			for (var jj =0 ; jj < promptsonpage.length ; jj++ ){
    				
    				if(("PN_"+removearr[j])==promptsonpage[jj])
    					{
    						flag=true;
    						break;
    					}
    			}
    			if(flag==true )
    				{
    					newarr[i]=removearr[j];
    					i++;
    				}
    		}
    	return newarr;
    }
    
    //calls all the functions required for cascadeing
    	// prompt: Listprompt on which has the values to filter the child prompts
    	//remove:  Child prompts on which the cascading is supposed to be performed
    function cascadePrompts(prompt,remove){
    	removearr = new Array();
    	removearr = remove.split(",");
    
    newarray=validatePrompts(removearr);
    emptyList(newarray);
    createList(prompt,newarray);
    }
    
    //Empty child prompts before you insert new filtered values
    function emptyList(arr){
    	for (var jj =0 ; jj < arr.length ; jj++ ){
    		 fW["_oLstChoicesPN_"+arr[jj]].selectedIndex = -1;
    		 fW["_oLstChoicesPN_"+arr[jj]].length = 0;
    	}		
    //------------------to avoid memory leak-----
    	arr=null;
    }
    
    //creates the filter array
    function createList(prompt,newarr) {
    	var len2 = fW["_oLstChoicesPN_"+prompt].length;
    	arrfilter = new Array();
    	var idx = 0;
    	for (var j = 0 ; j < len2; j++){
    		if (fW["_oLstChoicesPN_"+prompt][j].selected == true){
    			arrfilter[idx] = fW["_oLstChoicesPN_"+prompt][j].value;
    			idx++;
    		}
    	}
    	for (var jj =0 ; jj < newarr.length ; jj++ )
    	{
    		createListFilter(prompt,newarr[jj],arrfilter);
    	}
    	
    	//------------------to avoid memory leak-----
    	arrfilter=null;
    	newarr = null;
    }
    
    function createParentFilter(prompt)
    {
    var len2 = fW["_oLstChoicesPN_" + prompt].length;
      	var filter = new Array();
    	var idx = 0;
    
    	for (var j = 0 ; j < len2; j++){
    		if (fW["_oLstChoicesPN_"+prompt][j].selected == true){
    			
    			filter[idx] = fW["_oLstChoicesPN_"+ prompt][j].value;
    			idx++;
    		}
    	}
    	return filter;
    
    }
    function checkParentFilter(string,fltr,flg)
    {
    
    for(var k=0;k<fltr.length;k++)
    					{
    						if(fltr[k]!="All" && flg){
    							if(string.match(fltr[k]))
    							{
    								return true;
    							
    							}
    							
    						}
    						else { return flg;}
    						
    					}if(fltr.length==0) {return true;}
    					flg=false;
    					return flg;
    }					
    
    function createListFilter(filterlevel,filtervalues,filter){
    var parFiltersStr= "ProductLine,ProductType"; 
    	parentarr = new Array();
    	parentarr = parFiltersStr.split(",");
    	newParentArr = validatePrompts(parentarr);
    	parentFilterStr=newParentArr.join();
    	
    ProductLinefilter= new Array();
    	if(parentFilterStr.match("ProductLine")){
    		ProductLinefilter=createParentFilter("ProductLine");
    	}
    	else{
    		ProductLinefilter[0]="All"
    	}
    //-------------------------Product Line filters---
    
    ProductTypefilter = new Array();
    	if(parentFilterStr.match("ProductType")){
    		ProductTypefilter =createParentFilter("ProductType");
    	}
    	else{
    		ProductTypefilter[0]="All"
    	}
    //-------------------------Product Type filters---
    
    var fd;
    var sd;
    var td;
    var frd;
    var tdidx = 0;
    var fdidx = 0;
    var sdidx;
    var frdidx;
    arr = new Array ();
    if (filterlevel == "ProductLine"){
           fd = "!";
    	   sd = "~";				}
    
    	if (filterlevel == "ProductType"){
           fd = "~";
    	   sd = "#";				}
       
       if (filterlevel == "Product"){
           fd = "#";
    	   sd = "^";									
       }
     // ----------------------------------filter Values------------------------------
       
    if (filtervalues == "ProductLine"){
           td = "!";
    	   frd = "~";				
       }
    
    	if (filtervalues == "ProductType"){
           td = "~";
    	   frd = "#";				
       }
        if (filtervalues == "Product"){
           td = "#";
    	   frd = "^";				}   
          
      var len = fW._oLstChoicesPN_ConcatString.length;
    
       for (var idx= 0 ; idx < filter.length ; idx++){
    	for(var i = 0 ; i < len ; i++){
    		
    		var string2 = fW._oLstChoicesPN_ConcatString[i].value;
    		fdidx = string2.indexOf(fd);
    		sdidx = string2.indexOf(sd);
    		tdidx = string2.indexOf(td);
    		frdidx = string2.indexOf(frd);
    		var string3 = string2.substring(fdidx+1,sdidx);
    		if (filter[idx] != "All"){	
    		if ( string3.trim() == filter[idx].trim() ){
    			 var flag=true;
    			 flag=checkParentFilter(string2,ProductLinefilter,flag);
    			 flag=checkParentFilter(string2,ProductTypefilter,flag);
    			if(flag)
    				{
    				
    				arr[i] = string2.substring(tdidx+1,frdidx);
    				}
    		}
    	  }	
    	  else {
    		 	 var flag=true;
    			 flag=checkParentFilter(string2,ProductLinefilter,flag);
    			 flag=checkParentFilter(string2,ProductTypefilter,flag);
    			if(flag)
    				{
    				arr[i] = string2.substring(tdidx+1,frdidx);
    				}
    	 		  }
    	}
      }	
     
     	arr = uniqueVal(arr);
    	filtervalues = "_oLstChoicesPN_"+filtervalues;
    	for (var j=0 ; j < arr.length ; j++){
    	   if (arr[j] != undefined && arr[j] !=null && arr[j] != ""){ 
    		var elOptNew = document.createElement('option');
    		elOptNew.text =  arr[j];
    		elOptNew.value = arr[j];
    		fW[filtervalues].add(elOptNew);
    	   }	
    	}
    	//------------------to avoid memory leak-----
    	arr=null;
    	regionfilter=null;
    	countryfilter=null;
    	filter=null;
    	string2=null;
    }
    function uniqueVal(oldarr){
    oldarr.sort();j=0;
    newarr= new Array()
    for(var i=0;i<oldarr.length;i++){
    newarr[j]=oldarr[i];j++;
    if((i>0)&&(oldarr[i]==oldarr[i-1])){
    newarr.pop();j--
    }
    }
    return newarr;
    }
    </script>

    In the sample code, this HTML item is referred as ‘Cascading Prompts’. This code includes logic for the auto cascading of the prompt values without page refresh.
    Report Studio Prompt page showing the scrip objects below the Product Line, Product Type and Product Name prompt
    Report Studio Prompt page showing the scrip objects below the Product Line, Product Type and Product Name prompt
  7. Now go to Page One in the Report Pages and create a column chart showing order method and production cost. Create three filter items that will filter on Product Line, Product Type and Product Name if not done already. For Example:
    • [Sales (query)].[Product].[Product line] in ?P_ProductLine?
    • [Sales (query)].[Product].[Product type] in ?P_ProductType?
    • [Sales (query)].[Product].[Product name] in ?P_Product?
    Note: Make sure that the parameter names in the filter expressions, matches the parameter names on the prompt page.
  8. Save this report .

Steps to expand auto cascading to more value prompts

  1. In the ‘Cascading Prompts’ HTML item code, needs add ‘onchange’ event for a new prompt and need to pass the child prompt names for that prompt.
  2. Modification in the parFiltersStr variable needs to done as per the hierarchy of the prompts. Also new arrays for the same needs to be created.
  3. Modification in query (In the current example Q_Prompt) for the hidden prompt using which cascading functionality is working needs to be done. A Column needs to be added in the query with the delimiters. And the changes in the if conditions for filterlevel and filtervalues as per the delimiters defined in the query needs to be modified/added.
  4. Condition to check parent prompt values needs to be added as added in the current code "flag=checkParentFilter(string2,ProductLinefilter,flag);” .

Impact on User Productivity

Here is an example of how this technique could save time. A company with a Cognos 8.4.0 implementation with 200+ operational reports in which this method has been implemented with a user base of 700+ active users globally. With the out of the box functionality of Cognos 8, there is a increasing chances of getting ‘No Data Found’ results more often, impacting on the user productivity due to multiple times re-running report to get more data or waiting for prompts to retrieve the data to show the consumer.

With this method, it saved an average of about a minute in execution time per report per user. So, if each user ran all the reports 5 times a day (700*5)(Number of users*time saved in execution of reports), there could be a 3500 minute per day increase of productivity.

Now this is just an example, results may very depending on number of prompt, amount of data returned, amount of users, etc.


Download

DescriptionNameSize
SpecificationAuto_Cascading_Report_specs.zip10KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business analytics, Information Management
ArticleID=752385
ArticleTitle=IBM Cognos Proven Practices: Auto Cascading Prompts without Page Refresh in IBM Cognos Report Studio
publish-date=12102012