Calculate Subtotal And Grand Total In GridView

In the following lesson, you can learn how to implement each group calculation as Subtotal and Grand Total for all groups in ASP.Net GridView.

I have used Microsoft’s Pubs database for sample data. A Subtotal is a total of the subgroup values and a Grand Total is a total of all calculations on a report. Here we retrieve data from the STOR table of the PUBS database and find the subtotal of quantities from each store and finally find the Grand Total of quantity from all stores. For displaying quantity on each row we insert an ItemTemplate for the quantity field.


<ItemTemplate>
        <asp:Label ID="lblqty" runat="server" Text='<%# Eval("qty")%>' />
</ItemTemplate>

<asp:Label ID="lblqty" runat="server" Text='<%# Eval("qty")%>' />

</ItemTemplate>

For displaying Grand Total at the footer, we insert a FooterTemplate at the bottom of the GridView.


<FooterTemplate>
     <div style="text-align: right;">
           <asp:Label ID="lblTotalqty" runat="server" Font-Bold="true" />
     </div>
</FooterTemplate>

<asp:Label ID="lblTotalqty" runat="server" Font-Bold="true" />

</div>

</FooterTemplate>

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

To calculate subtotal and grand total in GridView, you can use the following code for the design view:


<!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>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="true"

onrowdatabound="GridView1_RowDataBound" onrowcreated="GridView1_RowCreated">

<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">

<asp:Label ID="lblqty" runat="server" Text='<%# Eval("qty")%>' />

</ItemTemplate>

<asp:Label ID="lblTotalqty" runat="server" Font-Bold="true" />

</div>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

To calculate subtotal and grand total in GridView, you can use the following code for the c# 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 &gt; 0) &amp;&amp; (DataBinder.Eval(e.Row.DataItem, "stor_id") != null))
        {
            if (storid != Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString()))
                newRow = true;
        }
        if ((storid &gt; 0) &amp;&amp; (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;
        }
    }
}

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;

}

Subtotal and Grand Total in GridView

The article was published on August 20, 2014 @ 10:36 AM

How to Calculate Subtotal and Grand Total in GridView

Subtotal and Grand Total in GridView

Leave a Comment X

How to Calculate Subtotal and Grand Total in GridView

Subtotal and Grand Total in GridView

You may also like

Leave a Comment X