DataTables Row Grouping add-in is released

jocapc

Hello DataTable fellows,

I have created one add-in that enchances DataTables plugin by adding various row grouping features. You can see more details on the http://jquery-datatables-row-grouping.googlecode.com/svn/trunk/index.html page. This add-in enables you to enchance your data tables and easily add row grouping features.
It supports grouping by name, letter, year, double level grouping, expandable/accordion grouping etc.

I hope that it will help you in implementation of enchanced UI with DataTables plugin.

All suggestions for improvements are welcome.

Regards,
Jovan

sd_zuo

That's convenient and useful.

Please add support for grouping by sorting columns. For example the table has column A, B and C. When the user sorts the table with column A, the table is group by A; sorts with B, group by B, etc.

And if possible, please add one more option to extract the "group by" value of a certain columns. For example, the column "Date" as the following format "yyyy-MM-dd", I'd like to group by the column value "yyMM", so I can use regular expression replacement to extract the group value: { pattern: "\d\d(\d\d)-(\d\d)-\d\d", value: "$1$2" } (which means extract the last two character in a year and the month, and concatenate them together for the grouping value). So when we sort and group the "Date" column, the table is group by two-digit-year-and-two-digit-month of the date value. Perhaps a function option such as fnGroupByValue can be introduced to implement such kind of behavior.

For example, the following defines the table should group with the sorting column, and specifies "group by" functions for two columns, "Date" and "Sales".

The "Date" column uses a regular expression to extract year and month from cell values and group by them.
The "Sales" column uses a function to map sales performance from number to literal remarks, "Poor", "So-so" and "Great", so when sorted by the "Sales" column, the table is grouped by three degrees rather than individual sales volume.

[code]
$(document).ready( function () {
$('#example').dataTable().rowGrouping (
{
iGroupingColumnIndex: -1, // group by the first sorting column
aGroupingValueDefs: [
"Date"/* column title */: {
fnGroupByValue : function (v/* cell value*/) {
return v ? v.replace (/\d\d(\d\d)-(\d\d)-\d\d/g, "$1$2") : "Not specified";
} },
"Sales"/* column title */: {
fnGroupByValue: function (v/* cell value*/) {
return v < 100 ? "Poor" : (v >= 100 && v < 200) ? "So-so" : v >= 200 ? "Great" : "Invalid value";
}
}
]
}
);
}
[/code]

zolson

What version of the product are you using?
[quote]---------------------------------------------
* File: jquery.dataTables.grouping.js
* Version: 1.0.
---------------------------------------------
[/quote]

I wasnt sure if I should post this in the DataTable forums or as an issue in the plugin page. I have put this in both, but If this should be moved or removed from either place then please let me know.

I am having an issue with applying row grouping properly after changing the value of the category that I am grouping on. In my case it is at RowIndex 5 (modCName). If I have an item in Cat1 and edit that row so that its now in Cat2 the values change properly in the table, but when I attempt to regroup the table with the following:

$('#my_table').dataTable().rowGrouping({ iGroupingColumnIndex: 5 });

It still thinks the row that I've edited is in Cat1.

Here is the structure of my dataTable:

[code]
var oTable = $("#my_table").dataTable({
"bPaginate": true,
"bLengthChange": false,
"bFilter": false,
"bSort": true,
"bInfo": false,
"bAutoWidth": false,
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [0, 1] }
],
"aoColumns": [
{ "sClass": "modID", "bVisible": false },
{ "sWidth": "5px" },
{ "sClass": "modDate", "sTitle": "Date", "bVisible": false },
{ "sClass": "modOdometer", "sTitle": "Odometer", "bVisible": false },
{ "sClass": "modCID", "bVisible": false },
{ "sClass": "modCName", "sTitle": "Category", "bVisible": false },
{ "sClass": "modMID", "bVisible": false },
{ "sClass": "modMName", "sTitle": "Manufacturer" },
{ "sClass": "modVID", "bVisible": false },
{ "sClass": "modVName", "bVisible": false },
{ "sClass": "modPID", "bVisible": false },
{ "sClass": "modPName", "sTitle": "Product" },
{ "sClass": "modPPrice", "sTitle": "Cost" },
{ "sClass": "modRating", "bVisible": false }
]
});
[/code]

Here is the code that I am using to modify the values in the dataTable (toggleModCol is a separate function that Ive supplied the code below) :

[code]
toggleModCols(oTable, true);

var cNode = oTable.fnGetNodes(eNode);
$("#" + cNode.id + " " + ".modDate").html($("#txtModDate").val());
$("#" + cNode.id + " " + ".modOdometer").html($("#txtModOdometer").val());
$("#" + cNode.id + " " + ".modCID").html($("#txtModCategory").val());
$("#" + cNode.id + " " + ".modCName").html($("#txtModCategory option:selected").text());
$("#" + cNode.id + " " + ".modMID").html($("#txtModMID").val());
$("#" + cNode.id + " " + ".modMName").html($("#txtModManufacturer").val());
$("#" + cNode.id + " " + ".modVID").html($("#txtModVID").val());
$("#" + cNode.id + " " + ".modVName").html($("#txtModVender").val());
$("#" + cNode.id + " " + ".modPID").html($("#txtModPID").val());
$("#" + cNode.id + " " + ".modPName").html($("#txtModName").val());
$("#" + cNode.id + " " + ".modPPrice").html("$" + $("#txtModPrice").val().replace("$", ""));
$("#" + cNode.id + " " + ".modRating").html(0);

toggleModCols(oTable, false);

function toggleModCols(oTable, isVisible) {
oTable.fnSetColumnVis(0, isVisible);
oTable.fnSetColumnVis(2, isVisible);
oTable.fnSetColumnVis(3, isVisible);
oTable.fnSetColumnVis(4, isVisible);
oTable.fnSetColumnVis(5, isVisible);
oTable.fnSetColumnVis(6, isVisible);
oTable.fnSetColumnVis(8, isVisible);
oTable.fnSetColumnVis(9, isVisible);
oTable.fnSetColumnVis(10, isVisible);
oTable.fnSetColumnVis(13, isVisible);
}
[/code]

If you need any further clarification or code please let me know and thank you in advance for taking the time to read over this.

zolson

My whole thought was to grab nodes based on the classes instead of by the index so that if I added a new column to the table I wouldnt have to modify as much of the code.

I have caved in and decided to use the built in DataTable fnUpdate:

[code]
oTable.fnUpdate($("#txtModDate").val(), eNode, 2, true, false);
oTable.fnUpdate($("#txtModOdometer").val(), eNode, 3, true, false);
oTable.fnUpdate($("#txtModCategory").val(),eNode, 4, true, false);
oTable.fnUpdate($("#txtModCategory option:selected").text(),eNode, 5, true, false);
oTable.fnUpdate($("#txtModMID").val(),eNode, 6, true, false);
oTable.fnUpdate($("#txtModManufacturer").val(),eNode, 7, true, false);
oTable.fnUpdate($("#txtModVID").val(),eNode, 8, true, false);
oTable.fnUpdate($("#txtModVender").val(),eNode, 9, true, false);
oTable.fnUpdate($("#txtModPID").val(),eNode, 10, true, false);
oTable.fnUpdate($("#txtModName").val(),eNode, 11, true, false);
oTable.fnUpdate("$" + $("#txtModPrice").val().replace("$", ""),eNode, 12, true, false);
oTable.fnUpdate("0",eNode, 13, true, true);
[/code]

This is working the way that I anticipated. If anyone has any other ideas or thoughts let me know. I am open to other suggestions.

diondu

Congratulation jocapc for this plugin. It's fantastic.

A sugestion: For future updates you could add an option that allows the user to customizing the tr class based on it's content or based on the aaData of each row.

For example: I used the plugin to group data in may table based on the month that each row way added in the database. I wanted to highlight the actual month. The actual month has red color in the group tr. So I did something like this:

[code]

oTable.rowGrouping({
"bExpandableGrouping": true,
"fnOnGrouped": function(){
var td = $("td[className$=expander]").each(function(){
var strBackground = "#A0B3C5"
var conteudoTr = $(this).text();

else if(conteudoTr.indexOf('Jully')!=-1)
strBackground = "red"

$(this).css(
{"background-color":strBackground}
)
})
}
});

[/code]

It's just a suggestion...

Thanks

arsiya

Tank you jocapc for this plugin. It's very useful.

How can I display totals when grouping ?

Bytewize

Very good work!

Is it currently possible to have the items in a list behave like an accordion(so clicking on a item shows extra data about it) AND still have the headers act like Asc or Desc.
Currently the only way I see this is to make every item into a group and add additional info as items, but then the table headers will only sort the items in the group, not the groups themselves, so I would have to add additional buttons for sorting groups and the original header sorting would make it confusing.

Also, why no pagination when using accordion?

Greets,
Byte

anupamsmaurya

Nice plugin.
Right now we can apply two level grouping. Is there any possibility of having multilevel (at least 3 level) grouping in the future or is there any way I can achieve this in the current version?
Thanks

MikeS

Hey joecapc, very nice plugin to dataTables. It does the job, but, I was wondering if the following enhancement would be possible:

Could row grouping be made a user defined thing? Currently, the way I see it, the row grouping must be done in code and called when dataTables is initialized. It would be nice if in addition to this, give the end user the ability to change the grouping. I was thinking something along the lines of making the column headers drag and droppable to some "grouping region" where the order in which the headers are dropped would determine the group hierarchy.

Not sure if I've explained it enough for you to understand and if not, I can try to expand on the functionality.

jnsohnumr

Hi,


Great Plugin!

I was wondering if this functionality is already there and I'm just not understanding the documentation, but what I'm wanting is for the rows to be grouped by a single column, and for sorting to sort based on the "top" item in each group. For example: I would like to group by an order number, but when the user clicks the price header, I would like for the GROUPS to be reordered by price based on the first item in each order. For this I'm assuming that within the grouping, it should be ordered by the item number column (which is unique).

If this functionality is not yet there, would it be a feature that could be implemented easily?



Thanks,

Jared

jnsohnumr

An example from previous comment / request:

[code]
--------------------------------------------------
--------------------------------------------------
| items purchased (pre-sort) |
--------------------------------------------------
| order_id, item_id, price, quantity, date |
--------------------------------------------------
--------------------------------------------------
| 3, 16, $12.34, 3, 1/1/2012 |
| 3, 19, $11.04, 3, 1/1/2012 |
| 3, 31, $9.16, 3, 1/1/2012 |
--------------------------------------------------
| 4, 16, $12.34, 3, 1/2/2012 |
--------------------------------------------------
| 5, 47, $12.54, 3, 1/4/2012 |
| 5, 51, $1.31, 3, 1/4/2012 |
--------------------------------------------------
| 9, 31, $9.16, 3, 1/4/2012 |
--------------------------------------------------
| 10, 13, $13.11, 3, 1/6/2012 |
| 10, 15, $0.16, 3, 1/6/2012 |
--------------------------------------------------
--------------------------------------------------
[/code]

Becomes this:


[code]
--------------------------------------------------
--------------------------------------------------
| items purchased (post-sort, by item_id) |
--------------------------------------------------
| order_id, item_id, price, quantity, date |
--------------------------------------------------
--------------------------------------------------
| 10, 13, $13.11, 3, 1/6/2012 |
| 10, 15, $0.16, 3, 1/6/2012 |
--------------------------------------------------
| 3, 16, $12.34, 3, 1/1/2012 |
| 3, 19, $11.04, 3, 1/1/2012 |
| 3, 31, $9.16, 3, 1/1/2012 |
--------------------------------------------------
| 4, 16, $12.34, 3, 1/2/2012 |
--------------------------------------------------
| 9, 31, $9.16, 3, 1/4/2012 |
--------------------------------------------------
| 5, 47, $12.54, 3, 1/4/2012 |
| 5, 51, $1.31, 3, 1/4/2012 |
--------------------------------------------------

[/code]
--------------------------------------------------

iecwebmast

Hello,
The row grouping is great! Is it possible to add expand/collapse all buttons?
Thank you for this add-in!!!
iecwebmast

aliwhite

I am having an issue with the accordion function not behaving correctly when I have a non-sortable table i.e. "bSort":false.
Can I fix this somehow?

allan

@allwhite and everyone else in this thread - I'd suggest opening issues on the plug-ins own web-site: http://code.google.com/p/jquery-datatables-row-grouping/issues/list . Its not supported as part of the main DataTables project, so you might have more luck there? not sure. I'm sure any patches to improve it will be welcomed by the plug-in project.

Allan