Show Grand Total Running Total Sub Total in GridView Footer C# VB.Net

In many scenarios we need to display Running Total, Sub Total as well as Grand Total in GridView Footer. In this post i will try to explain in an easy way that how we can display running total & Grand total in a GridView footer combindly. To explain this solution here I have designed an example based on a sales order report. The report contains all sales order amount in a tabular way. I will use a GridView to display sales order amount and in GridView footer I will display Running Total & Grand Total. Now our goal is to show all customers order with amount as well as page wise Running Total & Grand Total. Note that running total is necessary when you enable paging in a GridView where as Grand total is always you can consider.

Show Grand Total Running Total Sub Total in GridView Footer CSharp VB.Net

To do the Example First Create a Database Table:

CREATE TABLE [dbo].[SalesOrder](
	[OrderID] [bigint] NOT NULL,
	[Name] [varchar](max) NULL,
	[Date] [smalldatetime] NULL,
	[Amount] [numeric](18, 2) NULL,
	[OrderID] ASC

Now Insert Some Data:

INSERT INTO SalesOrder VALUES(1,'Shawpnendu',GETDATE()-10,353.82)
INSERT INTO SalesOrder VALUES(2,'Bimalandu',GETDATE()-9,110.35)
INSERT INTO SalesOrder VALUES(3,'Purnendu',GETDATE()-8,94.00)
INSERT INTO SalesOrder VALUES(4,'Kishore',GETDATE()-7,555.13)
INSERT INTO SalesOrder VALUES(5,'Zabbar',GETDATE()-6,231.23)
INSERT INTO SalesOrder VALUES(6,'Rohel',GETDATE()-5,783.77)
INSERT INTO SalesOrder VALUES(7,'Aslam',GETDATE()-4,456.34)
INSERT INTO SalesOrder VALUES(8,'Krishnendu',GETDATE()-3,987.12)
INSERT INTO SalesOrder VALUES(9,'Andrew Samsung',GETDATE()-2,16.28)

Now modify the Web.Config File to Connect to Database:

    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now design the GridView:

<asp:GridView runat="server" ID="gvEdit" AllowPaging="true" PageSize="5" ShowFooter="true" OnPageIndexChanging="gvEdit_PageIndexChanging" OnRowDataBound="gvEdit_RowDataBound" AutoGenerateColumns="false" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
        <asp:BoundField DataField="ORDERID" HeaderText="Order Code">
        <asp:BoundField DataField="Name" HeaderText="Name">

        <asp:TemplateField HeaderText="Date" FooterStyle-BorderWidth="1px" FooterStyle-BorderColor="maroon">
        <asp:Label ID="lblDate" runat="server" Text='<%# Convert.ToDateTime(Eval("Date")).ToString("dd-MM-yy")%>'></asp:Label>
        <asp:Label ID="lbltxtRTotal" runat="server" Font-Bold="true" ForeColor="Magenta">Running Total:</asp:Label><br />
        <asp:Label ID="Label1" runat="server" Font-Bold="true" ForeColor="Green">Grand Total:</asp:Label><br />

        <asp:TemplateField HeaderText="Amount" FooterStyle-BorderWidth="1px" FooterStyle-BorderColor="maroon">
        <asp:Label ID="lblAmount" runat="server" Text='<%# Eval("Amount").ToString()%>'></asp:Label>
        <asp:Label ID="lblRTotal" runat="server" Font-Bold="true" ForeColor="Magenta"></asp:Label><br />
        <asp:Label ID="lblGTotal" runat="server" Font-Bold="true" ForeColor="Green"></asp:Label>

Now Bind Some Data into the GridView:
C# Code:

    decimal RunningTotal = 0;
    protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
            string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(connectionString);
            using (conn)
                SqlDataAdapter ad = new SqlDataAdapter(
                "SELECT OrderID, Name, Date, Amount from SalesOrder", conn);

            decimal GrandTotal = 0;
            foreach (DataRow oRow in dt.Rows)
                GrandTotal += Convert.ToDecimal(oRow["Amount"]);

            ViewState["GrandTotal"] = GrandTotal;

            gvEdit.DataSource = dt;
            ViewState["dt"] = dt;

Note: Don’t forget to add “RunningTotal” variable.

VB.Net Code:

    Private RunningTotal As Decimal = 0
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
            Dim dt As New DataTable()
            Dim conn As New SqlConnection(connectionString)
            Using conn
                Dim ad As New SqlDataAdapter("SELECT OrderID, Name, Date, Amount from SalesOrder", conn)
            End Using

            Dim GrandTotal As Decimal = 0
            For Each oRow As DataRow In dt.Rows
                GrandTotal += Convert.ToDecimal(oRow("Amount"))

            ViewState("GrandTotal") = GrandTotal

            gvEdit.DataSource = dt
            ViewState("dt") = dt
        End If
    End Sub

Note: Don’t forget to add “RunningTotal” variable.

Now Enable Paging:
C# Code:

    protected void gvEdit_PageIndexChanging(object sender, GridViewPageEventArgs e)
        gvEdit.PageIndex = e.NewPageIndex;
        gvEdit.DataSource = (DataTable)ViewState["dt"];

VB.Net Code:

    Protected Sub gvEdit_PageIndexChanging(sender As Object, e As GridViewPageEventArgs)
        gvEdit.PageIndex = e.NewPageIndex
        gvEdit.DataSource = DirectCast(ViewState("dt"), DataTable)
    End Sub

Use GridView RowDataBound Event to calculate Running Total:
C# Code:

    protected void gvEdit_RowDataBound(object sender, GridViewRowEventArgs e)
        if (e.Row.RowType == DataControlRowType.DataRow)
            RunningTotal += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Amount"));

        if (e.Row.RowType == DataControlRowType.Footer)
            ((Label)e.Row.FindControl("lblRTotal")).Text = RunningTotal.ToString();
            ((Label)e.Row.FindControl("lblGTotal")).Text = ViewState["GrandTotal"].ToString();

Note: We have calculated Grand Total in first time under Page_load event.

VB.Net Code:

    Protected Sub gvEdit_RowDataBound(sender As Object, e As GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            RunningTotal += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Amount"))
        End If

        If e.Row.RowType = DataControlRowType.Footer Then
            DirectCast(e.Row.FindControl("lblRTotal"), Label).Text = RunningTotal.ToString()
            DirectCast(e.Row.FindControl("lblGTotal"), Label).Text = ViewState("GrandTotal").ToString()
        End If
    End Sub

Note: We have calculated Grand Total in first time under Page_load event.

Code explanation:
If you look at the page load event where i bind the datasource to the GridView control then i use a viewstate variable to store the retrieved sql server data because i do not go to the database server again when user click on another page of the gridview. At that time i also calculate Grand total value from the datatable & stored it in viewstate which i use in GridView RowDataBound event.

When i assign the datasource property to the GridView control then the RowDataBound event automatically fired. In this event i need to check that does the DataControl Row Type is DataRow or not. If its a DataRow that means i found the DataItem object which will be currently rendered in our page. So by accumulating the amount value will give me the Running Total and Grnad total value we have calculated previously in page load event. So now check the DataControl Row Type for Footer row. If you find the footer row then you knew that in Fotter row we have placed two label named lblRTotal and lblGTotal. Now by using the FindControl method you can catch both labels. Now just put the running total in lblRTotal Text property and Grand total in lblGTotal label Text property. That’s it.

Download Code Example C#        Download Code Example VB.Net

2 thoughts on “Show Grand Total Running Total Sub Total in GridView Footer C# VB.Net

  • January 26, 2015 at 8:58 pm

    Heya are using WordPress google plus account for business your site platform? I’m new to the blog world but I’m trying to get started and create my own. Do you need any coding knowledge to make your own blog? Any help would be really appreciated!


Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code