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

11 Responses to Cognos Mashup Services – a brief example

  1. First, thanks for this post, very educating.
    Second, you suspect correctly. CMS can help you create a REST or SOAP web services to embed in external applications (Not necessarily just apps that don’t allow iframes, but also in scenarios such as you want to embed reports in your website, which is out of your domain – in the DMZ – so you can’t use SSO for security).
    Lastly, I’m not sure, but if I’m not mistaken CMS is a part of Cognos SDK, which requires extra licensing. If I’m right about this the extra cose should be taken into consideration when planning CMS based solutions…

    • CognosPaul says:

      Thanks for the confirmation, it makes sense – I just wasn’t able to test it.

      About the licensing, take what I’m about to say with a grain of salt, as I am not employed by IBM.

      I remember heading a long time ago that, while CMS is related to the SDK, it is a separate tool and you do not need to pay an extra fee to use it. Of course I’m not seeing anything about it on the IBM site and according to a post from Motio and a post on Cognoise you do need an SDK license.

      That being said, each user connecting must be covered by a license. If the user needs to go through authentication, then he has to be a consumer. If the user will be able to change a parameter (like in my example) then he has to be an enhanced consumer. If it’s a static object that requires no authentication then the user has to be a recipient. Licensing deals can be made with IBM, so if you want to publish something live on the website for everyone to see, that is possible.

      • Like I said, I’m almost certain you need SDK. It’d be great to have someone from IBM confirm…

        About the need for a license when deploying in an external app, that’s true. What you probably want to do if you’re planning to deploy some of your reports in an open-to-the-world (Or for registered users) web site is to purchase an appropriate PVU license. PVU license cost you based on cpu and not named users, *to the best of my knowledge*.

        To sum this up, if you’re reading this post and comments because your planning to deploy reports for your hundreds of customers to see on your web site, with security and authentication applied, then you should know it’s possible. On the practicalities, you’d best do the following: Get your local IBM vendor to give you a quote for an SDK license for CMS and for relevant end user licenses. Also, I have some experience with this kind of application on the technical side, so you can feel free to email me with any questions.

  2. Michael Gibson says:

    I would love to see a simple example of how to embed an object (table or chart) in a website with or without iFrames. We’ve been trying to do this for some time with only limited success (i.e. we can get it to work in Firefox); we suspect this is because of our choice of Auth / SSO (LDAP / Shibboleth / Kerberos) seems to be playing havoc with IE.
    But perhaps other folk, like yourselves, would take an entrirely different approach to this problem which might not encounter our issues.
    Do you have any examples of this?

  3. BI Solution says:

    Hi Paul,
    Sorry, I know the question in not relevant but can one Install Cognos 10.2 SDK where I have Cognos 10.1.1 BI server installed? or Do you the version needs to be same here?

  4. ozhasan says:

    Hi Paul,

    how to embed a cognos report chart in our website without authentication or encrypt authentication ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: