Thursday, April 26, 2012

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:

31 comments:

  1. Sriram,

    This is interesting. But again, how much of script maintenance is required as Cognos versions are upgraded. And would it be possible to include an image next to each column header title and click to sort ASC or DESC.

    -Kavitha

    ReplyDelete
    Replies


    1. Hàn Giang Đào nói với Nhạc Thành, sau đó hướng về phía rừng cây xa xa mà đi tới.

      Nhạc Thành biết rằng Hàn Giang Đào có chuyện quan trọng cần nói với mình.

      - Viện trưởng, người gọi ta ra đây có chuyện gì vậy?
      đồng tâm
      game mu
      cho thuê phòng trọ
      cho thuê phòng trọ
      nhac san cuc manh
      tư vấn pháp luật qua điện thoại
      văn phòng luật
      số điện thoại tư vấn luật
      dịch vụ thành lập doanh nghiệp
      http://we-cooking.com/
      chém gió
      Nhạc Thành ở sau lưng Hàn Giang Đào hỏi.

      - Không sai.

      Hàn Giang Đào xoay người nhìn Nhạc Thành mà nói:

      - Ngươi biết thân phận của Đông Phương Lạc Nhan rồi đúng không.

      - Vâng.

      Nhạc Thành gật nhẹ đầu, Hàn Nguyệt mấy ngày trước cũng đã nói với hắn Đông Phương Lạc Nhan là người của Thần Hoàng tộc.

      Đông Phương Lạc Nhan là người của Thần hoàng tộc, hơn nữa địa vị cũng rất vi

      Delete
  2. Kavitha,

    This script doesn't use any cognos built in object names. So, it should work in any version of cognos. I didn't see the need to complicate it further by putting images there. But, it can be done.

    Sriram.

    ReplyDelete
    Replies
    1. Hi Sriram,
      Look like this a old blog but still hoping to get some reply on this.
      The above script is excellent option since its simpler than the IBM and the Js-sortable one.
      But still I have two problems using your script
      1. It doesn’t work on list section, it only works on very 1st list section.
      2. Sorting doesn’t happen properly on drill through (Hyperlink) columns, functionality works fine but sort order is not right.
      Through me some idea areas that I should touch to make your script works for above two cases also.
      If you have any modified one something like the above then it will save lot of time since I have no knowledge in Java scripting


      Thanks in advance.

      Delete
  3. Presumably this will only work with the rows currently displayed on the page? What if you have your list set to X rows and the actual query has > X rows of data?

    ReplyDelete
    Replies
    1. Sue,

      I had mentioned it under the notes section. All the rows must be on the same page for this to work.

      Sriram.

      Delete
  4. Sorry - didn't read the notes :)
    Thanks

    ReplyDelete
  5. Hey Sriram:

    Tried it--didn't seem to work. There was no error seen though. Will try to dig in more to find any issues.

    -Kavitha

    ReplyDelete
  6. Hi Sriram,

    I tried ur solution but unable to make it work. I am getting a JS error:
    Webpage error details

    Message: Object expected
    Line: 1
    Char: 1
    Code: 0
    URI: http:///ibmcognos/cgi-bin/cognosisapi.dll

    ReplyDelete
  7. Hi Sriram,

    I tried ur solution but unable to make it work. I am getting a JS error:
    Webpage error details

    Message: Object expected
    Line: 1
    Char: 1
    Code: 0
    URI: http:///ibmcognos/cgi-bin/cognosisapi.dll

    ReplyDelete
    Replies
    1. I had the same error in a report with 10 columns. After naming all 10 column names in var column_name instead of only the clickable ones, it works perfectly.

      Delete
  8. Hi Sriram,

    Great script man...had tried to use jquery, well thats too much of work and importing the .js from jquery. This is lot more simpler

    I have totals as the last row or row number 1. In this case how to over this or make the script to ignore the total row at the top or the last row.

    Thanks in advance!
    Kannan

    ReplyDelete
  9. Hii Sriram,

    I have a question. ihave list with plan_type, Plan_info columns. List is sectioned on the basis of plan_type.The plan_type column is having 3 plans(ABC, DEF, XYZ). I required a buttons named as "Show Plan Basics" and "Hide Plan Basics" for each plan in the list.

    When user clicks on ABC Plan Button, it will show only respective plan details and hide when click on respective plan button. lly for other plans also. I got only little bit. when click on any plan button it goes to first plan details.

    To do this , I applied "div tag" to list table. And I took two buttons one for show plans basics and one for hide plans basics inside list. Based on div id i am showing and hiding list. But it works only first plan.

    Could please help me ....

    Regards,
    Ram


    ReplyDelete
  10. Thanks for your work on this. I wasn't quite able to make it work, but this code seemed to work. It's more setup, but I didn't get any errors.

    ReplyDelete
  11. Hi there, this was a great article! I noted that while it worked for text values, sorting didn't invoke correctly for aggregated values. Is there a different set of code that I should be using for measures?

    ReplyDelete
  12. I tried its not worked with more than 2 columns..

    ReplyDelete
  13. Hi guys,
    I have tried it and it works, perfect.
    My question is, is it hard to modify to make it work also on CROSSTAB?

    Regards,
    Michal

    ReplyDelete
  14. i tried and tt works .
    But for number fields the sorting seems to be different.
    if the columns has values as 10,100,9,8000 then sorted values are 10,100,8000,9

    its not sorting as per number smallest to largest.please clarify

    ReplyDelete
    Replies
    1. This script works great for text values! As previous readers mentioned, sorting on numbers doesn't seem to quite work. Is there a workaround in the script?

      Delete
    2. I did find a solution for sorting numeric values. I have to test it. Will do it in a day or two and update the article.

      Delete
    3. Great script, works fast and simple to setup. Hats off to Sriram.
      The numeric sorting through still an issue and those fact columns are most likely to be used for sorting. To make thing more complicated, in case of a currency data format, the figures in a column, technically speaking, are not even numeric if looking from the JS perceptive.
      Would be really cool if this could properly sort numbers and even cooler if it could recognize the formatted numbers (e.g. $123 or 2.1%)

      Delete
    4. In case someone needs to deal with numerical sorting, I slightly enhanced the code to differentiate between alpha and numerical sorts. In the header section, I added another JS list that has formats of each column so when it comes to "return function (a,b)" it knows how to handle the values.
      The regular repressions are used to deal with (negative) currency (e.g. ($1,234.00) ) and percents (e.g. 123.00%)

      if( column_formats[colIndex-1]=='txt' )
      {
      return (a[colIndex] < b[colIndex]) ? -1 : (a[colIndex] > b[colIndex]) ? 1 : 0;
      }
      else if ( column_formats[colIndex-1] =='currency' || column_formats[colIndex-1] == 'percent' || column_formats[colIndex-1] == 'number' )
      {
      return ( Number( a[colIndex].replace(/[(]/g,'-').replace(/[^0-9-./-]/g, '') ) < Number(b[colIndex].replace(/[(]/g,'-').replace(/[^0-9-./-]/g, '')) ) ? -1 : ( Number(a[colIndex].replace(/[(]/g,'-').replace(/[^0-9-./-]/g, '')) > Number(b[colIndex].replace(/[(]/g,'-').replace(/[^0-9-./-]/g, '')) ) ? 1 : 0;
      }

      Delete
    5. Can you let me know where do i need to add this code? i had tried different places and can't make it work.

      thank you.

      Delete
    6. Can you show me how the function for column_format looks like?

      Delete
    7. Hi Sriram,

      Please publish the latest script along with resolved sorting issues on numeric columns.

      Thanks,

      Delete
    8. Hi the script is exact what i am looking for, But i am running into the numeric sorting too. could someone place the compleet script incl the numeric sorting on this site. thanks

      Delete
  15. Antonkov, where exactly do you add the new js list to deal with the numerical sorting. Thank you.

    ReplyDelete
  16. Hello Antonkov,

    Can i know where to replace the above numeric code?

    Thanks!

    ReplyDelete
  17. Hello Sriram,

    Can i have the updated code for including sort on the numeric columns ?

    Thanks for Helping!

    ReplyDelete
  18. Hi Sriram,

    Thanks for the how-to it works great. Is there a way to get the column names to appear in Excel when we export? At the moment they appear with the values set in Script 2

    Cheers.

    ReplyDelete
  19. Hi Sriram,

    Has anyone tried to adjust the code for more than two columns. The code works for me if I have two columns but I am not sure what I am doing wrong for three columns report.

    Thanks for your help.

    ReplyDelete