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 –
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 -
The implementation goes as below –
The XML source which bond to the GridView
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.
For more understanding, here is the use case of the requirement –
- The records defined in the XML should bound to the Grid View in a normal way.
- 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.
- 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.
- 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.
- The Customer Name, Order ID groups must be displayed with different background color to differentiate the groups.
- The Grand Total of all the records should be shown after all the records in the Grid.
- There must also be +, - buttons on each group total which helps to hide that particular group and make it visible.
- There should also be a +, - button at the grand total which used to hide and show all the rows and show only group total.
- There is also need to show +, - buttons on the header of each group column which can be used to show and hide the groups at that level for the whole rows.
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 -
- 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.
- 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.
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" Width="10px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="">
<ItemStyle Width="10px" CssClass="DataCell" />
<ItemTemplate></ItemTemplate>
<HeaderStyle CssClass="DataCell" Width="10px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="">
<ItemStyle Width="10px" CssClass="DataCell" />
<ItemTemplate></ItemTemplate>
<HeaderStyle CssClass="DataCell" Width="10px" />
</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)
// 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)
{
TableCell cell = grdViewOrders.HeaderRow.Cells[0]; // First Cell in the Header - Grand Total
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", "0");
cell.Controls.Add(img);
cell.HorizontalAlign = HorizontalAlign.Left;
//cell.CssClass = "HeaderCell";
cell.Attributes.Add("alt", "HeaderCell" + ",0");
cell = grdViewOrders.HeaderRow.Cells[1]; // Second Group
img = new System.Web.UI.HtmlControls.HtmlImage();
img.Src = "images/minus.gif";
img.Attributes.Add("class", "ExpandCollapseHeaderStyle");
img.Attributes.Add("alt", "1");
cell.Controls.Add(img);
cell.HorizontalAlign = HorizontalAlign.Left;
//cell.CssClass = "HeaderCell" + "1";
cell.Attributes.Add("alt", "HeaderCell" + ",1");
cell = grdViewOrders.HeaderRow.Cells[2]; // First Group
img = new System.Web.UI.HtmlControls.HtmlImage();
img.Src = "images/minus.gif";
img.Attributes.Add("class", "ExpandCollapseHeaderStyle");
img.Attributes.Add("alt", "2");
cell.Controls.Add(img);
cell.HorizontalAlign = HorizontalAlign.Left;
//cell.CssClass = "HeaderCell" + "2";
cell.Attributes.Add("alt", "HeaderCell" + ",2");
}
/// <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
// 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", "0");
//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();
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;
width:10px;
}
.ExpandCollapseGrandStyle
{
border:0px;
cursor:pointer;
width:10px;
}
.DataCell
{
border:solid 1px Black;
}
.ExpandCollapseHeaderStyle {
background-color:#6B696B;
cursor:pointer;
}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 selectedTrackId = $(this).attr('alt');
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') {
if (parseInt(selectedTrackId) + 1 == parseInt(currentTrackId.split(",")[1])) {
$(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
$('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "block");
}
if (parseInt(selectedTrackId) + 1 < parseInt(currentTrackId.split(",")[1])) {
$('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "none");
}
}
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");
$(this).css("display", "none");
}
else {
$(this).css("display", "none");
}
}
}
});
$(".grdViewOrders tr").children("th").each(function (index) {
var currentTrackId = $(this).attr('alt');
if (currentTrackId != null) {
if (parseInt(currentTrackId.split(",")[1]) > parseInt(selectedTrackId)) {
if (ExpandOrCollapse == 'images/minus.gif')
$(this).find('.ExpandCollapseHeaderStyle').attr('src', 'images/plus.gif');
}
}
});
if ($('.ExpandCollapseGrandStyle').attr('src') == 'images/minus.gif') {
$('.ExpandCollapseGrandStyle').attr('src', 'images/plus.gif');
}
else {
$('.ExpandCollapseGrandStyle').attr('src', 'images/minus.gif');
}
})
$('.ExpandCollapseHeaderStyle').click(function () {
var selectedTrackId = $(this).attr('alt');
var ExpandOrCollapse = $(this).attr('src');
$($(".grdViewOrders tr").get().reverse()).each(function () {
var currentTrackId = $(this).find(".ExpandCollapseStyle").attr('alt');
if (currentTrackId != null) {
if (ExpandOrCollapse == 'images/plus.gif') {
if (parseInt(currentTrackId.split(",")[1]) <= parseInt(selectedTrackId)) {
$(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
$('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "block");
}
if (parseInt(currentTrackId.split(",")[1]) == (parseInt(selectedTrackId) + 1)) {
$(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
$('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "block");
}
if (parseInt(currentTrackId.split(",")[1]) > (parseInt(selectedTrackId) + 1))
$('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "none");
}
else {
if (parseInt(currentTrackId.split(",")[1]) == parseInt(selectedTrackId))
$(this).find(".ExpandCollapseStyle").attr('src', 'images/plus.gif');
if (parseInt(currentTrackId.split(",")[1]) > parseInt(selectedTrackId)) {
$('.ExpandCollapse' + currentTrackId.split(",")[0]).css("display", "none");
}
if (parseInt(currentTrackId.split(",")[1]) < parseInt(selectedTrackId))
$(this).find(".ExpandCollapseStyle").attr('src', 'images/minus.gif');
}
}
currentTrackId = $(this).find(".DataRowStyle").attr('alt');
if (currentTrackId != null) {
if (parseInt(currentTrackId.split(",")[1]) > parseInt(selectedTrackId)) {
if (ExpandOrCollapse == 'images/plus.gif') {
if (parseInt(currentTrackId.split(",")[1]) == (parseInt(selectedTrackId) + 1))
$(this).css("display", "block");
else
$(this).css("display", "none");
}
else {
$(this).css("display", "none");
}
}
}
});
$(".grdViewOrders tr").children("th").each(function (index) {
var currentTrackId = $(this).attr('alt');
if (currentTrackId != null) {
if (parseInt(currentTrackId.split(",")[1]) > parseInt(selectedTrackId)) {
if (ExpandOrCollapse == 'images/minus.gif')
$(this).find('.ExpandCollapseHeaderStyle').attr('src', 'images/plus.gif');
}
if (parseInt(currentTrackId.split(",")[1]) < parseInt(selectedTrackId)) {
if (ExpandOrCollapse == 'images/plus.gif') {
$(this).find('.ExpandCollapseHeaderStyle').attr('src', 'images/minus.gif');
$('.ExpandCollapseGrandStyle').attr('src', 'images/minus.gif');
}
}
}
});
if ($(this).attr('src') == 'images/minus.gif') {
$(this).attr('src', 'images/plus.gif');
}
else {
$(this).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 8”
Post a Comment