Sunday, 6 January 2013

Group Total and Grand Total in GridView – Part 7

Thirumalai M - 11:42
Labels:
In last post, I had posted a post on Group Total and Grand Total on GridView, which displays records in groups and provides the total of each group at the end of the group. In this post, I am planning to include +, - buttons on each group totals which helps to analyze the records easily.

For more understanding, here is the use case of the 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 in first level and Order ID in second level and the Group Total for end of the each group should show.
  3. When Group Total shows at end of each group, the Customer Name and the Order ID information should show in addition to the group total.
  4. As there are two levels of grouping, the Order ID group should show little indent to the Customer Name and the actual value (data row) should show indent to the Order Id.
  5. The Customer Name, Order ID groups must be displayed with different background color to differentiate the groups.
  6. The Grand Total of all the records should be shown after all the records in the Grid.
  7. There must also be +, - buttons on each group total which helps to hide that particular group and make it visible.
  8. There should also be a +, - button at the grand total which used to hide and show all the rows and show only group total.
This requirement talks about having two different groups, Customer Name is the first group and Order ID is the second group. So the grid will have one or more Customer Name as groups, each Customer Name group will have one or more Order ID as group. Actual data row will be displayed under each Order ID.

At the end of the Order ID group, the Order ID value will be displayed with some additional information such as Order Date, Delivery Date. At the end of the Customer Name group, the Customer Name value will be displayed with the Customer ID. Every group will have Group Total.

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. It will be useful for calculating cumulative values together. Keeping records in different group will be considered as separate group and cumulative values will be calculated as another separate group. As we have two groups in this example, we must sort by Customer Name at first and then Order ID next.
In this page, I had provided two level of grouping as explained before. For more grouping, I provided in the downloadable source code at the end.

The implementation goes as below –

The XML source which bond to the GridView
<?xml version="1.0" encoding="utf-8" ?>
<Orders>
  
  <Order CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10643" OrderDate="12-Apr-2012" DeliveryDate="15-Apr-2012" 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 CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10643" OrderDate="12-Apr-2012" DeliveryDate="15-Apr-2012" 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 CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10643" OrderDate="12-Apr-2012" DeliveryDate="15-Apr-2012" 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 CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10692" OrderDate="21-May-2012" DeliveryDate="23-May-2012" 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 CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"
         OrderID="10702" OrderDate="01-Jun-2012" DeliveryDate="15-Jun-2012" 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 CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados"
         OrderID="10308" OrderDate="11-Aug-2012" DeliveryDate="21-Sep-2012" 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 CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados" 
         OrderID="10308" OrderDate="11-Aug-2012" DeliveryDate="21-Sep-2012" 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 CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados y helados"
         OrderID="10926" OrderDate="01-May-2012" DeliveryDate="12-Jun-2012" 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 CustomerID="AROUT" CompanyName="Around the Horn"
         OrderID="10927" OrderDate="11-Aug-2012" DeliveryDate="21-Sep-2012" 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 CustomerID="AROUT" CompanyName="Around the Horn"
         OrderID="10927" OrderDate="11-Aug-2012" DeliveryDate="21-Sep-2012" 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 CustomerID="AROUT" CompanyName="Around the Horn"
         OrderID="11016" OrderDate="15-May-2012" DeliveryDate="15-Jun-2012" 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 CustomerID="AROUT" CompanyName="Around the Horn"
         OrderID="11016" OrderDate="15-May-2012" DeliveryDate="15-Jun-2012" 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 CustomerID="BERGS" CompanyName="Berglunds snabbköp"
         OrderID="10278" OrderDate="02-Nov-2012" DeliveryDate="15-Nov-2012" 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 CustomerID="BERGS" CompanyName="Berglunds snabbköp"
         OrderID="10278" OrderDate="02-Nov-2012" DeliveryDate="15-Nov-2012" 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 CustomerID="BERGS" CompanyName="Berglunds snabbköp"
         OrderID="10278" OrderDate="02-Nov-2012" DeliveryDate="15-Nov-2012" 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 CustomerID="BERGS" CompanyName="Berglunds snabbköp" 
         OrderID="10278" OrderDate="02-Nov-2012" DeliveryDate="15-Nov-2012" 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 CustomerID="BERGS" CompanyName="Berglunds snabbköp" 
         OrderID="10280" OrderDate="15-Apr-2012" DeliveryDate="07-Jul-2012" 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" BackColor="White" 
 BorderColor="Black" BorderStyle="Solid" BorderWidth="1px"
 OnRowDataBound="grdViewOrders_RowDataBound"
 OnRowCreated="grdViewOrders_RowCreated" >            
 <Columns>
  <asp:TemplateField HeaderText="">
   <ItemStyle Width="10px" CssClass="DataCell" BorderStyle="Solid" BorderWidth="1" />
   <ItemTemplate></ItemTemplate>
   <HeaderStyle CssClass="DataCell" />
  </asp:TemplateField>
  <asp:TemplateField HeaderText="">
   <ItemStyle Width="10px" CssClass="DataCell" />
   <ItemTemplate></ItemTemplate>
   <HeaderStyle CssClass="DataCell" />
  </asp:TemplateField>
  <asp:TemplateField HeaderText="">
   <ItemStyle Width="10px" CssClass="DataCell" />
   <ItemTemplate></ItemTemplate>
   <HeaderStyle CssClass="DataCell" />
  </asp:TemplateField>
   
  <asp:BoundField DataField="ProductName" HeaderText="ProductName">
   <ItemStyle HorizontalAlign="Left" CssClass="DataCell"></ItemStyle>
   <HeaderStyle CssClass="DataCell" />
  </asp:BoundField>
  <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" DataFormatString="{0:c}">
   <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
   <HeaderStyle CssClass="DataCell" />
  </asp:BoundField>
  <asp:BoundField DataField="Quantity" HeaderText="Quantity" DataFormatString="{0:c}">
   <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
   <HeaderStyle CssClass="DataCell" />
  </asp:BoundField>
  <asp:BoundField DataField="Discount" HeaderText="Discount" DataFormatString="{0:c}">
   <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
   <HeaderStyle CssClass="DataCell" />
  </asp:BoundField>
  <asp:BoundField DataField="Amount" HeaderText="Amount" DataFormatString="{0:c}">
   <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
   <HeaderStyle CssClass="DataCell" />
  </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 behind
// To keep track of the previous row Group Identifier
string strPreviousRowCustomerID = string.Empty; // First Level Grouping Track Id (Used to identify the group is getting changed)
string strPreviousRowOrderID = string.Empty; // Second Level Grouping Track Id (Used to identify the group are getting changed)

string strPreviousRowTempCustomerID = string.Empty; // First Level Grouping Track Id (Used to identify the group is getting changed)
string strPreviousRowTempOrderID = string.Empty; // Second Level Grouping Track Id (Used to identify the group are getting changed)

// To keep track the Index of Group Total
int intSubTotalIndex = 0; // For increasing the row count (for inserting a row in the current row)

string strCustomerGroupHeaderText = string.Empty;
string strOrderGroupHeaderText = string.Empty;

// To customer temporarily store Sub Total - First Level Grouping (Declare variables for sum, count columns)
double dblCustomerGroupSubTotalUnitPrice = 0;
double dblCustomerGroupSubTotalQuantity = 0;
double dblCustomerGroupSubTotalDiscount = 0;
double dblCustomerGroupSubTotalAmount = 0;

// To order temporarily store Sub Total - Second Level Grouping
double dblOrderGroupSubTotalUnitPrice = 0;
double dblOrderGroupSubTotalQuantity = 0;
double dblOrderGroupSubTotalDiscount = 0;
double dblOrderGroupSubTotalAmount = 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 IsCustomerSubTotalRowNeedToAdd = false; // First Level Grouping
    bool IsOrderSubTotalRowNeedToAdd = false; // Second Level Grouping
    bool IsGrandTotalRowNeedtoAdd = false;

    #region Reset firstlevel, secondlevel counters for Sub Total.
    // Not First row
    if ((strPreviousRowCustomerID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null))
    {
        // When customer is not changing, but order is changing - second level grouping changing
        if ((strPreviousRowCustomerID == DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString()) &&
            (strPreviousRowOrderID != DataBinder.Eval(e.Row.DataItem, "OrderID").ToString())
        )
        {
            IsOrderSubTotalRowNeedToAdd = true;
        }
        // When customer changing - first level grouping changing
        if (strPreviousRowCustomerID != DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString())
        {
            IsCustomerSubTotalRowNeedToAdd = true;
            IsOrderSubTotalRowNeedToAdd = true;
        }
    }
    #endregion

    #region When final row completed. firstlevel, secondlevel, and Grand Total needed
    if ((strPreviousRowCustomerID != string.Empty) &&
        (strPreviousRowOrderID != string.Empty) &&
        (DataBinder.Eval(e.Row.DataItem, "CustomerID") == null) &&
        (DataBinder.Eval(e.Row.DataItem, "OrderID") == null)
        )
    {
        IsCustomerSubTotalRowNeedToAdd = true;
        IsOrderSubTotalRowNeedToAdd = true;
        IsGrandTotalRowNeedtoAdd = true;
    }
    #endregion

    #region Getting the Group Header Text
    if ((strPreviousRowCustomerID == string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null))
        strCustomerGroupHeaderText = DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString() + " (" + DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString() + " )";
    else
        strCustomerGroupHeaderText = strPreviousRowTempCustomerID;

    if ((strPreviousRowOrderID == string.Empty) && (DataBinder.Eval(e.Row.DataItem, "OrderID") != null))
        strOrderGroupHeaderText = DataBinder.Eval(e.Row.DataItem, "OrderID").ToString() + " (Order Date : " + DataBinder.Eval(e.Row.DataItem, "OrderDate").ToString() + ", Delivery Date : " + DataBinder.Eval(e.Row.DataItem, "DeliveryDate").ToString() + " )";
    else
        strOrderGroupHeaderText = strPreviousRowTempOrderID;
    #endregion

    // Second Level Grouping
    if (IsOrderSubTotalRowNeedToAdd)
    {
        #region Inserting Order Group Total row - Second Level Grouping
        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();
        cell.CssClass = "DataCell";
        row.Cells.Add(cell);
        row.CssClass = "ExpandCollapse" + strPreviousRowOrderID;

        //Adding Expand Collapse Cell 
        cell = new TableCell();
        cell.CssClass = "DataCell";
        row.Cells.Add(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", strPreviousRowOrderID + ",2"); // Second Level Grouping
        img.Attributes.Add("class", "ExpandCollapseStyle");
        cell.Controls.Add(img);
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.CssClass = "FirstGroupTotalRowStyle";
        row.Cells.Add(cell);

        //Adding Header Cell 
        cell = new TableCell();
        cell.Text = strOrderGroupHeaderText;
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.ColumnSpan = 1;
        cell.CssClass = "FirstGroupTotalRowStyle";
        row.Cells.Add(cell);

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

        //Adding Quantity Column
        cell = new TableCell();
        cell.Text = dblOrderGroupSubTotalQuantity.ToString();
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "FirstGroupTotalRowStyle";
        row.Cells.Add(cell);

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

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

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

        #region Reseting the Sub Total Variables
        dblOrderGroupSubTotalUnitPrice = 0;
        dblOrderGroupSubTotalQuantity = 0;
        dblOrderGroupSubTotalDiscount = 0;
        dblOrderGroupSubTotalAmount = 0;
        #endregion
    }

    // First Level Grouping
    if (IsCustomerSubTotalRowNeedToAdd)
    {
        #region Inserting Customer Group Total Row - First Level Grouping
        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(); 
        cell.CssClass = "DataCell";
        row.Cells.Add(cell);
        row.CssClass = "ExpandCollapse" + strPreviousRowCustomerID;

        //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", strPreviousRowCustomerID + ",1"); // First Level Grouping
        img.Attributes.Add("class", "ExpandCollapseStyle");
        cell.Controls.Add(img);
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);

        cell = new TableCell();
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);

        //Adding Header Cell 
        cell = new TableCell();
        cell.Text = strCustomerGroupHeaderText;
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.ColumnSpan = 1;
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);

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

        //Adding Quantity Column
        cell = new TableCell();
        cell.Text = dblCustomerGroupSubTotalQuantity.ToString();
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SecondGroupTotalRowStyle";
        row.Cells.Add(cell);

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

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

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

        #region Reseting the Sub Total Variables
        dblCustomerGroupSubTotalUnitPrice = 0;
        dblCustomerGroupSubTotalQuantity = 0;
        dblCustomerGroupSubTotalDiscount = 0;
        dblCustomerGroupSubTotalAmount = 0;
        #endregion
    }

    if (IsGrandTotalRowNeedtoAdd)
    {
        #region Grand Total Row - Third Level Grouping
        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);

        cell = new TableCell();
        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 = 1;
        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 = dblGrandTotalQuantity.ToString();
        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
    }

    #region Getting the Group Header Text
    if (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null)
        strCustomerGroupHeaderText = DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString() + " (" + DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString() + ")";

    if (DataBinder.Eval(e.Row.DataItem, "OrderID") != null)
        strOrderGroupHeaderText = DataBinder.Eval(e.Row.DataItem, "OrderID").ToString() + " (Order Date : " + DataBinder.Eval(e.Row.DataItem, "OrderDate").ToString() + ", Delivery Date : " + DataBinder.Eval(e.Row.DataItem, "DeliveryDate").ToString() + ")";
    #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)
    {
        strPreviousRowCustomerID = DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString();
        strPreviousRowOrderID = DataBinder.Eval(e.Row.DataItem, "OrderID").ToString();
        strPreviousRowTempCustomerID = DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString() + " (" + DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString() + " )";
        strPreviousRowTempOrderID = DataBinder.Eval(e.Row.DataItem, "OrderID").ToString() + " (Order Date : " + DataBinder.Eval(e.Row.DataItem, "OrderDate").ToString() + ", Delivery Date : " + DataBinder.Eval(e.Row.DataItem, "DeliveryDate").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
        dblCustomerGroupSubTotalUnitPrice += dblUnitPrice;
        dblCustomerGroupSubTotalQuantity += dblQuantity;
        dblCustomerGroupSubTotalDiscount += dblDiscount;
        dblCustomerGroupSubTotalAmount += dblAmount;

        dblOrderGroupSubTotalUnitPrice += dblUnitPrice;
        dblOrderGroupSubTotalQuantity += dblQuantity;
        dblOrderGroupSubTotalDiscount += dblDiscount;
        dblOrderGroupSubTotalAmount += dblAmount;

        // Cumulating Grand Total
        dblGrandTotalUnitPrice += dblUnitPrice;
        dblGrandTotalQuantity += dblQuantity;
        dblGrandTotalDiscount += dblDiscount;
        dblGrandTotalAmount += dblAmount;

        e.Row.Style.Add("display", "block");
        e.Row.Cells[0].CssClass = "DataRowStyle";
        e.Row.Cells[0].Attributes.Add("alt", ",3");
    }
    intSubTotalIndex++;
}
The Style sheet
.SecondGroupTotalRowStyle{
    border:solid 1px Black;
    background-color:chocolate;
    font-weight:bold;
}
.FirstGroupTotalRowStyle {
    border:solid 1px Black;
    background-color:#81BEF7;
}
.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;
}
.DataCell
{
    border:solid 1px Black;
}
The JavaScript
//ExpandCollapse
$(document).ready(function () {
    $('.ExpandCollapseStyle').click(function () {
        var selectedTrackId = $(this).attr('alt');

        var isSelectedTrackerFound = false;
        var selectedTrackerGroupIndex = 0;

        var ExpandOrCollapse = $(this).attr('src');


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

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

            if (currentTrackId == null)
                currentTrackId = $(this).find(".DataRowStyle").attr('alt');

            if (currentTrackId != null) {

                if (selectedTrackId.split(",")[0] == currentTrackId.split(",")[0]) {

                    isSelectedTrackerFound = true;
                    if (selectedTrackerGroupIndex == 0) {
                        selectedTrackerGroupIndex = currentTrackId.split(",")[1];

                        if (ExpandOrCollapse == 'images/plus.gif') {
                            $(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
                        }
                        else {
                            $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
                        }
                    }
                }
                else {
                    if (currentTrackId != null) {
                        if (parseInt(selectedTrackerGroupIndex) > 0) {
                            if (parseInt(selectedTrackerGroupIndex) >= parseInt(currentTrackId.split(",")[1]))
                                isSelectedTrackerFound = false;
                        }
                    }
                    if (isSelectedTrackerFound == true) {

                        if (ExpandOrCollapse == 'images/plus.gif') {
                            $(this).css("display", "block");
                            $(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
                        }
                        else {
                            $(this).css("display", "none");
                            $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
                        }
                    }
                }
            }
        });
    })
    $('.ExpandCollapseGrandStyle').click(function () {

        var ExpandOrCollapse = $(this).attr('src');

        var isSelectedTrackerFound = false;
        var selectedTrackerGroupIndex = 0;

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

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


            if (currentTrackId != null) {

                if (ExpandOrCollapse == 'images/plus.gif') {

                    $(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
                    $('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "block");
                }
                else {

                    $(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
                    $('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "none");
                }
            }
            else { // Data Row
                currentTrackId = $(this).find(".DataRowStyle").attr('alt');
                if (currentTrackId != null) {
                    if (ExpandOrCollapse == 'images/plus.gif') {
                        $(this).css("display", "block");
                    }
                    else {
                        $(this).css("display", "none");
                    }
                }
            }
        });
        if ($('.ExpandCollapseGrandStyle').attr('src') == 'images/minus.gif') {
            $('.ExpandCollapseGrandStyle').attr('src', 'images/plus.gif');
        }
        else {
            $('.ExpandCollapseGrandStyle').attr('src', 'images/minus.gif');
        }
    })
    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 output of this example will be as below.






Below is the screenshots for five level of grouping.





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

0 Responses to “Group Total and Grand Total in GridView – Part 7”

Post a Comment

Subscribe to: Post Comments (Atom)