Cumulative Total in GridView

Tweet

In previous posts, we had seen how to show Running Total and Grand Total in Grid View. In this post, I am planning to show how to show the Cumulative Total in Grid View.

The basic requirement in this implementation is as follows:

1. There should be a GridView which shows list of Order information from Northwind database with paggination.
2. The GridView should also have a column Cumulative Amount at the last which is a calculated field using the formula Cumulative Amount = Previous Row Cumulative Field + Current Row Amount Field. For the First row, the Amount field becomes Cumulative Field Amount.
   
   
   For more understanding about calculated (formula) field, please refer the post Formula Fields in GridView.
3. When the GridView shows next pagination, the cumulative total amount must be considering previous pages amount also.

GridView With & Without pagination (CumulativeTotal1.aspx):

The ASPX file

<asp:GridView ID="grdViewOrders" runat="server"
    AllowPaging="true" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="ProductID" Width="100%"
    CellPadding="4" ForeColor="#333333"
    onpageindexchanging="grdViewOrders_PageIndexChanging" 
    onrowdatabound="grdViewOrders_RowDataBound">
    <Columns>
        <asp:BoundField DataField="OrderID" HeaderText="Order ID" />
        <asp:BoundField DataField="OrderDate" HeaderText="Order Date" DataFormatString="{0:dd-MMMM-yyyy}" />
        <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" />
        <asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" DataFormatString="{0:C}" >
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:BoundField DataField="Quantity" HeaderText="Quantity" >
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:BoundField DataField="Discount" HeaderText="Discount" DataFormatString="{0:C}" >
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:BoundField DataField="Amount" HeaderText="Amount" DataFormatString="{0:C}" >
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:TemplateField HeaderText="Cumulative Amount">
            <ItemTemplate>
                <asp:Label runat="server" ID="lblRunningAmount" Text=""></asp:Label>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Right" />
            <FooterStyle HorizontalAlign="Right" />
        </asp:TemplateField>
    </Columns>
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
</asp:GridView>

The C# Code behind code

public partial class CumulativeTotal1 : System.Web.UI.Page
{
    // Variable for Cumulative Amount
    private double dblCumulativeAmount = 0;
    private bool IsPreviousPageTotalCalculated = false;

    IList<OrderView> orderViewList = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindGrid();
        }
    }
    /// <summary>
    /// Method which binds the data to the Grid
    /// </summary>
    private void BindGrid()
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
        {

            SqlCommand command = new SqlCommand(
                    "SELECT Orders.OrderID, Orders.OrderDate, Products.ProductName, Products.QuantityPerUnit, " +
                    "OrderDetails.ProductID,  OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount " +
                    "FROM Orders JOIN [Order Details] OrderDetails ON Orders.OrderID = OrderDetails.OrderID " +
                    "JOIN Products ON OrderDetails.ProductID = Products.ProductID " +
                    "ORDER BY Orders.OrderID, OrderDetails.ProductID", connection);

            connection.Open();
            SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

            orderViewList = new List<OrderView>();
            while (dr.Read())
            {
                OrderView orderView = new OrderView();
                orderView.OrderID = Convert.ToInt32(dr["OrderID"].ToString());
                orderView.OrderDate = Convert.ToDateTime(dr["OrderDate"].ToString());
                orderView.ProductName = dr["ProductName"].ToString();
                orderView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
                orderView.ProductID = Convert.ToInt32(dr["ProductID"].ToString());
                orderView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());
                orderView.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
                orderView.Discount = Convert.ToDouble(dr["Discount"].ToString());
                orderView.Amount = Convert.ToDouble(dr["UnitPrice"].ToString()) * Convert.ToDouble(dr["Quantity"].ToString()) - Convert.ToDouble(dr["Discount"].ToString());

                orderViewList.Add(orderView);
            }
            grdViewOrders.DataSource = orderViewList;
            grdViewOrders.DataBind();
        }
    }

    protected void grdViewOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grdViewOrders.PageIndex = e.NewPageIndex;
        BindGrid();
    }

    protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            // This code runs when the data rows getting bound. 
            // So we can calculate the cumulative total and keep to the temp variable declared in the class
            OrderView orderView = null;
            if ((IsPreviousPageTotalCalculated == false) && (grdViewOrders.PageIndex != 0))
            {
                // If Paggination shows in the GridView, this code calculates the previous page amount values.
                for (int intRow = 0; intRow < grdViewOrders.PageSize * grdViewOrders.PageIndex; intRow++)
                {
                    // Getting the order of intRow-th Row
                    orderView = (OrderView)orderViewList[intRow];

                    // Calculating cumuative amount of previous row
                    dblCumulativeAmount = dblCumulativeAmount + orderView.Amount;
                }
                IsPreviousPageTotalCalculated = true;
            }

            // Get the Row Amount
            orderView = (OrderView)e.Row.DataItem;

            // Each Row Amount values are added to the footer Amount variable
            dblCumulativeAmount = dblCumulativeAmount + orderView.Amount;

            Label lblRunningAmount = (Label)e.Row.FindControl("lblRunningAmount");
            lblRunningAmount.Text = dblCumulativeAmount.ToString("C");
        }
    }
}

The OrderView entity class

public class OrderView
{
    public int OrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public string ProductName { get; set; }
    public string QuantityPerUnit { get; set; }
    public int ProductID { get; set; }
    public double UnitPrice { get; set; }
    public int Quantity { get; set; }
    public double Discount { get; set; }
    public double Amount { get; set; }
}

The output of this code

GridView without pagination (CumulativeTotal.aspx)

The previous example will work perfectly for pagination and non-pagination GridView. But, if the requirement is to only show without pagination, the code looks very simple.

Remove the IsPreviousPageTotalCalculated variable declaration and change the RowDataBound event.

public partial class CumulativeTotal1 : System.Web.UI.Page
{
    // Variable for Cumulative Amount
    private double dblCumulativeAmount = 0;

    IList orderViewList = null;
}
protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // This code runs when the data rows getting bound. 
        // So we can calculate the cumulative total and keep to the temp variable declared in the class

        // Get the Row Amount
        OrderView orderView = (OrderView)e.Row.DataItem;

        // Each Row Amount values are added to the footer Amount variable
        dblCumulativeAmount = dblCumulativeAmount + orderView.Amount;

        Label lblRunningAmount = (Label)e.Row.FindControl("lblRunningAmount");
        lblRunningAmount.Text = dblCumulativeAmount.ToString("C");
    }
}

The output will be

GridView binding with DataTable (CumulativeTotal2.aspx)
In previous examples, we had used OrderView entity object for representing each records and an IList for binding to the GridView. But, some developer use DataTable, DataReader etc., to bind the records. In those situation, we required some modifications in the code to get the Amount for calculation.

Below is the code when I used DataTable for binding the records to the GridView.

private void BindGrid()
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
    {

        SqlCommand command = new SqlCommand(
                "SELECT Orders.OrderID, Orders.OrderDate, Products.ProductName, Products.QuantityPerUnit, " +
                "OrderDetails.ProductID,  OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount " +
                "FROM Orders JOIN [Order Details] OrderDetails ON Orders.OrderID = OrderDetails.OrderID " +
                "JOIN Products ON OrderDetails.ProductID = Products.ProductID " +
                "ORDER BY Orders.OrderID, OrderDetails.ProductID", connection);

        SqlDataAdapter adptaptor = new SqlDataAdapter(command);
        products = new DataTable("Products");
        adptaptor.Fill(products);
        grdViewOrders.DataSource = products;
        grdViewOrders.DataBind();
    }
}
protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        // Get the Amount control
        Label lblAmount = (Label)e.Row.FindControl("lblAmount");

        // Get the Row Amount
        Array arrOrderView = ((DataRowView)e.Row.DataItem).Row.ItemArray; // Getting the entity which bound for this row
        double dblAmount = Convert.ToDouble(arrOrderView.GetValue(5)) * Convert.ToDouble(arrOrderView.GetValue(6)) - Convert.ToDouble(arrOrderView.GetValue(7));
        lblAmount.Text = dblAmount.ToString("C");
        // Index Orders.UnitPrice = 5
        // Index Orders.Quantity = 6
        // Index Orders.Discount = 7

        // This code runs when the data rows getting bound. 
        // So we can calculate the cumulative total and keep to the temp variable declared in the class
        if ((IsPreviousPageTotalCalculated == false) && (grdViewOrders.PageIndex != 0))
        {
            // If Paggination shows in the GridView, this code calculates the previous page amount values.
            for (int intRow = 0; intRow < grdViewOrders.PageSize * grdViewOrders.PageIndex; intRow++)
            {
                arrOrderView = products.Rows[intRow].ItemArray;
                dblAmount = Convert.ToDouble(arrOrderView.GetValue(5)) * Convert.ToDouble(arrOrderView.GetValue(6)) - Convert.ToDouble(arrOrderView.GetValue(7));

                // Calculating cumuative amount of previous row
                dblCumulativeAmount = dblCumulativeAmount + dblAmount;
            }
            IsPreviousPageTotalCalculated = true;
        }

        // Each Row Amount values are added to the footer Amount variable
        dblCumulativeAmount = dblCumulativeAmount + dblAmount;

        Label lblRunningAmount = (Label)e.Row.FindControl("lblRunningAmount");
        lblRunningAmount.Text = dblCumulativeAmount.ToString("C");
    }
}

All other code are same as defined in CumulativeTotal1.aspx page.

download the working example of the source code in C# here and in VB here.