Group Total and Grand Total in GridView –Part 4

Tweet

Previously I had blogged some posts on Group Total and Grand Total on GridView. Today I am planning to post on the same topic with some more features included.

For reference of previous posts, please refer the following urls –
Group Total and Grand Total in GridView – Part 1
Group Total and Grand Total in GridView - Part 2
Group Total and Grand Total in GridView - Part 3

This post extends the same concept and use case of the requirement as below.

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. When Group Total shows at end of each group, the Customer Name must show in the Customer Name column.
4. There is no need to show Group Header in the Grid View.
5. The Grand Total of all the records should be shown after all the records in the Grid.
6. The Group Total and Grand Total rows background color must be in predefined style.

As defined in the requirement, the Group Header will be shown with the Group Total. This is same as what Excel shows when doing grouping.

Here the important point is, the Group Header can be decided in programming.

Before going for actual implementation, please note the following points -

1. 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.

The implementation follows –

The XML script

<?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>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" DataFile="Data/Orders.xml"></asp:XmlDataSource>

The C# Code

// To keep track of the previous row Group Identifier
string strPreviousRowID = string.Empty;
// To keep track the Index of Group Total
int intSubTotalIndex = 1;

string strGroupHeaderText = string.Empty;

// 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 Getting the first Group Header Text
    if ((strPreviousRowID == string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null))
        // Getting the First column text of first group
        strGroupHeaderText = DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString();
    #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();
        // Displaying the Group Total First Column Text here.
        // This value can be decided when assigning strGroupHeaderText variable value.
        cell.Text = strGroupHeaderText; 
        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 Getting Next Group Header Details
        if (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null)
            // Once each group completed, getting the first column text of next group.
            strGroupHeaderText = DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString();
        #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 Style Sheet

.SubTotalRowStyle{
    border:solid 1px Black;
    background-color:#81BEF7;
    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;
}

Below is the output of the source code -

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

The other links on Group Total, Grand Total in Grid View:

1. Group Total, Grand Total in Grid View - Part 1
2. Group Total, Grand Total in Grid View - Part 2
3. Group Total, Grand Total in Grid View - Part 3
4. Group Total, Grand Total in Grid View - Part 4
5. Group Total, Grand Total in Grid View - Part 5