Group Total and Grand Total in GridView –Part 5

Tweet

In last post, we had seen Group Total and Grand Total in Grid View where the Group Header will show with the Group Total itself. So it looks like the Sub Total concept done with Excel sheet.

In this post, I am taking the same example and extending it to have Expand and Collapse facility (means + and – buttons) for each groups. So the requirement for this implementation will be 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. The Grid View should show a column in front of the first column for showing Expand and Collapse (+ and -) buttons for expanding the collapsing the group.
5. The Grid View should show another column as first column which is used for expanding and collapsing all the groups at a single time.
6. There is no need to show Group Header in the Grid View.
7. The Grand Total of all the records should be shown after all the records in the Grid.
8. The Group Total and Grand Total rows background color must be in predefined style.

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" CssClass="grdViewOrders"
    CellPadding="4" ForeColor="Black" GridLines="Vertical"
    OnRowDataBound="grdViewOrders_RowDataBound"
    onrowcreated="grdViewOrders_RowCreated" BackColor="White" 
    BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px">            
    <Columns>
        <asp:TemplateField HeaderText="">
            <ItemStyle Width="10px" />
            <ItemTemplate>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="">
            <ItemStyle Width="10px" />
            <ItemTemplate>
            </ItemTemplate>
        </asp:TemplateField>
        <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>

Add JQuery version 1.7.2 in the page

<script src="Scripts/jquery-1.7.2.min.js" type="text/javascript"></script>

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))
        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 Group Expand Collapse Cell 
        TableCell cell = new TableCell();
        row.Cells.Add(cell);

        //Adding Expand Collapse Cell 
        cell = new TableCell();
        System.Web.UI.HtmlControls.HtmlImage img = new System.Web.UI.HtmlControls.HtmlImage();
        img.Src = "images/minus.gif";
        img.Attributes.Add("alt", strPreviousRowID);
        img.Attributes.Add("class", "ExpandCollapseStyle");
        cell.Controls.Add(img);
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.CssClass = "SubTotalRowStyle";
        row.Cells.Add(cell);

        //Adding Header Cell 
        cell = new TableCell();
        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)
            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 Group Expand Collapse Cell 
        TableCell cell = new TableCell();
        System.Web.UI.HtmlControls.HtmlImage img = new System.Web.UI.HtmlControls.HtmlImage();
        img.Src = "images/minus.gif";
        img.Attributes.Add("class", "ExpandCollapseGrandStyle");
        img.Attributes.Add("alt", "Expanded");
        cell.Controls.Add(img);
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);

        //Adding Expand Collapse Cell 
        cell = new TableCell();
        cell.CssClass = "GrandTotalRowStyle";
        row.Cells.Add(cell);

        //Adding Header Cell 
        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;

        e.Row.Style.Add("display", "block");
        e.Row.CssClass = "ExpandCollapse" + strPreviousRowID;
    }

}

The JQuery Code

//ExpandCollapse
$(document).ready(function () {
    $('.ExpandCollapseStyle').click(function () {

        var orderId = $(this).attr('alt');

        if (!isDisplayed($('.ExpandCollapse' + orderId))) {
            $(this).attr('src', 'images/minus.gif');
            $('.ExpandCollapse' + orderId).css("display", "block");
        }
        else {
            $(this).attr('src', 'images/plus.gif');
            $('.ExpandCollapse' + orderId).css("display", "none");
        }
    })
    $('.ExpandCollapseGrandStyle').click(function () {

        $(".grdViewOrders tr").each(function () {

            var orderId = $(this).find(".ExpandCollapseStyle").attr('alt');

            if (orderId != 'undefined') {

                if ($(this).attr('alt') == 'Expanded') {

                    $(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
                    $('.ExpandCollapse' + orderId).css("display", "block");
                    $(this).attr('alt', 'Collapsed');

                }
                else {

                    $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
                    $('.ExpandCollapse' + orderId).css("display", "none");
                    $(this).attr('alt', 'Expanded');

                }
            }
        });
        if ($('.ExpandCollapseGrandStyle').attr('alt') == 'Expanded') {
            $('.ExpandCollapseGrandStyle').attr('src', 'images/plus.gif');
            $('.ExpandCollapseGrandStyle').attr('alt', 'Collapsed');
        }
        else {
            $('.ExpandCollapseGrandStyle').attr('src', 'images/minus.gif');
            $('.ExpandCollapseGrandStyle').attr('alt', 'Expanded');
        }
    })
    function isDisplayed(object) {
        // if the object is visible return true
        if ($(object).css('display') == 'block') {
            return true;
        }
        // if the object is not visible return false
        return false;
    };
});

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;
}
.ExpandCollapseStyle
{
    border:0px;
    cursor:pointer;
}
.ExpandCollapseGrandStyle
{
    border:0px;
    cursor:pointer;
}

As defined in the code, when expanding and collapsing the group, the code will hide and visible the particular group rows by using the css style. In JQuery we can able to find controls by using class name by using . (dot) before the class name.

For Ex: The controls (means rows) with ExpandCollapse can get from

$('.ExpandCollapse')

In the the row databound I am setting the class name with the GroupName. For Ex:

e.Row.CssClass = "ExpandCollapse" + strPreviousRowID

When clicking the Expand and Collapse button, the JQuery code will find the rows using the class name with the GroupName. Finally the code will hide and visible based on the requirement.

$('.ExpandCollapse' + orderId).css("display", "block");

Below is the output of the source code -

download the workinig copy 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