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.
Database
In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.
DownloadIn reporting and analysis, subtotals and grand totals are essential elements that provide comprehensive insights into aggregated data. A subtotal represents the sum of values within a subgroup, typically grouped by a specific criterion, while a grand total encompasses the sum of all calculations within the entire report.
Calculate the GrandTotal
As of now, we are serving a selective query from the STOR table of PUBS database to find out the product quantity subtotal in the store-wise manner. Achieving results of this sort is possible through combining the data provided per store into their groups using the right SQL queries, generating final subtotals by store quantity.
Additionally, we aim to calculate the GrandTotal, which entails summing up the quantities from all stores in the report. This involves performing calculations on the entire dataset retrieved from the STOR table, disregarding any specific subgrouping criteria.
For displaying quantity on each row we insert an ItemTemplate for quantity field.
For displaying GrandTotal at the footer, we insert a FooterTemplate at thebottom of the GridView.
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
Related Topics
- ASP.NET Simple GridView
- Sorting , Paging and AutoGenerateColumns
- ASP.NET GridView Editing
- ASP.NET GridView Delete
- DropDownList in GridView
- Create Gridview at runtime
- Asp.Net Gridview - Add, Edit and delete
- Gridview export to Excel
- Gridview export to CSV
- Asp.Net GridView summary on Footer
- Subtotal row in Gridview
- Create Gridview without database
- GridView from Stored Procedure
- How to create Gridview Popup Form
- How to create a Blank row in gridview
- Nested GridView in ASP.NET
- Freeze Gridview Header row, Scroll in GridView
- Auto Generate Row Number in GridView
- Image between GridView rows
- How to select a row in gridview
- Checkbox in ASP.NET GridView Control