JavaScript in Cognos Analytics, and I need your help!

This is a repost of an article I wrote for the PMsquare journal, with permission of course. The original can be found here. Make sure you subscribe to their newsletter for other great articles!

In every new release of Cognos, there are some ups, and there are some downs. And while some people may have a lot to complain about in the new version, there is are a few shining advances that force me to forgive all the questionable design decisions (even the loss of the menu and button bars in Report Studio).

Today, and the in next few articles, we’ll be talking about JavaScript. They don’t call me JavaScriptPaul for nothing, y’know (nobody does yet, but someone might some day).

JavaScript and Cognos has always been a touchy subject. Historically unsupported, incompatible with most libraries, and with a cryptic undocumented internal API, JavaScript has been a major challenge to implement in a Cognos report. In Cognos 10.2, IBM started officially recognizing that people wanted more, creating the basic Prompt API. While limited, it was a start to making truly interactive reports.

And now, in Cognos 11, we finally have a fully supported JavaScript control.

The new JavaScript control is for use with the new Interactive Mode. Non-interactive mode appears to work the same way as C10. Inline JS will only work with non-interactive mode. The big problem I have with this is you have to save a JS file onto a server somewhere. This makes development a problem, especially if you’re a lowly dev who doesn’t have direct access to save files on the server. On the flip side, if you are a lowly dev, all you need to know is where these JS file are and what to pass to them.

The Interactive Mode will dynamically download the files and cache them in the browser. This makes for a slightly faster user experience.

Unlike the C10 API everything available is documented. On the positive side, this means that all the JS functions are fully supported. On the negative side, this does mean that there aren’t a lot of them yet. All of the undocumented and unsupported functions, like oCV_NS_.getSelectionController().isDrillLinkOnCrosstabCell() (Yes, this is a real function and yes I’ve used it) have been compiled into a random string of letters of numbers.

2. Randomized functions

I’ll touch on a few of the new features briefly, then show a working example.

In C10 and previous there were three ways of getting data into a JavaScript Object. Easiest way would be to associate a value prompt with one, but then we’re limited to only two attributes. Second way would be to dump everything into a list, but then we need to loop through a table – slow and annoying. The third way is to use repeaters to inline the JS. The big problem with this is there’s no formatting option for numbers, and some strings are problematic.

In C11 the JavaScript controls can be assigned to a specific dataset from a query. This circumvents the problem with excess data AND the issue with invalid characters. In addition to datasets, we can pass a JSON string to the control containing additional configuration information.

3. Data and Configuration

Calling specific report elements, such as blocks and lists, can be done with a simple call to the page, stacking .getControlByName. Once you have the control, there are a few basic things you can – setting visability, width, height, colors. But you CAN get the HTML element – and with that you can do a lot.

An often requested function is the ability to select visible columns in a list. In fact, IBM even has an example of this on their demo server.
4. IBM showing and hiding columns

Personally I don’t like that solution. End users don’t want to type the column index, and when they page down it doesn’t remember the selection. I solved that using sessionStorage, but let’s focus on the Cognos centric code.

The JavaScript starts by defining the function.

define( function() {
"use strict";
function columnSelector(){};

Next, we initialize the function.

columnSelector.prototype.initialize = function( oControlHost, fnDoneInitializing )
{
  var o = oControlHost.configuration;
	this.m_sListName = o ? o["List name"] : "List1";
  this.m_aStatic = o ? o["Static choices"] : [];

  if(!window.sessionStorage.getItem(this.m_sListName+'SelCols')) window.sessionStorage.setItem(this.m_sListName+'SelCols','[]');

  if(!window.sessionStorage.getItem(this.m_sListName+'SelColsFR')) window.sessionStorage.setItem(this.m_sListName+'SelColsFR','1');

  fnDoneInitializing();
};

oControlHost is the object passed to the script from Cognos. It’s a unique identifier that includes any extra configuration data defined in Report Studio. The List name is optional, so long as you have List1 in the output. The static choices also, optional. Next we have sessionStorage. This is what lets the page navigation remember what the user selected.

I believe fnDoneInitializing instructs Cognos that it’s actually ready to go to the next step.

Next we can actually start building the control on the page. Notice these functions are attaching themselves to the parent. This allows us to use other variables attached to it, like this.m_sListName, across the various functions.

columnSelector.prototype.draw = function( oControlHost )
{
	var elm = oControlHost.container,
      list = oControlHost.page.getControlByName( this.m_sListName ).element,
      listHeaders = list.rows[0].childNodes,
      listHCount = listHeaders.length,
      selArr = eval(window.sessionStorage.getItem(this.m_sListName+'SelCols')),
      firstRun = window.sessionStorage.getItem(this.m_sListName+'SelColsFR'),
      sel = document.createElement('select');

  sel.multiple=true;
  sel.style.height="100%";
  sel.style.width="100%";
    
  for (var i = 0;i<listHCount;++i){
    var selected = listHeaders[i].style.display=='none'?false:true,
        opt = document.createElement('option');

    opt.value = i;
    opt.text = listHeaders[i].innerText;
    
    if(window.sessionStorage.getItem(this.m_sListName+'SelColsFR')==0){
      if(selArr.includes(i)) {
          opt.selected=true;
          oControlHost.page.getControlByName( this.m_sListName ).setColumnDisplay(i,true)
        } else {opt.selected=false
        
        oControlHost.page.getControlByName( this.m_sListName ).setColumnDisplay(i,false)
        }
    }
    else{opt.selected=selected};
     
    sel.appendChild(opt);
  };

	elm.appendChild(sel);
  
  window.sessionStorage.setItem(this.m_sListName+'SelColsFR',0);
	this.elm = sel;
	this.elm.onchange = this.onChange.bind( this, oControlHost );
};

We define the select prompt, find the selected list, and loop through the first row. If the cell style is set to display:none, then it’s hidden and the option in the select prompt should not be selected. The important thing though is the select is defined using the list as a source. This makes it easier for the developer.

The sessionStorage bit is to ensure the first run works as expected, and the page down remembers what’s selected.

Next we have to define what happens when the select is changed.

columnSelector.prototype.onChange = function( oControlHost ){
	var ctrl = oControlHost.page.getControlByName( this.m_sListName ),
      selOpts = this.elm.options,
      selArr = [],
      selLen = selOpts.length;
    
  for (var i=0;i<selLen;++i){
    
    if(this.m_aStatic.includes(i)) {
      selOpts[i].selected=true;
    };
  
    if(selOpts[i].selected) selArr.push(i);
    ctrl.setColumnDisplay( i, selOpts[i].selected );
  };
  
  window.sessionStorage.setItem(this.m_sListName+'SelCols','['+selArr+']')
  
};
[/sourecode]

And finally, let's close off the function.


return columnSelector;
});

Using this in Cognos is fairly easy. First, we need to make sure it’s saved somewhere accessible. In this case I’m keeping it \cognos\analytics\webcontent\javascript. Referencing it in the report isn’t as smooth as I’d like, the developer will actually have to enter the path to the file.
5. JS Path

Next we define the configuration object manually.
6. Configuration

The last bit here is the UI Type.
7. UI Type

For a control like this where we’re creating an input, we’d want to use “UI without event propogation”. If we were setting up a Prompt API script, one that interacts with the page without creating an object on the page, we’d use “none”. Something that requires bubbling, “UI with event propagation”.

And now when we run it, everything works! As an added bonus, when a user pages down to a new page, it will use the previous page’s columns. When paging up, it remembers the state of that page.

8. It works

Now, the savvy reader may have noticed some negativity I have towards the way report developers select the desired control. I need YOUR help to fix it! I have submitted an RFE to the IBM Request For Enhancement site. IBM prioritizes fixes and changes based on demand, so I need everyone to click here to vote. You will need to log in with your IBM account. Vote early, and vote often, and I’ll personally send you 10 CognosPoints for your vote!

Different Drillthroughs for Graph Elements

I recently received an interesting problem. A multi bar graph needed to have drillthroughs pointing to separate reports. The requirement is to have it seamless for the end-user, no transitional screens. If the user clicks on the revenue bar, it needs to go to the revenue report. If they click on the planned revenue bar, it goes to the planned revenue report.

As the product is currently built, drillthroughs are defined on the graph level, not a measure level. Let’s take a look at the actual HTML being generated:

separateDrillsDefaultChart

<area 
  dttargets="<drillTarget drillIdx=\"2\" label=\"Planned revenue\"/><drillTarget drillIdx=\"3\" label=\"Revenue\"/>" 
  display="914,352,803.72" 
  tabindex="-1" 
  ctx="27::22::18" 
  title="Year = 2010 Revenue = 914,352,803.72" 
  coords="157, 309, 157, 174, 118, 174, 118, 310, 157, 310" 
  shape="POLY" 
  type="chartElement" 
  class="dl chart_area" 
  ddc="1" 
  href="#"
>

In this example, I have a chart with two bars. In the area of each bar, the dttargets is defined with both drills. The drills themselves I’ve named the same as the data item of the measure. We can then use JavaScript to extract the dttargets string, match the label of the drill to the data item name, and place the correct one in there.

/*
 * This will loop through every chart and replace multiple drill definitions with one. 
 */
paulScripts.fixChartDrills = function(chartName){
  var oCV = window['oCV'+'_THIS_']
  , areas = paulScripts.getElement(chartName).parentNode.previousSibling.getElementsByClassName('chart_area')
  , areasLen = areas.length
  , areaDataItemName
  , drills=[]
  , dtargets=[]
;

for (var i=0;i<areasLen;++i){
  if(!areas[i].getAttribute('dttargets')) continue;
  areaDataItemName=oCV.getDataItemName(areas[i].getAttribute('ctx'));

  drills = areas[i].getAttribute('dttargets');
  dtargets =drills.split('>');

  for (var j=0;j<dtargets.length;++j){
    var regexp = /label...(.+?)."/g;
    var match = regexp.exec(dtargets[j]);

    if(match&&match[1] == areaDataItemName) areas[i].setAttribute('dttargets',dtargets[j]+'>');

  }

First we’re finding the chart that we want to do this on, and finding the area map. We loop through the areas, skipping the ones that don’t have any dttargets (like the legend or labels).
For each area with a dttarget, we get the source data item name. Fortunately for us, there’s a useful Cognos JavaScript function to do it! Then a little hackey JS magic to get the label for each individual dttarget we can finally match and replace the dttargets attribute.

Let’s see it in action!
separateDrills

Now it’s very important that the drillthroughs have exactly the same names as the data items. If they don’t do that, this script won’t work – but of course that wouldn’t stop you from using different logic. I built this in Cognos 10.2.2, but I have no reason to think it’s not backwards compatible. The full JavaScript, including the paulScripts.getElement can be found in the report XML below.

separateDrills.txt (607 downloads)

Guest Post: A New Take on Date Range Prompts

Since version 10.2 of IBM Cognos BI Suite, IBM included an API to access and manipulate prompt objects. Since prompt objects are the main instrument we use to allow users to communicate with a report (Interactivity or user selection), being able to manipulate them however we see fit can change user experience dramatically for the better. There are countless examples of how the prompt API can be used to achieve this. For example, dynamic defaults: Suppose you have two prompts, for region and for products. You want the default product selected to be the best selling product in the region selected. With prompt API, this can be achieved easily.

In this post I’d like to showcase one of the first solutions I ever wrote using Prompt API, because it was one of the things I wanted to solve for a long time.

Every so often we add “from date” and “to date” prompts to a report, to use for filtering the report output to show only data from the date range selected. The problem is, most users and most use cases don’t require the sort of flexibility a date range offers: most users will not run their sales-per-branch report between April 23rd and May 2nd, for instance, because it’s an arbitrary chunk of dates. Instead, users are likely to filter dates for last month, this MTD, QTD, YTD, last week and so on. So, basically, set, standard, comparable time frames. And sometimes the date range prompt can be replaced with a drop down list of such pre-set ranges, but other times, users ask to still have the flexibility of choosing to and from date, but nonetheless, still mostly use the set, comparable ranges.

Now, in order to select last month’s dates with two date prompts, your average user will need 6 clicks: One to open from date calendar, one to page back to last month, one to click on “1”, and the same process with the “To date” prompt. For YTD, they might need more. That’s a lot of clicks. Also, developers often have to write scripts to get the default value right, and because these date prompts are never done in a centralised, reusable manner, they end up writing a script for each report. I have long fought the war on developers wasting time by doing things more than once, and this case is no different. Even if reports require different default times, the solution can still be generalised, and therefore made reusable.

My solution uses JavaScript and Prompt API to add to the date prompt functionality. Here is how it works:

Date Solution

I’m using two date prompts, and adding 7 pre-defined links, which, when clicked, fill in their respective dates. So, for example, clicking on MTD will set the from date prompt to the 1st of the current month, and the to date prompt to today’s date. There’s also a verification mechanism in place to ensure that from date is always earlier than to date, or equal to it.

But how do I make this solution generalised? Let’s take a look at the report studio report:

RS Look

The bit in blue is the error message the user will get if they choose an end date that’s prior to the start date. The bit in blue is a text that should be replaced with another text – containing just one number between 1 and 7, corresponding with a dynamic ate range.  “1” is YTD, 4 is WTD and so on.

Now, if you drag in a layout reference object to this interface, here’s what you’ll get:

override

You can override&replace the warning message and the default text. So, if the default for a certain report is “last month”, you’ll override “Defaults”

replace

Drag in a text item and insert “5”

default set

When you run the report, the default would be last month:

final result

This way you can set a different default value for each report in a reusable manner.

I’m attaching the XML, of course, but pay attention to these caveats:

1. The script has seven preconfigured date ranges. You can change them or add to them as you require, and use the general example in the code, but it requires some knowledge of scripting. Unfortunately, I will not be able to provide support for such customisations.

2. If you’re relying on my script to manipulate weeks, pay attention that my script assumes Monday is the first day of the week. Israelis especially, this means you’ll have to change this logic (Weeks in Israel begin on Sundays).

3.This is 10.2.2 – You can downgrade it to 10.2.x by changing the version number at the top.

 

daterange.txt (6162 downloads)

 

Nimrod (Rod) Avissar is a Cognos expert, with a penchant for specialized UX solutions. Feel free to drop me a line! (LinkedIn).