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

Comments

Content series:

This content is part # of # in the series: IBM Cognos Proven Practices

Stay tuned for additional content in this series.

This content is part of the series:IBM Cognos Proven Practices

Stay tuned for additional content in this series.

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
    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
    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
    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.


Downloadable resources


Comments

Sign in or register to add and subscribe to comments.

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