Calculated Measure, Member or a Query Calculation?

I posted previously about calculated members, but until recently, I never really understood when to use a calculated measure, a calculated member or a query calculation when building a report. By chance, I had the opportunity to bring this up to an IBM Cognos employee and was delighted to learn the following:

• Use Calculated Member or Calculated Measure for dimensional reports where the expression is a member or a tuple-based (as opposed to property-based) value expression.

• Use Calculate Measure if you intend it to behave like measure (or to replace a measure). In this case, the value expression must not depend on the current measure context.

• Use Calculate Member if you intend it to behave like measure (or to replace a measure). In this case, you must choose a hierarchy. The value expression must not depend on the current context in this hierarchy.

• Use Set Expression for set expressions. In this case, you must choose a hierarchy. The set expression must contain only members in this hierarchy.

Finally, use Query Calculation in all other cases.

Using a different port with Cognos 8.4

The result of some specific security measures we were asked to temporarily designate an alternative port for our Cognos 8.4 development environment.

I was under an assumption that this change would be directed through our webserver but quickly discovered that when an e-mail is sent, the link does not honor the specified port number. After some head scratching and paying some much needed attention to the manuals I found the correct solution:

1. 1. open Cognos Configuration on the server you're using
2. 2. select Environment
3. 3. under Gateway Settings, select Gateway URI
4. 4. change the URI to a fully qualified domain name adding your port number
5. 5. save and restart

Remove the Dotted Line/Title(parameter) in Cognos 8.4

Need to remove the dotted line and title of a value prompt? Create an HTML Item in your report and fill it with the following Javascript:

<script>

var f = getFormWarpRequest();

var prompt = f._oLstChoicesX;

prompt.remove(1);

prompt.remove(0);

prompt.removeAttribute("hasLabel");

</script>

Where X is the name of the prompt specified in the properties. This tip works with Cognos 8.4. There are other Javascript methods for earlier versions.

What version of IBM Cognos are you using in production?

Hello all. I'm back from an extended summer break and working on some new posts. While away, I had left comment moderation on (in order to avoid spam) so I'll be catching up on your comments soon.

Back in June I ran a poll asking the question "What IBM Cognos version are you using in production?". Thanks to everyone who responded. The results showed that the majority of those who responded are using Cognos 8.3 (60%) in production followed by Cognos 8.4 (23%) (see pie chart below - Flash enabled):

get Widget!

Case Expression in Report Studio

Based on a reporting requirement I was asked to present data grouped in year range categories, (e.g. <2000, 2000-2005, 2006-2009, >2009...). The "search case" expression met my objective.

The syntax looks something like this:

case
WHEN [year] < 2000 THEN ("category 1")
WHEN [year] < 2006 THEN ("category 2")
WHEN [year] < 2009 THEN ("category 3")
WHEN [year] > 2009 THEN ("category 4")
...
end

Notice that within the case expression I started with the lessor value (2000) and followed up to the top value. I could have also used an IF ... THEN statement but my feeling is that is it would look quite cluttered.

A Tabbed Interface for Your Reports

I wanted a way to show a series of reports within the context of the same window without using the dashboarding tools available from the Cognos interface. Here is a simple way in IBM Cognos 8.3 (using Javascript and an iframe) to create a tab-style interface which provides quick access for your reports:

1. The first step is to create a new report – a shell report – which will be the main tab interface to your reports. In this new report, I've removed the header title block (but not the header) and added in two new HTML Items from the toolbox, one in the header area and the other in the body of the new report. I've given each a description (< tab menu > and < iframe >) using the Properties pane for easier reference.

2. The HTML Item referred to as < iframe > will house the iframe element. Open it and paste in the following:

<iframe name="content" src="http://localhost:80/cognos8/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui.object=CAMID%28%22Local%20NT%20ID%3au%3dS-1-5-21-1004336348-688789844-682003330-1118%22%29%2ffolder%5b%40name%3d%27My%20Folders%27%5d%2freport%5b%40name%3d%27tabbed-revenue%27%5d&ui.name=tabbed-revenue&run.outputFormat=&run.prompt=true&cv.header=false" frameborder="0" height="500" width="100%"></iframe>

Notice that the src of the iframe is targeting the report path to my default report (this will be the report used on the first tab selection). You'll want to replace this with your own report path information (in Cognos Connection, open properties for the report. Click on the 'View the search path, ID and URL'. Copy the 'Default action URL' contents and paste it as the src to your iframe. Append the end of this string with '&cv.header=false' (this parameter hides the header of the report –the part that says 'Cognos Viewer', etc).

3. The HTML Item referred to as < tab menu > is the actual tabbed interface and contains three main parts: a Javascript function; some CSS style syntax; and some html tags. Open the HTML Item. Let's paste in the CSS style syntax:

<style type="text/css">
#demo-container{padding:25px 15px 0 15px;margin:0 0 15px 0;background:#c3d1e0;}

ul#simple-menu{list-style-type:none;width:100%;position:relative;height:27px;font-family:"Trebuchet MS",Arial,sans-serif;font-size:13px;font-weight:bold;margin:0;padding:11px 0 0 0;}

ul#simple-menu li{display:block;float:left;margin:0 0 0 4px;height:27px;}

ul#simple-menu li.left{margin:0;}

ul#simple-menu li a{display:block;float:left;color:#fff;background:#426695;line-height:27px;text-decoration:none;padding:0 17px 0 18px;height:27px;}

ul#simple-menu li a.right{padding-right:19px;}

ul#simple-menu li a:hover{background:#2E4560;}

ul#simple-menu li a.on{color:#2E4560;background:#fff;}

ul#simple-menu li a.on:hover{color:#2E4560;background:#fff;}

</style>

The CSS is responsible for the look of the tabbed interface (I've customized the colors with a bit of a Cognos 8.3 theme – change these colors as needed).

Now let's add the Javascript:

<script>

function putContent(n) {

var aLinks = document.getElementsByTagName("a");

for (i=0;i<aLinks.length;i++) {

if(aLinks[i].className == "on") {

aLinks[i].className = "off";

}

document.getElementById("link"+n).className = "on";

}

switch(n) {

case 2: document.getElementById("content").src = "http://localhost:80/cognos8/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui=h1h2h3h4&ui.object=CAMID(%22Local%20NT%20ID%3au%3dS-1-5-21-1004336348-688789844-682003330-1118%22)%2ffolder%5b%40name%3d%27My%20Folders%27%5d%2freport%5b%40name%3d%27tabbed-margin%27%5d&ui.name=tabbed-margin&run.outputFormat=&run.prompt=true&cv.header=false"; break;

case 3: document.getElementById("content").src = "http://localhost:80/cognos8/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui=h1h2h3h4&ui.object=CAMID(%22Local%20NT%20ID%3au%3dS-1-5-21-1004336348-688789844-682003330-1118%22)%2ffolder%5b%40name%3d%27My%20Folders%27%5d%2freport%5b%40name%3d%27tabbed-inventory%27%5d&ui.name=tabbed-inventory&run.outputFormat=&run.prompt=true&cv.header=false"; break;

default:document.getElementById("content").src = "http://localhost:80/cognos8/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui=h1h2h3h4&ui.object=CAMID(%22Local%20NT%20ID%3au%3dS-1-5-21-1004336348-688789844-682003330-1118%22)%2ffolder%5b%40name%3d%27My%20Folders%27%5d%2freport%5b%40name%3d%27tabbed-revenue%27%5d&ui.name=tabbed-revenue&run.outputFormat=&run.prompt=true&cv.header=false";

}

}

</script>

And finally, let's add the HTML elements:

<div id="demo-container">

<ul id="simple-menu">

<li><a href="javascript:putContent(1);void(0);" title="Revenue" id="link1" class="on">Revenue</a></li>

<li><a href="javascript:putContent(2);void(0);" title="Margin" id="link2" class="off">Margin</a></li>

<li><a href="javascript:putContent(3);void(0);" title="Inventory" id="link3" class="off">Inventory</a></li>

</ul>

</div>

In this example, the tabbed report interface contains three tabs (Revenue, Margin and Inventory), but you could add as few or as many as you want (room permitting). Each tab element is actually an HTML un-ordered list item (referenced as &ltli>) which the CSS alters to make look like the tabbed menu interface. The <a href> tag within the list element contains some Javascript (ie javascript:putContent(1);void(0);") which calls a function in the Javascript code. The function 'putContent()' serves 2 purposes: First, it targets the iframe with a new source object, the URL (report path) of your report; and second, it changes the display of the tabs to show the most recent clicked tab (and sets the other tabs to their off-click states). The report paths are specified in the switch method (case 2: document.getElementById("content").src = "") and should be changed to match the report path of your own reports. Remember to add the '&cv.header=false' parameter. The final report tab interface should look something like this.

Tip: Setting the default skin

Quick tip - I've defined a new skin for IBM Cognos 8.4 and want to set it as the default for all new users:

Browse to Cognos Administration >Security>Users, Groups and Roles, and click into the "Cognos" directory. Now set this in the "Edit Default User" settings (the icon is in the upper right corner).