Sort List Report Dynamically using Javascript

Keywords: Dynamic Sorting OR List Report OR Javascript

Business Case:

User wants to sort the list report output based on list columns dynamically, after the report output has been rendered in HTML format.

IBM Solution:
IBM has an article for doing the same thing. I found that this article was extremely complicated with a lot of steps. So, I wrote this script from scratch, which in my opinion is much simpler to implement.  Here's the link to IBM's article in case you are interested.

http://www.ibm.com/developerworks/data/library/cognos/reporting/scripting_techniques/page515.html?ca=drs-

Solution:

• Most of the work here is done by the Javascript.
• The script goes through each row and loads the data into an array.
• When the column titles are clicked, the array is sorted in memory and the script re-writes the data into the list.
• In this example, my report has 2 columns.
• The column names in list report header will be clickable.
• Initially, the data is unsorted. Clicking the column header will sort the list based on that column in ascending order.
• Clicking the column header again will sort in descending order.
• After that the sort order keeps toggling.

Note:

• If sorting is required before running the report, prompts can be used.
• The solution provided here is only for simple sorting. Sorting is by one column at a time only. Excel style advanced sorting cannot be done with this method (E.g. Sort by Column 1 Ascending, Column 2 Descending).
• Only data on the current page will be sorted, so if you have data spanning more than one page, set the "Rows per Page" property of the list report to a high number (e.g. 10000) so that all the data is displayed in one page.

Implementation:

• There are a total of 4 scripts.
• Script 1:
• This is a placeholder to identify the list report. The main script will look for this script, and access the data inside it.
• Unlock the cells and place an HTML item in the first column header.
• Paste the following script inside this HTML item

<span id="list1"></span>

• Scripts 2 & 3 are wrapper scripts for the column headers. This will make the column headers clickable.
• Script 2:
• This is the first part of the wrapper script.
• Place an HTML items before each column header and paste the following code inside it.

<a href="JavaScript:sortList(1)">

• Note: The number inside the "sortList()" function should match the column number. So it should be sortList(2) for the second column, sortList(3) for the third column and so on.
• Script 3:
• This is the second part of the wrapper script.
• Place an HTML items after each column header and paste the following code inside it.

</a>

• Script 4:
• This is the main script that does all the sorting.
• The first section of the report has 3 variable declarations that you have to edit according to your report.
• "var column_count = 2" indicates the number of columns in your report. If you add more columns, change this value.
• "var current_sort = new Array("","")" is the initial state of the sorting. If you have 3 columns, put three "" separated by commas.
• "var column_names = ["Group","Market"]" is an array with column names. If you note in the screenshots below, the column names are different (Market Group instead of Group). This is for script reference only and doesn't have to match with the list column names.
• The rest of the script should be used as is.
• You can download this script here.
• Place an HTML item after the list report (outside the list) and copy/paste the following code:

<script type="text/javascript">
/*******************   Customize this section according to the report   ***********************************/
var column_count = 2; //Number of columns in the report
var current_sort = new Array("",""); //This array should contain one empty value per column.
var column_names = ["Group","Market"];  // List the names of columns which should be sortable
/**********************************************************************************************************/

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;i++) //Loop through the rows. Skipping header row and starting from 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(current_sort[col_num-1] == '' || current_sort[col_num-1] == 'desc'){
        list_array.sort(dynamicSortAsc(column_names[col_num-1]));
        current_sort[col_num-1] = 'asc';
    }
    else{
        list_array.sort(dynamicSortDesc(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 function used for sorting. Don't change.
function dynamicSortAsc(colIndex) {
    return function (a,b) {
        return (a[colIndex] < b[colIndex]) ? -1 : (a[colIndex] > b[colIndex]) ? 1 : 0;
    }
}

function dynamicSortDesc(colIndex) {
    return function (a,b) {
        return (a[colIndex] > b[colIndex]) ? -1 : (a[colIndex] < b[colIndex]) ? 1 : 0;
    }
}
</script>

• Once you have placed all the scripts, your report layout should look like this:

•  When you run the report the initial output will be like this:

•  Clicking on "Market Group" will sort the list in ascending order by Market Group and the report will look like this:

•  Clicking on "Market Group" again will sort the list in descending order by Market Group and the report will look like this:

•   Clicking on "Market" will sort the list in ascending order by Market and the report will look like this:

• To sort by Market in descending order, click Market again:

• You can download the full report specification here: Dynamic_Sort_Report_Spec.xml