This post is a continuation of a series. Please look at the end of this post for more information
In previous posts of GroupTotal using GridView, we had seen some implementation on showing Group Total and Grand Total in GridView. This is another post on the same series which provides implementing Group Total and show them as separate grid each other.
To understand more, the GridView should have the following points while showing details of the records.
- The developer can bind to the Grid View in the same way as other binding method.
But the records must be sorted by Group Name. So the Group can be separated and Group Total can be calculated for in each Group else if the records scattered the records comes together will be a separate group. - The Grid View should show the record Group by Group and provide Group Header and Group Total for each Group.
- The Group Header must show not a normal header for each column, but it should be decided by the developer the way customer required. So the layout can be modified using HTML script.
- After each Group is completed with Group Total, there should be an empty space before next Group starts. So each group must be entirely separated from other groups.
- Grand Total must show once all the Groups completed and there should be a space before the Grand Total.
The project source code contains two examples; the only difference is clean separation between each group. But here I am going to provide the second implementation in the Source Code (Example1.aspx).
I have XML file for the data source to bind to the Grid View
<?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 GridView Script
<asp:GridView ID="grdViewProducts" runat="server" AutoGenerateColumns="False" TabIndex="1" Width="100%" DataSourceID="XmlDataSource1" CellPadding="4" ForeColor="Black" GridLines="Vertical" OnRowDataBound="grdViewProducts_RowDataBound" onrowcreated="grdViewProducts_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="CompanyName" HeaderText="CompanyName"> <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" BorderColor="Black" BorderWidth="1px" /> <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" /> <AlternatingRowStyle BackColor="White" BorderStyle="Solid" BorderColor="Black" BorderWidth="1px" /> <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 Style Sheet
.SubTotalRowStyle{ border:solid 1px Black; background-color:#F5A9BC; font-weight:bold; } .GrandTotalRowStyle{ border:solid 1px Black; background-color:Gray; font-weight:bold; } .GroupDetailsStyle{ border:solid 1px Black; background-color:#81BEF7; } .GroupHeaderStyle{ border:solid 1px Black; background-color:#507CD1; color:White; } .Spliter { border-left:none; border-right:none; }The C# Source 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; // 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) { grdViewProducts.HeaderRow.Visible = false; } /// <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 grdViewProducts_RowCreated(object sender, GridViewRowEventArgs e) { bool IsSubTotalRowNeedToAdd = false; bool IsGrandTotalRowNeedtoAdd = false; if ((strPreviousRowID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null)) if (strPreviousRowID != DataBinder.Eval(e.Row.DataItem, "CustomerID").ToString()) IsSubTotalRowNeedToAdd = true; if ((strPreviousRowID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") == null)) { IsSubTotalRowNeedToAdd = true; IsGrandTotalRowNeedtoAdd = true; intSubTotalIndex = 0; } #region Inserting first Row and populating fist Group Header details if ((strPreviousRowID == string.Empty) && (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null)) { GridView grdViewProducts = (GridView)sender; GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert); TableCell cell = new TableCell(); cell.Text = "<div style='width:100%;'>" + "<div style='width:50%;float:left;'>" + "<b style='float:left;'>Customer Details : </b>" + "<div style='float:left;'>" + DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString() + "</br>" + DataBinder.Eval(e.Row.DataItem, "Address").ToString() + "</br>" + DataBinder.Eval(e.Row.DataItem, "City").ToString() + "</br>" + DataBinder.Eval(e.Row.DataItem, "Country").ToString() + "</br>" + "</div>" + "</div>" + "<div style='width:50%;float:left;'>" + "<b>Contact Number : </b>" + DataBinder.Eval(e.Row.DataItem, "Phone").ToString() + "</br>" + "<b>Fax : </b>" + DataBinder.Eval(e.Row.DataItem, "Fax").ToString() + "</div>" + "</div>"; cell.ColumnSpan = 7; cell.CssClass = "GroupDetailsStyle"; row.Cells.Add(cell); grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row); intSubTotalIndex++; #region Add Group Header // Creating a Row row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert); //Adding Order ID Header Cell cell = new TableCell(); cell.Text = "Order ID"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Company Name Header Cell cell = new TableCell(); cell.Text = "Company Name"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Product Name Header Cell cell = new TableCell(); cell.Text = "Product Name"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Unit Price Header Cell cell = new TableCell(); cell.Text = "Unit Price"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Quantity Header Cell cell = new TableCell(); cell.Text = "Quantity"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Discount Header Cell cell = new TableCell(); cell.Text = "Discount"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Amount Header Cell cell = new TableCell(); cell.Text = "Amount"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding the Row at the RowIndex position in the Grid grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row); intSubTotalIndex++; #endregion } #endregion if (IsSubTotalRowNeedToAdd) { #region Adding Sub Total Row GridView grdViewProducts = (GridView)sender; // Creating a Row GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert); //Adding Total Cell TableCell cell = new TableCell(); cell.Text = "Sub Total"; cell.HorizontalAlign = HorizontalAlign.Left; cell.ColumnSpan = 3; 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 = dblSubTotalQuantity.ToString(); 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 grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row); intSubTotalIndex++; #endregion #region Adding Next Group Header Details if (DataBinder.Eval(e.Row.DataItem, "CustomerID") != null) { #region Adding Empty Row after each Group Total row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert); cell = new TableCell(); cell.Text = string.Empty; cell.Height = Unit.Parse("10px"); cell.ColumnSpan = 7; row.Cells.Add(cell); row.BorderStyle = BorderStyle.None; grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row); intSubTotalIndex++; #endregion #region Adding Next Group Header Details row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert); cell = new TableCell(); cell.Text = "<div style='width:100%;'>" + "<div style='width:50%;float:left;'>" + "<b style='float:left;'>Customer Details : </b>" + "<div style='float:left;'>" + DataBinder.Eval(e.Row.DataItem, "CompanyName").ToString() + "</br>" + DataBinder.Eval(e.Row.DataItem, "Address").ToString() + "</br>" + DataBinder.Eval(e.Row.DataItem, "City").ToString() + "</br>" + DataBinder.Eval(e.Row.DataItem, "Country").ToString() + "</br>" + "</div>" + "</div>" + "<div style='width:50%;float:left;'>" + "<b>Contact Number : </b>" + DataBinder.Eval(e.Row.DataItem, "Phone").ToString() + "</br>" + "<b>Fax : </b>" + DataBinder.Eval(e.Row.DataItem, "Fax").ToString() + "</div>" + "</div>"; cell.ColumnSpan = 7; cell.CssClass = "GroupDetailsStyle"; row.Cells.Add(cell); grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row); intSubTotalIndex++; #endregion #region Add Group Header // Creating a Row row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert); //Adding Order ID Header Cell cell = new TableCell(); cell.Text = "Order ID"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Company Name Header Cell cell = new TableCell(); cell.Text = "Company Name"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Product Name Header Cell cell = new TableCell(); cell.Text = "Product Name"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Unit Price Header Cell cell = new TableCell(); cell.Text = "Unit Price"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Quantity Header Cell cell = new TableCell(); cell.Text = "Quantity"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Discount Header Cell cell = new TableCell(); cell.Text = "Discount"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding Amount Header Cell cell = new TableCell(); cell.Text = "Amount"; cell.CssClass = "GroupHeaderStyle"; row.Cells.Add(cell); //Adding the Row at the RowIndex position in the Grid grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, row); intSubTotalIndex++; #endregion } #endregion #region Reseting the Sub Total Variables dblSubTotalUnitPrice = 0; dblSubTotalQuantity = 0; dblSubTotalDiscount = 0; dblSubTotalAmount = 0; #endregion } if (IsGrandTotalRowNeedtoAdd) { GridView grdViewProducts = (GridView)sender; #region Adding Empty Row before Grand Total GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert); TableCell cell = new TableCell(); cell.Text = string.Empty; cell.Height = Unit.Parse("10px"); cell.ColumnSpan = 7; row.Cells.Add(cell); row.BorderStyle = BorderStyle.None; grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex, row); intSubTotalIndex++; #endregion #region Grand Total Row // Creating a Row row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert); //Adding Total Cell cell = new TableCell(); cell.Text = "Grand Total"; cell.HorizontalAlign = HorizontalAlign.Left; cell.ColumnSpan = 3; 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 grdViewProducts.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 grdViewProducts_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 screen output of the implementation is follows
download the working source code in C# here and VB here.
The other links on Group Total, Grand Total in Grid View:
- Group Total, Grand Total in Grid View - Part 1
- Group Total, Grand Total in Grid View - Part 2
- Group Total, Grand Total in Grid View - Part 3
- Group Total, Grand Total in Grid View - Part 4
- Group Total, Grand Total in Grid View - Part 5
0 Responses to “Group Total and Grand Total in GridView - Part 3”
Post a Comment