JavaScript for Dynamic Sorting

[jeff_rob]

Hello,
I'm working to modify a script that I found that will allow one-click column sorting on HTML output.  I know very little about JavaScript, but with the help of a good search engine, I've been able to modify it so that it does a numeric sort as well as a lexical sort without sorting the column total footer.

However, to get it to do a numeric sort I had to strip out any commas and percent signs.  I'd like to be able to add those back in, but have been unsuccessful in my attempts.

I found a couple of code snippets that can add commas, but can't see how to implement either.  Both are in the bottom of the script as unreferenced functions.  Does anyone know how to accomplish adding the commas and percent signs back?

Here is the site where I found the original code:

http://www.cognosonsteroids.com/2012/04/sort-list-report-dynamically-using.html

And here is the modified version:

Code Select Expand

<script type="text/javascript">
/*******************   Notes   ****************************************************************************
- Works only with lists in HTML output.  Does not work for crosstabs.
- The Rows per Page property for the list must be set to a high enough number so that all the output rows are on one page.
**********************************************************************************************************/

/*******************   Customize this section according to the report   ***********************************/
var column_count = 10; //Number of columns in the report
var current_sort = new Array("","","","","","","","","",""); //This array should contain one empty value per column.
var column_names = ["Line","Class","Desc","Written","PctTotWr","PctTotErnd","PriorWr","PctTotPrWr","PctTotPrErnd","LossRatio"];  // List the names of columns which should be sortable
var column_type = new Array("string","string","string","number","number","number","number","number","number","number");  // Put either "sting", "number", or "integer" to represent the data type of each column
/**********************************************************************************************************/

var list_cell = document.getElementById("list1").parentNode; // Lookup the placeholder span item
var list_row = list_cell.parentNode; // Create Handle for the header row which contains the span item
var list_table = list_row.parentNode.parentNode; // Create Handle for the table

var rows = list_table.getElementsByTagName("tr");  //Get List of rows in the table
var row_count = rows.length; // Get row count

var list_array = new Array(); // Create parent array. Each element of this array will represent a row in the list report.

for (i=1; i<row_count-1;i++) //Loop through the rows. Skipping header row and starting from 1 and leaving out the footer total (row_count-1)
{
    columns = rows[i].getElementsByTagName("td");
    var list_row_array = new Array();  // Create child array. Each element of this array represents a column on the row.
    for(j=0;j<column_count;j++) // Loop through the columns for each row
    {
        // Read value from the cell and store it in array
        list_row_array[column_names[j]] = columns[j].getElementsByTagName("span")[0].innerHTML;
    }
     list_array[i-1] = list_row_array;
}


// Main Function used for sorting.
function sortList(col_num)
{
if(column_type[col_num-1] == "number" || column_type[col_num-1] == "integer"){
//alert("Test - Alert Column Type "+column_type[col_num-1]);

// Number sorts, ascending or descending
if(current_sort[col_num-1] == '' || current_sort[col_num-1] == 'desc'){
   list_array.sort(dynamicSortAscNum(column_names[col_num-1]));
   current_sort[col_num-1] = 'asc';
}
else{
list_array.sort(dynamicSortDescNum(column_names[col_num-1]));
current_sort[col_num-1] = 'desc';
}
}
else{
// String sorts, ascending or descending
//alert("Test - Alert Column Type "+column_type[col_num-1]);
if(current_sort[col_num-1] == '' || current_sort[col_num-1] == 'desc'){
   list_array.sort(dynamicSortAscStr(column_names[col_num-1]));
   current_sort[col_num-1] = 'asc';
}
else{
list_array.sort(dynamicSortDescStr(column_names[col_num-1]));
current_sort[col_num-1] = 'desc';
}
}

for(i=0; i<list_array.length;i++)
{
columns = rows[i+1].getElementsByTagName("td");
for(j=0; j<column_count; j++)
{
columns[j].getElementsByTagName("span")[0].innerHTML = list_array[i][column_names[j]];
}
}
}



// Sub functions used for sorting and adding commas back to the display.
function dynamicSortAscNum(colIndex) {
// Sort numeric ascending
        //alert("Asc Num "+colIndex);

        return function (a,b) {
        a[colIndex] = a[colIndex].replace(/,/g, "").replace(/%/g, "");  // Replaces comma or percent with blank
        b[colIndex] = b[colIndex].replace(/,/g, "").replace(/%/g, "");
        return (a[colIndex] - b[colIndex]);


        }
}

function dynamicSortAscStr(colIndex) {
// Sort string ascending
        //alert("Test Alert Asc Str");

        return function (a,b) {

        return (a[colIndex] < b[colIndex]) ? -1 : (a[colIndex] > b[colIndex]) ? 1 : 0;
   
}

}

function dynamicSortDescNum(colIndex) {
// Sort numeric descending
        //alert("Test Alert Desc Num");

        return function (a,b) {
        a[colIndex] = a[colIndex].replace(/,/g, "").replace(/%/g, "");  // Replaces comma or percent with blank
        b[colIndex] = b[colIndex].replace(/,/g, "").replace(/%/g, "");
        return (b[colIndex] - a[colIndex]);

    }
}

function dynamicSortDescStr(colIndex) {
// Sort string descending
        //alert("Test Alert Desc Str");
        return function (a,b) {

        return (a[colIndex] > b[colIndex]) ? -1 : (a[colIndex] < b[colIndex]) ? 1 : 0;

    }
}

function addCommas(nStr) {
// Add commas back
nStr += '';
x = nStr.split('.');
x1 = x[0];
x2 = x.length > 1 ? '.' + x[1] : '';
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
x1 = x1.replace(rgx, '$1' + ',' + '$2');
}
return x1 + x2;
}

function addCommasToNumber(input){

var numberWithOptionalDecimal = /^(d+)(.?)(d+)$/g;

return (input.toString()).replace(numberWithOptionalDecimal, function(match, before, decimal, after) {
var insertCommas = function(string) {
                                      return string.replace(/(d)(?=(d{3})+$)/g, "$1,");
  };
if (before == '0′) { return match; }
return decimal ? insertCommas(before) + decimal + after : insertCommas(before + after);
});
}
</script>

At any rate, maybe the current code will be helpful to some of you if you don't mind the disappearing commas and percent signs.  If you decide to use the script, you'll need to go to the original's site to get instructions on how to employ the tags that are needed in the report list headers.

Thanks in advance for any help.

Jeff

[praveenb77]

I tried to use the above script, but it does not seem to be working. Could you please help me with this code ?

Thanks!

[DNA]

I'm new to Cognos, so I will appologize in advance if this question is easier than most.

I'm trying to apply your adjusted code to my report. Using Cognos 10.2.1, SQL backend, List using the HTML items on headers.

On the original post (before your modifications): I'm able to click and it will dynamically sort, however because all the metrics are either a number or a percent, it won't sort properly (Example: It will sort by 1, 10, 11, 2, 21, 24, 3, 39 and percentages are the same it will sort 0%, 22%, 54%,....100% and then the other way from 99%, 75% etc. basically need to get rid of the leading Zeros)

When I saw this post, it's exactly what I need!!!

I've changed everything - list name = "list1" I have 14 columns and changed whether the data item type was number or string. I have percentages in there would that be considered a number or string... to get these columns I had to take a data item and FLAG differences (Case When Then End) and COUNT([DataItem] for [Col])
So to me, they would all be strings.

First Column is characterLength16, display type: value. All other columns I have created using a data item to get the variance difference between dates and the percentages.

None of the columns will sort  :-\ would you mind walking me through this?

Code Select Expand

<script type="text/javascript">
/*******************   Notes   ****************************************************************************
- Works only with lists in HTML output.  Does not work for crosstabs.
- The Rows per Page property for the list must be set to a high enough number so that all the output rows are on one page.
**********************************************************************************************************/

/*******************   Customize this section according to the report   ***********************************/
var column_count = 14; //Number of columns in the report
var current_sort = new Array("","","","","","","","","","","","","",""); //This array should contain one empty value per column.
var column_names = ["1","2","3","4","5","6","7","8","9","10","11","12","13","14"];  // List the names of columns which should be sortable
var column_type = new Array("string","integer","integer","string","integer","string","integer","string","integer","string","integer","string","integer","string");  // Put either "string", "number", or "integer" to represent the data type of each column
/**********************************************************************************************************/

var list_cell = document.getElementById("list1").parentNode; // Lookup the placeholder span item
var list_row = list_cell.parentNode; // Create Handle for the header row which contains the span item
var list_table = list_row.parentNode.parentNode; // Create Handle for the table

var rows = list_table.getElementsByTagName("tr");  //Get List of rows in the table
var row_count = rows.length; // Get row count

var list_array = new Array(); // Create parent array. Each element of this array will represent a row in the list report.

for (i=1; i<row_count-1;i++) //Loop through the rows. Skipping header row and starting from 1 and leaving out the footer total (row_count-1)
{
    columns = rows[i].getElementsByTagName("td");
    var list_row_array = new Array();  // Create child array. Each element of this array represents a column on the row.
    for(j=0;j<column_count;j++) // Loop through the columns for each row
    {
        // Read value from the cell and store it in array
        list_row_array[column_names[j]] = columns[j].getElementsByTagName("span")[0].innerHTML;
    }
     list_array[i-1] = list_row_array;
}


// Main Function used for sorting.
function sortList(col_num)
{
if(column_type[col_num-1] == "number" || column_type[col_num-1] == "integer"){
//alert("Test - Alert Column Type "+column_type[col_num-1]);

// Number sorts, ascending or descending
if(current_sort[col_num-1] == '' || current_sort[col_num-1] == 'desc'){
   list_array.sort(dynamicSortAscNum(column_names[col_num-1]));
   current_sort[col_num-1] = 'asc';
}
else{
list_array.sort(dynamicSortDescNum(column_names[col_num-1]));
current_sort[col_num-1] = 'desc';
}
}
else{
// String sorts, ascending or descending
//alert("Test - Alert Column Type "+column_type[col_num-1]);
if(current_sort[col_num-1] == '' || current_sort[col_num-1] == 'desc'){
   list_array.sort(dynamicSortAscStr(column_names[col_num-1]));
   current_sort[col_num-1] = 'asc';
}
else{
list_array.sort(dynamicSortDescStr(column_names[col_num-1]));
current_sort[col_num-1] = 'desc';
}
}

for(i=0; i<list_array.length;i++)
{
columns = rows[i+1].getElementsByTagName("td");
for(j=0; j<column_count; j++)
{
columns[j].getElementsByTagName("span")[0].innerHTML = list_array[i][column_names[j]];
}
}
}



// Sub functions used for sorting and adding commas back to the display.
function dynamicSortAscNum(colIndex) {
// Sort numeric ascending
        //alert("Asc Num "+colIndex);

        return function (a,b) {
        a[colIndex] = a[colIndex].replace(/,/g, "").replace(/%/g, "");  // Replaces comma or percent with blank
        b[colIndex] = b[colIndex].replace(/,/g, "").replace(/%/g, "");
        return (a[colIndex] - b[colIndex]);


        }
}

function dynamicSortAscStr(colIndex) {
// Sort string ascending
        //alert("Test Alert Asc Str");

        return function (a,b) {

        return (a[colIndex] < b[colIndex]) ? -1 : (a[colIndex] > b[colIndex]) ? 1 : 0;
   
}

}

function dynamicSortDescNum(colIndex) {
// Sort numeric descending
        //alert("Test Alert Desc Num");

        return function (a,b) {
        a[colIndex] = a[colIndex].replace(/,/g, "").replace(/%/g, "");  // Replaces comma or percent with blank
        b[colIndex] = b[colIndex].replace(/,/g, "").replace(/%/g, "");
        return (b[colIndex] - a[colIndex]);

    }
}

function dynamicSortDescStr(colIndex) {
// Sort string descending
        //alert("Test Alert Desc Str");
        return function (a,b) {

        return (a[colIndex] > b[colIndex]) ? -1 : (a[colIndex] < b[colIndex]) ? 1 : 0;

    }
}

function addCommas(nStr) {
// Add commas back
nStr += '';
x = nStr.split('.');
x1 = x[0];
x2 = x.length > 1 ? '.' + x[1] : '';
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
x1 = x1.replace(rgx, '$1' + ',' + '$2');
}
return x1 + x2;
}

function addCommasToNumber(input){

var numberWithOptionalDecimal = /^(d+)(.?)(d+)$/g;

return (input.toString()).replace(numberWithOptionalDecimal, function(match, before, decimal, after) {
var insertCommas = function(string) {
                                      return string.replace(/(d)(?=(d{3})+$)/g, "$1,");
  };
if (before == '0′) { return match; }
return decimal ? insertCommas(before) + decimal + after : insertCommas(before + after);
});
}
</script>