Playing with MotioCI

A few posts back I gave a sneak peek of a new tool Motio was working on. Recently I was fortunate enough to get into the beta.

The installation, for the most part, is fairly straight forward. You download, decompress, modify a few config files to declare which version of Cognos you’re running, location of your JDK. The only major stumbling block I had was Motio requires some sort of authentication against Cognos. Since I was running Cognos anonymously on my laptop, it simply wouldn’t connect. To get around this, I set up OpenDJ and was able to progress.

Once MotioCI is installed and set up, it will create an instance. Each instance is a connection to a specific Cognos server. You can have as many instances as you have Cognos gateways. You can communicate between instances. So, for example, you can a report in Instance 1 to get specific results, and compare them to results in Instance 2. This is all part of the assertions, which I will get into later.

After you create an instance, you will be prompted to create a new project. Create the new project with a descriptive name and continue. The wizard will walk you through selecting which folders you want to test against which assertions. It comes with a suite of default assertions created by Motio and the Best Practices team. Once selected, it will generate test cases and run them.

Generate Test Cases

After the test cases run, you can see which reports have failures or warnings. You can see the results of the test cases, and the outputs of the reports themselves (if the test cases required the reports to run).
failing on assertions
The most interesting part of this tool is the Assertion Studio. This is where you can define the assertions.

Assertions allow you to test almost every aspect of any Cognos object. Do you have a corporate look and feel that every report must follow? Set up a template report and compare each and every report against that. Do you want to find every report that has a run time of more than 5 minutes? Do you want to automatically compare the output of a series of reports against specific SQL queries? Are you upgrading from 8.2 and need to find all instances of the old JS API? Do you want to test your dispatchers for certain settings and response times? The possibilities are endless.

When you design your own assertions you can specify whether the report needs to be executed or not. Executing the report allows you to check things like run time, or results match. Does the third row in the list match the first row of another report? Not executing allows you to use combinations of xpath and regex to parse the report (or model) xml. You might use that to find all reports that contain “formwarprequest” in HTML items.

There is a bit of a learning curve when it comes to MotioCI. It is definitely not something you’d give end users. It does seem to be an invaluable tool for administrators.

You can read more about it at the Motio Website.

Cognos Mashup Services – a brief example

In a previous post I showed how to embed Cognos reports in other applications or reports by using iframes. Unfortunately there are many problems with using iframes, difficulty interacting with objects with JavaScript, security issues, even positioning objects well into a page.

Instead it might be better to use CMS. You can pull a specific object from the report, and it becomes an actual element of the page you’re working on. CMS also allows you to pull elements in a number of different formats. HTMLFragment will return the report object exactly as it appears in the report, while JSON will allow you to easily use the data from the report in a JS function. This will post will two examples, HTMLFragment and JSON.

We’ll start with a JSON example.

In this report, we’ll create a text box prompt and a list prompt. Typing anything into the text prompt will populate the list prompt, like a search and select, but without refreshing the page.

This example is using Cognos 10.2 and the sales and marketing cube. The JavaScript will not downgrade to previous versions as I’m using the new Prompt API. People using previous versions can get around it by attaching events to their prompts.

To begin, create a list report with a value and key. In my example I’m using Product and Product – Category Code from the Sales and Marketing cube.
1. Source list

The Product field is actually a filter expression:

filter(
  [sales_and_marketing].[Products].[Products].[Product]
  , upper([sales_and_marketing].[Products].[Products].[Product].[Product - Long Name]) contains upper(#prompt('SearchString','string')#)
    or [sales_and_marketing].[Products].[Products].[Product].[Product - Category Code]  contains (#prompt('SearchString','string')#)
)

As CMS allows us to reference objects directly, it’s important to remember to give each object a name that describes what it is, what it contains, while being short enough to be easily referenceable . In this case, I’m calling the list object “List”.

When you run the report, you’re prompted to enter a string, and any product that contains the caption or code will be returned.

Save it under /CMS/Source and create a new report.

This report should have a table with 3 rows, 1 column. In the first row, put a text box prompt. In the second, a multi select list prompt. Leave the third blank for now. Remember to name the text box and value prompts. Let’s call them Text and Select.

Drag in an HTML item in the bottom row of the table, and paste in the following code.

<script>
/*
 * Fake Namespace and prompt getters.
 */ 	
var paulScripts = {}
var oCR = cognos.Report.getReport("_THIS_");

paulScripts.getSource = function()
{
    var targ;
    if (!e) var e = window.event;
    if(!e) return false;
    if (e.target) targ = e.target;
    else if (e.srcElement) targ = e.srcElement;
    if (targ.nodeType == 3) // defeat Safari bug
      targ = targ.parentNode;
    return targ;
}

paulScripts.getControl = function(promptName) 
{
  return oCR.prompt.getControlByName(promptName);
}


paulScripts.setPromptValue = function ( promptName, value ) {
var 
  newOption = ''
  , selElm = document.getElementById('PRMT_SV_'+paulScripts.getControl ( promptName )._id_);
//  selElm.options.length=0;
  for(i=0;i<selElm.options.length;i++)
  {
    if(selElm.options[i].selected==true){
      for(x in value) {if(value[x].use == selElm.options[i].value) {value.splice(x,1); break;}}
    } else {selElm.remove(i);i--}
  }
  
  for(i=0;i<value.length;i++)
  {
    newOption = document.createElement( 'option');
    newOption.value=value [i].use ;
    newOption.innerHTML = value[i].display ;
    newOption.dv = value [i].display ;

    selElm.appendChild(newOption );

  }
}

/*
* This creates the XMLHttpRequest object used to communicate with CMS.
* The initialization of the object depends on what browser is being used. This
* code is compatible with IE 5.5, 6, 7, 8 and all versions of Firefox and Chrome
*
* For more information on the XMLHttpRequest object, see http://www.w3.org/TR/XMLHttpRequest/
*/
try  {
var objXHR = new XMLHttpRequest();
} catch (e) {
try {
var objXHR = new ActiveXObject('Msxml2.XMLHTTP');
} catch (e) {
try {
var objXHR = new ActiveXObject('Microsoft.XMLHTTP');
} catch (e) {
alert('XMLHttpRequest not supported'); }
}
}


paulScripts.getValues = function (searchString)
{
  var 
    searchString = searchString?searchString:'',
    url= '../ibmcognos/cgi-bin/cognos.cgi/rds/reportData/searchPath/%2fcontent%2ffolder%5b%40name%3d%27CMS%27%5d%2freport%5b%40name%3d%27Source%27%5d?fmt=JSON&async=off&selection=List&p_SearchString=' + searchString;
   objXHR.open("POST", url, false);
   objXHR.send(null);
   if (objXHR.status == 200)
   {
     dataCache = (eval('(' + objXHR.responseText + ')'));
     return paulScripts.parseJSON(dataCache);
   }
}

/*
 * Loop through tableData, extract the use and display fields, and dump them into 
 * a JS object.
 */
paulScripts.parseJSON = function(tableData)
{
  if(!tableData.filterResultSet.filterResult) return false;
  var 
      rows = tableData.filterResultSet.filterResult[0].reportElement[0].lst.group.row
    , JSONData = [];

  for (var i=0; i < rows.length; i++)
  {
    JSONData.push ( {use :  rows[i].cell[1].item[0].txt.fmtVal, display : rows[i].cell[0].item[0].txt.fmtVal  });
  }
return JSONData;
}


/*
 * function loadOptions. Paul Mendelson - 2013-01-15
 * When text is entered into the text box, this will be triggered. It will wait for further input
 * before loading the select box with values.
 */
paulScripts.loadOptions= (function () {
  var timer;
  return function (){
    var name = this.getName()
    , search = this.getValue();
    clearTimeout(timer);
    timer = window.setTimeout(function() {  
      if(this.oldValue==search) {return true} else {this.oldValue=search}
      paulScripts.setPromptValue( 'Select', paulScripts.getValues(search));
    },1000);
    return true;
    };
})();

paulScripts.getControl('Text').setValidator(paulScripts.loadOptions);

</script>

When you run the report the select box will be empty. Start typing into the textbox. The JS will wait 1 second after the last keystroke, then pass the value to the Source report, retrieve data in JSON format, parse it and populate the select.

I’m not going to get into all of the JS here, just what is salient to CMS.

The paulScripts.getValues first coalesces the search string into nothing. You can make prompts optional by making your filters “this = ?searchString? or ‘-1’ = ?searchstring?”, and having the searchString set to ‘-1’. The URL in this example uses the search path of the report. While longer, I find it preferable over using the storeID. Just remember to URL Encode it. Notice the search string is appended to the URL. It then opens an XMLHttpRequest to Cognos. Cognos will interpret the request and send back a responseText.

The responseText will need to be handled differently depending on the format of the request. In this case, Cognos is returning JSON, and the results will need to be parsed as such.

The paulScripts.parseJSON will loop through the rows in the table. I know that the first cell is the label, and the second is the code, I also know there is only a single object in each table cell.

The monster tableData.filterResultSet.filterResult[0].reportElement[0].lst.group.row[1].cell[0].item[0].txt is how we reference text of the first item in the first cell of the second row (indexes are 0 based). If I managed to pull two lists, I could decide to use reportElement[1] to get the second list.

When parseJSON finishes creating the JS object, it will return it to getValues which in turn returns it to setPromptValue. setPromptValue will loop through the JS object and create the options in the select list.

Now that we have functional prompts. Let’s create a chart that shows revenue per month for each of the selected products.

Put the Month level in the categories, Revenue in the Measure and a new Query Calculation: filter(
[sales_and_marketing].[Products].[Products].[Product]
, [sales_and_marketing].[Products].[Products].[Product].[Product - Category Code] in (#promptmany('Products','string')#)
)

When run, it will prompt for codes.

Now let’s change the HTML item to:

<div id="chart"></div>
<script>
/*
 * Fake Namespace and prompt getters.
 */ 	
var paulScripts = {}
var oCR = cognos.Report.getReport("_THIS_");

paulScripts.getSource = function()
{
    var targ;
    if (!e) var e = window.event;
    if(!e) return false;
    if (e.target) targ = e.target;
    else if (e.srcElement) targ = e.srcElement;
    if (targ.nodeType == 3) // defeat Safari bug
      targ = targ.parentNode;
    return targ;
}

paulScripts.getControl = function(promptName) 
{
  return oCR.prompt.getControlByName(promptName);
}


paulScripts.setPromptValue = function ( promptName, value ) {
var 
  newOption = ''
  , selElm = document.getElementById('PRMT_SV_'+paulScripts.getControl ( promptName )._id_);
//  selElm.options.length=0;
  for(i=0;i<selElm.options.length;i++)
  {
    if(selElm.options[i].selected==true){
      for(x in value) {if(value[x].use == selElm.options[i].value) {value.splice(x,1); break;}}
    } else {selElm.remove(i);i--}
  }
  
  for(i=0;i<value.length;i++)
  {
    newOption = document.createElement( 'option');
    newOption.value=value [i].use ;
    newOption.innerHTML = value[i].display ;
    newOption.dv = value [i].display ;

    selElm.appendChild(newOption );

  }
}

/*
* This creates the XMLHttpRequest object used to communicate with CMS.
* The initialization of the object depends on what browser is being used. This
* code is compatible with IE 5.5, 6, 7, 8 and all versions of Firefox and Chrome
*
* For more information on the XMLHttpRequest object, see http://www.w3.org/TR/XMLHttpRequest/
*/
try  {
var objXHR = new XMLHttpRequest();
} catch (e) {
try {
var objXHR = new ActiveXObject('Msxml2.XMLHTTP');
} catch (e) {
try {
var objXHR = new ActiveXObject('Microsoft.XMLHTTP');
} catch (e) {
alert('XMLHttpRequest not supported'); }
}
}


paulScripts.getValues = function (searchString)
{
  var 
    searchString = searchString?searchString:'',
    url= '../cgi-bin/cognos.cgi/rds/reportData/searchPath/%2fcontent%2ffolder%5b%40name%3d%27CMS%27%5d%2freport%5b%40name%3d%27Source%27%5d?fmt=JSON&async=off&selection=List&p_SearchString=' + searchString;
   objXHR.open("POST", url, false);
   objXHR.send(null);
   if (objXHR.status == 200)
   {
     dataCache = (eval('(' + objXHR.responseText + ')'));
     return paulScripts.parseJSON(dataCache);
   }
}

/*
 * Loop through tableData, extract the use and display fields, and dump them into 
 * a JS object.
 */
paulScripts.parseJSON = function(tableData)
{
  if(!tableData.filterResultSet.filterResult) return false;
  var 
      rows = tableData.filterResultSet.filterResult[0].reportElement[0].lst.group.row
    , JSONData = [];

  for (var i=0; i < rows.length; i++)
  {
    JSONData.push ( {use :  rows[i].cell[1].item[0].txt.fmtVal, display : rows[i].cell[0].item[0].txt.fmtVal  });
  }
return JSONData;
}


/*
 * function loadOptions. Paul Mendelson - 2013-01-15
 * When text is entered into the text box, this will be triggered. It will wait for further input
 * before loading the select box with values.
 */
paulScripts.loadOptions= (function () {
  var timer;
  return function (){
    var name = this.getName()
    , search = this.getValue();
    clearTimeout(timer);
    timer = window.setTimeout(function() {  
      if(this.oldValue==search) {return true} else {this.oldValue=search}
      paulScripts.setPromptValue( 'Select', paulScripts.getValues(search));
    },1000);
    return true;
    };
})();

paulScripts.getControl('Text').setValidator(paulScripts.loadOptions);

/*
 * function loadProducts. Paul Mendelson - 2013-01-15
 * When a product is selected in the select, this will be triggered. It will wait for further input
 * before attempting to retrieve the chart.
 */
paulScripts.loadProducts= (function () {
  var timer;
  return function (){
    var name = this.getName()
    , products = this.getValues()
    , productsLabel='';
    clearTimeout(timer);
    timer = window.setTimeout(function() {  
    if(products.length===0) return true;
    for (i=0;i<products.length;i++) {productsLabel+='&p_Products='+products[i].use}
    paulScripts.getChart(productsLabel);
    },1000);
    return true;
    };
})();

paulScripts.getChart = function (products)
{
  var 
    url= '../cgi-bin/cognos.cgi/rds/reportData/searchPath/%2fcontent%2ffolder%5b%40name%3d%27CMS%27%5d%2freport%5b%40name%3d%27Chart%27%5d?fmt=HTMLFragment&async=off&selection=Chart' + products;
   objXHR.open("POST", url, false);
   objXHR.send(null);
   if (objXHR.status == 200)
   {
     document.getElementById('chart').innerHTML = objXHR.responseText ;
   }
}

paulScripts.getControl('Select').setValidator(paulScripts.loadProducts);

</script>

A div has been added above the scripts node. A validator for the Select prompt has been added. When the user selects a value it will wait one second for further input, then pass the selected codes to the chart report. The chart report will return an HTMLFragment as a string, which is then passed to the div as it’s innerHTML.

Cognos Mashup Services is an incredibly versatile tool. The possibilities are limitless. I suspect, but haven’t tried, that it will allow you to embed objects in systems that do not allow iFrames. The only drawback is that it will only work in HTML. You can’t use this to merge objects from different models into a single PDF

IBM has a few guides on it. Start here.

Report XML:

<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en-us">
				<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
				<drillBehavior modelBasedDrillThru="true"/>
				<layouts>
					<layout>
						<reportPages>
							<page name="Page1">
								<style>
									<defaultStyles>
										<defaultStyle refStyle="pg"/>
									</defaultStyles>
								</style>
								<pageBody>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="pb"/>
										</defaultStyles>
									</style>
									<contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><textBox parameter="Parameter1" name="Text" required="false"/></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><selectValue parameter="Parameter2" multiSelect="true" selectValueUI="listBox" name="Select"/></contents></tableCell></tableCells></tableRow></tableRows></table><HTMLItem description="scripts">
			<dataSource>
				<staticValue>&lt;div id="chart"&gt;&lt;/div&gt;
&lt;script&gt;
/*
 * Fake Namespace and prompt getters.
 */ 	
var paulScripts = {}
var oCR = cognos.Report.getReport("_THIS_");

paulScripts.getSource = function()
{
    var targ;
    if (!e) var e = window.event;
    if(!e) return false;
    if (e.target) targ = e.target;
    else if (e.srcElement) targ = e.srcElement;
    if (targ.nodeType == 3) // defeat Safari bug
      targ = targ.parentNode;
    return targ;
}

paulScripts.getControl = function(promptName) 
{
  return oCR.prompt.getControlByName(promptName);
}


paulScripts.setPromptValue = function ( promptName, value ) {
var 
  newOption = ''
  , selElm = document.getElementById('PRMT_SV_'+paulScripts.getControl ( promptName )._id_);
//  selElm.options.length=0;
  for(i=0;i&lt;selElm.options.length;i++)
  {
    if(selElm.options[i].selected==true){
      for(x in value) {if(value[x].use == selElm.options[i].value) {value.splice(x,1); break;}}
    } else {selElm.remove(i);i--}
  }
  
  for(i=0;i&lt;value.length;i++)
  {
    newOption = document.createElement( 'option');
    newOption.value=value [i].use ;
    newOption.innerHTML = value[i].display ;
    newOption.dv = value [i].display ;

    selElm.appendChild(newOption );

  }
}

/*
* This creates the XMLHttpRequest object used to communicate with CMS.
* The initialization of the object depends on what browser is being used. This
* code is compatible with IE 5.5, 6, 7, 8 and all versions of Firefox and Chrome
*
* For more information on the XMLHttpRequest object, see http://www.w3.org/TR/XMLHttpRequest/
*/
try  {
var objXHR = new XMLHttpRequest();
} catch (e) {
try {
var objXHR = new ActiveXObject('Msxml2.XMLHTTP');
} catch (e) {
try {
var objXHR = new ActiveXObject('Microsoft.XMLHTTP');
} catch (e) {
alert('XMLHttpRequest not supported'); }
}
}


paulScripts.getValues = function (searchString)
{
  var 
    searchString = searchString?searchString:'',
    url= '../cgi-bin/cognos.cgi/rds/reportData/searchPath/%2fcontent%2ffolder%5b%40name%3d%27CMS%27%5d%2freport%5b%40name%3d%27Source%27%5d?fmt=JSON&amp;async=off&amp;selection=List&amp;p_SearchString=' + searchString;
   objXHR.open("POST", url, false);
   objXHR.send(null);
   if (objXHR.status == 200)
   {
     dataCache = (eval('(' + objXHR.responseText + ')'));
     return paulScripts.parseJSON(dataCache);
   }
}

/*
 * Loop through tableData, extract the use and display fields, and dump them into 
 * a JS object.
 */
paulScripts.parseJSON = function(tableData)
{
  if(!tableData.filterResultSet.filterResult) return false;
  var 
      rows = tableData.filterResultSet.filterResult[0].reportElement[0].lst.group.row
    , JSONData = [];

  for (var i=0; i &lt; rows.length; i++)
  {
    JSONData.push ( {use :  rows[i].cell[1].item[0].txt.fmtVal, display : rows[i].cell[0].item[0].txt.fmtVal  });
  }
return JSONData;
}


/*
 * function loadOptions. Paul Mendelson - 2013-01-15
 * When text is entered into the text box, this will be triggered. It will wait for further input
 * before loading the select box with values.
 */
paulScripts.loadOptions= (function () {
  var timer;
  return function (){
    var name = this.getName()
    , search = this.getValue();
    clearTimeout(timer);
    timer = window.setTimeout(function() {  
      if(this.oldValue==search) {return true} else {this.oldValue=search}
      paulScripts.setPromptValue( 'Select', paulScripts.getValues(search));
    },1000);
    return true;
    };
})();

paulScripts.getControl('Text').setValidator(paulScripts.loadOptions);

/*
 * function loadProducts. Paul Mendelson - 2013-01-15
 * When a product is selected in the select, this will be triggered. It will wait for further input
 * before attempting to retrieve the chart.
 */
paulScripts.loadProducts= (function () {
  var timer;
  return function (){
    var name = this.getName()
    , products = this.getValues()
    , productsLabel='';
    clearTimeout(timer);
    timer = window.setTimeout(function() {  
    if(products.length===0) return true;
    for (i=0;i&lt;products.length;i++) {productsLabel+='&amp;p_Products='+products[i].use}
    paulScripts.getChart(productsLabel);
    },1000);
    return true;
    };
})();

paulScripts.getChart = function (products)
{
  var 
    url= '../cgi-bin/cognos.cgi/rds/reportData/searchPath/%2fcontent%2ffolder%5b%40name%3d%27CMS%27%5d%2freport%5b%40name%3d%27Chart%27%5d?fmt=HTMLFragment&amp;async=off&amp;selection=Chart' + products;
   objXHR.open("POST", url, false);
   objXHR.send(null);
   if (objXHR.status == 200)
   {
     document.getElementById('chart').innerHTML = objXHR.responseText ;
   }
}

paulScripts.getControl('Select').setValidator(paulScripts.loadProducts);

&lt;/script&gt;
</staticValue>
			</dataSource>
		</HTMLItem></contents>
								</pageBody>
							</page>
						</reportPages>
					</layout>
				</layouts>
			<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes><reportName>start2</reportName><reportVariables><reportVariable type="boolean" name="dontRender">
			<reportExpression>1=0</reportExpression>
			<variableValues>
				<variableValue value="1"/>
			</variableValues>
		</reportVariable></reportVariables></report>

Recent Java exploits and Cognos

Several new Java exploits have recently been uncovered. So far the all of the exploits that IBM has tested against their JDK and JREs have been unsuccessful. As my source in the upper echelons of IBM has said, “Thus, IBM products shipping the IBM JDK are not vulnerable to this exploit.”

Products that ship the Oracle JRE may be vulnerable to the exploits, and care should be taken until all of the fixes are released. Remember, nothing is sure until the fix is in.

Please read: Java Vulnerability Blog, from Tivoli AVP

In general it would be best to stay subscribed to the IBM Product Security Incident Response Blog to make sure you have the latest information on any security concerns with IBM products.

Review: IBM Cognos Insight

While I’ve got a bit of experience working with the various Cognos studios, I haven’t had much chance to play with Insight. Fortunately I have IBM Cognos Insight, by Sanjeev Datta.

As a in depth study, it is a bit lacking. The book doesn’t get into serious detail about the inner workings, effects on the server, nor does it go into detail about every single available function. That is fine however, as the book is not targeted at administrators. The only load balancing the readers of this book need to deal with are in the laundry.

This book is very well suited for analysts – people who need to actually play with the data, and who need to learn about the tools available to them. As an example, I have a friend who is an internal auditor at a very large port. His days are spent pulling data from different sources into Access and running queries on them. He is not very technically inclined, and has only a rudimentary understanding of SQL. This would be a perfect guide for him.

As a guide, it is split logically in sections. What is BI and how does it help, installing, configuring, importing the data, and the various ways of manipulating the data. It walks the through each step clearing and succinctly, with screenshots to guide the way. (One small complaint though, I read the book on my black and white Kindle, and there was at least one instance referring to text highlighted in color that I couldn’t see.)

The meat of the book is where it describes how to design and use your cube. From building the hierarchy to writing custom members, it touches on each area. It shows how you can build TurboIntegrator scripts, and why you would, but unfortunately doesn’t go into detail. Ultimately this book shows a user how to go from raw data to a complex dashboard that meets the user’s needs.

To the seasoned veteran of Insight, this book won’t be so useful. To everyone else, this book is invaluable and will get them to the seasoned part. When working with Insight, keep this book open and you won’t go wrong.

IBM Cognos Insight was written by Sanjeev Datta, and published by Packt Publishing.

Quickie: Referencing the prompt DOM element with the Cognos 10.2 Prompt API

With all of the nice things in the new API, there are still a few annoyances. For instance, the functions for adding values only work on textboxes, meaning you can’t programmatically add and remove elements. You also can’t easily move things around, or flip switches, or do any of the normal things you would do with Javascript.

Poking through the JS files, I did find a few things that should make life easier. Today I had an issue where I had to make a value prompt to allowing users the choice of viewing the report YTD or for the entire year. The cube has a nice hierarchy for that, with the captions yes/no or the all level for everything (yes, I know the cube could have been designed so that the No choice shows the entire year, this is a post about JS not OLAP so shush). The issue is that the users want to select “Everything” or “Until December 11”. The everything choice is fairly easy, it’s a static choice with the display set to “Everything”. The “Until December 11” is more difficult, the caption should change every day. Instead of altering the cube to put in a dynamic caption, I opted to use another static choice (this way I don’t need to go to the cube at all), and change the option text with JS.

In order to do this, I need to find the prompt on the page. The ID for the prompt itself is “PRMT_N19F64920x0B67C9E4RS” but that looks like it will change every time the report is run, how can I guarantee that I will always be able to access that. Keep in mind that I’m incredibly lazy, and don’t want to spend the time wrapping the prompt with my own uniquely identified div (because then I’d have to keep track of all of the div IDs, and make sure to move the HTML items when I move the prompt and it’s just too much of a hassle). Surely there must be an easier way.

It turns out that you can get the ID of the prompt with the Prompt API:

var acme = {};
acme.getControl = function(promptName)
{
  var ocr = cognos.Report.getReport("_THIS_");
  return ocr.prompt.getControlByName(promptName);
};
alert(acme.getControl('FilterType')._id_);

That would return N19F64920x0B67C9E4RS.

So that means that we can very easily modify the text of the prompt:

<script>
var acme = {};
acme.getControl = function(promptName)
{
  var ocr = cognos.Report.getReport("_THIS_");
  return ocr.prompt.getControlByName(promptName);
};

Date.prototype.getMonthName = function() {
  var monthNames = [ 'January','February','March','April','May','June','July','August','September','October','November','December'];
  return monthNames[this.getMonth()];
}

var dt = new Date(), 
    dtCaption = 'Until ' + dt.getMonthName() + ' ' + dt.getDate();

document.getElementById('PRMT_' + acme.getControl('FilterType')._id_).getElementsByTagName('OPTION')[1].dv = dtCaption;
document.getElementById('PRMT_' + acme.getControl('FilterType')._id_).getElementsByTagName('OPTION')[1].innerHTML = dtCaption;
</script>

This will change the display value (dv) and the label (innerHTML) with Until December 11 (or whatever the current date is).

Quickie: Changing default option in a Select and Search Prompt

I’ve been somewhat negligent in responding to the comments lately. One of the most common questions appears to be how to change the default option in a select and search prompt.

The following should work for all versions of Cognos.

Put an HTML item before the prompt, with the expression:

<div id="mySnS">

Now put another HTML item after the prompt with the expression:

</div>
<script>
/* Select and search options
 * 2 - Starts with any of these keywords * DEFAULT
 * 3 - Starts with the first keyword and contains all of the remaining keywords 
 * 4 - Contains any of these keywords 
 * 5 - Contains all of these keywords 
 */
document.getElementById('mySnS').getElementsByTagName('input')[5].click();
</script>

The script will locate the mySnS element that surrounds the prompt, generate an array of the input tags, and click() on the one specified. In the example, 5 is selected so it will click on the sixth element (0 based array).

Going through the Cognos JavaScript files, it looks like there is supposed to be a function in the new Prompt API that would let you do it in an easier manner, unfortunately it appears to be an incomplete function, maybe it will be released in the new fixpack.

In theory, you should be able to do something like the following:

var acme = {};
acme.getControl = function(promptName) 
{
  var ocr = cognos.Report.getReport("_THIS_");
  return ocr.prompt.getControlByName(promptName);
};

acme.getControl('mySnS2').setProperty( "caseInsensitive", false);
acme.getControl('mySnS2').setProperty( "searchType", "containsAny");

But, as I said, the function doesn’t appear to be complete in this version. Use the other method for now.

Blatant self promotion

As I wrap up some of my existing contracts, I realize I have some spare time coming up. Far be it from me to goof off in my spare time (the alternative being cleaning and other house work, and I can’t let the wife get used to that idea), I’d much rather be solving problems people are having with their Cognos set up.

So, does your company need help building that new dashboard for the CFO? Are the reports running too slowly over your new OLAP server? Does JavaScript make your brains leak out? Drop me a line at cognospaul@gmail.com and we can come to an arrangement.