Sumation Error

Question

Hello Team,

Am trying to sum up the quantity and Total Amount in my dataTable footer, but is showing underline red, how do I modified the code to to perform this sumation and the total should only display the Quantity and TotalAmount at the footer after a date range is selected such as StartDate to EndDate ,

public ActionResult getNewOrder()
{
using (db = new ASPNETMASTERPOSTEntities())
{
var dataList = db.tblCustomers.Include("tblOrders");
var modifiedData = dataList.AsEnumerable().SelectMany(x => x.tblOrders.Select(d => new OrderViewModel
{
CustomerId = x.CustomerId,
CustomerName = d.tblCustomer.CustomerName,
PhoneNo = d.tblCustomer.PhoneNo,
OrderDate = d.tblCustomer.OrderDate,
CategoryName = d.CategoryName,
ProductName = d.ProductName,
Quantity = d.Sum(x=>x.tblOrders.Quantity),
UnitPrice = d.UnitPrice,
TotalAmount = d.Sum(x => x.tblOrders.TotalAmount),
OrderId = d.OrderId
})).ToList();
return Json(modifiedData, JsonRequestBehavior.AllowGet);
}
}

Vishal Joshi · Answer

Hello Emma, You need to use the footerCallback method of the jquery data table for summation. Please check below sample code for the same. new DataTable('#example', { footerCallback: function (row, data, start, end, display) { let api = this.api(); // Remove the formatting to get integer data for summation let intVal = function (i) { return typeof i === 'string' ? i.replace(/[\$,]/g, '') * 1 : typeof i === 'number' ? i : 0; }; // Total over all pages total = api .column(4) .data() .reduce((a, b) => intVal(a) + intVal(b), 0); // Total over this page pageTotal = api .column(4, { page: 'current' }) .data() .reduce((a, b) => intVal(a) + intVal(b), 0); // Update footer api.column(4).footer().innerHTML = '$' + pageTotal + ' ( $' + total + ' total)'; } }); Thanks Vishal Joshi

Amit Mohanty · Answer

If the OrderDate property is of type string instead of DateTime, then you need to convert the OrderDate string to DateTime before performing the comparison. public ActionResult getNewOrder(DateTime startDate, DateTime endDate) { using (db = new ASPNETMASTERPOSTEntities()) { var dataList = db.tblCustomers.Include("tblOrders"); var modifiedData = dataList.AsEnumerable() .SelectMany(x => x.tblOrders.Where(d => DateTime.Parse(d.tblCustomer.OrderDate) >= startDate && DateTime.Parse(d.tblCustomer.OrderDate) new OrderViewModel { CustomerId = x.CustomerId, CustomerName = d.tblCustomer.CustomerName, PhoneNo = d.tblCustomer.PhoneNo, OrderDate = d.tblCustomer.OrderDate, CategoryName = d.CategoryName, ProductName = d.ProductName, Quantity = d.Quantity, UnitPrice = d.UnitPrice, TotalAmount = d.TotalAmount, OrderId = d.OrderId })) .ToList(); var totalQuantity = modifiedData.Sum(x => x.Quantity); var totalAmount = modifiedData.Sum(x => x.TotalAmount); // Add the total values to the footer modifiedData.Add(new OrderViewModel { Quantity = totalQuantity, TotalAmount = totalAmount }); return Json(modifiedData, JsonRequestBehavior.AllowGet); } }

Deepak Rawat · Answer

to calculate the sum of quantities and total amounts in a DataTable footer, but the code you provided is showing an underline in red. Unfortunately, I don't have the complete context of your code or the specific error message you're encountering, which makes it difficult to pinpoint the exact issue. However, I can provide you with some general guidance to help you modify your code. using System.Linq; using System.Data.Entity; // If using Entity Framework consider the following modifications to your code: public ActionResult getNewOrder(DateTime startDate, DateTime endDate) { using (db = new ASPNETMASTERPOSTEntities()) { var dataList = db.tblCustomers.Include("tblOrders"); var modifiedData = dataList .SelectMany(x => x.tblOrders.Where(d => d.tblCustomer.OrderDate >= startDate && d.tblCustomer.OrderDate new OrderViewModel { CustomerId = x.CustomerId, CustomerName = d.tblCustomer.CustomerName, PhoneNo = d.tblCustomer.PhoneNo, OrderDate = d.tblCustomer.OrderDate, CategoryName = d.CategoryName, ProductName = d.ProductName, Quantity = d.Quantity, UnitPrice = d.UnitPrice, TotalAmount = d.TotalAmount, OrderId = d.OrderId })) .ToList(); var totalQuantity = modifiedData.Sum(d => d.Quantity); var totalAmount = modifiedData.Sum(d => d.TotalAmount); var footerData = new OrderViewModel { CustomerName = "Total", Quantity = totalQuantity, TotalAmount = totalAmount }; modifiedData.Add(footerData); return Json(modifiedData, JsonRequestBehavior.AllowGet); } }

Emmmanuel FIADUFE · Answer

Senior Amit, I encounter this error after trying your code

Amit Mohanty · Answer

public ActionResult getNewOrder(DateTime startDate, DateTime endDate) { using (db = new ASPNETMASTERPOSTEntities()) { var dataList = db.tblCustomers.Include("tblOrders"); var modifiedData = dataList.AsEnumerable() .SelectMany(x => x.tblOrders.Where(d => d.tblCustomer.OrderDate >= startDate && d.tblCustomer.OrderDate new OrderViewModel { CustomerId = x.CustomerId, CustomerName = d.tblCustomer.CustomerName, PhoneNo = d.tblCustomer.PhoneNo, OrderDate = d.tblCustomer.OrderDate, CategoryName = d.CategoryName, ProductName = d.ProductName, Quantity = d.Quantity, UnitPrice = d.UnitPrice, TotalAmount = d.TotalAmount, OrderId = d.OrderId })) .ToList(); var totalQuantity = modifiedData.Sum(x => x.Quantity); var totalAmount = modifiedData.Sum(x => x.TotalAmount); // Add the total values to the footer modifiedData.Add(new OrderViewModel { Quantity = totalQuantity, TotalAmount = totalAmount }); return Json(modifiedData, JsonRequestBehavior.AllowGet); } }

Sumation Error

Mahesh Chand

Sam Hobbs

Tahir Ansari