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.
- 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
- 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.
- 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.
- 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:
- Once you have placed all the scripts, your report layout should look like this:
<span id="list1"></span>
<a href="JavaScript:sortList(1)">
</a>
<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>
- 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
Sriram,
ReplyDeleteThis 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
DeleteHà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
Kavitha,
ReplyDeleteThis 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.
Hi Sriram,
DeleteLook 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.
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?
ReplyDeleteSue,
DeleteI had mentioned it under the notes section. All the rows must be on the same page for this to work.
Sriram.
Sorry - didn't read the notes :)
ReplyDeleteThanks
Hey Sriram:
ReplyDeleteTried it--didn't seem to work. There was no error seen though. Will try to dig in more to find any issues.
-Kavitha
Hi Sriram,
ReplyDeleteI 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
Hi Sriram,
ReplyDeleteI 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
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.
DeleteHi Sriram,
ReplyDeleteGreat 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
Hii Sriram,
ReplyDeleteI 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
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.
ReplyDeleteHi 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?
ReplyDeleteI tried its not worked with more than 2 columns..
ReplyDeleteHi guys,
ReplyDeleteI have tried it and it works, perfect.
My question is, is it hard to modify to make it work also on CROSSTAB?
Regards,
Michal
i tried and tt works .
ReplyDeleteBut 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
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?
DeleteI 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.
DeleteGreat script, works fast and simple to setup. Hats off to Sriram.
DeleteThe 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%)
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.
DeleteThe 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;
}
Can you let me know where do i need to add this code? i had tried different places and can't make it work.
Deletethank you.
Can you show me how the function for column_format looks like?
DeleteHi Sriram,
DeletePlease publish the latest script along with resolved sorting issues on numeric columns.
Thanks,
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
DeleteAntonkov, where exactly do you add the new js list to deal with the numerical sorting. Thank you.
ReplyDeleteHello Antonkov,
ReplyDeleteCan i know where to replace the above numeric code?
Thanks!
Hello Sriram,
ReplyDeleteCan i have the updated code for including sort on the numeric columns ?
Thanks for Helping!
Hi Sriram,
ReplyDeleteThanks 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.
Hi Sriram,
ReplyDeleteHas 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.
Any way to sort a date column?
ReplyDelete