sort column using JavaScript in Cognos 8.3

[surgents]

Hi,

we made a simple list report with two columns:
diagnosis----number_of_patients

we unlocked the report and added html item next to number_of_patients column header with this html:

<span onClick="doSort(this,1,'n',0)">
<img src="../pat/images/sortState_ascending.gif"/ alt="sort ascending">
</span><span onClick="doSort(this,1,'n',1)">
<img src="../pat/images/sortState_descending.gif"/ alt="sort descending">
</span>

then we added another html item under this simple list whose html contains javascript:

<script language="Javascript" type="text/javascript">

var currentCol;
var dataArray;

function sortAlphaAsc(a,b){
   if(a[currentCol] < b[currentCol]) { return -1; }
   if(a[currentCol] > b[currentCol]) { return 1; }
   return 0;   
}
function sortAlphaDesc(a,b){
   if(a[currentCol] > b[currentCol]) { return -1; }
   if(a[currentCol] < b[currentCol]) { return 1; }
   return 0;   
}
function sortNumericAsc(a,b){
   numA = a[currentCol];
   numB = b[currentCol];
   if (isNaN(numA)) { return 0;}
   else {
      if (isNaN(numB)) { return 0; }
      else { return numA - numB; }
   }
}
function sortNumericDesc(a,b){
   numA = a[currentCol];
   numB = b[currentCol];
   if (isNaN(numA)) { return 0;}
   else {
      if (isNaN(numB)) { return 0; }
      else { return numB - numA; }
   }
}

// sortType = { a = alpha; n = numeric; fn = formatted numeric }
// sortOrder = { 0=asc, 1=desc }
function doSort(el, colIndex, sortType, sortOrder){
   
   var tb = el.parentElement.parentElement.parentElement;
   var rowsLength = tb.rows.length;
   var colsLength = tb.rows[0].cells.length;
   
   currentCol = colIndex;
   
   dataArray = new Array(rowsLength);
   
   for(i=1;i<rowsLength;i++){
      dataArray=new Array(colsLength);
      for(j=0;j<colsLength;j++){
         dataArray[j] = tb.rows.cells[j].innerText;
         //alert(dataArray[j]);
      }
   }
   if(sortType=='a'){
      if(sortOrder==0)
         result=dataArray.sort(sortAlphaAsc);
      else
         result=dataArray.sort(sortAlphaDesc);
   }
   if(sortType=='n'){
      if(sortOrder==0)
         result=dataArray.sort(sortNumericAsc);
      else
         result=dataArray.sort(sortNumericDesc);
   }
      
   for(i=1;i<rowsLength;i++){
      for(j=0;j<colsLength;j++){
         tb.rows.cells[j].innerText=result[i-1][j];
         }
   }
}
</script>

--------------------------------------------------------------------

This is all we made...
When we run report, clicking on ascending/descending image next to second column header results in an error on the page (it doesn't sort). Are we missing something? Maybe those two html items should be somehow linked so that the first knows where to search for doSort function...

We got that javascript and steps for creating that report from a sample that was i think created with Cognos 8.2. We have Cognos 8.3 so that might be a problem as well as i read there are differences when using JavaScripts in 8.3...

So basically, how to to this in Cognos 8.3?
thanx

[Sunchaser]

Hello,

I don't know if it could help you (it is talking about Cognos8 javascript technique "in general" not only dedicated on 8.3), but I saw (and put in my bookmarks) something about dynamically sorting columns, here is the link -> http://wiki.ittoolbox.com/index.php/Dynamically%5Fsort%5Fcolumns%2C%5Fjavascript%2C%5FCognos8%3A%5Fexample%5F1

++
;)

[surgents]

the code has mistakes, because i can't open a report from clipboard when i select it...

and it looks like a more complicated solution to our problem as it uses prompts.

still thanx

[surgents]

i have modified the XML of that wiki example report and tried to remake the report in Cognos 8.3 environment on GO Sales (analysis) sample package, but the report fails to run after i specify prompt values for Asc_Desc and sort_column...

has anyone managed to run that wiki example?

i would really appreciate any help...
maybe there is some other way how to do this - the problem is basically that we want the user of a report to have an option of selecting a column by which the report will be sorted...

thanx

[Sunchaser]

Hello,

Can't you put any "prompt object" in some area of the main page of your report ? (may be it's forbidden by some requirements of your report (?)).
Else, you can use a new DataItem in the Query that gives you the list to sort, in this DataItem's expression you can put something like:

Code Select Expand


case ?Param_choice?
when 1 then [COLUMN_NAME1]
when 2 then [COLUMN_NAME2]
end

where ?Param_choice? is the parameter of a prompt, that could be defined by default.
If you:
1.) put a value prompt using this parameter (Displaying your Static-choices for sorting option, and having property like "Auto-Submit = Yes") in your report (in tested in the Header area of the page)
2.) insert a column in the List, representing the new DataItem, define the sort type (asc/desc), and give it the property "Box-type = none"
then you should have what you expected ...

[Sunchaser]

+
another example of sorting, without using javascript but it seems to allow users to click on Column-headers to choose the sorting:
http://support.cognos.com/supported/supportlink/techniques/ta_0706_03.html

Hope it could help you...

++
;)

[surgents]

Hi again,

i attached a report specification of the simplest of reports based on GO Sales (query) sample package in Cognos 8.3.

I made this because this is exactly what we need and i simply can't make it work... Could anyone take a look on that report and give us a hint why it is not working in 8.3?

I managed to do that custom sorting using prompts but it isn't really the best solution for us...

Cognos doesn't provide support for reports using javascripts, that's why i'm asking for help here again.

Thank You

[rockytopmark]

Cognos 8.3 has a new (undesired) feature, in that prompt objects get "magically" renamed after a parameter change and a re-run of the report.  Word has it they will be "fixing" this as many Javascripts we write depend on the prompt's object name.

[gilwalker]

hi there

you can use the following javascript code to sort the columns in a list report in 8.3, just inserted a HTML item on the beginning of the page and paste the code.

Note that only the rows currently being displayed will be sorted. If you have a multiple pages report, you will need to use a solution that resubmits the request to the server.

Cheers

Gilberto

Code Select Expand

<script>

//  SortTable  version 2
//  7th April 2007
//  Stuart Langridge, http://www.kryogenix.org/code/browser/sorttable/
// 
//  Thanks to many, many people for contributions and suggestions.
//  Licenced as X11: http://www.kryogenix.org/code/browser/licence.html
//  This basically means: do what you want with it.
//
// Changed by Gilberto Walker to be used in Cognos 8.3

var stIsIE = /*@cc_on!@*/false;

sorttable = {
  init: function() {
    // quit if this function has already been called
    if (arguments.callee.done) return;
    // flag this function so we don't do the same thing twice
    arguments.callee.done = true;
    // kill the timer
    if (_timer) clearInterval(_timer);
   
    if (!document.createElement || !document.getElementsByTagName) return;
   
    sorttable.DATE_RE = /^(\d\d?)[\/\.-](\d\d?)[\/\.-]((\d\d)?\d\d)$/;
   
    forEach(document.getElementsByTagName('table'), function(table) {
      if (table.className.search(/\bls\b/) != -1) {
        sorttable.makeSortable(table);
      }
    });
   
  },
 
  makeSortable: function(table) {
    if (table.getElementsByTagName('thead').length == 0) {
      // table doesn't have a tHead. Since it should have, create one and
      // put the first table row in it.
      the = document.createElement('thead');
      the.appendChild(table.rows[0]);
      table.insertBefore(the,table.firstChild);
    }
    // Safari doesn't support table.tHead, sigh
    if (table.tHead == null) table.tHead = table.getElementsByTagName('thead')[0];
   
    if (table.tHead.rows.length != 1) return; // can't cope with two header rows
   
    // Sorttable v1 put rows with a class of "sortbottom" at the bottom (as
    // "total" rows, for example). This is B&R, since what you're supposed
    // to do is put them in a tfoot. So, if there are sortbottom rows,
    // for backwards compatibility, move them to tfoot (creating it if needed).
    sortbottomrows = [];
    for (var i=0; i<table.rows.length; i++) {
      if (table.rows[i].className.search(/\bsortbottom\b/) != -1) {
        sortbottomrows[sortbottomrows.length] = table.rows[i];
      }
    }
    if (sortbottomrows) {
      if (table.tFoot == null) {
        // table doesn't have a tfoot. Create one.
        tfo = document.createElement('tfoot');
        table.appendChild(tfo);
      }
      for (var i=0; i<sortbottomrows.length; i++) {
        tfo.appendChild(sortbottomrows[i]);
      }
      delete sortbottomrows;
    }
   
    // work through each column and calculate its type
    headrow = table.tHead.rows[0].cells;
    for (var i=0; i<headrow.length; i++) {
      // manually override the type with a sorttable_type attribute
      if (!headrow[i].className.match(/\bsorttable_nosort\b/)) { // skip this col
        mtch = headrow[i].className.match(/\bsorttable_([a-z0-9]+)\b/);
        if (mtch) { override = mtch[1]; }
      if (mtch && typeof sorttable["sort_"+override] == 'function') {
        headrow[i].sorttable_sortfunction = sorttable["sort_"+override];
      } else {
        headrow[i].sorttable_sortfunction = sorttable.guessType(table,i);
      }
      // make it clickable to sort
      headrow[i].sorttable_columnindex = i;
      headrow[i].sorttable_tbody = table.tBodies[0];
      dean_addEvent(headrow[i],"click", function(e) {

          if (this.className.search(/\bsorttable_sorted\b/) != -1) {
            // if we're already sorted by this column, just
            // reverse the table, which is quicker
            sorttable.reverse(this.sorttable_tbody);
            this.className = this.className.replace('sorttable_sorted',
                                                    'sorttable_sorted_reverse');
            this.removeChild(document.getElementById('sorttable_sortfwdind'));
            sortrevind = document.createElement('span');
            sortrevind.id = "sorttable_sortrevind";
            sortrevind.innerHTML = stIsIE ? '&nbsp<font face="webdings">5</font>' : '&nbsp;&#x25B4;';
            this.appendChild(sortrevind);
            return;
          }
          if (this.className.search(/\bsorttable_sorted_reverse\b/) != -1) {
            // if we're already sorted by this column in reverse, just
            // re-reverse the table, which is quicker
            sorttable.reverse(this.sorttable_tbody);
            this.className = this.className.replace('sorttable_sorted_reverse',
                                                    'sorttable_sorted');
            this.removeChild(document.getElementById('sorttable_sortrevind'));
            sortfwdind = document.createElement('span');
            sortfwdind.id = "sorttable_sortfwdind";
            sortfwdind.innerHTML = stIsIE ? '&nbsp<font face="webdings">6</font>' : '&nbsp;&#x25BE;';
            this.appendChild(sortfwdind);
            return;
          }
         
          // remove sorttable_sorted classes
          theadrow = this.parentNode;
          forEach(theadrow.childNodes, function(cell) {
            if (cell.nodeType == 1) { // an element
              cell.className = cell.className.replace('sorttable_sorted_reverse','');
              cell.className = cell.className.replace('sorttable_sorted','');
            }
          });
          sortfwdind = document.getElementById('sorttable_sortfwdind');
          if (sortfwdind) { sortfwdind.parentNode.removeChild(sortfwdind); }
          sortrevind = document.getElementById('sorttable_sortrevind');
          if (sortrevind) { sortrevind.parentNode.removeChild(sortrevind); }
         
          this.className += ' sorttable_sorted';
          sortfwdind = document.createElement('span');
          sortfwdind.id = "sorttable_sortfwdind";
          sortfwdind.innerHTML = stIsIE ? '&nbsp<font face="webdings">6</font>' : '&nbsp;&#x25BE;';
          this.appendChild(sortfwdind);

        // build an array to sort. This is a Schwartzian transform thing,
        // i.e., we "decorate" each row with the actual sort key,
        // sort based on the sort keys, and then put the rows back in order
        // which is a lot faster because you only do getInnerText once per row
        row_array = [];
        col = this.sorttable_columnindex;
        rows = this.sorttable_tbody.rows;
        for (var j=0; j<rows.length; j++) {
          row_array[row_array.length] = [sorttable.getInnerText(rows[j].cells[col]), rows[j]];
        }
        /* If you want a stable sort, uncomment the following line */
        //sorttable.shaker_sort(row_array, this.sorttable_sortfunction);
        /* and comment out this one */
        row_array.sort(this.sorttable_sortfunction);
       
        tb = this.sorttable_tbody;
        for (var j=0; j<row_array.length; j++) {
          tb.appendChild(row_array[j][1]);
        }
       
        delete row_array;
      });
    }
    }
  },
 
  guessType: function(table, column) {
    // guess the type of a column based on its first non-blank row
    sortfn = sorttable.sort_alpha;
    for (var i=0; i<table.tBodies[0].rows.length; i++) {
      text = sorttable.getInnerText(table.tBodies[0].rows[i].cells[column]);
      if (text != '') {
        if (text.match(/^-?[£$¤]?[\d,.]+%?$/)) {
          return sorttable.sort_numeric;
        }
        // check for a date: dd/mm/yyyy or dd/mm/yy
        // can have / or . or - as separator
        // can be mm/dd as well
        possdate = text.match(sorttable.DATE_RE)
        if (possdate) {
          // looks like a date
          first = parseInt(possdate[1]);
          second = parseInt(possdate[2]);
          if (first > 12) {
            // definitely dd/mm
            return sorttable.sort_ddmm;
          } else if (second > 12) {
            return sorttable.sort_mmdd;
          } else {
            // looks like a date, but we can't tell which, so assume
            // that it's dd/mm (English imperialism!) and keep looking
            sortfn = sorttable.sort_ddmm;
          }
        }
      }
    }
    return sortfn;
  },
 
  getInnerText: function(node) {
    // gets the text we want to use for sorting for a cell.
    // strips leading and trailing whitespace.
    // this is *not* a generic getInnerText function; it's special to sorttable.
    // for example, you can override the cell text with a customkey attribute.
    // it also gets .value for <input> fields.
   
    hasInputs = (typeof node.getElementsByTagName == 'function') &&
                 node.getElementsByTagName('input').length;
   
    if (node.getAttribute("sorttable_customkey") != null) {
      return node.getAttribute("sorttable_customkey");
    }
    else if (typeof node.textContent != 'undefined' && !hasInputs) {
      return node.textContent.replace(/^\s+|\s+$/g, '');
    }
    else if (typeof node.innerText != 'undefined' && !hasInputs) {
      return node.innerText.replace(/^\s+|\s+$/g, '');
    }
    else if (typeof node.text != 'undefined' && !hasInputs) {
      return node.text.replace(/^\s+|\s+$/g, '');
    }
    else {
      switch (node.nodeType) {
        case 3:
          if (node.nodeName.toLowerCase() == 'input') {
            return node.value.replace(/^\s+|\s+$/g, '');
          }
        case 4:
          return node.nodeValue.replace(/^\s+|\s+$/g, '');
          break;
        case 1:
        case 11:
          var innerText = '';
          for (var i = 0; i < node.childNodes.length; i++) {
            innerText += sorttable.getInnerText(node.childNodes[i]);
          }
          return innerText.replace(/^\s+|\s+$/g, '');
          break;
        default:
          return '';
      }
    }
  },
 
  reverse: function(tbody) {
    // reverse the rows in a tbody
    newrows = [];
    for (var i=0; i<tbody.rows.length; i++) {
      newrows[newrows.length] = tbody.rows[i];
    }
    for (var i=newrows.length-1; i>=0; i--) {
       tbody.appendChild(newrows[i]);
    }
    delete newrows;
  },
 
  /* sort functions
     each sort function takes two parameters, a and b
     you are comparing a[0] and b[0] */
  sort_numeric: function(a,b) {
    aa = parseFloat(a[0].replace(/[^0-9.-]/g,''));
    if (isNaN(aa)) aa = 0;
    bb = parseFloat(b[0].replace(/[^0-9.-]/g,''));
    if (isNaN(bb)) bb = 0;
    return aa-bb;
  },
  sort_alpha: function(a,b) {
    if (a[0]==b[0]) return 0;
    if (a[0]<b[0]) return -1;
    return 1;
  },
  sort_ddmm: function(a,b) {
    mtch = a[0].match(sorttable.DATE_RE);
    y = mtch[3]; m = mtch[2]; d = mtch[1];
    if (m.length == 1) m = '0'+m;
    if (d.length == 1) d = '0'+d;
    dt1 = y+m+d;
    mtch = b[0].match(sorttable.DATE_RE);
    y = mtch[3]; m = mtch[2]; d = mtch[1];
    if (m.length == 1) m = '0'+m;
    if (d.length == 1) d = '0'+d;
    dt2 = y+m+d;
    if (dt1==dt2) return 0;
    if (dt1<dt2) return -1;
    return 1;
  },
  sort_mmdd: function(a,b) {
    mtch = a[0].match(sorttable.DATE_RE);
    y = mtch[3]; d = mtch[2]; m = mtch[1];
    if (m.length == 1) m = '0'+m;
    if (d.length == 1) d = '0'+d;
    dt1 = y+m+d;
    mtch = b[0].match(sorttable.DATE_RE);
    y = mtch[3]; d = mtch[2]; m = mtch[1];
    if (m.length == 1) m = '0'+m;
    if (d.length == 1) d = '0'+d;
    dt2 = y+m+d;
    if (dt1==dt2) return 0;
    if (dt1<dt2) return -1;
    return 1;
  },
 
  shaker_sort: function(list, comp_func) {
    // A stable sort function to allow multi-level sorting of data
    // see: http://en.wikipedia.org/wiki/Cocktail_sort
    // thanks to Joseph Nahmias
    var b = 0;
    var t = list.length - 1;
    var swap = true;

    while(swap) {
        swap = false;
        for(var i = b; i < t; ++i) {
            if ( comp_func(list[i], list[i+1]) > 0 ) {
                var q = list[i]; list[i] = list[i+1]; list[i+1] = q;
                swap = true;
            }
        } // for
        t--;

        if (!swap) break;

        for(var i = t; i > b; --i) {
            if ( comp_func(list[i], list[i-1]) < 0 ) {
                var q = list[i]; list[i] = list[i-1]; list[i-1] = q;
                swap = true;
            }
        } // for
        b++;

    } // while(swap)
  } 
}

/* ******************************************************************
   Supporting functions: bundled here to avoid depending on a library
   ****************************************************************** */

// Dean Edwards/Matthias Miller/John Resig

/* for Mozilla/Opera9 */
if (document.addEventListener) {
    document.addEventListener("DOMContentLoaded", sorttable.init, false);
}

/* for Internet Explorer */
/*@cc_on @*/
/*@if (@_win32)
    document.write("<script id=__ie_onload defer src=javascript:void(0)><\/script>");
    var script = document.getElementById("__ie_onload");
    script.onreadystatechange = function() {
        if (this.readyState == "complete") {
            sorttable.init(); // call the onload handler
        }
    };
/*@end @*/

/* for Safari */
if (/WebKit/i.test(navigator.userAgent)) { // sniff
    var _timer = setInterval(function() {
        if (/loaded|complete/.test(document.readyState)) {
            sorttable.init(); // call the onload handler
        }
    }, 10);
}

/* for other browsers */
window.onload = sorttable.init;

// written by Dean Edwards, 2005
// with input from Tino Zijdel, Matthias Miller, Diego Perini

// http://dean.edwards.name/weblog/2005/10/add-event/

function dean_addEvent(element, type, handler) {
if (element.addEventListener) {
element.addEventListener(type, handler, false);
} else {
// assign each event handler a unique ID
if (!handler.$$guid) handler.$$guid = dean_addEvent.guid++;
// create a hash table of event types for the element
if (!element.events) element.events = {};
// create a hash table of event handlers for each element/event pair
var handlers = element.events[type];
if (!handlers) {
handlers = element.events[type] = {};
// store the existing event handler (if there is one)
if (element["on" + type]) {
handlers[0] = element["on" + type];
}
}
// store the event handler in the hash table
handlers[handler.$$guid] = handler;
// assign a global event handler to do all the work
element["on" + type] = handleEvent;
}
};
// a counter used to create unique IDs
dean_addEvent.guid = 1;

function removeEvent(element, type, handler) {
if (element.removeEventListener) {
element.removeEventListener(type, handler, false);
} else {
// delete the event handler from the hash table
if (element.events && element.events[type]) {
delete element.events[type][handler.$$guid];
}
}
};

function handleEvent(event) {
var returnValue = true;
// grab the event object (IE uses a global event object)
event = event || fixEvent(((this.ownerDocument || this.document || this).parentWindow || window).event);
// get a reference to the hash table of event handlers
var handlers = this.events[event.type];
// execute each event handler
for (var i in handlers) {
this.$$handleEvent = handlers[i];
if (this.$$handleEvent(event) === false) {
returnValue = false;
}
}
return returnValue;
};

function fixEvent(event) {
// add W3C standard event methods
event.preventDefault = fixEvent.preventDefault;
event.stopPropagation = fixEvent.stopPropagation;
return event;
};
fixEvent.preventDefault = function() {
this.returnValue = false;
};
fixEvent.stopPropagation = function() {
  this.cancelBubble = true;
}

// Dean's forEach: http://dean.edwards.name/base/forEach.js
/*
forEach, version 1.0
Copyright 2006, Dean Edwards
License: http://www.opensource.org/licenses/mit-license.php
*/

// array-like enumeration
if (!Array.forEach) { // mozilla already supports this
Array.forEach = function(array, block, context) {
for (var i = 0; i < array.length; i++) {
block.call(context, array[i], i, array);
}
};
}

// generic enumeration
Function.prototype.forEach = function(object, block, context) {
for (var key in object) {
if (typeof this.prototype[key] == "undefined") {
block.call(context, object[key], key, object);
}
}
};

// character enumeration
String.forEach = function(string, block, context) {
Array.forEach(string.split(""), function(chr, index) {
block.call(context, chr, index, string);
});
};

// globally resolve forEach enumeration
var forEach = function(object, block, context) {
if (object) {
var resolve = Object; // default
if (object instanceof Function) {
// functions have a "length" property
resolve = Function;
} else if (object.forEach instanceof Function) {
// the object implements a custom forEach method so use that
object.forEach(block, context);
return;
} else if (typeof object == "string") {
// the object is a string
resolve = String;
} else if (typeof object.length == "number") {
// the object is array-like
resolve = Array;
}
resolve.forEach(object, block, context);
}
};

</script>

[surgents]

Hi,
i just want to say that using the technique provided in http://support.cognos.com/supported/supportlink/techniques/ta_0706_03.html that was posted by Sunchaser in this topic helped to solve us the problem.

But there is one thing we had to change - we had to use case when instead of if then else statement in Sort_item_asc (desc) query calculations.

Ciao

[khanna100]

Gilberto

Have you ever used your dynamic sorting technique on a crosstab report.  I am trying to replicate the dynamic sorting functionality on a crosstab report and would appreciate any tips you could offer.

Many thanks

Khalid

[Habitat]

Hi surgents,
you said you had unlocked the report.
can you say me how?
still thanks

[MFGF]

Hi surgents,
you said you had unlocked the report.
can you say me how?
still thanks

Hi,

There is a button on the toolbar which looks like a locked padlock.  Press this once, and it unlocks the structure of the report (and the button icon changes to represent an open padlock).  Press a second time and the report structure locks again.

MF.

[praveenb77]

Hi gilwalker,

There is one more issue with your modified code. The arrow appear opposite for Ascending and Descending orders. Is there any fix for that ?

Thanks!