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