Tuesday, 24 January 2012

Group Total and Grand Total in GridView - Part 2


Previously I blogged Group Total and Grand Total in Grid View, which has some good example for showing Group Total and Grand Total on records showing on the Grid.

On the same concept, I am planning to extend some more functionality with Group Total and Grand Total on records in Grid View.

Note:
  1. As defined in previous post, to implement these examples; all the records must show in a single page of the Grid View (So, no pagination). Because for calculating the Group Total and Grand Total, the code required all the records must be in loop.
  2. The records must be sorted on the group wise. So all the records related to a particular group will show one after another. So it will be useful for calculating cumulative values together. Keeping records in different group will be considered as it another separate group and cumulative values will be calculated as another separate group.
  3. There was a question in asp.net forums about Totals in GridView (http://forums.asp.net/t/1755649.aspx/1/10?Totals+row+in+GridView). I pointed the previous post as reply and I got couple of nice enhancement on the same example. I am taking the same requirements first here to show with a perfect example and then giving with some additional enhancements.

In the both the examples listed below, I have an XML file defined in the project for binding the records to the Grid View. The XML file is as defined below:


First Requirement:

  1. The records defined in the XML should bound to the Grid View in a normal way.
  2. The records should be grouped by Customer Name and the Group Total should be under each of the Group.
  3. The Grand Total of all the records should be shown after all the records in the Grid.
  4. When each group started, the Customer Name should be shown as the header of each group.
  5. The Group Header, Group Total and Grand Total rows background color must be in predefined style.

In this requirement, the first three points already been implemented in the previous post. We are going to implement fourth requirement additionally.

The XML
<?xml version="1.0" encoding="utf-8" ?>
<Orders>
  <Order OrderID="10643" CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545" ProductID="28" ProductName="Rössle Sauerkraut" UnitPrice="45.60" Quantity="15" Discount="0.25" Amount="683.75"/>
  <Order OrderID="10643" CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545" ProductID="39" ProductName="Chartreuse verte" UnitPrice="18.00" Quantity="21" Discount="0.25" Amount="377.75"/>
  <Order OrderID="10643" CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545" ProductID="46" ProductName="Spegesild" UnitPrice="12.00" Quantity="2" Discount="0.25" Amount="23.75"/>
  <Order OrderID="10692" CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545" ProductID="63" ProductName="Vegie-spread" UnitPrice="43.90" Quantity="20" Discount="0.00" Amount="878.00"/>
  <Order OrderID="10702" CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactTitle="Sales Representative" Address="Obere Str. 5711" City="Berlin" Country="Germany" Phone="030-0074321" Fax="030-0076545" ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10.00" Quantity="6" Discount="0.00" Amount="60.00"/>
  <Order OrderID="10308" CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados" ContactTitle="Owner" Address="Avda. de la Constitución 2222" City="México D.F." Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745" ProductID="69" ProductName="Gudbrandsdalsost" UnitPrice="28.80" Quantity="1" Discount="0.00" Amount="28.80"/>
  <Order OrderID="10308" CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados" ContactTitle="Owner" Address="Avda. de la Constitución 2222" City="México D.F." Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745" ProductID="70" ProductName="Outback Lager" UnitPrice="12.00" Quantity="5" Discount="0.00" Amount="60.00"/>
  <Order OrderID="10926" CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados" ContactTitle="Owner" Address="Avda. de la Constitución 2222" City="México D.F." Country="Mexico" Phone="(5) 555-4729" Fax="(5) 555-3745" ProductID="72" ProductName="Mozzarella di Giovanni" UnitPrice="34.80" Quantity="10" Discount="0.00" Amount="348.00"/>
  <Order OrderID="10355" CustomerID="AROUT" CompanyName="Around the Horn" ContactTitle="Sales Representative" Address="120 Hanover Sq." City="London" Country="UK" Phone="(171) 555-7788" Fax="(171) 555-6750" ProductID="24" ProductName="Guaraná Fantástica" UnitPrice="3.60" Quantity="25" Discount="0.00" Amount="90.00"/>
  <Order OrderID="10953" CustomerID="AROUT" CompanyName="Around the Horn" ContactTitle="Sales Representative" Address="120 Hanover Sq." City="London" Country="UK" Phone="(171) 555-7788" Fax="(171) 555-6750" ProductID="31" ProductName="Gorgonzola Telino" UnitPrice="12.50" Quantity="50" Discount="0.05" Amount="624.95"/>
  <Order OrderID="11016" CustomerID="AROUT" CompanyName="Around the Horn" ContactTitle="Sales Representative" Address="120 Hanover Sq." City="London" Country="UK" Phone="(171) 555-7788" Fax="(171) 555-6750" ProductID="31" ProductName="Gorgonzola Telino" UnitPrice="12.50" Quantity="15" Discount="0.00" Amount="187.50"/>
  <Order OrderID="11016" CustomerID="AROUT" CompanyName="Around the Horn" ContactTitle="Sales Representative" Address="120 Hanover Sq." City="London" Country="UK" Phone="(171) 555-7788" Fax="(171) 555-6750" ProductID="36" ProductName="Inlagd Sill" UnitPrice="19.00" Quantity="16" Discount="0.00" Amount="304.00"/>
  <Order OrderID="10278" CustomerID="BERGS" CompanyName="Berglunds snabbköp" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67" ProductID="44" ProductName="Gula Malacca" UnitPrice="15.50" Quantity="16" Discount="0.00" Amount="248.00"/>
  <Order OrderID="10278" CustomerID="BERGS" CompanyName="Berglunds snabbköp" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67" ProductID="59" ProductName="Raclette Courdavault" UnitPrice="44.00" Quantity="15" Discount="0.00" Amount="660.00"/>
  <Order OrderID="10278" CustomerID="BERGS" CompanyName="Berglunds snabbköp" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67" ProductID="63" ProductName="Vegie-spread" UnitPrice="35.10" Quantity="8" Discount="0.00" Amount="280.80"/>
  <Order OrderID="10278" CustomerID="BERGS" CompanyName="Berglunds snabbköp" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67" ProductID="73" ProductName="Röd Kaviar" UnitPrice="12.00" Quantity="25" Discount="0.00" Amount="300.00"/>
  <Order OrderID="10280" CustomerID="BERGS" CompanyName="Berglunds snabbköp" ContactTitle="Order Administrator" Address="Berguvsvägen  8" City="Luleå" Country="Sweden" Phone="0921-12 34 65" Fax="0921-12 34 67" ProductID="24" ProductName="Guaraná Fantástica" UnitPrice="3.60" Quantity="12" Discount="0.00" Amount="43.20"/>
</Orders>
The ASPX script
<asp:GridView ID="grdViewOrders" runat="server" AutoGenerateColumns="False" TabIndex="1"
    Width="100%" DataSourceID="XmlDataSource1"
    CellPadding="4" ForeColor="Black" GridLines="Vertical"
    OnRowDataBound="grdViewOrders_RowDataBound"
    onrowcreated="grdViewOrders_RowCreated" BackColor="White" 
    BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px">            
    <Columns>
        <asp:BoundField DataField="OrderID" HeaderText="OrderID">
            <ItemStyle HorizontalAlign="Left"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="ProductName" HeaderText="ProductName">
            <ItemStyle HorizontalAlign="Left"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice">
            <ItemStyle HorizontalAlign="Right"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="Quantity" HeaderText="Quantity">
            <ItemStyle HorizontalAlign="Right"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="Discount" HeaderText="Discount">
            <ItemStyle HorizontalAlign="Right"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="Amount" HeaderText="Amount">
            <ItemStyle HorizontalAlign="Right"></ItemStyle>
        </asp:BoundField>
    </Columns>
    <RowStyle BackColor="#F7F7DE" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <FooterStyle BackColor="#CCCC99" />
    <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
    <SelectedRowStyle BackColor="#CE5D5A" ForeColor="White" Font-Bold="True" />
    <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <AlternatingRowStyle BackColor="White" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <SortedAscendingCellStyle BackColor="#FBFBF2" />
    <SortedAscendingHeaderStyle BackColor="#848384" />
    <SortedDescendingCellStyle BackColor="#EAEAD3" />
    <SortedDescendingHeaderStyle BackColor="#575357" />
</asp:GridView>
The C# Code behind
public partial class Example1 : System.Web.UI.Page
{
    // To keep track of the previous row Group Identifier
    string strPreviousRowID = string.Empty;
    // To keep track the Index of Group Total
    int intSubTotalIndex = 1;

    // To temporarily store Sub Total
    double dblSubTotalUnitPrice = 0;
    double dblSubTotalQuantity = 0;
    double dblSubTotalDiscount = 0;
    double dblSubTotalAmount = 0;

    // To temporarily store Grand Total
    double dblGrandTotalUnitPrice = 0;
    double dblGrandTotalQuantity = 0;
    double dblGrandTotalDiscount = 0;
    double dblGrandTotalAmount = 0;

    protected void Page_Load(object sender, EventArgs e) {    }
 
    /// <summary>
    /// Event fires for every row creation
    /// Used for creating SubTotal row when next group starts by adding Group Total at previous row manually
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void grdViewOrders_RowCreated(object sender, GridViewRowEventArgs e)
    {
        bool IsSubTotalRowNeedToAdd = false;
        bool IsGrandTotalRowNeedtoAdd = false;

        if ((strPreviousRowID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null))
            if (strPreviousRowID != DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString())
                IsSubTotalRowNeedToAdd = true;

        if ((strPreviousRowID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") == null))
        {
            IsSubTotalRowNeedToAdd = true;
            IsGrandTotalRowNeedtoAdd = true;
            intSubTotalIndex = 0;
        }

        #region Inserting first Row and populating fist Group Header details
        if ((strPreviousRowID == string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null))
        {
            GridView grdViewOrders = (GridView)sender;

            GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

            TableCell cell = new TableCell();
            cell.Text = "Customer Name : " + DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString();
            cell.ColumnSpan = 6;
            cell.CssClass = "GroupHeaderStyle";
            row.Cells.Add(cell);

            grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
            intSubTotalIndex++;
        }
        #endregion

        if (IsSubTotalRowNeedToAdd)
        {
            #region Adding Sub Total Row
            GridView grdViewOrders = (GridView)sender;

            // Creating a Row
            GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

            //Adding Total Cell 
            TableCell cell = new TableCell();
            cell.Text = "Sub Total";
            cell.HorizontalAlign = HorizontalAlign.Left;
            cell.ColumnSpan = 2;
            cell.CssClass = "SubTotalRowStyle";
            row.Cells.Add(cell);

            //Adding Unit Price Column
            cell = new TableCell();
            cell.Text = string.Format("{0:0.00}", dblSubTotalUnitPrice);
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "SubTotalRowStyle";
            row.Cells.Add(cell);

            //Adding Quantity Column
            cell = new TableCell();
            cell.Text = string.Format("{0:0.00}", dblSubTotalQuantity);
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "SubTotalRowStyle";
            row.Cells.Add(cell);

            //Adding Discount Column
            cell = new TableCell();
            cell.Text = string.Format("{0:0.00}", dblSubTotalDiscount);
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "SubTotalRowStyle";
            row.Cells.Add(cell);

            //Adding Amount Column
            cell = new TableCell();
            cell.Text = string.Format("{0:0.00}", dblSubTotalAmount);
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "SubTotalRowStyle";
            row.Cells.Add(cell);

            //Adding the Row at the RowIndex position in the Grid
            grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
            intSubTotalIndex++;
            #endregion

            #region Adding Next Group Header Details
            if (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null)
            {
                row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

                cell = new TableCell();
                cell.Text = "Customer Name : " + DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString();
                cell.ColumnSpan = 6;
                cell.CssClass = "GroupHeaderStyle";
                row.Cells.Add(cell);

                grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
                intSubTotalIndex++;
            }
            #endregion

            #region Reseting the Sub Total Variables
            dblSubTotalUnitPrice = 0;
            dblSubTotalQuantity = 0;
            dblSubTotalDiscount = 0;
            dblSubTotalAmount = 0;
            #endregion
        }
        if (IsGrandTotalRowNeedtoAdd)
        {
            #region Grand Total Row
            GridView grdViewOrders = (GridView)sender;

            // Creating a Row
            GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

            //Adding Total Cell 
            TableCell cell = new TableCell();
            cell.Text = "Grand Total";
            cell.HorizontalAlign = HorizontalAlign.Left;
            cell.ColumnSpan = 2;
            cell.CssClass = "GrandTotalRowStyle";
            row.Cells.Add(cell);

            //Adding Unit Price Column
            cell = new TableCell();
            cell.Text = string.Format("{0:0.00}", dblGrandTotalUnitPrice);
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "GrandTotalRowStyle";
            row.Cells.Add(cell);

            //Adding Quantity Column
            cell = new TableCell();
            cell.Text = string.Format("{0:0.00}", dblGrandTotalQuantity);
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "GrandTotalRowStyle";
            row.Cells.Add(cell);

            //Adding Discount Column
            cell = new TableCell();
            cell.Text = string.Format("{0:0.00}", dblGrandTotalDiscount);
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "GrandTotalRowStyle";
            row.Cells.Add(cell);

            //Adding Amount Column
            cell = new TableCell();
            cell.Text = string.Format("{0:0.00}", dblGrandTotalAmount);
            cell.HorizontalAlign = HorizontalAlign.Right;
            cell.CssClass = "GrandTotalRowStyle";
            row.Cells.Add(cell);

            //Adding the Row at the RowIndex position in the Grid
            grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex, row);
            #endregion
        }
    }
    /// <summary>
    /// Event fires when data binds to each row
    /// Used for calculating Group Total 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        // This is for cumulating the values
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            strPreviousRowID = DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString();

            double dblUnitPrice = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "UnitPrice").ToString());
            double dblQuantity = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Quantity").ToString());
            double dblDiscount = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Discount").ToString());
            double dblAmount = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Amount").ToString());

            // Cumulating Sub Total
            dblSubTotalUnitPrice += dblUnitPrice;
            dblSubTotalQuantity += dblQuantity;
            dblSubTotalDiscount += dblDiscount;
            dblSubTotalAmount += dblAmount;

            // Cumulating Grand Total
            dblGrandTotalUnitPrice += dblUnitPrice;
            dblGrandTotalQuantity += dblQuantity;
            dblGrandTotalDiscount += dblDiscount;
            dblGrandTotalAmount += dblAmount;
        }
    }
}
The VB Code behind
Public Class Example1
    Inherits System.Web.UI.Page

    ' To keep track of the previous row Group Identifier
    Dim strPreviousRowID As String = String.Empty
    ' To keep track the Index of Group Total
    Dim intSubTotalIndex As Integer = 1

    ' To temporarily store Sub Total
    Dim dblSubTotalUnitPrice As Double = 0
    Dim dblSubTotalQuantity As Double = 0
    Dim dblSubTotalDiscount As Double = 0
    Dim dblSubTotalAmount As Double = 0

    ' To temporarily store Grand Total
    Dim dblGrandTotalUnitPrice As Double = 0
    Dim dblGrandTotalQuantity As Double = 0
    Dim dblGrandTotalDiscount As Double = 0
    Dim dblGrandTotalAmount As Double = 0

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    End Sub

    ''' <summary>
    ''' Event fires when data binds to each row
    ''' Used for calculating Group Total 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub grdViewOrders_RowDataBound(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdViewOrders.RowDataBound
        ' This is for cumulating the values
        If e.Row.RowType = DataControlRowType.DataRow Then
            strPreviousRowID = DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString()

            Dim dblUnitPrice As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "UnitPrice").ToString())
            Dim dblQuantity As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Quantity").ToString())
            Dim dblDiscount As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Discount").ToString())
            Dim dblAmount As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Amount").ToString())

            ' Cumulating Sub Total
            dblSubTotalUnitPrice += dblUnitPrice
            dblSubTotalQuantity += dblQuantity
            dblSubTotalDiscount += dblDiscount
            dblSubTotalAmount += dblAmount

            ' Cumulating Grand Total
            dblGrandTotalUnitPrice += dblUnitPrice
            dblGrandTotalQuantity += dblQuantity
            dblGrandTotalDiscount += dblDiscount
            dblGrandTotalAmount += dblAmount
        End If
    End Sub

    ''' <summary>
    ''' Event fires for every row creation
    ''' Used for creating SubTotal row when next group starts by adding Group Total at previous row manually
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub grdViewOrders_RowCreated(sender As Object, e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdViewOrders.RowCreated
        Dim IsSubTotalRowNeedToAdd As Boolean = False
        Dim IsGrandTotalRowNeedtoAdd As Boolean = False

        If (strPreviousRowID <> String.Empty) AndAlso (DataBinder.Eval(e.Row.DataItem, "CustomerID") IsNot Nothing) Then
            If strPreviousRowID <> DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString() Then
                IsSubTotalRowNeedToAdd = True
            End If
        End If

        If (strPreviousRowID <> String.Empty) AndAlso (DataBinder.Eval(e.Row.DataItem, "CustomerID") Is Nothing) Then
            IsSubTotalRowNeedToAdd = True
            IsGrandTotalRowNeedtoAdd = True
            intSubTotalIndex = 0
        End If

        ' "Inserting first Row and populating fist Group Header details"
        If (strPreviousRowID = String.Empty) AndAlso (DataBinder.Eval(e.Row.DataItem, "CustomerID") IsNot Nothing) Then
            Dim grdViewOrders As GridView = DirectCast(sender, GridView)

            Dim row As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

            Dim cell As New TableCell()
            cell.Text = "Customer Name : " & DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString()
            cell.ColumnSpan = 6
            cell.CssClass = "GroupHeaderStyle"
            row.Cells.Add(cell)

            grdViewOrders.Controls(0).Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row)
            intSubTotalIndex += 1
        End If


        If IsSubTotalRowNeedToAdd Then
            ' "Adding Sub Total Row"
            Dim grdViewOrders As GridView = DirectCast(sender, GridView)

            ' Creating a Row
            Dim row As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

            'Adding Total Cell 
            Dim cell As New TableCell()
            cell.Text = "Sub Total"
            cell.HorizontalAlign = HorizontalAlign.Left
            cell.ColumnSpan = 2
            cell.CssClass = "SubTotalRowStyle"
            row.Cells.Add(cell)

            'Adding Unit Price Column
            cell = New TableCell()
            cell.Text = String.Format("{0:0.00}", dblSubTotalUnitPrice)
            cell.HorizontalAlign = HorizontalAlign.Right
            cell.CssClass = "SubTotalRowStyle"
            row.Cells.Add(cell)

            'Adding Quantity Column
            cell = New TableCell()
            cell.Text = String.Format("{0:0.00}", dblSubTotalQuantity)
            cell.HorizontalAlign = HorizontalAlign.Right
            cell.CssClass = "SubTotalRowStyle"
            row.Cells.Add(cell)

            'Adding Discount Column
            cell = New TableCell()
            cell.Text = String.Format("{0:0.00}", dblSubTotalDiscount)
            cell.HorizontalAlign = HorizontalAlign.Right
            cell.CssClass = "SubTotalRowStyle"
            row.Cells.Add(cell)

            'Adding Amount Column
            cell = New TableCell()
            cell.Text = String.Format("{0:0.00}", dblSubTotalAmount)
            cell.HorizontalAlign = HorizontalAlign.Right
            cell.CssClass = "SubTotalRowStyle"
            row.Cells.Add(cell)

            'Adding the Row at the RowIndex position in the Grid
            grdViewOrders.Controls(0).Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row)
            intSubTotalIndex += 1

            ' "Adding Next Group Header Details"
            If DataBinder.Eval(e.Row.DataItem, "CustomerID") IsNot Nothing Then
                row = New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

                cell = New TableCell()
                cell.Text = "Customer Name : " & DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString()
                cell.ColumnSpan = 6
                cell.CssClass = "GroupHeaderStyle"
                row.Cells.Add(cell)

                grdViewOrders.Controls(0).Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row)
                intSubTotalIndex += 1
            End If

            ' "Reseting the Sub Total Variables"
            dblSubTotalUnitPrice = 0
            dblSubTotalQuantity = 0
            dblSubTotalDiscount = 0

            dblSubTotalAmount = 0
        End If
        If IsGrandTotalRowNeedtoAdd Then
            ' "Grand Total Row"
            Dim grdViewOrders As GridView = DirectCast(sender, GridView)

            ' Creating a Row
            Dim row As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

            'Adding Total Cell 
            Dim cell As New TableCell()
            cell.Text = "Grand Total"
            cell.HorizontalAlign = HorizontalAlign.Left
            cell.ColumnSpan = 2
            cell.CssClass = "GrandTotalRowStyle"
            row.Cells.Add(cell)

            'Adding Unit Price Column
            cell = New TableCell()
            cell.Text = String.Format("{0:0.00}", dblGrandTotalUnitPrice)
            cell.HorizontalAlign = HorizontalAlign.Right
            cell.CssClass = "GrandTotalRowStyle"
            row.Cells.Add(cell)

            'Adding Quantity Column
            cell = New TableCell()
            cell.Text = String.Format("{0:0.00}", dblGrandTotalQuantity)
            cell.HorizontalAlign = HorizontalAlign.Right
            cell.CssClass = "GrandTotalRowStyle"
            row.Cells.Add(cell)

            'Adding Discount Column
            cell = New TableCell()
            cell.Text = String.Format("{0:0.00}", dblGrandTotalDiscount)
            cell.HorizontalAlign = HorizontalAlign.Right
            cell.CssClass = "GrandTotalRowStyle"
            row.Cells.Add(cell)

            'Adding Amount Column
            cell = New TableCell()
            cell.Text = String.Format("{0:0.00}", dblGrandTotalAmount)
            cell.HorizontalAlign = HorizontalAlign.Right
            cell.CssClass = "GrandTotalRowStyle"
            row.Cells.Add(cell)

            'Adding the Row at the RowIndex position in the Grid

            grdViewOrders.Controls(0).Controls.AddAt(e.Row.RowIndex, row)
        End If

    End Sub
End Class
The Style Sheet
.SubTotalRowStyle{
    border:solid 1px Black;
    background-color:#F5A9BC;
    font-weight:bold;
}
.GrandTotalRowStyle{
    border:solid 1px Black;  
    background-color:Gray;
    font-weight:bold;
}
    .GroupHeaderStyle{
    border:solid 1px Black;
    background-color:#81BEF7;
    font-weight:bold;
}
.Spliter
{
    border-left:none;
    border-right:none;
    background-color:Aqua;
}
The Screen shot of the code output is:



Second Requirement


Addition to the above requirement mentioned in the first requirement, I need to give a empty space between each group sections. So each group sections will be separated clearly.

The implementation is as similar as above code, but we need to add required code for giving empty row after each group section completed. It means, after Group Total Completed.

So all the code is same, but additionally below section of code required to be added in the RowCreated event between Adding Sub Total Row region and Adding Next Group Header Details region (Adding Empty Row after each Group Total region in the code).
The C# Code
row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

cell = new TableCell();
cell.Text = string.Empty;
cell.Height = Unit.Parse("10px");
cell.ColumnSpan = 6;
row.Cells.Add(cell);

grdViewOrders.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row);
intSubTotalIndex++;
The VB Code
row = New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

cell = New TableCell()
cell.Text = String.Empty
cell.Height = Unit.Parse("10px")
cell.ColumnSpan = 6
row.Cells.Add(cell)

grdViewOrders.Controls(0).Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row)
intSubTotalIndex += 1

The Screen shot of the code output is:


Download the working source code in C# here and in VB here.

27 Responses to “Group Total and Grand Total in GridView - Part 2”

  • Anonymous says:
    14 March 2012 at 21:25

    Is the source code available somewhere for download? Thanks!

  • Thiru says:
    15 March 2012 at 10:34

    Hi. The source code is downloadable from the link provided at the end of the post.
    Below are the same links
    http://www.box.com/s/5da35oqefxa303tjv66u
    http://www.box.com/s/6a0jott0dyzk0czlfvv5

    If you still face issue, send me a mail. I will reply with the source code..

  • Frank Nacht says:
    9 May 2012 at 16:20

    Awesome! Great work with this post, i am sure people should have got impressed with this post.

  • Anonymous says:
    19 July 2012 at 01:25

    These examples are not working for me! :(

  • Thiru says:
    19 July 2012 at 11:23

    Hi, Pls let me know what is the error you are getting. If possible send me your code to me, so that I can verify and let you know if issue.

    For verification, download the source code and run the code in the system. If it works, there might be an issue with the code. There is an XML file which needs to bind to the grid for running this sample. Check the source code.

  • Anonymous says:
    25 January 2013 at 18:43

    Hi, There is problem with editing!

  • Thirumalai M says:
    25 January 2013 at 22:17

    What is the issue you are getting. Pls let me know in detail.

  • Anonymous says:
    2 April 2013 at 14:12

    Awesome! Great work with this post.
    I have 1 question.
    I try to export the data to Ms Excel.
    Only data for SUBTOTAL & GRAND TOTAL cannot displayed in MS Excel.
    Any idea ?

  • Thirumalai M says:
    2 April 2013 at 18:52

    Hi, i published a small on exporting such kind of senario. Please look at this post.
    http://www.dotnettwitter.com/2013/04/exporting-to-excel-from-gridview-when.html

  • Anonymous says:
    4 April 2013 at 08:14

    Thank you.Awesome

  • Anonymous says:
    8 April 2013 at 15:18

    i am getting a another sub total column after the sub total column.

  • Anonymous says:
    8 April 2013 at 16:04

    After the sub total there is a duplicate sub total column with amount as 0.00
    same with grand total

  • Thirumalai M says:
    8 April 2013 at 17:56

    There many be additional if loop running for sub total. You required to verify by breakpoint and debug.

    Are you using the source code provided in the downloaded link or it is from your code. The downloaded project must work fine.

    If it is your code, please send me sample copy (removing all privacy). I can verify and let you know.

  • Anonymous says:
    9 April 2013 at 14:49

    thanks the issue is resolved.

  • Anonymous says:
    9 April 2013 at 14:52

    I want to add just one row with a column detail just after gridview's header....
    as Parent Name: [Name of the parent]
    How can I acheive that????

  • Thirumalai M says:
    9 April 2013 at 16:21

    I am not able to understand the requirement. But I feel you are asking to add one row after first gridview header. If yes, add a variable like IsSubTotalRowNeedToAdd. In the RowCreated event, you can have one more if condition and write code to add the header and related information then the flag must be false.

    If this is not the requirement, you can send the information to my mail id.

  • Anonymous says:
    10 April 2013 at 09:15

    thanks....for the help...

    I have a Amount column which have values as 500.00,etc... when i am exporting these to excel the value becomes just 500. I want the decimal places too in excel....any idea how i can achieve that.
    Thanks!!!!

  • Thirumalai M says:
    10 April 2013 at 12:14

    Hi. There are various ways to solve the issue. a simple solution follows -

    I am providing the following for exporting amount field to excel with 00 precision.

    1. Change the GridView source from asp:BoundField to asp:TemplateField as below.
    <asp:TemplateField HeaderText="Amount">
    <ItemStyle HorizontalAlign="Right"></ItemStyle>
    <ItemTemplate>
    &nbsp;<asp:Label ID="lblOrderID" Text='<%# Eval("Amount") %>' runat="server"></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>

    If you see here, I use &nbsp; for providing a space in front of amount. So it becomes string instead of numeric.

    When you try to export now, all the data row (excel sub total, grand total will be like xxx.xx format.

    2. Now in the code where you specify the amount field for subtotal, change the amount value with a space as below (Line number 111 in downloaded source, Line number 101 in the C# code on this page).
    cell.Text = string.Format("{0:0.00}", "&nbsp;" + string.Format("{0:N}", dblSubTotalAmount));

    Now if you export, the subtotal for amount will be the format expected.

    The same way goes for grand total also.

    The another way is to use code behind itself to format every data and export.

    The other way is using templated excel sheet for exporting data.

    The last way ways will be little complex. So try the first way provided. If you are not happy, we can try other two.

  • Anonymous says:
    10 April 2013 at 16:45

    thanks

  • Anonymous says:
    25 April 2013 at 00:43

    Awsome! How would you do let say I want to blank out the repeating data on the subsequent rows if they are the same. Ex: 10278 should only shown once.

    Thanks.

  • Thirumalai M says:
    25 April 2013 at 18:12

    Hi,

    Check the following url, this contains example for merging rows. You can follow that portion of code here too.

    http://www.dotnettwitter.com/2010/12/how-to-create-multiple-row-header-and.html

  • Heba Attia says:
    2 September 2013 at 14:36

    Dear Thiru,

    Thank you for the very informative post. Your code is awesomely neat :)
    I just had one problem as a consequence of adopting this approach. In my gridview I have a column of checkboxes. On looping over the rows for storing the checkboxes' values, the loop stops before it finishes all the rows.

    For example, My datasource retrieves 24 rows, I add 4 rows as group headers so the total rows.Count should equal 28 but this is not the case. It remains 24 and the loop ignores the last 4 rows of data despite of the fact they get drawn normally.

    I hope I explained myself clearly...
    Your help will be very much appreciated.

    Thank you,

  • Anonymous says:
    1 November 2013 at 22:39

    Hi thanks for the code . I am having some issues my first sub group total is giving me the correct amount . But my other sub totals are wrong also my grand totals are showing 0 value. Can you please help me .PS I am using the data from a database and not an xml file.

  • Anonymous says:
    17 December 2013 at 17:23

    This is an excellent post. Can you please show how to do the same for an SQL Server database instead of an XML data source?

    Thanks.

    Harshit
    hmehta@iswl.in

  • malayalee says:
    13 February 2014 at 11:03

    is it possible to use edit functionality with grouping

  • Anonymous says:
    28 April 2014 at 18:14

    i want to insert another subtotal in the code for different orderid...pls help

  • Abhishek Nigam says:
    28 June 2014 at 12:12

    is pagination pssible?
    what is the code

Post a Comment