If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Sorting columns with JavaScript

Started by gggrrr10, 22 Oct 2007 07:19:49 PM

Previous topic - Next topic

gggrrr10

Hello all,
I found a nice example of how your end-users can sort columns on their reports.

Enjoy!


COGNOiSe administrator

Thanks for sharing. I hope it is not proprietary ...  8)

maries14

Please, could you explain step by step where I have to insert the HTML tag in the report ?.

I really appreciate your help.

nugget

i need an step by step tutorial too. (no xml)

can somebody tell me where i can find a tutorial?

greetz

Rajaggopal

I will try to enumerate the steps from what i observed from the xml... im not in a position to verify this immediately but if i can i will confirm the same later on...

1. Drag HTML items and place them adjacent to the column titles.
2. In each of these HTML items type in the following.
Quote
<span onClick="doSort(this,0,'a',0)">
     <img src="../pat/images/sortAscending_neu.gif"/ alt="Ascending">
</span>
<span onClick="doSort(this,0,'a',1)">
     <img src="../pat/images/sortDescending_neu.gif"/ alt="Descending">
</span>
NOTE: parameters for doSort varies depending on the column. the 2nd parameter should indicate the column index (0 for first column, 1 for second column, etc...) and 3rd parameter specifies the data type of the column ('a' for alphabet, 'n' for numeric, 'fn' for formatted numeric - numbers with dollar signs). The fourth parameter indicates the sort order 0 - ascending 1 - descending.
     
3. Place another HTML item (i think anywhere in the list/page) and type the following inside it.
Quote
<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; }
   }
}
function sortFormattedNumericAsc(a,b){
   
   //var searchCurrencySign = "\$";
   //var searchThousandSeperator = "\,";
   
   //var re1 = new RegExp(searchCurrencySign,"g");
   //var re2 = new RegExp(searchThousandSeperator,"g");
   
   //var a_raw = a[currentCol].replace(re1,"");
   //a_raw = a_raw.replace(re2,"");
   
   //var b_raw = b[currentCol].replace(re1,"");
   //b_raw = b_raw.replace(re2,"");
   
   
   var a1 = a[currentCol].substring(2,a[currentCol].length);
   var a_array = a1.split(",");
   var a_raw="";
   for(i=0;i<a_array.length;i++) a_raw += a_array;
   a_raw=a_raw*100;
   
   var b1 = b[currentCol].substring(2,b[currentCol].length);
   var b_array = b1.split(",");
   var b_raw="";
   for(i=0;i<b_array.length;i++) b_raw += b_array;
   b_raw=b_raw*100;

   
   if (isNaN(a_raw)) { return 0;}
   else {
      if (isNaN(b_raw)) { return 0; }
      else { return a_raw - b_raw; }
   }
}

function sortFormattedNumericDesc(a,b){
   
   //var searchCurrencySign = "\$";
   //var searchThousandSeperator = "\,";
   
   //var re1 = new RegExp(searchCurrencySign,"g");
   //var re2 = new RegExp(searchThousandSeperator,"g");
   
   //var a_raw = a[currentCol].replace(re1,"");
   //a_raw = a_raw.replace(re2,"");
   
   //var b_raw = b[currentCol].replace(re1,"");
   //b_raw = b_raw.replace(re2,"");
   
   
   var a1 = a[currentCol].substring(2,a[currentCol].length);
   var a_array = a1.split(",");
   var a_raw="";
   for(i=0;i<a_array.length;i++) a_raw += a_array;
   a_raw=a_raw*100;
   
   var b1 = b[currentCol].substring(2,b[currentCol].length);
   var b_array = b1.split(",");
   var b_raw="";
   for(i=0;i<b_array.length;i++) b_raw += b_array;
   b_raw=b_raw*100;

   
   if (isNaN(a_raw)) { return 0;}
   else {
      if (isNaN(b_raw)) { return 0; }
      else { return b_raw - a_raw; }
   }
}

// 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);
   }
   if(sortType=='fn'){
      if(sortOrder==0)
         result=dataArray.sort(sortFormattedNumericAsc);
      else
         result=dataArray.sort(sortFormattedNumericDesc);
   }
   
   
   for(i=1;i<rowsLength;i++){
      for(j=0;j<colsLength;j++){
         tb.rows.cells[j].innerText=result[i-1][j];
         }
   }
}
</script>

nugget

@Rajaggopal

i tried your tutorial but it didnt work for me :-(
have you tested it?

did you know where i can find a working tutorial. i have no login to the cognos knowledgebase but there should be a tutorial. have you one?

if so can u create an pdf file of the cognos tut and attach it to this thread?

greets

Rajaggopal

like i said i dint have cognos access at the time i posted it... will try to verify it sooner or later and update the same. also this is not what i have suggested will work.. i just read the xml attched in the first post and tried to figure out what they have done in the report. :)

nugget

i found a very easy method to sort the columns. its with JavaScript too and very easy to implement. the document is published by cognos.
i attached it on this post.

but be careful:
There may be a risk associated with this technique in that support for these capabilities may change or be dropped entirely in some future release.

i tested it and it works fine. but i have the problem that when i create a sum of a list and sort it the sum will be sort too. did anybody know how i can set the sum row fix in connection with this method.