CognosPaul’s Bag o’ Tricks!

CognosPaul’s Bag o’ Tricks!

Back in August I presented at BACon on the extensions that I wrote for PMsquare, and now I’m very happy to announce that we’re giving them away for free! We actually started giving them away for free at the conference, but I’ve been a bit too lazy busy to actually get around to writing this.

First you might ask, what are extensions? As I wrote about in a previous article extensions allow you to modify the UI, adding or removing features as you like. One of the best things about adding new features is using the internal JavaScript API in Report Studio, making it do what you want it to do instead of what IBM wants it to do.

One of the features of that article was an early version of the Clipboard Editor. The basic structure is there, but it’s been improved immensely since. My good friend Michael Hoggard and I worked on it to add a new feature, and to clean it up a little.

changes in clipboard editor

But that’s not all! I’ve also added another new and awesome feature. One of my biggest issues with building reports is testing out different prompt scenarios. We could run the report and manually set each prompt, but that can be incredibly time consuming. You could force Cognos to prompt you, by validating or by clicking on “view generated SQL/MDX” but that’s clunky. You also can’t view the currently set parameters, and if you want to change anything you have to clear the parameter values and start again.

To get around this clunky interface, I’ve decided to write my own. I’m very proud to present the aptly named “Parameter Editor”!

parameter editor

This interface allows you to add, remove, and change session level parameters in the report. What this means is you can now see what scenario you’re running the report in, without having to go through the tedious process of running the report, selecting the prompts, and rerunning. But that’s not all! When running in interactive mode, you an also use it to see exactly what parameters were sent through a drillthrough. When previewing a report, you can also play with the parameters. See what happens when you change them from one scenario to another. In the below animation I’m testing several different scenarios, where one fails for some reason!

Param editor

As you can see you can set both the display and use values. The plus button allows you to add multiple values for each parameter. If both boxes are empty, the script ignores it and doesn’t set a value for the parameter. If only one of the boxes is empty, it will use the value from the other box.

These are session level parameters as opposed to report level. It’s an important distinction. The parameters are set on the report development session only – they aren’t saved with the report.

I do have additional tools that I’m planning on adding to it. Data Modules present a host of opportunity. I feel there is a lot of room for improvement in the UI, especially when it comes to quickly editing multiple items.

And finally, you can download this extension from the PMsquare website here: https://pmsquare.com/freebies

Setting the default search type in Cognos 11 interactive mode

The search prompts haven’t gone through many changes in Cognos since version 8. Despite many requests, changing the default search type (from starting However, one big whopping change is the popup element containing the search items is no longer inside the search control. Instead it’s hidden somewhere else on the page, with nothing tying it to the actual control.

This makes the old script useless, and finding that popup is incredibly difficult. In Cognos 11.1 the method for finding the popup is promptElement._FZH._9FF while in 11.0 it’s promptElement._REG._FZD. This obfuscation means we have to rely on trickery and shenanigans.

Look away now if you value your sanity

prmptElmLoop: //yes I'm using a label here. I'm not feeling at all defensive about it.
  for(var a in  $(prmptElm).find('.clsComboBox')[0]){
    
    if(typeof($(prmptElm).find('.clsComboBox')[0][a])=="object" ){ //if the object itself is an object...
      for(var b in $(prmptElm).find('.clsComboBox')[0][a]){ //...dig deeper.
        if($(prmptElm).find('.clsComboBox')[0][a][b].classList=="po_clsListView_dropdown po"){ //this identifies the actual popup element. 
          $($(prmptElm).find('.clsComboBox')[0][a][b]).find("tr:eq("+(opt-1)+")").trigger('mousedown'); //finds the correct table row and performs a mousedown. 
          if(resizeOptions) prmptElm.style.width='unset';
          break prmptElmLoop;
        }
        
      }
    }
    else continue;

  }

In order to find that options popup, we’re looping through the prompt element and examining each attached method. If it’s an object, we go deeper. Finally we’ll find a method that’s a pointer to the element (which we can identify using the class), and then it’s just a matter of triggering a mousedown event on the correct row.

In order to use it, you need to give the search prompt a name, like mySnS. The custom control needs to have the following configuration:

{
"promptName": "mySnS",
"option": 2,
"resizeOptions": true
}

That’s looking for the prompt name mySnS, selecting “Starts with the first keyword and contains all of the remaining keywords”, and then resizing the options list.

The options are as follows:
* 1 – Starts with any of these keywords * DEFAULT
* 2 – Starts with the first keyword and contains all of the remaining keywords
* 3 – Contains any of these keywords
* 4 – Contains all of these keywords

Important note! I’ve tested this on 11.0.5 and 11.1.0. I have no reason to think it won’t work on other C11 versions but I can’t promise it will continue working. Hopefully methods like this will be exposed with a consistent name in future versions.

The attached zip contains the report XML and the JavaScript.
search-Default.zip (2807 downloads)

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!