Sumation Error

Emmmanuel FIADUFE

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);
}
}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Joshi3y ago

Accepted 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

Deepak Rawat3y ago

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 <= endDate)
                .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.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);
    }
}

Amit Mohanty3y ago

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) <= endDate)
                .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.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);
    }
}

Emmmanuel FIADUFE3y ago
Senior Amit, I encounter this error after trying your code

+1

Amit Mohanty3y ago

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 <= endDate)
                .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.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);
    }
}