cumulative column

ppanta

Hi Alan and all,

Is there a way to add column with cumulative values? For example,

I have a table:


id | product | price
---------------
1 | camera | 200
2 | watch | 100
3 | pencil | 20
4 | wallet | 10

I want to add an extra column and make the table like this:

id | product | price | cumulative price |
--------------------------------
1 | camera | 200 | 200 |
2 | watch | 100 | 300 |
3 | pencil | 20 | 320 |
4 | wallet | 10 | 330 |

allan

You could use fnDrawCallack something like along these lines: http://datatables.net/release-datatables/examples/api/counter_column.html . Use fnGetData or the '_' method to get the data for the table in the callback.

Allan

ppanta

Thank you Allan. Can we have some similar example?

ppanta

Or any plug in is available so far?

allan

There is no plug-in available for this, and I'm afraid I don't have time at the moment to create a demo - things are a bit busy here at the moment! I'll try to do so in future, but can't currently say when that will be. I'd just trying it and seeing how far yo get - post your code here and perhaps I can offer a suggestion.

Allan

ppanta

Thank you Allan for your response.
I have my table row grouped by (and then cumulated column created on each of these group).
I have one of the external dropdown list created out of the table (upon selection of months from the dropdown, the report comes for that particular month)

I would appreciate any suggestion.

Thank you.

The sort / search functions and the dropdown list doesnt work. My table code looks like:




var oTable;
//the main function starts here with the complete load of the page
$(document).ready(function() {

oTable = $('#my_summary_data_table').dataTable ({
"sScrollX": "100%","sScrollY": "600px","sScrollXInner": "100%","sScrollYInner": "100%",
"bScrollCollapse": true, "bProcessing": false,
//"sAjaxDataProp": "data.inner",
//"sAjaxDataProp": "data",
//"bScrollInfinite": true,
"bServerSide": true, //Performance penalty occurs if we run all the query in db server
"bSortClasses": false, //Client-side processing - DOM sourced data: ~5'000 rows. Speed options
"bStateSave": false,
"bDeferRender": true, //Client-side processing - Ajax sourced data: ~50'000 rows. Speed options

//"bRetrieve": true,
//"bDestroy": true,

//"bLengthChange": false
//"bAutoWidth": true, //Performance penalty occurs
//"iTotalRecords": 57,
//"iTotalDisplayRecords": 57,
"iDisplayLength": "-1", //It is useful when we disable the infinite scroller
//"aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
"aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],

"bJQueryUI": true,
"sServerMethod": "POST", 'sPaginationType': 'full_numbers',
"sAjaxSource": "<?php echo site_url('time_point_assessment_summary/get_time_point_assessment_summary_table'); ?>",
"sEcho": 3, "bInfo": true,
"aaSorting": [[1, 'asc']],
//"sDom": '<"H"Tlf><"H"ipr>Rt<"F"rip>',
"sDom": '<"H"Tf><"H"r>Rt<"F"r>',
"oTableTools": {
"sSwfPath": "../../../assets/DataTables/extras/TableTools/media/swf/copy_csv_xls_pdf.swf",
"aButtons": [
{"sExtends": "copy","sButtonText": "Copy",},
/*{"sExtends": "csv","sButtonText": "CSV"},*/
{"sExtends": "xls","sButtonText": "Excel","sFileName": "*.xls"},
/*{"sExtends": "pdf","sButtonText": "PDF"}*/
{"sExtends": "print","sButtonText": "Print","sMessage": ""}
]
},

"oLanguage": {
"sLengthMenu": "Display _MENU_ records per page",
//"sZeroRecords": "No record found for the selected report time",
"sInfo": "Showing _START_ to _END_ of _TOTAL_ records",
"sInfoEmpty": "Showing 0 to 0 of 0 records",
"sInfoFiltered": "(filtered from _MAX_ total records)"
},

ppanta

"aoColumns": [
//{ "sTitle": "View Details", "sClass": "size1", "bVisible": true, "bSortable": false, "bSearchable": false, "mData": [ 4 ], "mRender": [ 0 ].value},
{ "sTitle": "Health Risk", "sClass": "size2", "bVisible": true, "aTargets": ["health_risk"], "mData": [ 0 ], "mRender": [ 0 ].value },
{ "sTitle": "S11 Progress", "sClass": "size2", "bVisible": true, "aTargets": ["s11_progress"], "mData": [ 1 ], "mRender": [ 1 ].value },
{ "sTitle": "Frequency", "sClass": "size2", "bVisible": true, "aTargets": ["frequency"], "mData": [ 2 ], "mRender": [ 2 ].value },
{ "sTitle": "Time Point ID", "sClass": "size2", "bVisible": true, "aTargets": ["time_point_id"], "mData": [ 3 ], "mRender": [ 3 ].value },
{ "sTitle": "S11 Progress Text", "sClass": "size2", "bVisible": true, "aTargets": ["s11_progress_text"], "mData": [ 4 ], "mRender": [ 4 ].value, "bSortable": false, },

//{ "sTitle": "Cumulative Frequency", "sClass": "size2", "bVisible": true, "aTargets": ["cumulative_frequency"], "mData": [ 5 ], "mRender": [ 5 ].value },
{ "sTitle": "Cumulative Freq", "sClass": "size2", "bVisible": true, "bSortable": true, },

{ "sTitle": "Total Group Freq", "sClass": "size2", "bVisible": true, "bSortable": true, },
{ "sTitle": "Percent", "sClass": "size2", "bVisible": true, "bSortable": true, },
{ "sTitle": "Cumulative Percent", "sClass": "size2", "bVisible": true, "bSortable": true, },

{ "sTitle": "Total rows", "sClass": "size2", "bVisible": true, "bSortable": true, },
],

"aoColumnDefs": [
{ "bSortable": false, "aTargets": [ 0 ] }
],

//START OF THE SNIPPET
//The following snippet will creates the row grouping
"fnDrawCallback": function ( oSettings ) {
if ( oSettings.aiDisplay.length == 0 )
{
return;
}
var nTrs = $('#my_summary_data_table tbody tr');
var iColspan = nTrs[0].getElementsByTagName('td').length;
var sLastGroup = "";
for ( var i=0 ; i 0 && (oTable.fnGetData( i, 0 ) !== oTable.fnGetData( i-1, 0 ))) {
percent = 0.0; //We dont need to reset this variable here, but it is ok to reset.
cumulative_freq = 0;

j=0;
total_group_freq = 0;
cumulative_percent = 0.0;
}

cumulative_freq = cumulative_freq + parseInt(oTable.fnGetData( i, 2 ), 10);
oTable.fnUpdate( cumulative_freq, this.parentNode, 5, false, false );

//Calculating the percentage

total_rows = parseInt(oTable.fnSettings().fnRecordsTotal()); //should do it.
if(j == 0) {
while (oTable.fnGetData( i, 0 ) == oTable.fnGetData( i+j, 0 )) {
total_group_freq += parseInt(oTable.fnGetData( i+j, 2 ), 10)
j++;
}

} else {
total_group_freq = parseInt(oTable.fnGetData( i-1, 6 ), 10)
}
//dividend = parseInt(oTable.fnGetData( i, 2 ), 10 ); // We are not using this variable, instead we are using the value directly

percent = parseFloat((parseInt(oTable.fnGetData( i, 2 ), 10 ) / total_group_freq) * 100);
percent = percent.toPrecision(4);
//percent = percent.toFixed(4);

cumulative_percent += parseFloat(percent);
//cumulative_percent = cumulative_percent.toPrecision(2);


//Rounding the cumulative percentage into 100
if(cumulative_percent > 99.00 && cumulative_percent < 101.00)
cumulative_percent = 100.00;

oTable.fnUpdate( total_group_freq, this.parentNode, 6, false, false );
oTable.fnUpdate( percent, this.parentNode, 7, false, false );
oTable.fnUpdate( cumulative_percent, this.parentNode, 8, false, false );


//total_rows = oTable.fnSettings().fnRecordsTotal(); //should do it.
//total_rows = oTable.fnSettings().fnRecordsDisplay() //will give you the number after filtering has occurred.
oTable.fnUpdate( total_group_freq, this.parentNode, 9, false, false );

} );
}
},

"aoColumnDefs": [
{ "bVisible": false, "aTargets": [ 0 ] }
],
"aaSortingFixed": [[ 0, 'asc' ]],
//"aaSorting": [[ 1, 'asc' ]],
//"sDom": '<"H"Tlf><"H"ipr>Rt<"F"rip>',

//END OF THE SNIPPET

"fnServerParams": function ( aoData ) {
aoData.push( { "name": "selected_time_point_digit", "value": $("select#time_point_list option:selected").val() } );
//aoData.push( { "name": "selected_time_point_text", "value": $("select#time_point_list option:selected").text() } );
},

"fnServerData": (function(sSource, aoData, fnCallback) {
$.ajax ({
'dataType': 'json',
'type' : 'POST',
'url' : sSource,
'data' : aoData,
'success' : fnCallback
});
}),

});

//oTable.fnDraw(true); //No need to run fnDraw here
//Executes on every change in the select option list

$( "select#time_point_list" ).on( "change", function(e){
//iDisplayIndex = iDisplayIndex % oSettings._iDisplayLength;
//var sGroup = oSettings.aoData[ oSettings.aiDisplay[iDisplayIndex] ]._aData[0];
//oTable.fnReloadAjax();
oTable.fnDraw(true);
});

/* Add event listener for opening and closing details
* Note that the indicator for showing which row is open is not controlled by DataTables,
* rather it is done here
*/

$('#my_summary_data_table tbody td img').live('click', function () {
var nTr = $(this).parents('tr')[0];
if ( oTable.fnIsOpen(nTr) )
{
/* This row is already open - close it */
this.src = "../../../assets/DataTables/extras/TableTools/media/images/details_open.png";
oTable.fnClose( nTr );
}
else
{
/* Open this row */
this.src = "../../../assets/DataTables/extras/TableTools/media/images/details_close_blue.png";
oTable.fnOpen( nTr, fnFormatDetails_tpa(oTable, nTr), 'details' );
}
} );
});