• How to display Running Total in GridView Footer

    Posted on July 10, 2012 by in ASP.NET, C#, Dotnet

    In this article I am going to explain how to display running total in GridView Footer Row. This kind of ability is needed especially when we show report kind of information to the end-user.

    Overview:
    I am using Adventure Works as our datasource. In below example, we are going to fetch order details from the database and show them on the grid. On each page we show the sum of order amounts of all orders on that page along with the complete total of all the orders.

    Database Connection
    Added following entry under connectionStrings element in web.config.

    <add name="Sql" connectionString="Data Source=(local); Initial Catalog=AdventureWorks; User=testuser; Password=testuser;" providerName="System.Data.SqlClient"/>
    

    Page Design
    I have created a basic ASP.NET web application project and added a new web page “GridViewTotalExample.aspx.”.  I have added the Gridview control to the web page and applied some styles for header & item controls. We are interested in SalesOrderDetailID, OrderDate,  Name, OrderQty, UnitPrice and LineTotal attributes of the product. I have added 5 BoundColumns to the GridView to render these attributes. Since  UnitPrice  & ListPrice are decimal types, I have used custom format string to render them as currency values.  I have enabled default paging and set the page size to 10. I have also set ShowFooter property to true, so that a default footer row will be added to the grid. On the default footer row, we show grand total and for displaying running total we create a new footer grdViewOrders_RowCreated routine.

    <asp:GridView ID="gvOrders" AutoGenerateColumns="False"  CssClass="GridStyle"
        PageSize="10" AllowPaging="True"
        runat="server" DataKeyNames="SalesOrderDetailID"  ShowFooter="true"
        OnPageIndexChanging="gvOrders_PageIndexChanging"
        OnRowDataBound="gvOrders_RowDataBound"
        OnRowCreated="grdViewOrders_RowCreated"    
        PagerStyle-CssClass="pgr">   
        <RowStyle CssClass="GridRowStyle"/>
        <AlternatingRowStyle CssClass="AlternateRowStyle" />
        <HeaderStyle CssClass="HeaderStyle" />
        <FooterStyle CssClass="GridFooterStyle" />    
        <Columns>
            <asp:BoundField DataField="SalesOrderDetailID" HeaderText="Order Detail ID" />
            <asp:BoundField DataField="OrderDate" HeaderText="Order Date" DataFormatString="{0:D}" />
            <asp:BoundField DataField="Name" HeaderText="Product Name" />
            <asp:BoundField DataField="OrderQty" HeaderText="Quantity" ItemStyle-HorizontalAlign="Right"/>            
            <asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" DataFormatString="{0:C2}" ItemStyle-HorizontalAlign="Right"/>
            <asp:BoundField DataField="LineTotal" HeaderText="Line Total" DataFormatString="{0:C2}" ItemStyle-HorizontalAlign="Right"/>        
        </Columns>  
    </asp:GridView>
    

    Source Code:
    Following are the namespaces using in the code.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;
    using System.Data;
    using System.IO;
    using System.Configuration;
    using System.Data.SqlClient;
    

    I am using LINQ to query dataset. So I have added reference to System.Data.DataSetExtensions.dll in the project. I have added following class variables were created in the code-behind file.

    //class variable to store sum of order amounts of all orders on that page
    private double dblRunningTotal;
    //class variable to store sum of order amounts of all orders on all pages
    private double dblTotalAmount;
    //class variable to store order amount
    private double lineTotal;
    

    In the Page_Load function, we get the data by calling RetrieveOrders function and bind it to the gridview. In the RetrieveOrders function  I am using tradional ADO.NET objects (SQLConnection, SQLCommand & SQLAdapter) objects to get  orders  from the database. Once we fill the orders dataset, we use LINQ API to query the dataset to fetch the sum of all the order amounts. We store this value in the viewstate as we need it when we navigate from page to page.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindData();
        }
        else
        {
            //if this is a post back, we might have already saved grandtotal in the viewstate
            //just fetch that directly from viewstate
            dblTotalAmount = Convert.ToDouble(ViewState["GrandTotal"]);
        }
    }
    
    private void BindData()
    {
        gvOrders.DataSource = RetrieveOrders();
        gvOrders.DataBind();
    }
    
    private DataSet RetrieveOrders()
    {
        //if the data is already stored in the view state, use that instead of
        //makin another call to database.
        if (ViewState["Orders"] != null)
            return (DataSet)ViewState["Orders"];
    
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    
        //SQL statement to fetch order entries            
        string sql = @"SELECT TOP 30 SOD.SalesOrderDetailID, SOH.OrderDate, P.Name, SOD.OrderQty, 
                        SOD.UnitPrice, SOD.LineTotal
                        FROM Sales.SalesOrderDetail SOD 
                        INNER JOIN Sales.SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderID
                        INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID
                        WHERE OrderQty > 1";
    
        DataSet dsOrders = new DataSet();
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dsOrders);
            }
        }
    
        //sum up the total order amount and store it in dblTotalAmount
        dblTotalAmount  = Convert.ToDouble(dsOrders.Tables[0].AsEnumerable().Sum(x => x.Field<Decimal>("LineTotal")));
    
        //save the total order amount in the view state
        ViewState["GrandTotal"] = dblTotalAmount;
    
        //save the data into viewstate which could be used for subsequent postbacks
        ViewState["Orders"] = dsOrders;
    
        return dsOrders;
    }
    

    When the GridView is bound to a data source, it creates a GridViewRow for each record returned by the data source and raises DataBound event. In our gvOrders_RowDataBound event handler routine, we calculate the running order amount total. We have created a class variable dblRunningTotal which will be incremented with linetotal dataitem from each gridviewrow on that page.

    protected void gvOrders_RowDataBound(object sender, GridViewRowEventArgs e)
    {            
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //keep on adding line totals to the dblRunningTotal which is the 
            //total of all line items on that page
            lineTotal = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "LineTotal"));
            dblRunningTotal = dblRunningTotal + lineTotal;
        }            
    }
    

    Before the GridView control can be rendered, a GridViewRow object must be created for each row in the control. The RowCreated event is raised when each row in the GridView control is created. This enables you to provide an event-handling method that performs a custom routine, such as adding custom content to a row, whenever this event occurs.  In this routine, we create a new running total row and also update the footer row with grand total values.

    protected void grdViewOrders_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            //calculate colspan. we want to display totals in the last column 
            //and label in the last but one column. since we dont want to deal 
            //with the borders and cell widths we simply merge remaining columns
            int labelTextCellColsSpan = e.Row.Cells.Count - 1;
    
            //create a new row. this is our footer row
            GridViewRow runningTotalRow = new GridViewRow(0, 0, 
                DataControlRowType.Footer, DataControlRowState.Normal);
            
            //create running Total label text
            TableCell cell = new TableCell();
            cell.Text = "Running Total : ";
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.ColumnSpan = labelTextCellColsSpan;
    
            //add the label text cell to the row
            runningTotalRow.Cells.Add(cell);
    
            //create running total amount cell
            cell = new TableCell();              
            cell.Text = dblRunningTotal.ToString("C");
            cell.HorizontalAlign = HorizontalAlign.Right;
    
            //add the amount cell to the row
            runningTotalRow.Cells.Add(cell);
    
            //add the row to the gridview
            gvOrders.Controls[0].Controls.Add(runningTotalRow);
            
            //we only want two cells, hide the rest of them                 
            for (int colIndex = 2; colIndex <= labelTextCellColsSpan; colIndex++)
                e.Row.Cells[colIndex].Visible = false;
            
            //set the grant total text
            e.Row.Cells[0].ColumnSpan = labelTextCellColsSpan;
            e.Row.Cells[0].Text = "Grand Total : ";
            e.Row.Cells[0].HorizontalAlign = HorizontalAlign.Right;                
    
            //set the grant total amount value
            e.Row.Cells[1].Text = dblTotalAmount.ToString("C");
            e.Row.Cells[1].HorizontalAlign = HorizontalAlign.Right;
        }
    }
    

    When user clicks one of the pager buttons is clicked, PageIndexChanging event is raised. We have to implement this event handler. Since it occurs before the GridView control handles the paging operation, we need to set the new page index to the grid and rebind the data as it is a postback.

    protected void gvOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        //set the new page index
        gvOrders.PageIndex = e.NewPageIndex;
        //rebind data
        BindData();
    }
    

    If you run the application now, you will see the following page with Running Total & Grand Total fields in the footer.
    running total in gridview footer 1

    Click on page # 2 and you will notice that the running total value is changed and grand total will be the same.
    running total in gridview footer 2

    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply