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

A Technique to Audit Cognos Active Reports

Cognos Active Reports can be great, but one huge drawback is that the viewing of these reports does not show up in the Cognos audit database.  For environments where you need to justify your existence, this can be a serious problem.

While you can’t get the Active Report to show up in the audit database, you can certainly have a Report Studio report show up.  So all you need to do is embed the execution of a dummy Report Studio report in the Active Report.  Of course this doesn’t work if the user takes the Active Report offline, or if they are running it from a Cognos Mobile app, but for our environment this covered 98% of the Active Report executions.

The first thing you do is create the dummy report.  The content for the report can be anything, as the user will not see it.  Just keep it simple (perhaps just a  text item describing it’s purpose),  so that it will not affect the performance of the Active report.  We chose to give the report a name identical to the Active Report, but in a different folder.  Another option is  to prefix or suffix the name with the word “Audit” (ex: “My Report – Audit”).

Next, go into the properties of the new dummy report and click “View the search path, ID and URL” and copy the default URL Action.  Paste this into your favorite text editor because you’re going to make a couple of changes:

  1. Replace every ampersand (“&”) in the URL with “&”
  2. Change “&run.prompt=true” at the end to “&run.prompt=false”

Finally, add an HTML item to your Active Report with the following HTML:

<i frame
     style="visibility:hidden;display:none; border:0; overflow:hidden;"
     seamless="seamless"
     allowTransparency="true"
     width="10"
     height="10"
     src="EncodedReportGoesHere"
></i frame>

You’ll need to remove the space between “i” and “frame”. Believe it or not, WordPress won’t let me post that word.
Replace “EncodedReportGoesHere” with the dummy report URL you edited.

Save the Active Report and you’re done.  Now just watch the audit tables after the Active Report is run and you’ll see the dummy report logged in there.

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.