Integration Software

IBM Cognos URL Mapping in SharePoint

The problem: MS SharePoint’s hyperlink column length is limited to 255 chars.

IBM Cognos URL Mapping in SharePoint URL MAPPING IMAGE 01 285x288
When creating a list or document library in Microsoft SharePoint, you can choose to create a column type “hyperlink” as in this example.
IBM Cognos URL Mapping in SharePoint URL MAPPING IMAGE 02 285x222

IBM Cognos Report Default Action URLs, however, are much longer than 255 chars, for example:

http://vmxplus09:81/ibmcognos/cgi-bin/cognos.cgi?
b_action=cognosViewer&
ui.action=view&
ui.object=defaultOutput(%2fcontent%2ffolder%5b%40name%3d%27
Samples%27%5d%2f
folder%5b%40name%3d%27Models%27%5d%2f
package%5b%40name%3d%27GO%20Data%20Warehouse%20(query)%27%5d%2f
report%5b%40name%3d%27Great%20Outdoors%20-%20Finance%20-%20Submissions%27%5d)&
ui.name=Great%20Outdoors%20-%20Finance%20-%20Submissions&
ui.format=spreadsheetML

First generate your IBM Cognos Report SearchPath in bulk using CoMetReporter.

By creating a default CoMetReporter configuration for parsing a Cognos Export (via Cognos Content Administration) of one or more folders containing your reports you can automatically generate an XML file containing the necessary URL information through CoMetreporter’s reporting package:

IBM Cognos URL Mapping in SharePoint URL MAPPING IMAGE 03 600x194

The reportID can be StoreID or your own ID (you need to map this information manually, for example using the Description metadata field available in Cognos Connection for every object).

URL Mapping using JavaScript

Using a simple URL mapping JavaScript (in an html file placed on the Cognos Server), the short Cognos report URL could look like this in SharePoint (hyperlink column):

http://vmxplus09:81/urlmapping/c10_url_map.html?reportID=REP_0001

Javascript URL mapping


<html>
<head>
	<script language="JavaScript">
    {
		//write wait message
		document.write("please wait while your report is being run...");
    }
	</script>
	<script language="JavaScript">
		function getParameter(paramName) {
		  var searchString = window.location.search.substring(1), i, val, params = searchString.split("&");

		  for (i=0;i<params.length;i++) {
			val = params[i].split("=");
			if (val[0] == paramName) {
			  return unescape(val[1]);
			}
		  }
		  return null;
		}
	</script>
	<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.0/jquery.min.js"></script>
	<script type="text/javascript">
		var myXML;
		//base url to run the report
		var baseURL = "http://bi.prod.telenet.be:80/cognos8/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui.object=";
		function searchXML(){
		  $.ajax({
			 type:"GET",
			 url: "cognos_url_mapping.xml",
			 dataType: "xml",
			 success: function(xml){
					// filter the report using reportID as provided in the short URL
					myXML = $(xml).find("cognosReport").filter(function() {
						return $(this).find('reportID').text() == getParameter("reportID");
					});
					// get the searchPath for the report
					var searchPath = $("searchPath", myXML).text();
				    // navigate to the actual Cognos URL to run the report
					window.location=baseURL+escape(searchPath);
				}
		  });
		}
		searchXML();
	</script>
</head>
<body>

</body>
</html>

When clicking the URL a lookup in the XML (containing the report searchPaths) is performed and forwards the URL to the actual (long) Cognos URL to run or view the report.

Complete this automation using CoRePublisher to load and maintain the short URLs in MS SharePoint.

Since CoRePublisher v2.2, an new SharePoint target publication is available, allowing to insert, update and delete records in a SharePoint list using an IBM Cognos listreport as a source. Using this type of publication you could automatically maintain the SharePoint list or document library with the short URLs without any manual interaction.

Download the XML and HTML example files here.

 

If you aren’t using CoRePublisher already…
  Try CoRePublisher
Read more here

1 Comment
  1. Appreciating the commitment you put into your website and detailed information you provide.
    It’s good to come across a blog every once in a while that isn’t the same outdated rehashed material.
    Excellent read! I’ve bookmarked your site
    and I’m including your RSS feeds to my Google
    account.

Leave a Reply

News

Blog

Twitter Feed

Contact us


7 + eight =