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 (974 downloads)

 

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

 

 

Checking an “All” option in a checkbox prompt

One of my readers sent me an interesting problem. They need a checkbox prompt in which the top option is “All” and checking on any other option would automatically uncheck the “All” choice. Similarly, checking “All” should uncheck the other choices. Taking it to the conclusion, when checking or unchecking all of the options, the “All” should be checked.

Since they are still on 10.1, I have not used the Prompt API, meaning this should work on all versions since 8.4. To begin, the prompt itself.
check this out

In this case, the prompt is based on the Retailers hierarchy in the Sales and Marketing cube. Other times you might want to add a static value.

The JavaScript itself is not that difficult. The JS will loop through the prompt each time an option is clicked. If the first option is clicked, it will check it and uncheck the other options. If any other option is click it will loop through the prompt, counting the number of checked options and act accordingly. If 0 or all of the options are checked, it will check the first option, otherwise it will simply uncheck it.

When working with Checkbox prompts in JavaScript, the thing to remember is that, for whatever reason, the checkboxes that we see are actually images. To show the prompt as checked, the input needs to have the class “dijitCheckBoxChecked”.

Now the JS:

<script>
/* 
  * Function: addEvent
  * Author: Dan Fruendel
  * Attachs an event or adds an event listener depending on the browser.
  */
var addEvent = function(element, event, func){
    if(element.addEventListener){
      addEvent = function(element, event, func) {
        element.addEventListener(event, func, false);
        return true;
      };
    }
    else if(element.attachEvent) {
      addEvent = function(element, event, func) {
        return element.attachEvent("on" + event, func);
      };
    }
    else {
      addEvent = function(element, event, func) {
        var oldEventHandler = element['on' + event];
        element['on' + event] = function() {
         //using .apply to pass on anything this function gets.
          if(typeof(oldEventHandler) === "function") {
            oldEventHandler.apply(element, arguments);
          }
          func.apply(element, arguments);
        }
        return true;
      };
    }
    addEvent(element, event, func);
  }


// Cognos form and namespace identifier. Don't touch.
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]); 
if ( !fW || fW == undefined)   {
       fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );
   } 
 var preFix = "";
 if (fW.elements["cv.id"]) {     preFix = fW.elements["cv.id"].value;   }
     var nameSpace = "oCV" + preFix;
 
function attacher(elm,prompt,clicked){
  var func = function() {
    //if all is selected, zero out everything else.
    if(clicked==0) {prompt[0].selected=true;prompt[0].checked=true;prompt[0].parentNode.className="dijitInline dijitCheckBox dijitCheckBoxChecked";for(var i=1;i<prompt.length;++i){prompt[i].selected=false;prompt[i].checked=false;prompt[i].parentNode.className="dijitInline dijitCheckBox"}}

    //if individual, count number of selected
    if(clicked>0) {var c=0;
      for(var i=1;i<prompt.length;++i){
        if(prompt[i].checked){++c}
      }
      //if the count of checked is 0, then set the all to checked
      if(c==0) {setter=false;prompt[0].selected=true;prompt[0].checked=true;prompt[0].parentNode.className="dijitInline dijitCheckBox dijitCheckBoxChecked"}
      //if the count of checked is equal to the length of the prompt, then set the all to checked and uncheck everything else
      else if(c==prompt.length-1) {setter=false;prompt[0].selected=true;prompt[0].checked=true;prompt[0].parentNode.className="dijitInline dijitCheckBox dijitCheckBoxChecked";        for(var i=1;i<prompt.length;++i){prompt[i].selected=false;prompt[i].checked=false;prompt[i].parentNode.className="dijitInline dijitCheckBox"}}
      //if the count is one and less than the length of the prompt then just set all to unchecked;
      else if(c>0&&c<prompt.length-1){prompt[0].checked=false;prompt[0].selected=false;prompt[0].parentNode.className="dijitInline dijitCheckBox"}
    }

    canSubmitPrompt();
    }
  addEvent(elm,'click',func)

}

var prompt=fW._oLstChoicesCountries;
for (var i=0;i<prompt.length;++i){
  attacher(prompt[i],prompt,i);
}

</script>

EDIT: An eagle-eyed reader noticed that the appearance of the checks are actually slightly different than they are when first rendered. The solution was to put a parentNode after prompt[i] when calling the className. Thanks Sue!

Checkbox Prompt - All option (678 downloads)

Checkbox List Prompts revisited

A long while back, I published an article detailing how to use a list to interact with a checkbox prompt. Since then, several people have asked me to add features to the code, and to fix some bugs. I’ve also learned quite a bit since I wrote that, so I’ve rewritten a parts of it.

The basic core of the code is the same. Using div tags to identify the list and prompt objects, with more HTML items to build the actual checkbox in the list. This is by no means optimized JavaScript, nor is it using the 10.2 Prompt API.

Previously the code would only work with one list. The reference to the list was hardcoded in the script and the checkboxes and select all buttons would fail for the second list. Also requested, using grouped rows. For instance, a user might want to select all of the months in 2011 by checking on the checkbox in the 2011 group. Another thing I fixed was the way it would loop through value prompts to find the correct value. If the key had any special characters, it wouldn’t be able to find it. Cognos would automatically escape those special characters.

The XML below was written on 10.2, but will downgrade easily to 10.1 by changing

xmlns="http://developer.cognos.com/schemas/report/9.0/"

to

xmlns="http://developer.cognos.com/schemas/report/8.0/"

The JavaScript will work in 8.4 as well, but downgrading will be a bit more difficult – you need to use /report/6.0/ and remove all of the version 10 only XML elements. If there’s a demand for it, I’ll go through and post all of the different JavaScript objects here. (But there shouldn’t be, because 10.2 is the greatest things since 10.1.1 and you should all upgrade and IBM should be paying me push upgrades like this.)

checkbox-List-Prompt.txt (481 downloads)