Quando omni flunkus, mortati. Die dulci fruere.

| Subscribe via RSS

Tuesday, September 1, 2009

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.

Monday, August 31, 2009

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

Wednesday, August 26, 2009

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:

var f = getFormWarpRequest();
var prompt = f._oLstChoicesX;

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!

Wednesday, June 10, 2009

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:

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


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.

Tuesday, June 9, 2009

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;}


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:


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";




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>



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.

Friday, June 5, 2009

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

Thursday, June 4, 2009

Viewing burst versions - not just for your admin


Something that I've recently discovered in Cognos 8.4 (which was probably there all along): the "owner" of a given report can also see all the burst versions, even if they aren't set as the administrator.

So what about the recipient? Bursting, keys and the accessibility of the bursts in Cognos has always been a bit of a grey area for me. So, who gets to see what? In a graphical format, the burst output is accessible as:

Burst Key Recipient
Key1 Recipient A
Super Reader
Key2 Recipient B
Super Reader
Key3 Recipient C
Super Reader

If I log in as "Recipient A" then I will have access to output for Key1. If I log in as an account with membership to "Super Reader" then I will have access to Key1, Key2, and Key3.

Obviously a user with administrator rights would have sufficient access to view all keys for any given burst.

Tuesday, April 7, 2009

Go! Dashboard Install Fun


In a surprise twist I was successful at installing IBM Cognos Go! Dashboard. Why the surprise? Go! Dashboard is not one of the easiest installs from Cognos. Initially, here were the steps I followed:
  • Install Cognos Go! Dashboard
  • Change the portnumber from 8080 to 8085 in the system.xml
  • Generated the WAR file
  • Deployed it to the Tomcat server
  • Changed the Gateway URI in Content Administration
  • Generate a service

And Go! Dashboard replied: "The IBM Cognos 8 Go! Dashboard service terminated with service-specific error 0 (0x0)."

With some persistent investigation I found the following workaround:

First, I added the JAVA_HOME environment variable in the ..\cognos\c8\dashboard\bin\services.bat:

set JAVA_HOME=C:/Program Files/cognos/c8/bin/jre/1.5.0

Next, instead of generating the service, start the startup.bat file in the C:\Program Files\cognos\c8\dashboard\bin directory (default file location). After this the Tomcat server windows are spawned and Go! Dashboard actually seems to work (and everyone is happy).

Wednesday, April 1, 2009

Calculated Members


The member function is used to create a new member in a specified hierarchy. The initial argument of the member function is the expression used to create the member. The next two arguments are the business key and caption values for the new member. The final argument is the hierarchy in which your new member will reside.

A calculated member can serve almost any purpose. One example of using many calculated members is to create a Variance calculation. If you wish to calculate a variance for many different measures you wouldn't use the measures themselves. Instead, you would create a calculated member in another dimension that would form the basis of your variance calculation. Say you wanted to compare the current period values to the prior period values. This would use an expression something like:

([Current Period] - [Prior Period])/[Prior Period]

You could define this as a new member of your time hierarchy using the member function as:

member(([Current Period] - [Prior Period])/[Prior Period], 'CurPriorVar', 'Variance to Prior Period', [Cube].[Time Dimension].[Time Hierarchy])

Tuesday, March 24, 2009

Cognos on the iPhone?


Built a couple of the report samples out to an iPhone emulator yesterday. Even out-of-box the viewer looks pretty decent. The Cognos Viewer navigation buttons are manageable but would be obviously better if specifically designed for the iPhone.

Oracle and MicroStrategy BI both have offerings now for the iPhone so I'm sure we can expect it is only a matter of time until IBM Cognos picks this up.

(Click the image to see it larger)

Thursday, March 19, 2009

Drop down lists: Customize label


After creating a simple list in Report Studio you may want to create a drop down parametrized filter for it. First thing you'll notice when run the report is that the default selected text on the drop down is always set to the first field name of the list. I had requests to make this a little more representative, to something like 'Select field...' or 'Choose something...' which would give the user an indication that there were more choices available in the drop down.

To solve this issue, I found 2 techniques:

First is to create a static choice with the right correct display value for this prompt and set the default value to this static choice's value. Obviously this won't prevent the user from seeing the first line when he opens the drop down list.

The second technique would harness the following Javascript code. Drag a new html item into the report and include the following:

<script language="javascript">

var x = document.getElementsByTagName('select');

var RN_SelectClass = "clsSelectControl pv";

var is_Required;

for (var i=0;i 1) {
switch (x[i].options[0].text) {
case 'parameter1': x[i].options[0].text = 'NewLabel1'; break;
case 'parameter2': x[i].options[0].text = 'NewLabel2'; break;
default: x[i].options[0].text;

Next, change the parameter and NewLabel in the above syntax to suit your required label name conventions.

Wednesday, March 18, 2009

IBM is looking at Sun


There is breaking news that IBM is in talks to buy Sun Microsystems according to a report in The Wall Street Journal. This could be good news for the open source community especially with projects like Open Solaris. Also - IBM is heavily into Java and development of products using Java so I can see a real benefit in this area for them. Not sure what this does to Sun's recent cloud announcement...

Is $6 billion too much?

Monday, March 2, 2009

Need PDF with Digital Signature?


I was surprised to learn that there is no internal functionality to digitally sign a PDF file rendered by Cognos. I would expect that this would be a necessity for most Banks or financial institutions. After a quick bit of research (and some Google searches) I came up with one out-of-the-box method which might facilitate. The method in theory goes like:

Step 1: have the burst report run and save the output to filesystem in PDF format. Step 2: use a 3rd party software to process (digitally sign) the output file.

For step 2 I found something called "iText" (http://www.lowagie.com/iText/) which uses a Java JDK to post-process and add the digital signature to a PDF file.

I plan on investigating this method further but I'm interested in feedback or additional insight.

Thursday, February 26, 2009

Using Relative Dates In Business Reporting


Here's an interesting example / tutorial / technique for using relative dates in Cognos Report Studio as well as the scheduling of such reports. In this example the report administrator would setup a report that would display activity for the past week that would run every Monday morning: Relative Dates In Business Reporting

Examples of relative dates that many organizations might use are activity in the last 7 days, Month to date (MTD), Year to Date (YTD) to name a few.

Wednesday, February 11, 2009

Prompt date formats


In Cognos, date formats used in prompts are controlled by the locale settings. Although the locales each have their own format for dates you can change the format of the prompt date for your desired locale.

In the directory ...\cognos\c8\webcontent\prompting\res are two files called promptLocale_.js and promptLocale_.xml.

In these files you can see the date formats used for each locale. For example, if you want to use the en-us locale within Cognos 8, and you prefer a different date format for the prompt you can easily edit the promptLocale_en-us.js and promptLocale_en-us.xml files to change the medium date format. Let's say that you prefer to use the date format of yyyy-MM-dd, you'll just need to do the following:

In the promptLocale_en-us.js file,
  • Change the value for g_mediumFormat to this: yyyy-MM-dd h:mm:ss a
  • Change the value for g_mediumFormatDateOrder to this: YMD
  • Change the value for g_monthFormatMedium to this: MM
  • Change the value for g_dayFormatMedium to this: dd
In the promptLocale_en-us.xml file, change the following 4 properties to reflect the changes below:
  • yyyy-MM-dd h:mm:ss a
  • YMD
  • MM
  • dd
Always make sure you backup these files before any changes are made.

Monday, January 26, 2009

Freeze rows and columns on Report Studio crosstab


I had a request recently by a business user who wanted to know if Cognos could freeze the header rows and columns of a crosstab (an Excel-like functionality). I found a fairly simple technique on how to accomplish this.


  1. 1. Add two blocks on a blank report page.
  2. 2. Add a table with one cell and one row in the top block (assuming the report contains only one column).
  3. 3. Add a list on the lower block, and a data item from the model (order number).
  4. 4. Select the page, and associate the Query to the page.
  5. 5. Drag a text item in the cell of the table (in the top block).
  6. 6. Select the text item, and in the properties under query items, select order number (see step 3) and content as Label.
  7. 7. Select the second block containing the list.
  8. 8. Under Properties, Positioning, Size and Overflow, set Height = 12 cm and select Use scroll bar when necessary.
  9. 9. Select the Order Number column title in the list. Set its Box Type properties to None.

Friday, January 23, 2009

Set default "from date" in a date range prompt


I discovered recently that you can't set the "default" of any Cognos prompts to a dynamic (non-static) value.

Let me explain: In my example, within Report Studio, I needed to change a date range prompt. I needed the "from date" to default to the beginning of the current year (i.e. Jan. 1, 2009), and the "to date" to default to the current date (today's date).

The best (non-Javascript) method of handling this as I discovered, is to create a radio-button prompt with the static choices of "From Start of Year" and "Select From Date". Next, I set these to values of 1 and 0 respectively. Finally, in the filter, I used the following logic:

(?DateType?=1 AND [MyDate] > _make_timestamp(extract('year',currentdate),01,01))


(?DateType?=0 AND [MyDate] > ?FromDate?)

Wednesday, January 14, 2009

Recommended reading: The Challenge of Good Performance Management


A short article on Enterprise Systems magazine features a Q&A with the IBM Cognos VP of Worldwide Research and Development, Peter Griffiths. The article includes a concise and simple description of Performance Management (bookmarking this for my own reference) and how the new Cognos now under IBM management will continue to be effective in this space.

Read the complete article here: The Challenge of Good Performance Management

Follow me on Twitter (but keep in mind that I am not one of those Twitter addicts).