show total and grand total without postback

RAVI 1,056 Reputation points
2023-11-29T16:44:27.1966667+00:00

Hello

This Is my sql data

  SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Table_1]( 	[ID] [int] IDENTITY(1,1) NOT NULL, 	[Field1] [varchar](50) NULL, 	[Field2] [varchar](50) NULL, 	[Field3] [varchar](50) NULL, 	[Field4] [float] NULL,  CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED  ( 	[ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[Table_1] ON INSERT [dbo].[Table_1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (1, N'Apple', N'AA', N'BB', 100) INSERT [dbo].[Table_1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (2, N'Mango', N'AA', N'CC', 200) INSERT [dbo].[Table_1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (3, N'Orange', N'CC', N'DD', 50) INSERT [dbo].[Table_1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (4, N'Apple', N'CC', N'KK', 100) INSERT [dbo].[Table_1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (5, N'Mango', N'JJ', N'UP', 10) INSERT [dbo].[Table_1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (6, N'Mango', N'GG', N'EE', 90) SET IDENTITY_INSERT [dbo].[Table_1] OFF 

This is my aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<html>
<head runat="server">
    <title>Untitled Page</title>
 <script type="text/javascript" src="https://code.jquery.com/jquery-3.2.1.min.js"></script> 
<script type="text/javascript" src="http://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css" href="http://cdn.datatables.net/1.13.7/css/jquery.dataTables.min.css" />

    
 <script type="text/javascript">
     
     $(document).ready(function() {
    $('#list').DataTable( {
    
     lengthMenu: [
            [1000, 500, 200, -1],
            [1000, 500, 250, 'All'],
        ],
        initComplete: function () {
            this.api().columns("0").every( function () {
                var column = this;
                var select = $('<select STYLE="width:150px"><option value=""></option></select>')
                    .appendTo( $("#Span1").empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
 
                        column
                            .search( val ? '^'+val+'$' : '', true, true )
                            .draw();
                    } );
 
                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
            
            
             this.api().columns("1").every( function () {
                var column = this;
                var select = $('<select STYLE="width:150px"><option value=""></option></select>')
                    .appendTo( $("#Span2").empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
 
                        column
                            .search( val ? '^'+val+'$' : '', true, true )
                            .draw();
                    } );
 
                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
            
            
            
             
            
            
			//           next
            
        }
    } );
} );
			 
</Script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        FROM DATE
        <asp:TextBox ID="TextBox1" runat="server" Width="131px">25-NOV-2023</asp:TextBox>
        TO DATE
        <asp:TextBox ID="TextBox2" runat="server" Width="131px">30-NOV-2023</asp:TextBox>&nbsp;<br />
    
    
      
  <TABLE>
  <TR>
 
  <TD style="WIDTH: 100px; height: 31px;"><span style="color: darkcyan; font-family: Calibri"><strong>Field1</strong></span></TD>
  <TD style="WIDTH: 100px; height: 31px;"><span style="color: darkcyan; font-family: Calibri"><strong>Field2</strong></span></TD>
 
   
 
  </TR>
  <TR>
   <TD onChange="calc()"; style="WIDTH: 100px;font-size:12px; font-weight:bold;font-family:Calibri;color:Black;"> <span id="Span1"></Span></TD>
  <TD onChange="calc()"; style="WIDTH: 100px;font-size:12px; font-weight:bold;font-family:Calibri;color:Black;"> <span id="Span2"></span></TD>
    
  </TR> </TABLE>
                         
    <div class="e_list">
    <asp:Panel runat="server" ID="Panel1">
      <table cellspacing="0" class="myClass" id="list" style="width:500px;font-family:Calibri;font-size:11px;border-collapse: collapse; border: 1px solid black;">
        <thead>
        
          <tr >
          
           <td style="border-collapse: collapse; border: 1px solid black; background: #FAC794;">Field1</td>
           <td style="border-collapse: collapse; border: 1px solid black; background: #FAC794;">Field2</td>
             <td style="border-collapse: collapse; border: 1px solid black; background: #FAC794;">Field3</td>
            <td style="border-collapse: collapse; border: 1px solid black; background: #FAC794;">Field4</td>           
            
                           
                           
          </tr>
        </thead>
        <tbody style="border-collapse: collapse; border: 1px solid black;">
         <asp:Repeater ID="Repeater1" runat="server" >
            <ItemTemplate>
          <tr>
         
             
            <td style="border-collapse: collapse; border: 1px solid black;font-size:larger;"><%#Eval("Field1")%></td>            
            <td style="border-collapse: collapse; border: 1px solid black;font-size:larger;"><%#Eval("Field2")%></td>
             <td style="border-collapse: collapse; border: 1px solid black;font-size:larger;"><%#Eval("Field3")%></td>
             <td style="border-collapse: collapse; border: 1px solid black;font-size:larger;"><%#Eval("Field4")%></td>
              
             
          </tr>
            
            </ItemTemplate>
        </asp:Repeater>

         
        </tbody>
           <tfoot>
      
    </tfoot>
      </table>
      </asp:Panel>

    </div>                        
                            
                
    </div>
    </form>
</body>
</html>

This is my aspx code behind page

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Text;

public partial class _Default : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            //Table_1
            DataTable dt = new DataTable();
            SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            con1.Open();
            SqlCommand cmd1 = new SqlCommand("select * from Table_1 order by Field1 asc", con1);
            SqlDataAdapter ada1 = new SqlDataAdapter(cmd1);
            ada1.Fill(dt);
            Repeater1.DataSource = dt;
            Repeater1.DataBind();
            con1.Close();
            con1.Dispose();
        } 
    }

    
   
    
 
   
   
}

This is my output

BB

I need two things from above code

  1. I want output like this as below using my above code

User's image

2)If I Do Field2 Filter For Example AA Select Then It Has To Show Like this Below
mnb

How to do using my code
Thanking You

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,410 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lan Huang-MSFT 28,836 Reputation points Microsoft Vendor
    2023-11-30T06:56:18.3533333+00:00

    Hi @RAVI,

    You can add a footer and then use the footerCallback function to handle the totaling logic. In the drawCallbackfunction, subtotal is calculated and then drawn.

    footerCallback

    footerCallback: function (tfoot, data, start, end, display) {
                        let api = this.api();
                        api.column(2).footer().innerHTML = "Grand total";
                        // Total over all pages
                        total = api
                            .column(3, { search: 'applied' })
                            .data()
                            .reduce((a, b) => intVal(a) + intVal(b), 0);
    
                        // Update footer
    
                        api.column(3).footer().innerHTML = total;
                    },
    

    drawCallback

     "drawCallback": function (settings) {
                        var api = this.api(), data;
                        var rows = api.rows({ page: 'current' }).nodes();                
                        if (sessionStorage.getItem("rowCount") == rows.length) {
                            var last = null;
                            total = new Array();
                            api.column(0, { page: 'current' }).data().each(function (group, i) {
                                group_assoc = group.replace(' ', "_");
                                if (typeof total[group_assoc] != 'undefined') {
                                    total[group_assoc] = total[group_assoc] + intVal(api.column(3).data()[i]);
                                } else {
                                    total[group_assoc] = intVal(api.column(3).data()[i]);
                                }
                                if (last !== group) {
                                    $(rows).eq(i).before(
                                        '<tr class="group"><td colspan="3"; style="font-weight: bold;background:forestgreen; text-align: right;border: 1px solid black">' + "Totol(" + group + ")" + '</td><td style="font-weight: bold;background:forestgreen" class="' + group_assoc + '"></td></tr>'
                                    );
    
                                    last = group;
                                }
                            });
                            for (var key in total) {
                                $("." + key).html(total[key]);
                            }
                        }
                    }, 
    

    All code

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>Untitled Page</title>
        <script type="text/javascript" src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.7/css/jquery.dataTables.min.css" />
    
    
        <script type="text/javascript">
           
            $(document).ready(function () {
                var rowCount = $('table#list tbody tr').length;
                sessionStorage.setItem("rowCount", rowCount);
               
                // Remove the formatting to get integer data for summation
                var intVal = function (i) {
                    return typeof i === 'string' ?
                        i.replace(/[\$,]/g, '') * 1 :
                        typeof i === 'number' ?
                            i : 0;
                };
                $('#list').DataTable({
    
                    lengthMenu: [
                        [1000, 500, 200, -1],
                        [1000, 500, 250, 'All'],
                    ],
                    "drawCallback": function (settings) {
                        var api = this.api(), data;
                        var rows = api.rows({ page: 'current' }).nodes();                
                        if (sessionStorage.getItem("rowCount") == rows.length) {
                            var last = null;
                            total = new Array();
                            api.column(0, { page: 'current' }).data().each(function (group, i) {
                                group_assoc = group.replace(' ', "_");
                                if (typeof total[group_assoc] != 'undefined') {
                                    total[group_assoc] = total[group_assoc] + intVal(api.column(3).data()[i]);
                                } else {
                                    total[group_assoc] = intVal(api.column(3).data()[i]);
                                }
                                if (last !== group) {
                                    $(rows).eq(i).before(
                                        '<tr class="group"><td colspan="3"; style="font-weight: bold;background:forestgreen; text-align: right;border: 1px solid black">' + "Totol(" + group + ")" + '</td><td style="font-weight: bold;background:forestgreen" class="' + group_assoc + '"></td></tr>'
                                    );
    
                                    last = group;
                                }
                            });
                            for (var key in total) {
                                $("." + key).html(total[key]);
                            }
                        }
                    },               
                    footerCallback: function (tfoot, data, start, end, display) {
                        let api = this.api();
                        api.column(2).footer().innerHTML = "Grand total";
                        // Total over all pages
                        total = api
                            .column(3, { search: 'applied' })
                            .data()
                            .reduce((a, b) => intVal(a) + intVal(b), 0);
    
                        // Update footer
    
                        api.column(3).footer().innerHTML = total;
                    },
                    initComplete: function () {
                        this.api().columns("0").every(function () {
                            var column = this;
                            var select = $('<select STYLE="width:150px"><option value=""></option></select>')
                                .appendTo($("#Span1").empty())
                                .on('change', function () {
                                    var val = $.fn.dataTable.util.escapeRegex(
                                        $(this).val()
                                    );
    
                                    column
                                        .search(val ? '^' + val + '$' : '', true, true)
                                        .draw();
                                });
    
                            column.data().unique().sort().each(function (d, j) {
                                select.append('<option value="' + d + '">' + d + '</option>')
                            });
                        });
    
    
                        this.api().columns("1").every(function () {
                            var column = this;
                            var select = $('<select STYLE="width:150px"><option value=""></option></select>')
                                .appendTo($("#Span2").empty())
                                .on('change', function () {
                                    var val = $.fn.dataTable.util.escapeRegex(
                                        $(this).val()
                                    );
    
                                    column
                                        .search(val ? '^' + val + '$' : '', true, true)
                                        .draw();
                                });
    
                            column.data().unique().sort().each(function (d, j) {
                                select.append('<option value="' + d + '">' + d + '</option>')
                            });
                        });
                        //           next
    
                    }
                });
            });
    
        </script>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                FROM DATE
            <asp:TextBox ID="TextBox1" runat="server" Width="131px">25-NOV-2023</asp:TextBox>
                TO DATE
            <asp:TextBox ID="TextBox2" runat="server" Width="131px">30-NOV-2023</asp:TextBox>&nbsp;<br />
                <table>
                    <tr>
                        <td style="width: 100px; height: 31px;"><span style="color: darkcyan; font-family: Calibri"><strong>Field1</strong></span></td>
                        <td style="width: 100px; height: 31px;"><span style="color: darkcyan; font-family: Calibri"><strong>Field2</strong></span></td>
                    </tr>
                    <tr>
                        <td style="width: 100px; font-size: 12px; font-weight: bold; font-family: Calibri; color: Black;"><span id="Span1"></span></td>
                        <td style="width: 100px; font-size: 12px; font-weight: bold; font-family: Calibri; color: Black;"><span id="Span2"></span></td>
                    </tr>
                </table>
                <div class="e_list">
                    <asp:Panel runat="server" ID="Panel1">
                        <table cellspacing="0" class="myClass" id="list" style="width: 500px; font-family: Calibri; font-size: 11px; border-collapse: collapse; border: 1px solid black;">
                            <thead>
                                <tr>
                                    <td style="border-collapse: collapse; border: 1px solid black; background: #FAC794;">Field1</td>
                                    <td style="border-collapse: collapse; border: 1px solid black; background: #FAC794;">Field2</td>
                                    <td style="border-collapse: collapse; border: 1px solid black; background: #FAC794;">Field3</td>
                                    <td style="border-collapse: collapse; border: 1px solid black; background: #FAC794;">Field4</td>
                                </tr>
                            </thead>
                            <tbody style="border-collapse: collapse; border: 1px solid black;">
                                <asp:Repeater ID="Repeater1" runat="server">
                                    <ItemTemplate>
                                        <tr>
                                            <td style="border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("Field1")%></td>
                                            <td style="border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("Field2")%></td>
                                            <td style="border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("Field3")%></td>
                                            <td style="border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("Field4")%></td>
                                        </tr>
                                    </ItemTemplate>
                                </asp:Repeater>
                            </tbody>
                            <tfoot>
                                <tr>
                                    <td></td>
    
                                    <td></td>
    
                                    <td style="border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;"></td>
                                    <td style="border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;"></td>
                                </tr>
                            </tfoot>
                        </table>
                    </asp:Panel>
    
                </div>
    
    
            </div>
        </form>
    </body>
    </html>
    

    4

    Best regards,
    Lan Huang


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful