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.

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.

Creating JavaScript Functions in Active Reports

For those of us who love to (of just need to) expand Cognos beyond it’s built-in capabilities, one of the fundamental tools we use is the JavaScript function.  We use JavaScript to perform all kinds of reporting sorcery, and we create functions for those things that we call over and over again.For years this worked well.  Even after major releases, most of our functions continued to work with little or no modifications.

Then, one day IBM came out with Active Reports.  We were awed by it’s ability to make reports portable, and the fact that data was embedded into the reports also meant that the reports were super-fast when users changed filters or parameters.  But all of this came with a cost.  We lost the ability to create JavaScript functions and the <script> tags that should execute as soon as the report opened no longer functioned. We were still able to execute in-line JavaScript, but this had significant limitations.

Recently, I was reading a comment in LinkedIn by Paul Mendelson (aka CognosPaul) where he talked about creating a function when a user clicked a button.  Within the inline JavaScript, it checked to see if the function existed.  If it didn’t exist, it created it, otherwise it went on with the rest of the actions that the button-click demanded.  This did not satisfy my immediate requirement because I needed something to happen the moment the screen opened up, but it set me in the right direction.

How To Create Functions and Run JavaScript When the Report Opens

The key to this technique is the <img> tag which is one of the few to include an onLoad event.  This event fires as soon as the image is displayed.

Add an HTML item anywhere on the report, just make sure it’s somewhere that will always be rendered (on the page, but not inside any decks). Give the HTML item the following text:

<img src="hal/imagesirot/progress.gif" width="1" height="1" onload="
     while( ! alert) {} 
     /* Define the function */ 
     if (!document.testFunction ) { 
          testFunction = function() { 
               alert('We are running the test function'); 
          } 
     } 
     /* Run any startup actions */ 
     testFunction(); 
"/>

The first line of the onload event is sure to raise hairs on the back of your head. “while( ! alert) {}” will endlessly loop until it detects the existence of the “alert” function.  This is necessary because in Active Reports, the image will load (and the onload event of the image will fire) before the document has completed loading.  JavaScript developers usually avoid this type of loop because JavaScipt runs synchronously, so the browser locks up until the loop is satisfied.  In my testing, this loop has never caused a problem, however, if someone can suggest a better alternative, I’m certainly open to trying it.

The image is hidden (style=”display:none;”), so it won’t affect your layout. I chose to use the progress.gif image because it’s always loaded.  You can use your own image, however I would not recommend a 1×1 transparent gif.  I tested this and it worked fine on Windows (IE, Firefox and Chrome), but did not work correctly on Cognos Mobile (iPad or Android).  If you do use your own image, it must also be somewhere in the report using a standard Cognos Image element from the toolbox, so that the image is properly registered.  Otherwise the image will only work while you are online.

The first thing you should do following the “while” loop is to define any functions you need.  In the example above I’ve demonstrated the “testFunction”.After that you can run any JavaScript that needs to run when the report first opens.Coming soon… getElementById in Active Reports.

Revisions
2014-09-17 – Added details about the images that can be used and the placement of the HTML Item.

A List Prompt From an Actual List

A colleague recently used this technique on my current project, so I want to make sure that credit was given where credit is due.

From Paul’s Cognos Blog: Checkbox List Prompt

Conditionally Hide the Cognos Header Bar Like a Flyout Menu

The technique to hide the Cognos Header bar from within a Report Studio report has been well documented.  But just to recap:

  • Add an HTML item to the report, setting the HTML text to the following:
    <style>
    #headerBack
    {
    display: none;
    }
    .mainHeader1
    {
    display: none;
    }
    .mainViewerHeader3
    {
    display: none;
    }
    </style>
This will completely hide the header bar, but it will also prevent you from returning to Cognos Connection, saving the report, running the report in a different format, etc..  Instead of completely getting rid of the header bar, what if you could click a button to allow it to pop out like a fly-out menu?  It’s actually pretty easy:
  1. Add the HTML item shown above.  This way the menu bar will be hidden by default when the report first opens.
  2. At the very top of the report (usually in the page header) add a block.  Leave the block’s width at the default 100%.
  3. Change the new block’s horizontal alignment to centered.  This will be the position of the flyout button.
  4. Within the block, add a second HTML Item. Set it’s HTML to:
    <table style=”background-color: silver; collapse-border: collapse;” cellpadding=0 cellspacing=0>
    <tr>
    <td><a Title=”Show/Hide the Cognos toolbar”; style=”cursor:pointer; font-size: ‘6px’; color: ‘#222222’;”; onClick=”javascript:toggleCognosHeader(this);”>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp” + “\/” + &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp</a></td>
    </tr>
    </table>
    <script>
    function toggleCognosHeader(anchor)
    {
    if (anchor.innerText == ‘                    \\/                    ‘)
    {
    changeCss(‘.mainViewerHeader3’, ‘display’, ‘inline’);
    changeCss(‘.mainHeader1’, ‘display’, ‘inline’);
    document.getElementById(‘headerBack’).style.display=’inline’;
    anchor.innerText = ‘                    /\\                    ‘ ;
    }
    else
    {
    changeCss(‘.mainViewerHeader3’, ‘display’, ‘none’);
    changeCss(‘.mainHeader1’, ‘display’, ‘none’);
    document.getElementById(‘headerBack’).style.display=’none’;
    anchor.innerText = ‘                    \\/                    ‘ ;
    }
    }//Custom JavaScript Functions by Shawn Olson
    //Copyright 2004
    //http://www.shawnolson.net
    //If you copy any functions from this page into your scripts, you must provide credit to Shawn Olson & [shawnolson.net…]
    //*******************************************function changeCss(theClass,element,value)
    {
    //documentation for this script at [shawnolson.net…]
    var cssRules;
    if (document.all)
    {
    cssRules = ‘rules’;
    }
    else if (document.getElementById)
    {
    cssRules = ‘cssRules’;
    }
    for (var S = 0; S < document.styleSheets.length; S++)
    {
    for (var R = 0; R < document.styleSheets[S][cssRules].length; R++)
    {
    if (document.styleSheets[S][cssRules][R].selectorText == theClass)
    {
    document.styleSheets[S][cssRules][R].style[element] = value;
    }
    }
    }
    }
    </script>
    That’s it!  Run the report and you’ll see a small silver bar at the top with an arrow pointing down.  Click on the bar and voila!  The Cognos header bar appears and the arrow points up.  Click it again and the Cognos header bar is gone.
    You could improve on this by creating a pair of images for the bar (one pointing up and one pointing down) and changing the above code to swap images instead of text.
    Let me know how this works out for you.

Cognos Report Studio Prompts as a JavaScript Popup

Cognos Report Studio prompt pages are okay for showing a list of prompts before the report is generated, and for long-running reports they are certainly the way to go.  However a lot of the reports that I have developed over the years needed to feel more interactive.  So instead (or often in addition) to prompts on a prompt page, I add them strategically on the HTML report itself, so the user can change the filters quickly.  This has worked well, until recently when I needed to use up all of the screen’s real estate for an interactive dashboard.  So I couldn’t put them on the report page, and the report users didn’t want the one to three second delay of returning to a prompt page.  They needed to see the prompts instantly.

A little bit of HTML and JavaScript, and I was able to hide the prompts completely, until the user clicked a button.  Immediately the prompts would pop-up in the middle of the screen.  One nice thing about this technique is that even when the prompts were hidden, I could still use JavaScript to interact with them if necessary (see Changing a Value Prompt in Report Studio using JavaScript).
Here are the steps:
  1. At the bottom of the page body, add the following HTML item:
    <div id=”divPrompts” style=”display:none; position:absolute; left:100; top:50; height=100; width=500; background-color=ffffff;”>

    You’ll want to tweak the size, position, and color to match your particular needs

  2. Add a table to your report after the HTML item and place your prompts inside the table.  Use the table to format the borders of the pop-up and the layout of the prompts.
  3. After the table, add an HTML item to close the <div> tag:
    </div>
    Anything between the <div> tab and the </div> tag will be hidden when the report page is first displayed
  4. Somewhere at the top of the report (or wherever you choose to place the button to display the prompts), add the following HTML item:
    <a style=”cursor:pointer”; onClick=’javascript:togglePrompts();’>
  5. After the html item containing the anchor tag, add a text item or an image that will act as your button.  For example, you could just add the text “Display Prompts”.
  6. After your text or image, add the following HTML item to close the anchor tag and hold the JavaScript function that hides/shows the prompts:
    </a><script>
    function togglePrompts()
    {
    var divPrompts = document.getElementById(“divPrompts”);
    if (divPrompts.style.display==”inline”)
    {
    divPrompts.style.display = “none”;
    }
    else
    {
    divPrompts.style.display  = “inline”;
    }}
    </script>

Of course, this technique doesn’t have to be limited to prompts.  It can be used to pop up any report element you want.

***** Edit 07/18/2012 *****
By popular demand… here’s the XML for a sample version of the report. Copy it to your clipboard, then open the report from the clipboard in Report Studio. The sample uses no data objects, so you can change it to point to any package you have published on your Cognos server.

Toggle Prompts Example