Net-informations.com
SiteMap  | About    

SubTotal and GrandTotal in GridView

In the following lesson, you can learn how to implement a grouping and calculate subtotal for each group and GrandTotal for all groups in ASP.Net GridView.

subtotal and grandtotal in gridview

Database

In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.

Download

A subtotal is a total of the subgroup values and a grand total is a total of all calculations on a report. Here we retrieves data from the STOR table of PUBS databae and find the subtotal of quantities from each store and finally find the GrandTotal of quantity from all stores.

For displaying quantity on each row we insert an ItemTemplate for quantity field.

subtotal and grandtotal

For displaying GrandTotal at the footer, we insert a FooterTemplate at thebottom of the GridView.

 grandtotal footer

And for displaying subtotal , the program dynamically add a new row after each group in the Gridview.

Default.aspx

  

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
	<title></title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
	<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="true"
	onrowdatabound="GridView1_RowDataBound"  onrowcreated="GridView1_RowCreated">
		<Columns>
		  <asp:BoundField DataField="stor_id" HeaderText="stor_id" />
		  <asp:BoundField DataField="ord_num" HeaderText="ord_num" />
		  <asp:BoundField DataField="title_id" HeaderText="title_id" />
		  <asp:TemplateField HeaderText="Quantity" ItemStyle-HorizontalAlign="Right">
			 <ItemTemplate>
				<asp:Label ID="lblqty" runat="server" Text='<%# Eval("qty") %>' />
			 </ItemTemplate>
			 <FooterTemplate>
				<div style="text-align: right;">
				<asp:Label ID="lblTotalqty" runat="server"  Font-Bold=true  />
				</div>
			 </FooterTemplate>
		  </asp:TemplateField>
		</Columns>
	</asp:GridView>
	</div>
	</form>
</body>
</html>

C# Source Code

  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
    int qtyTotal = 0;
    int grQtyTotal = 0;
    int storid = 0;
    int rowIndex = 1;
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        DataSet ds = new DataSet();
        int i = 0;
        string sql = null;
        string connetionString = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****";
        sql = "select  distinct top 14 stor_id,ord_num,title_id,qty from sales group by stor_id,ord_num,title_id,qty";
        SqlConnection connection = new SqlConnection(connetionString);
        connection.Open();
        SqlCommand command = new SqlCommand(sql, connection);
        adapter.SelectCommand = command;
        adapter.Fill(ds);
        adapter.Dispose();
        command.Dispose();
        connection.Close();
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString());
            int tmpTotal = Convert.ToInt32 (DataBinder.Eval(e.Row.DataItem, "qty").ToString());
            qtyTotal += tmpTotal;
            grQtyTotal += tmpTotal;
        }
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            Label lblTotalqty = (Label)e.Row.FindControl("lblTotalqty");
            lblTotalqty.Text = grQtyTotal.ToString();
        }
    }
    protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        bool newRow = false;
        if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "stor_id") != null))
        {
            if (storid != Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString()))
                newRow = true;
        }
        if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "stor_id") == null))
        {
            newRow = true;
            rowIndex = 0;
        }
        if (newRow)
        {
            GridView GridView1 = (GridView)sender;
            GridViewRow NewTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
            NewTotalRow.Font.Bold = true;
            NewTotalRow.BackColor = System.Drawing.Color.Gray;
            NewTotalRow.ForeColor = System.Drawing.Color.White ;
            TableCell HeaderCell = new TableCell();
            HeaderCell.Text = "Sub Total";
            HeaderCell.HorizontalAlign = HorizontalAlign.Left;
            HeaderCell.ColumnSpan = 3;
            NewTotalRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.Text = qtyTotal.ToString();
            NewTotalRow.Cells.Add(HeaderCell);
            GridView1.Controls[0].Controls.AddAt(e.Row.RowIndex + rowIndex, NewTotalRow);
            rowIndex++;
            qtyTotal = 0;
        }
    }
}

VB.Net Source Code

  

Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Dim qtyTotal As Integer = 0
    Dim grQtyTotal As Integer = 0
    Dim storid As Integer = 0
    Dim rowIndex As Integer = 1
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim adapter As New SqlDataAdapter()
        Dim ds As New DataSet()
        Dim i As Integer = 0
        Dim sql As String = Nothing
        Dim connetionString As String = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****"
        sql = "select  distinct top 14 stor_id,ord_num,title_id,qty from sales group by stor_id,ord_num,title_id,qty"
        Dim connection As New SqlConnection(connetionString)
        connection.Open()
        Dim command As New SqlCommand(sql, connection)
        adapter.SelectCommand = command
        adapter.Fill(ds)
        adapter.Dispose()
        command.Dispose()
        connection.Close()
        GridView1.DataSource = ds.Tables(0)
        GridView1.DataBind()
    End Sub
    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString())
            Dim tmpTotal As Integer = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "qty").ToString())
            qtyTotal += tmpTotal
            grQtyTotal += tmpTotal
        End If
        If e.Row.RowType = DataControlRowType.Footer Then
            Dim lblTotalqty As Label = DirectCast(e.Row.FindControl("lblTotalqty"), Label)
            lblTotalqty.Text = grQtyTotal.ToString()
        End If
    End Sub
    Protected Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
        Dim newRow As Boolean = False
        If (storid > 0) AndAlso (DataBinder.Eval(e.Row.DataItem, "stor_id") IsNot Nothing) Then
            If storid <> Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString()) Then
                newRow = True
            End If
        End If
        If (storid > 0) AndAlso (DataBinder.Eval(e.Row.DataItem, "stor_id") Is Nothing) Then
            newRow = True
            rowIndex = 0
        End If
        If newRow Then
            Dim GridView1 As GridView = DirectCast(sender, GridView)
            Dim NewTotalRow As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)
            NewTotalRow.Font.Bold = True
            NewTotalRow.BackColor = System.Drawing.Color.Gray
            NewTotalRow.ForeColor = System.Drawing.Color.White
            Dim HeaderCell As New TableCell()
            HeaderCell.Text = "Sub Total"
            HeaderCell.HorizontalAlign = HorizontalAlign.Left
            HeaderCell.ColumnSpan = 3
            NewTotalRow.Cells.Add(HeaderCell)
            HeaderCell = New TableCell()
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.Text = qtyTotal.ToString()
            NewTotalRow.Cells.Add(HeaderCell)
            GridView1.Controls(0).Controls.AddAt(e.Row.RowIndex + rowIndex, NewTotalRow)
            rowIndex += 1
            qtyTotal = 0
        End If
    End Sub
End Class






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.