jQuery UI Widgets › Forums › Grid › jqxPivotGrid custom grandtotal
Tagged: grandtotal, jqxpivotgrid
This topic contains 9 replies, has 2 voices, and was last updated by njenga 4 years, 3 months ago.
-
Author
-
Hi!
Is it possible to create a custom column in the pivot grid, based off of the rows grandtotal?
Specifically, I have a grandTotals column and I’d like to add a new column calculated from the rows grand total value (e.g. grandTotal value x 2).
Possible??Hello njenga,
I am not absolutely sure what exactly you want to achieve.
But I would like to suggest you look at this example.Best Regards,
Hristo HristovjQWidgets team
https://www.jqwidgets.comThanks.
If you tweak the pivot settings in the example as follows:
(1) totals: added
(2) rows: limited to lastName
(3) columns: limited to productName`pivotValuesOnRows: false,
totals: {rows: {subtotals: false, grandtotals: false}, columns: {subtotals: false, grandtotals: true}},
rows: [{ dataField: ‘lastname’ }],
columns: [
{ dataField: ‘productname’ }
],
values: [
{ dataField: ‘price’, ‘function’: ‘sum’, text: ‘Price sum’, formatSettings: { prefix: ‘$’, decimalPlaces: 2, align: ‘right’, } },
{ dataField: ‘items’, ‘function’: ‘sum’, text: ‘Items sum’ }
]`
This will result in a new Totals column onPrice SumandItems Sum.
Now, what I want to achieve is an additional column whos value is calculated from one of these Totals column values the e.g. =Totals:Price Sum * 10%(or some other formula) – of course for each row.Hello njenga,
If you want one additional column that has a calculation based on the
Price sumcolumn then you could use thebeforeLoadCompletecallback.
Please, take a look at this demo which demonstrates how to use the mentioned callback:
https://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/foreignkeycolumn.htm?light
I hope this will helpBest Regards,
Hristo HristovjQWidgets team
https://www.jqwidgets.comThanks! I’ll give it a shot and respond back here.
Hi Hristo:
I could not get it to work with the beforeLoadComplete.…this is the fiddle for current out put:
https://jsfiddle.net/njenga/wneg6qjL/14/…this is what i’m looking for:
http://jsfiddle.net/njenga/17v5s8ht/2/i.e. 2 new columns:
(1) RATE which is sourced from the records (it’s a constant for a given row)
(2) Total*RATE which is a product of the Rate value and the total value for each row.Any other ideas/approach??
Regards
Hello njenga,
I would like to mention that we do not provide customizations.
If you have some trouble with our widgets we could provide you a solution or we will create a work item.
Please, take a look at this example that I prepared for you:<!DOCTYPE html> <html lang="en"> <head> <title id="Description">JavaScript PivotGrid - Custom Pivot Function</title> <link rel="stylesheet" href="../../../jqwidgets/styles/jqx.base.css" type="text/css" /> <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" /> <meta name="viewport" content="width=device-width, initial-scale=1 maximum-scale=1 minimum-scale=1" /> <link rel="stylesheet" href="../../../jqwidgets/styles/jqx.light.css" type="text/css" /> <script type="text/javascript" src="../../../scripts/jquery-1.12.4.min.js"></script> <script type="text/javascript" src="../../../jqwidgets/jqxcore.js"></script> <script type="text/javascript" src="../../../jqwidgets/jqxdata.js"></script> <script type="text/javascript" src="../../../jqwidgets/jqxbuttons.js"></script> <script type="text/javascript" src="../../../jqwidgets/jqxscrollbar.js"></script> <script type="text/javascript" src="../../../jqwidgets/jqxmenu.js"></script> <script type="text/javascript" src="../../../jqwidgets/jqxpivot.js"></script> <script type="text/javascript" src="../../../jqwidgets/jqxpivotgrid.js"></script> <script type="text/javascript" src="../../../scripts/demos.js"></script> <script type="text/javascript"> $(document).ready(function () { var records = [ { "attendanceOid": 205808, "attendanceDt": "2020-08-13T21:00:00.000Z", "aDay": "Fri14", "employeeOid": 216, "employeeName": "Alice N Masea", "weight": 60, "pickingRate": 8, "uid": 0 }, { "attendanceOid": 205967, "attendanceDt": "2020-08-14T21:00:00.000Z", "aDay": "Sat15", "employeeOid": 216, "employeeName": "Alice N Masea", "weight": 0, "pickingRate": 8, "uid": 1 }, { "attendanceOid": 206126, "attendanceDt": "2020-08-15T21:00:00.000Z", "aDay": "Sun16", "employeeOid": 216, "employeeName": "Alice N Masea", "weight": 15, "pickingRate": 8, "uid": 2 }, { "attendanceOid": 205490, "attendanceDt": "2020-08-16T21:00:00.000Z", "aDay": "Mon17", "employeeOid": 216, "employeeName": "Alice N Masea", "weight": 25, "pickingRate": 8, "uid": 3 }, { "attendanceOid": 205331, "attendanceDt": "2020-08-17T21:00:00.000Z", "aDay": "Tue18", "employeeOid": 216, "employeeName": "Alice N Masea", "weight": 0, "pickingRate": 8, "uid": 4 }, { "attendanceOid": 206494, "attendanceDt": "2020-08-18T21:00:00.000Z", "aDay": "Wed19", "employeeOid": 216, "employeeName": "Alice N Masea", "weight": 45, "pickingRate": 8, "uid": 5 }, { "attendanceOid": 206285, "attendanceDt": "2020-08-19T21:00:00.000Z", "aDay": "Thu20", "employeeOid": 216, "employeeName": "Alice N Masea", "weight": 15, "pickingRate": 8, "uid": 6 }, { "attendanceOid": 206654, "attendanceDt": "2020-08-20T21:00:00.000Z", "aDay": "Fri21", "employeeOid": 216, "employeeName": "Alice N Masea", "weight": 5, "pickingRate": 8, "uid": 7 }, { "attendanceOid": 206814, "attendanceDt": "2020-08-22T21:00:00.000Z", "aDay": "Sun23", "employeeOid": 216, "employeeName": "Alice N Masea", "weight": 0, "pickingRate": 8, "uid": 8 }, { "attendanceOid": 205863, "attendanceDt": "2020-08-13T21:00:00.000Z", "aDay": "Fri14", "employeeOid": 302, "employeeName": "Ann x Wanjiru", "weight": 20, "pickingRate": 8, "uid": 9 }, { "attendanceOid": 206022, "attendanceDt": "2020-08-14T21:00:00.000Z", "aDay": "Sat15", "employeeOid": 302, "employeeName": "Ann x Wanjiru", "weight": 0, "pickingRate": 8, "uid": 10 }, { "attendanceOid": 206181, "attendanceDt": "2020-08-15T21:00:00.000Z", "aDay": "Sun16", "employeeOid": 302, "employeeName": "Ann x Wanjiru", "weight": 40, "pickingRate": 8, "uid": 11 }, { "attendanceOid": 205545, "attendanceDt": "2020-08-16T21:00:00.000Z", "aDay": "Mon17", "employeeOid": 302, "employeeName": "Ann x Wanjiru", "weight": 0, "pickingRate": 8, "uid": 12 }, { "attendanceOid": 205386, "attendanceDt": "2020-08-17T21:00:00.000Z", "aDay": "Tue18", "employeeOid": 302, "employeeName": "Ann x Wanjiru", "weight": 65, "pickingRate": 8, "uid": 13 }, { "attendanceOid": 206549, "attendanceDt": "2020-08-18T21:00:00.000Z", "aDay": "Wed19", "employeeOid": 302, "employeeName": "Ann x Wanjiru", "weight": 0, "pickingRate": 8, "uid": 14 }, { "attendanceOid": 206340, "attendanceDt": "2020-08-19T21:00:00.000Z", "aDay": "Thu20", "employeeOid": 302, "employeeName": "Ann x Wanjiru", "weight": 80, "pickingRate": 8, "uid": 15 }, { "attendanceOid": 206709, "attendanceDt": "2020-08-20T21:00:00.000Z", "aDay": "Fri21", "employeeOid": 302, "employeeName": "Ann x Wanjiru", "weight": 0, "pickingRate": 8, "uid": 16 }, { "attendanceOid": 206869, "attendanceDt": "2020-08-22T21:00:00.000Z", "aDay": "Sun23", "employeeOid": 302, "employeeName": "Ann x Wanjiru", "weight": 0, "pickingRate": 8, "uid": 17 }, { "attendanceOid": 205818, "attendanceDt": "2020-08-13T21:00:00.000Z", "aDay": "Fri14", "employeeOid": 238, "employeeName": "Augustus K Mutisya", "weight": 0, "pickingRate": 8, "uid": 18 }, { "attendanceOid": 205977, "attendanceDt": "2020-08-14T21:00:00.000Z", "aDay": "Sat15", "employeeOid": 238, "employeeName": "Augustus K Mutisya", "weight": 0, "pickingRate": 8, "uid": 19 }, { "attendanceOid": 206136, "attendanceDt": "2020-08-15T21:00:00.000Z", "aDay": "Sun16", "employeeOid": 238, "employeeName": "Augustus K Mutisya", "weight": 50, "pickingRate": 8, "uid": 20 }, { "attendanceOid": 205500, "attendanceDt": "2020-08-16T21:00:00.000Z", "aDay": "Mon17", "employeeOid": 238, "employeeName": "Augustus K Mutisya", "weight": 0, "pickingRate": 8, "uid": 21 }, { "attendanceOid": 205341, "attendanceDt": "2020-08-17T21:00:00.000Z", "aDay": "Tue18", "employeeOid": 238, "employeeName": "Augustus K Mutisya", "weight": 0, "pickingRate": 8, "uid": 22 }, { "attendanceOid": 206504, "attendanceDt": "2020-08-18T21:00:00.000Z", "aDay": "Wed19", "employeeOid": 238, "employeeName": "Augustus K Mutisya", "weight": 0, "pickingRate": 8, "uid": 23 }, { "attendanceOid": 206295, "attendanceDt": "2020-08-19T21:00:00.000Z", "aDay": "Thu20", "employeeOid": 238, "employeeName": "Augustus K Mutisya", "weight": 60, "pickingRate": 8, "uid": 24 }, { "attendanceOid": 206664, "attendanceDt": "2020-08-20T21:00:00.000Z", "aDay": "Fri21", "employeeOid": 238, "employeeName": "Augustus K Mutisya", "weight": 0, "pickingRate": 8, "uid": 25 }, { "attendanceOid": 206824, "attendanceDt": "2020-08-22T21:00:00.000Z", "aDay": "Sun23", "employeeOid": 238, "employeeName": "Augustus K Mutisya", "weight": 15, "pickingRate": 8, "uid": 26 } ]; var source = { localdata: records, datatype: "array", datafields: [ { name: "attendanceOid", type: "number" }, { name: "attendanceDt", type: "date", format: "MMM dd yyyy" }, { name: "aDay", type: "string" }, { name: "employeeOid", type: "number" }, { name: "employeeName", type: "string" }, { name: "weight", type: "number" }, { name: "pickingRate", type: "number" } ] }; var dataAdapter = new $.jqx.dataAdapter(source, { beforeLoadComplete: function (records) { var arrayOfNames = []; for (let i = 0; i < records.length; i++) { const element = records[i]; if (arrayOfNames.indexOf(element.employeeName) == -1) { arrayOfNames.push(element.employeeName); } } for (let j = 0; j < arrayOfNames.length; j++) { const name = arrayOfNames[j]; records.push({ "attendanceOid": null, "attendanceDt": null, "aDay": "RATE", "employeeOid": null, "employeeName": name, "weight": 8, // because the RATE is fixed now but it could be calculated "pickingRate": null, "uid": null }); // compensation records.push({ "attendanceOid": null, "attendanceDt": null, "aDay": "Total * RATE", "employeeOid": null, "employeeName": name, "weight": -8, "pickingRate": null, "uid": null }); } return records; } }); dataAdapter.dataBind(); var pivotAdapter = new $.jqx.pivot( dataAdapter, { customAggregationFunctions: { "payoutCalc": function (values) { console.log("values = " + values); return } }, pivotValuesOnRows: false, totals: { rows: { subtotals: false, grandtotals: true }, columns: { subtotals: false, grandtotals: true } }, rows: [ { dataField: "employeeName", height: 30 } ], columns: [ { dataField: "aDay", align: "right" } ], values: [ { dataField: "weight", "function": "sum", text: "-", width: 60, height: 30, formatSettings: { decimalPlaces: 0 } } ] }); $("#divPivotGrid").jqxPivotGrid({ source: pivotAdapter, scrollBarsEnabled: true, treeStyleRows: true, autoResize: true, multipleSelectionEnabled: false, cellsRenderer: function (pivotCell) { if (pivotCell.pivotColumn.parentItem.adapterItem.text == "Total * RATE") { var myPivotGridRows = $("#divPivotGrid").jqxPivotGrid("getPivotRows"); var myPivotGridColumns = $("#divPivotGrid").jqxPivotGrid("getPivotColumns"); var myPivotGridCells = $("#divPivotGrid").jqxPivotGrid("getPivotCells"); var getSpecificRow = function (cells, rows, columns, id) { var specificRow = null; for (var i = 0; i < myPivotGridRows.items.length; i++) { var currentRow = myPivotGridRows.items[i] var currentRowInnerItems = currentRow.items; if (currentRow.id == id) { specificRow = currentRow; break; } } return specificRow; }; var getSpecificColumn = function (columnsArray, key) { var column = null; for (var i = 0; i < columnsArray.length; i++) { if (myPivotGridColumns.visibleLeafItems[i].text == "-") { var currentColumn = columnsArray[i]; var currentColumnKey = currentColumn.parentItem.text; if (currentColumnKey == key) { column = myPivotGridColumns.visibleLeafItems[i]; break; } } } return column; }; var cellParentColumnKey = "Total"; // GetSpecific Row var specificRow = getSpecificRow(myPivotGridCells, myPivotGridRows, myPivotGridColumns, pivotCell.pivotRow.id); // GetSpecific Column var specificColumn = getSpecificColumn(myPivotGridColumns.visibleLeafItems, cellParentColumnKey); var cellValueTotal = myPivotGridCells.getCellValue(specificRow, specificColumn); var cellParentColumnKeyRate = "RATE"; var specificRowRate = getSpecificRow(myPivotGridCells, myPivotGridRows, myPivotGridColumns, pivotCell.pivotRow.id); var specificColumnRate = getSpecificColumn(myPivotGridColumns.visibleLeafItems, cellParentColumnKeyRate); var cellValueRate = myPivotGridCells.getCellValue(specificRowRate, specificColumnRate); return cellValueTotal.value * cellValueRate.value; } var cellText = pivotCell.value == 0 ? "" : pivotCell.formattedValue; return "<div style='width: calc(100%-8px); height: 100%; padding: 4px; margin: 0px;'>" + cellText + "</div>"; } }); }); </script> </head> <body class="default"> <div id="divPivotGrid" style="height: 400px; width: 800px; background-color: white;"> </div> </body> </html>You need to optimize it.
Also, I would like to mention that this example (as provided) could be achieved with the jqxGrid.
Best Regards,
Hristo HristovjQWidgets team
https://www.jqwidgets.comThanks Hristo!
I took a break for while so I’ll take a look at your suggestion and respond back shortly.Note: the jxgrid option would work BUT the unfortunately data in the DB is not in this flat format. I’ve had to use a messy “pivot sql” statement to get the pivot layout
Regards.
RESOLVED!
Works like a charm! Was also able to add additional calculated columns to the table!Thanks Very Much!!
-
AuthorPosts
You must be logged in to reply to this topic.