Your (almost) Daily Cognos

Calculated Measure, Member or a Query Calculation?

2009-09-01T12:35:00.004-04:00

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

2009-08-31T10:32:00.005-04:00

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

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

2009-08-26T11:08:00.007-04:00

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;

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?

2009-08-26T10:09:00.006-04:00

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

2009-06-10T10:28:00.006-04:00

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

2009-06-09T00:06:00.001-04:00

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

2009-06-05T03:02:00.000-04:00

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

Viewing burst versions - not just for your admin

2009-06-04T00:26:00.000-04:00

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.

Go! Dashboard Install Fun

2009-04-07T09:50:00.000-04:00

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

Calculated Members

2009-04-01T10:02:00.000-04:00

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

Cognos on the iPhone?

2009-03-24T11:45:00.000-04:00

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)

Drop down lists: Customize label

2009-03-19T23:23:00.000-04:00

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

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

IBM is looking at Sun

2009-03-18T11:29:00.000-04:00

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?

Need PDF with Digital Signature?

2009-03-02T15:45:00.000-05:00

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.

Using Relative Dates In Business Reporting

2009-02-26T14:11:00.000-05:00

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.

Prompt date formats

2009-02-11T15:28:00.000-05:00

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.

Freeze rows and columns on Report Studio crosstab

2009-01-26T14:25:00.000-05:00

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.

Steps:

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

Set default "from date" in a date range prompt

2009-01-23T12:43:00.000-05:00

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

OR

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

IBM Cognos 8.4 - Released to the masses

2008-12-18T23:57:00.000-05:00

Just in time for Christmas, Cognos 8.4 was released for download on December 15th. Highlights include Flash-based dashboards, improved BI search, location-aware mobile delivery and new data lineage capabilities. A new viewpoint interface lets business users manage hierarchies and dimensions. A bus full of new features have been added:

Increased visual appeal includes new background designs and colors. Users can add gradient colors and behind charts
New chart types: Merimekko, Stepline, Sparkline, Sparkbar, Spark Win/Loss
Report annotations: Users can add comments directly within the body of HTML-rendered reports
Enhanced suppression: Suppress all types of reports against relational, dimensionally-modeled relational, and OLAP sources. Also, users can also suppress rows, columns, or rows and columns based on "zero" values, "divide by zero" values, missing values, and overflow values
Extended filters now include wildcards, range, and between operations
Associated frames now drill together
Strategy Maps and Impact Diagrams now available as portlets that can be integrated into IBM Cognos Connection, IBM WebSphere, and MS Sharepoint portals
Data lineage: Users can access and view major transformations to data as it moves through the system
Expanded data access: Use data from IBM Cognos Now! and IBM Cognos TM1

The reviews are fairly positive (quote from intelligententerprise.com):

"I give Cognos a lot of credit for taking the time, a few years ago, to rewrite the entire platform, but with that effort they fell somewhat behind on bells and whistles," says Forrester Research analyst Boris Evelson. "With 8.4, Cognos is filling out many of the pieces that competitors have provided over the years."

Read the complete review here: After Leading on Platform, Cognos Catches Up On Details

More information can be found on the Cognos site: What's New in 8.4

Your Corporate Brand on the C8 UI

2008-12-17T16:48:00.000-05:00

Want to modify / customize your C8 headers (and add your company name and logo for example)? Look to make these changes directly in the system.xml (cognos\c8\templates\ps\portal\system.xml).

Steps:

1. Place an image in the folder \c8\webcontent\skins\my_style\branding, where "my_style" is one of the skins/styling that is being used for the portal.
2. Edit the /c8/templates/ps/portal/system.xml file using Notepad or an XML editor.
3. Search for You will notice that this section is commented out. Un-comment out this section and customize as needed.
4. Restart the Cognos 8 service for these changes to take effect.

Ensure you make a backup of your original system.xml file. Refer to the section: "Part 8 Customization" in the Cognos 8 Administration and Security Guide.

Reference book: IBM Cognos 8 Business Intelligence

2008-12-08T16:14:00.000-05:00

From the editorial review: Develop, deploy, and maintain a complete BI solution across your enterprise. IBM Cognos 8 Business Intelligence: The Official Guide shows you how to take full advantage of the robust service-oriented architecture and simplified role-based user interfaces. Learn how to create, share, and analyze data-rich reports that lead to increased productivity and better business decisions. Featuring real-world insight and expert tips, this is a must-have guide for IBM Cognos 8 Business Intelligence users of all levels, from administrators to end users.

Measure and monitor performance using dashboards and scorecards; Assign user roles and privileges; Provide complete and consistent access to a broad range of data sources via Cognos Connection; Use Cognos 8 Go! to extend BI to Office applications, mobile devices, and search engines; Enable business users to create reports with Query Studio; Develop new insights by exploring data in multiple dimensions and perspectives with Analysis Studio; Build Dimensional Reports with Report Studio; Use Event Studio to identify and deliver mission-critical information; Create, modify, organize, and publish a model from Cognos Framework Manager;
Implement sound security measures; Take advantage of the management tools in Cognos Administration to ensure maximum reliability and availability.

We'll review this book soon. You can purchase this book on Amazon or as an eBook.

Disclaimer: The author of this blog is not affiliated with the author or publisher of the above described book.

Javascript technique: Tag cloud

2008-11-23T08:59:00.000-05:00

I was recently asked to build a quick view dashboard for the Sales team which contained a web 2.0 style tag cloud. What's a tag cloud? A tag cloud or word cloud (or weighted list in visual design) are usually single words and are typically listed alphabetically, and the importance of a tag is shown with font size or color.

Since Cognos 8 doesn't include such a widget, I utilized my peer network and was given this example which utilizes javascript. Here are the steps I followed to put it together (I'll use the standard demo data in Cognos called Go Sales):

Open a new Report Studio List report with the GO Sales package and populate it with [Staff Name] and [Revenue]. Next add [Order year] to the Query. Click the Filters button on the toolbar and create new filter:

[gosales_goretailers].[Orders].[Order year] = ?date?

Set it to Required. Drag a [Value Prompt] from the Toolbox (Insertable Objects) to left of your list so it appears on top. Set it to use the existing parameter ‘date’ and click Finish. In the properties for this prompt, set the Query to [Query1]. Set the Use Value to [Order year] and the Display Value to [Order year]. Create new Static Choices for this prompt. The new variable should be a string (call it anything) and add the use/display values 2003, 2004, 2005 and 2006 as below:

Next set the Properties to [Order year]. Set the Auto-Submit and Hide Adornments for this prompt to ‘Yes’. Finally, set the Default Selections to 2004 and change the width in the Size & Overflow to 75px.

From the Insertable Objects Toolbox drag an [HTML Item] to the right of the newly created Value Prompt. Add another [HTML Item] to the right of the List. Your page should look like this:

Open the top [HTML Item] and add the following:

<div id="cloud"></div><div id="dg" style="display:none">

Click OK. Open the second [HTML Item] (the one below the list) and add the following:

</div>

Click OK. Delete the contents of the page footer and add a new [HTML Item]. Open it and add the following modified open source code:

<textarea readonly="" cols="40" rows="20" id="cloudhtml" style="display:none;"></textarea>
<script>
// ====================================
// params that might need changin.
// DON'T forget to include a drill url in the href section below (see ###) if you want this report to be drillable
var delimit = "|";
var subdelimit = "[]"; // change this as needed (ex: Smith, Michael[]$500,000.00|)
var labelColumnNumber = 0; // first column is 0
var valueColumnNumber = 1;
var columnCount = 2; // how many columns are there in the list?
// ====================================

function formatCurrency(num) {
num = num.toString().replace(/\$|\,/g,'');
if(isNaN(num))
num = "0";
sign = (num == (num = Math.abs(num)));
num = Math.floor(num*100+0.50000000001);
cents = num%100;
num = Math.floor(num/100).toString();
if(cents<10)
cents = "0" + cents;
for (var i = 0; i < Math.floor((num.length-(1+i))/3); i++)
num = num.substring(0,num.length-(4*i+3))+','+ num.substring(num.length-(4*i+3));
return (((sign)?'':'-') + '$' + num + '.' + cents);
}

function filterNum(str) {
re = /\$|,|@|#|~|`|\%|\*|\^|\&|$|$|\+|\=|\[|\-|\_|\]|\[|\}|\{|\;|\:|\'|\"|\<|\>|\?|\||\\|\!|\$|/g;
// remove special characters like "$" and "," etc...
return str.replace(re, "");
}

tags = document.getElementById("dg").getElementsByTagName("SPAN");
txt = "";
for (var i=columnCount; i<tags.length; i++) {
valu = filterNum(tags[i+valueColumnNumber].innerHTML);
txt += valu;
txt += subdelimit+tags[i+labelColumnNumber].innerHTML+delimit;
i = i+columnCount;
}

function getFontSize(min,max,val) {
return Math.round((150.0*(1.0+(1.5*val-max/2)/max)));
}

function generateCloud(txt) {
//var txt = "48.1,Google|28.1,Yahoo!|10.5,Live/MSN|4.9,Ask|5,AOL";
var logarithmic = false;
var lines = txt.split(delimit);
var min = 10000000000;
var max = 0;
for(var i=0;i<lines.length;i++) {
var line = lines[i];
var data = line.split(subdelimit);
if(data.length != 2) {
lines.splice(i,1);
continue;
}
data[0] = parseFloat(data[0]);
lines[i] = data;
if(data[0] > max)
max = data[0];
if(data[0] < min)
min = data[0];
}lines.sort(function (a,b) {
var A = a[1].toLowerCase();
var B = b[1].toLowerCase();
return A>B ? 1 : (A<B ? -1 : 0);
});

var html = "<style type='text/css'>#jscloud a:hover { text-decoration: underline; }</style> <div id='jscloud'>";
if(logarithmic) {
max = Math.log(max);
min = Math.log(min);
}
for(var i=0;i<lines.length;i++) {
var val = lines[i][0];
if(logarithmic) val = Math.log(val);
var fsize = getFontSize(min,max,val);
dollar = formatCurrency(lines[i][0]);
html += " <a href='###Some drillthrough url which includes the param "+lines[i][1]+"' style='font-size:"+fsize+"%;' title='"+dollar+"'>"+lines[i][1]+"</a> ";
}
html += "</div>";
var cloud = document.getElementById("cloud");
cloud.innerHTML = html;
var cloudhtml = document.getElementById("cloudhtml");
cloudhtml.value = html;
}
function setClass(layer,cls) {
layer.setAttribute("class",cls);
layer.setAttribute("className",cls);
}
function show(display) {
var cloud = document.getElementById("cloud");
var cloudhtml = document.getElementById("cloudhtml");if(display == "cloud") {
setClass(cloud,"visible");
setClass(cloudhtml,"hidden");
}
else if(display == "html") {
setClass(cloud,"hidden");
setClass(cloudhtml,"visible");
}
}
generateCloud(txt);
</script>

Click OK, run the report. Roll over the individual Sales rep to see the actual revenue in the flyout. You can now customize this for your needs and add it as a Cognos Viewer portlet to a Cognos portal page or your preferred portal.

YouTube find: MB Dashboards for Cognos 8

2008-11-17T07:56:00.000-05:00

A UK company called morgan benjamin (lower case on purpose) has developed a sharp looking dashboarding tool for Cognos 8 business intelligence. From the visuals, it seems to integrate nicely into the Cognos suite and is available for all Cognos 8 releases.

Key features:

Data driven ticker
Pop up & Transition facilities
Zoom Functionality
Links to other Studios (Report Studio, Analysis Studio, Query Studio)
Animated KPI’s
Content and functionality Rich Dashboards created in hours not weeks
Compatible with all versions of Cognos 8
Leverages your in house report studio skills
Employs professionally designed graphical layout
Multiple templates and colour schemes
Simple to add corporate identity
Allows users to add comments to dashboards
Links seamlessly with existing reports, cubes and data sources

I'm most interested in the ability to add comments or annotations to reports although I have read that this type of functionality will be included when Cognos 8.4 is released.

Top 5 and ranking

2008-11-07T11:09:00.000-05:00

Need to build a top 5 list into your dashboard report? In my simple example, I have a 2 column list with [Products] and [Sales]. I create a new data item with:

rank([Sales] within set [Products])

I called this new data item [Rank] then applied a filter:

[Rank] < 6

That does it. But not really, because the rank() function will potentially give more then 5 rows if there are multiple entries with the same value. For example, if we were ranking on the following list and filtering for top 3:

Products: Sales: Rank:
Product1 50,000 1
Product2 40,000 2
Product3 30,000 3
Product4 30,000 3
Product5 20,000 5

You will end up with 4 rows in your report.

To restrict this to a only 3 rows, you'll need to sort the query by [Sales], and then use "running-count(1 for report)" in your [Rank] function. Then you'll get just the first 3 rows. Note that whether you get Product3 or Product4 will depend on other sorting criteria or the natural order of the data in the database, so take care!

The tip I've explained above uses the rank() function which is part of the member summary functions and utilized on non-dimensional source. For dimensional source, you will want to use the topCount() function to define [Products].