The problem: MS SharePoint’s hyperlink column length is limited to 255 chars.
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:
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
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.