How to Get Auto-Submit in a Cognos Multi-Select Prompt

This week, we had a report that we wanted to function like an Active Report, but we couldn’t use Active Reports because the report prompts would have resulted in hundreds of thousands of charts being generated. So we were forced to use Report Studio.

To make it look like an Active Report, we would have the drop-down value prompts auto-submit, so each change in the prompt would re-submit the report and change the chart. However auto-submit does not work for multi-select value prompts, so we had to get creative. The solution was to wrap the multi-select value prompt in a div tag with an onmouseup event. To implement this, add an HTML item before the multi-select value prompt and set it’s source to this:

<div id="cbPrompt1" onmouseup="
setTimeout(function(){
  var inputs =getElementById('cbPrompt1').getElementsByTagName('INPUT');
  for (var i =0; inputs.length > i; i++) {
    inputs[i].disabled=true;
  }
  promptAction( 'reprompt' );
}, 50);
"> 

Then add an HTML item after the prompt to close the div tag:

<div>

That’s all there is to it! When the user clicks on an option they want to select the mouse-up is triggered.  This first disables the prompts to prevent users who click very fast from selecting another option (which would be ignored), and then it triggers a re-prompt..
Of course, only use this technique on reports that run quickly, otherwise it could be very frustrating for your users.

Advertisements

Turn a Report Studio Text Prompt into a Table (Pivot a parameter)

I recently had a requirement where I needed to allow the report users to paste a number of values into a prompt, all at once.  Not only did the values need to be used in a filter, but they also had to be validated against the database.  The idea was that if they had a value that was invalid, that they could be notified before running the report so that they could correct the invalid value.

The technique for accomplishing this was relatively straightforward; Create a query in the report using the (manual) SQL object, and then use macro functions to parse the parameter and append the appropriate SQL to accomplish a union of each value.
This macro/SQL assumes a space delimiter (in the split() function), but could be easily modified to accommodate other delimiters.
# 'SELECT ' + join( ''' AS ColumnName FROM DUAL UNION ALL SELECT ''', 
split(' ', prompt('Parameter1'))) + ' AS ColumnName FROM DUAL' #

(In this case, I used the Oracle system table Dual, which always has exactly one row.  If you’re not on Oracle, you may need to find another single-row table.)

What this does is takes a prompt such as this one:
Value1 Value2 Value3 Value4

and creates a SQL statement that looks like this:

SELECT 'Value1' as ColumnName FROM DUAL
UNION ALL
SELECT 'Value2' as ColumnName FROM DUAL
UNION ALL
SELECT 'Value3' as ColumnName FROM DUAL
UNION ALL
SELECT 'Value4' as ColumnName FROM DUAL
The query can then be joined to other queries with an outer join to show the values that are not valid.

An Alternative to getElementById for Active Reports

A major frustration of mine with Cognos Active Reports has been that when the report is created, it renames the ids of every element.  This makes it very difficult to write Javascript that affects a specific element.  As far as I can tell, there is no way around this, so function getElementById() becomes useless to us.

The technique I came up with to work around this issue involves giving each element you need to identify a custom attribute (I call it “ARId”), and adding an array to the document that contains each of those elements.  To accomplish this I created four functions:

  1. ARIdExists(ARId) – Tests for the existence of the ARId in the array.  This is used to prevent us from accidentally adding the element to the array twice.
  2. pushNewAEId(node) – This adds a node to the array.
  3. registerARElements(init) – This initializes the array (if the first parameter is 1), and then goes through the document looking for any elements with an ARId attribute.  If it finds any, it adds them to the array.  To improve the speed of this, the function includes a list of tagNames that could possibly be tagged with an ARId.  You’ll want to edit this list to match your requirements.
  4. getElementByARId(ARId) – This is the replacement for getElementById.  Pass into it the ARId and it returns the corresponding element.

To get the functions to be created automatically, I use a hidden image and build them off the image’s onLoad event (see my post Creating JavaScript Functions in Active Reports).

The following script creates the functions above and then runs registerARElements(1) to initialize the array and load it.  It needs to be in an HTML item placed on the report’s page, outside any decks.

<img src="hal/imagesirot/progress.gif" width="1" height="1"  style="display:none;" onload="
while( ! alert) {} 
if(!document.ARIdExists ) { 
	ARIdExists = function(value) { 
		for (var i = 0; document.registeredARElements.length > i; i++ ) { 
			if (document.registeredARElements[i] == value) { 
				return true; 
			} 
		} 
		return false;	
	} 
} 
if (!document.pushNewARId) { 
	pushNewARId = function(node) { 
		if ( ARIdExists(node.arid) == false ) { 
			document.registeredARElements.push( node ); 
		} 
	} 
} 
if (!document.registerARElements ) { 
	registerARElements = function(init) { 
		if (init == 1) { 
			document.registeredARElements = []; 
		} 
		tagNames=['SPAN', 'DIV', 'TABLE', 'IMG']; /* edit this list to include any tagnames you give an arid */ 
		for (var i = 0; tagNames.length > i; i++) { 
			es = document.getElementsByTagName( tagNames[i] ); 
			for (var x = 0; es.length > x; x++) { 
				pushNewARId( es[x] );  
			} 
		}  
	} 
} 
if (!document.getElementByARId ) { 
	getElementByARId = function(id) { 
		for (var i=0; document.registeredARElements.length > i; i++) { 
			if ( document.registeredARElements[i].getAttribute('arid') == id ) { 
				return document.registeredARElements[i]; 
			} 
		} 
	} 
} 
registerARElements(1); 

"/>

If you have any decks on the page, Cognos does not render these when the page first loads, so you’ll need to register the deck’s elements separately by placing this HTML item in each deck:

<img src="hal/imagesirot/progress.gif" width="1" height="1"  style="display:none;" onload="
registerARElements();  
"/>

No, all you need to do is tag any elements you’ll need to get with the new ARId.  For example:

<span arid="spanElementA" style="background-color: silver;">Span Element A</span>

And then to get the element, use the new get function:

<div onClick=" 
	getElementByARId('spanElementA').style.backgroundColor='red'; 
 ">

As always, if you can think of any ways to improve this technique, please leave a comment.

In the Beginning…

I’ve been developing data warehouses for more than a decade now.  Back when there was no such thing as an “ETL Tool”.  Mustering up by best “crotchety old man voice”; I remember when ETL meant developing Oracle functions by hand (yes, they were actually chiseled in stone). Years before I first heard of dimensional modelling, I was creating denormalized tables to feed Cognos PowerPlay cubes, and I’ve been working with the Cognos Business Intelligence tools ever since.

Over those years, I often had to come up with creative solutions to many of the obstacles I had faced. Some were used once and forgotten. Some became part of my standard tool kit and using them became second nature.  But what I was never good at was sharing these solutions with the rest of the world.  It’s not that I’m niggardly (I certainly share my ideas with my colleagues), but I’ve always known I should put them out there for the world to use, and to improve upon.

This new blog is my attempt to do that.  I plan to publish a  few in a short amount of time to get things started, and then to put out one or two a month. I don’t know how I’m going to come up with things worthy of publishing that frequently, but I figure if I set that expectation, then it’ll motivate me to identify things worth sharing that I would have otherwise missed.

I welcome constructive comments on all of my posts, and together, I hope we can come up with some really useful, and occasionally interesting BI techniques.