Saturday, 12 February 2011

Group Total, Grand Total in Grid View


This post concentrates on how to design the GridView to show Group Total, Grand Total as per the data.

One important point to know here is, this code will work for the grid if it shows all the records in a single page without any pagination. Why because, the group total calculated from the records which binds to the grid. For Example, if we bind 100 records to the grid and show 5th page, then the grid will bind 41 to 50 records only. So from the GridView we will not come to know other records.

The following XML file is used to bind to the Grid. (I name the XML file as RevenueReport.xml and stored under Data folder in Application path)
<?xml version="1.0" encoding="utf-8" ?>
<RevenueReport>
     
    <Data Year="2008" Period="Q1" AuditedBy="Maria Anders" DirectRevenue="12500.00" ReferralRevenue="2500.00" />
    <Data Year="2008" Period="Q2" AuditedBy="Ana Trujillo" DirectRevenue="21000.00" ReferralRevenue="8000.00" />
    <Data Year="2008" Period="Q3" AuditedBy="Antonio Moreno" DirectRevenue="20000.00" ReferralRevenue="5000.00" />
    <Data Year="2008" Period="Q4" AuditedBy="Thomas Hardy" DirectRevenue="25000.00" ReferralRevenue="1200.00" />
   
    <Data Year="2009" Period="Q1" AuditedBy="Christina Berglund" DirectRevenue="72500.00" ReferralRevenue="5000.00" />
    <Data Year="2009" Period="Q2" AuditedBy="Hanna Moos" DirectRevenue="15000.00" ReferralRevenue="6500.00" />
    <Data Year="2009" Period="Q3" AuditedBy="Thomas Hardy" DirectRevenue="25000.00" ReferralRevenue="1520.00" />
    <Data Year="2009" Period="Q4" AuditedBy="Martín Sommer" DirectRevenue="42000.00" ReferralRevenue="2580.00" />
   
    <Data Year="2010" Period="Q1" AuditedBy="Laurence Lebihan" DirectRevenue="12500.00" ReferralRevenue="1500.00" />
    <Data Year="2010" Period="Q2" AuditedBy="Elizabeth Lincoln" DirectRevenue="25000.00" ReferralRevenue="5500.00" />
    <Data Year="2010" Period="Q3" AuditedBy="Hanna Moos" DirectRevenue="12000.00" ReferralRevenue="1800.00" />
    <Data Year="2010" Period="Q4" AuditedBy="Antonio Moreno" DirectRevenue="10000.00" ReferralRevenue="1200.00" />

</RevenueReport>

The basic concept of this implementation is –
  1. Declare a variable (intPreviousRowID ) to keep track of the previous rows unique group Identifier, it can be any column value which will be unique. For example, Employee Id when Grid View contains a record for each employee, Customer Id when revenue report for each customer etc., In our example here Year is the value used as unique identifier as the grid displaying revenue report for each year).
    If you have more than one column, you can have variable for each column.

    Note: The records must be sorted in the group identifier which we are using. When one record shows in first five records and another in last five records, each record will be considered as separate group and Group Total will also be separate for each group.
  2. Declare a variable to keep track the Index of Sub Total. This will be used to place the Sub Total row in correct place.
  3. Declare variables which are required to temporarily store the Group Total.
  4. RowDataBound event is used to calculate the Group Total
  5. RowCreated event is used to create the Group Total Row and assign the Group Total value which are calculated.
I have categorized this implementation into two ways, but both the example has the same logic to calculate the Group Total, only difference is how to keep the cumulative value on each row on each columns. For Ex:
  1. Declare separate variable for each column in the page level and the cumulative value for that column will be assigned when row are getting bound (RowDataBound).
  2. If there are lots of column on the GridView required Group Total or Grand Total, instead of defining separate variable at the page level I created a separate entity class with properties for each column. In the code behind created an object of that class and the cumulative values are assigned as normal.
So understanding first example is enough and second is just for clear code.

Lets not start the implementation for the first method.

Calculating Group Total

The script of Grid View
<asp:GridView ID="grdViewProducts" runat="server" AutoGenerateColumns="False" TabIndex="1"
    Width="100%" DataSourceID="XmlDataSource1" AllowPaging="false"
    CellPadding="4" ForeColor="#333333" GridLines="None"
    OnRowDataBound="grdViewProducts_RowDataBound"
    onrowcreated="grdViewProducts_RowCreated">
    <RowStyle BackColor="#EFF3FB" />
    <Columns>
        <asp:BoundField DataField="Year" HeaderText="Year">
            <ItemStyle HorizontalAlign="Left"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="Period" HeaderText="Period">
            <ItemStyle HorizontalAlign="Left"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="AuditedBy" HeaderText="Audited By">
            <ItemStyle HorizontalAlign="Left"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="DirectRevenue" HeaderText="Direct">
            <ItemStyle HorizontalAlign="Right"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="ReferralRevenue" HeaderText="Referral">
            <ItemStyle HorizontalAlign="Right"></ItemStyle>
        </asp:BoundField>
        <asp:TemplateField HeaderText="Total">
            <ItemStyle HorizontalAlign="Right" />
            <ItemTemplate>
                <asp:Label runat="server" ID="lblTotalRevenue" Text="0" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
     
    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#D1DDF1" ForeColor="#333333" Font-Bold="True" />
    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#2461BF" />
    <AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" DataFile="Data/RevenueReport.xml"></asp:XmlDataSource>
The C# Code behind code
public partial class SubTotalGrid : System.Web.UI.Page
{
    // To keep track of the previous row Group Identifier
    int intPreviousRowID = 0;
    // To keep track the Index of Group Total
    int intSubTotalIndex = 1;

    // To temporarily store Group Total
    double dblSubTotalDirectRevenue = 0;
    double dblSubTotalReferralRevenue = 0;
    double dblSubTotalTotalRevenue = 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 grdViewProducts_RowCreated(object sender, GridViewRowEventArgs e)
    {
        bool IsTotalRowNeedToAdd = false;
        if ((intPreviousRowID > 0) && (DataBinder.Eval(e.Row.DataItem, "Year") != null))
            if (intPreviousRowID != Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Year").ToString()))
                IsTotalRowNeedToAdd = true;

        if ((intPreviousRowID > 0) && (DataBinder.Eval(e.Row.DataItem, "Year") == null))
        {
            IsTotalRowNeedToAdd = true;
            intSubTotalIndex = 0;
        }

        if (IsTotalRowNeedToAdd)
        {
            GridView grdViewProducts = (GridView)sender;

            // Creating a Row
            GridViewRow SubTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

            //Adding Total Cell 
            TableCell HeaderCell = new TableCell();
            HeaderCell.Text = "Sub Total";
            HeaderCell.HorizontalAlign = HorizontalAlign.Left;
            HeaderCell.ColumnSpan = 3; // For merging first, second row cells to one
            HeaderCell.CssClass = "SubTotalRowStyle";
            SubTotalRow.Cells.Add(HeaderCell);

            //Adding Direct Revenue Column
            HeaderCell = new TableCell();
            HeaderCell.Text = string.Format("{0:0.00}", dblSubTotalDirectRevenue);
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.CssClass = "SubTotalRowStyle";
            SubTotalRow.Cells.Add(HeaderCell);

            //Adding Referral Revenue Column
            HeaderCell = new TableCell();
            HeaderCell.Text = string.Format("{0:0.00}", dblSubTotalReferralRevenue);
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.CssClass = "SubTotalRowStyle";
            SubTotalRow.Cells.Add(HeaderCell);

            //Adding Total Revenue Column
            HeaderCell = new TableCell();
            HeaderCell.Text = string.Format("{0:0.00}", dblSubTotalTotalRevenue);
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.CssClass = "SubTotalRowStyle";
            SubTotalRow.Cells.Add(HeaderCell);

            //Adding the Row at the RowIndex position in the Grid
            grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, SubTotalRow);
            intSubTotalIndex++;
            dblSubTotalDirectRevenue = 0;
            dblSubTotalReferralRevenue = 0;
            dblSubTotalTotalRevenue = 0;
        }
    }
    
    /// <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 calculation of column (Total = Direct + Referral)
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            intPreviousRowID = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Year").ToString());
            double dblDirectRevenue = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "DirectRevenue").ToString());
            double dblReferralRevenue = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "ReferralRevenue").ToString());
            Label lblTotalRevenue = ((Label)e.Row.FindControl("lblTotalRevenue"));
            lblTotalRevenue.Text = string.Format("{0:0.00}", (dblDirectRevenue + dblReferralRevenue));

            dblSubTotalDirectRevenue += dblDirectRevenue;
            dblSubTotalReferralRevenue += dblReferralRevenue;
            dblSubTotalTotalRevenue += (dblDirectRevenue + dblReferralRevenue);
        }
    }
}
The VB code behind
Public Partial Class SubTotalGrid
    Inherits System.Web.UI.Page

    ' To keep track of the previous row Group Identifier
    Dim strPreviousRowID As String = String.Empty
    ' To keep track the Index of Group Total
    Dim intSubTotalIndex As Integer = 1

    ' To temporarily store Group Total
    Dim dblSubTotalDirectRevenue As Double = 0
    Dim dblSubTotalReferralRevenue As Double = 0
    Dim dblSubTotalTotalRevenue As Double = 0

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    ''' <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 Sub grdViewProducts_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdViewProducts.RowCreated

        Dim IsSubTotalRowNeedToAdd As Boolean = False

        If (strPreviousRowID <> String.Empty) AndAlso (DataBinder.Eval(e.Row.DataItem, "Year") IsNot Nothing) Then
            If strPreviousRowID <> DataBinder.Eval(e.Row.DataItem, "Year").ToString() Then
                IsSubTotalRowNeedToAdd = True
            End If
        End If

        If (strPreviousRowID <> String.Empty) AndAlso (DataBinder.Eval(e.Row.DataItem, "Year") Is Nothing) Then
            IsSubTotalRowNeedToAdd = True
            intSubTotalIndex = 0
        End If

        If IsSubTotalRowNeedToAdd Then
            Dim grdViewProducts As GridView = DirectCast(sender, GridView)

            ' Creating a Row
            Dim SubTotalRow As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

            'Adding Total Cell 
            Dim HeaderCell As New TableCell()
            HeaderCell.Text = "Sub Total"
            HeaderCell.HorizontalAlign = HorizontalAlign.Left
            HeaderCell.ColumnSpan = 3
            ' For merging first, second row cells to one
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding Direct Revenue Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0.00}", dblSubTotalDirectRevenue)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding Referral Revenue Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0.00}", dblSubTotalReferralRevenue)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding Total Revenue Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0.00}", dblSubTotalTotalRevenue)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding the Row at the RowIndex position in the Grid
            grdViewProducts.Controls(0).Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, SubTotalRow)
            intSubTotalIndex += 1

            dblSubTotalDirectRevenue = 0
            dblSubTotalReferralRevenue = 0
            dblSubTotalTotalRevenue = 0
        End If
    End Sub


    ''' <summary>
    ''' Event fires when data binds to each row
    ''' Used for calculating Group Total 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Protected Sub grdViewProducts_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdViewProducts.RowDataBound
        ' This is for calculation of column (Total = Direct + Referral)
        If e.Row.RowType = DataControlRowType.DataRow Then

            strPreviousRowID = DataBinder.Eval(e.Row.DataItem, "Year").ToString()

            Dim dblDirectRevenue As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "DirectRevenue").ToString())
            Dim dblReferralRevenue As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "ReferralRevenue").ToString())

            Dim lblTotalRevenue As Label = DirectCast(e.Row.FindControl("lblTotalRevenue"), Label)
            lblTotalRevenue.Text = String.Format("{0:0.00}", (dblDirectRevenue + dblReferralRevenue))

            dblSubTotalDirectRevenue += dblDirectRevenue
            dblSubTotalReferralRevenue += dblReferralRevenue
            dblSubTotalTotalRevenue += (dblDirectRevenue + dblReferralRevenue)
        End If
    End Sub
End Class
The style sheet for the Grid View
.SubTotalRowStyle{
    border:solid 1px White;
    background-color:#81BEF7;
    font-weight:bold;
}
The output of the code would be


Calculating Group Total and Grand Total

Below implementation is for Group Total and Grand Total

In this implementation, we need to have additionally one more variable for each column for keeping cumulative value for each column for Grand Total. But in Group Total the variables will be recalculated for each group from zero and in Grand Total the values will keep adding till end of the row.

The Grid View script
<asp:GridView ID="grdViewProducts" runat="server" AutoGenerateColumns="False" TabIndex="1"
    Width="100%" DataSourceID="XmlDataSource1" AllowPaging="false"
    CellPadding="4" ForeColor="#333333" GridLines="None"
    OnRowDataBound="grdViewProducts_RowDataBound"
    onrowcreated="grdViewProducts_RowCreated">
    <RowStyle BackColor="#EFF3FB" />
    <Columns>
        <asp:BoundField DataField="Year" HeaderText="Year">
            <ItemStyle HorizontalAlign="Left"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="Period" HeaderText="Period">
            <ItemStyle HorizontalAlign="Left"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="AuditedBy" HeaderText="Audited By">
            <ItemStyle HorizontalAlign="Left"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="DirectRevenue" HeaderText="Direct">
            <ItemStyle HorizontalAlign="Right"></ItemStyle>
        </asp:BoundField>
        <asp:BoundField DataField="ReferralRevenue" HeaderText="Referral">
            <ItemStyle HorizontalAlign="Right"></ItemStyle>
        </asp:BoundField>
        <asp:TemplateField HeaderText="Total">
            <ItemStyle HorizontalAlign="Right" />
            <ItemTemplate>
                <asp:Label runat="server" ID="lblTotalRevenue" Text="0" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
     
    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#D1DDF1" ForeColor="#333333" Font-Bold="True" />
    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#2461BF" />
    <AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" DataFile="Data/RevenueReport.xml"></asp:XmlDataSource>
C# Code behind
public partial class GrandTotalGrid : System.Web.UI.Page
{
    // 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 dblSubTotalDirectRevenue = 0;
    double dblSubTotalReferralRevenue = 0;
    double dblSubTotalTotalRevenue = 0;

    // To temporarily store Grand Total
    double dblGrandTotalDirectRevenue = 0;
    double dblGrandTotalReferralRevenue = 0;
    double dblGrandTotalTotalRevenue = 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 grdViewProducts_RowCreated(object sender, GridViewRowEventArgs e)
    {
        bool IsSubTotalRowNeedToAdd = false;
        bool IsGrandTotalRowNeedtoAdd = false;

        if ((strPreviousRowID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Year") != null))
            if (strPreviousRowID != DataBinder.Eval(e.Row.DataItem, "Year").ToString())
                IsSubTotalRowNeedToAdd = true;

        if ((strPreviousRowID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Year") == null))
        {
            IsSubTotalRowNeedToAdd = true;
            IsGrandTotalRowNeedtoAdd = true;
            intSubTotalIndex = 0;
        }

        if (IsSubTotalRowNeedToAdd)
        {
            GridView grdViewProducts = (GridView)sender;

            // Creating a Row
            GridViewRow SubTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

            //Adding Total Cell 
            TableCell HeaderCell = new TableCell();
            HeaderCell.Text = "Sub Total";
            HeaderCell.HorizontalAlign = HorizontalAlign.Left;
            HeaderCell.ColumnSpan = 3; // For merging first, second row cells to one
            HeaderCell.CssClass = "SubTotalRowStyle";
            SubTotalRow.Cells.Add(HeaderCell);

            //Adding Direct Revenue Column
            HeaderCell = new TableCell();
            HeaderCell.Text = string.Format("{0:0.00}", dblSubTotalDirectRevenue);
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.CssClass = "SubTotalRowStyle";
            SubTotalRow.Cells.Add(HeaderCell);

            //Adding Referral Revenue Column
            HeaderCell = new TableCell();
            HeaderCell.Text = string.Format("{0:0.00}", dblSubTotalReferralRevenue);
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.CssClass = "SubTotalRowStyle";
            SubTotalRow.Cells.Add(HeaderCell);

            //Adding Total Revenue Column
            HeaderCell = new TableCell();
            HeaderCell.Text = string.Format("{0:0.00}", dblSubTotalTotalRevenue);
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.CssClass = "SubTotalRowStyle";
            SubTotalRow.Cells.Add(HeaderCell);

            //Adding the Row at the RowIndex position in the Grid
            grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, SubTotalRow);
            intSubTotalIndex++;

            dblSubTotalDirectRevenue = 0;
            dblSubTotalReferralRevenue = 0;
            dblSubTotalTotalRevenue = 0;
        }
        if (IsGrandTotalRowNeedtoAdd)
        {
            GridView grdViewProducts = (GridView)sender;

            // Creating a Row
            GridViewRow GrandTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

            //Adding Total Cell 
            TableCell HeaderCell = new TableCell();
            HeaderCell.Text = "Grand Total";
            HeaderCell.HorizontalAlign = HorizontalAlign.Left;
            HeaderCell.ColumnSpan = 3; // For merging first, second row cells to one
            HeaderCell.CssClass = "GrandTotalRowStyle";
            GrandTotalRow.Cells.Add(HeaderCell);

            //Adding Direct Revenue Column
            HeaderCell = new TableCell();
            HeaderCell.Text = string.Format("{0:0.00}", dblGrandTotalDirectRevenue);
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.CssClass = "GrandTotalRowStyle";
            GrandTotalRow.Cells.Add(HeaderCell);

            //Adding Referral Revenue Column
            HeaderCell = new TableCell();
            HeaderCell.Text = string.Format("{0:0.00}", dblGrandTotalReferralRevenue);
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.CssClass = "GrandTotalRowStyle";
            GrandTotalRow.Cells.Add(HeaderCell);

            //Adding Total Revenue Column
            HeaderCell = new TableCell();
            HeaderCell.Text = string.Format("{0:0.00}", dblGrandTotalTotalRevenue);
            HeaderCell.HorizontalAlign = HorizontalAlign.Right;
            HeaderCell.CssClass = "GrandTotalRowStyle";
            GrandTotalRow.Cells.Add(HeaderCell);

            //Adding the Row at the RowIndex position in the Grid
            grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex, GrandTotalRow);
        }
    }

    /// <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 calculation of column (Total = Direct + Referral)
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            strPreviousRowID = DataBinder.Eval(e.Row.DataItem, "Year").ToString();

            double dblDirectRevenue = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "DirectRevenue").ToString());
            double dblReferralRevenue = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "ReferralRevenue").ToString());

            Label lblTotalRevenue = ((Label)e.Row.FindControl("lblTotalRevenue"));
            lblTotalRevenue.Text = string.Format("{0:0.00}", (dblDirectRevenue + dblReferralRevenue));

            dblSubTotalDirectRevenue += dblDirectRevenue;
            dblSubTotalReferralRevenue += dblReferralRevenue;
            dblSubTotalTotalRevenue += (dblDirectRevenue + dblReferralRevenue);

            dblGrandTotalDirectRevenue += dblDirectRevenue;
            dblGrandTotalReferralRevenue += dblReferralRevenue;
            dblGrandTotalTotalRevenue += (dblDirectRevenue + dblReferralRevenue);
        }
    }
}
The VB code behind
Public Partial Class GrandTotalGrid
    Inherits System.Web.UI.Page

    ' To keep track of the previous row Group Identifier
    Dim strPreviousRowID As String = String.Empty
    ' To keep track the Index of Group Total
    Dim intSubTotalIndex As Integer = 1

    ' To temporarily store Group Total
    Dim dblSubTotalDirectRevenue As Double = 0
    Dim dblSubTotalReferralRevenue As Double = 0
    Dim dblSubTotalTotalRevenue As Double = 0

    ' To temporarily store Group Total
    Dim dblGrandTotalDirectRevenue As Double = 0
    Dim dblGrandTotalReferralRevenue As Double = 0
    Dim dblGrandTotalTotalRevenue As Double = 0

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    ''' <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 Sub grdViewProducts_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdViewProducts.RowCreated
        Dim IsSubTotalRowNeedToAdd As Boolean = False
        Dim IsGrandTotalRowNeedtoAdd As Boolean = False

        If (strPreviousRowID <> String.Empty) AndAlso (DataBinder.Eval(e.Row.DataItem, "Year") IsNot Nothing) Then
            If strPreviousRowID <> DataBinder.Eval(e.Row.DataItem, "Year").ToString() Then
                IsSubTotalRowNeedToAdd = True
            End If
        End If

        If (strPreviousRowID <> String.Empty) AndAlso (DataBinder.Eval(e.Row.DataItem, "Year") Is Nothing) Then
            IsSubTotalRowNeedToAdd = True
            IsGrandTotalRowNeedtoAdd = True
            intSubTotalIndex = 0
        End If

        If IsSubTotalRowNeedToAdd Then
            Dim grdViewProducts As GridView = DirectCast(sender, GridView)

            ' Creating a Row
            Dim SubTotalRow As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

            'Adding Total Cell 
            Dim HeaderCell As New TableCell()
            HeaderCell.Text = "Sub Total"
            HeaderCell.HorizontalAlign = HorizontalAlign.Left
            HeaderCell.ColumnSpan = 3
            ' For merging first, second row cells to one
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding Direct Revenue Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0.00}", dblSubTotalDirectRevenue)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding Referral Revenue Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0.00}", dblSubTotalReferralRevenue)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding Total Revenue Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0.00}", dblSubTotalTotalRevenue)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "SubTotalRowStyle"
            SubTotalRow.Cells.Add(HeaderCell)

            'Adding the Row at the RowIndex position in the Grid
            grdViewProducts.Controls(0).Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, SubTotalRow)
            intSubTotalIndex += 1

            dblSubTotalDirectRevenue = 0
            dblSubTotalReferralRevenue = 0
            dblSubTotalTotalRevenue = 0
        End If
        If IsGrandTotalRowNeedtoAdd Then
            Dim grdViewProducts As GridView = DirectCast(sender, GridView)

            ' Creating a Row
            Dim GrandTotalRow As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)

            'Adding Total Cell 
            Dim HeaderCell As New TableCell()
            HeaderCell.Text = "Grand Total"
            HeaderCell.HorizontalAlign = HorizontalAlign.Left
            HeaderCell.ColumnSpan = 3
            ' For merging first, second row cells to one
            HeaderCell.CssClass = "GrandTotalRowStyle"
            GrandTotalRow.Cells.Add(HeaderCell)

            'Adding Direct Revenue Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0.00}", dblGrandTotalDirectRevenue)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "GrandTotalRowStyle"
            GrandTotalRow.Cells.Add(HeaderCell)

            'Adding Referral Revenue Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0.00}", dblGrandTotalReferralRevenue)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "GrandTotalRowStyle"
            GrandTotalRow.Cells.Add(HeaderCell)

            'Adding Total Revenue Column
            HeaderCell = New TableCell()
            HeaderCell.Text = String.Format("{0:0.00}", dblGrandTotalTotalRevenue)
            HeaderCell.HorizontalAlign = HorizontalAlign.Right
            HeaderCell.CssClass = "GrandTotalRowStyle"
            GrandTotalRow.Cells.Add(HeaderCell)

            'Adding the Row at the RowIndex position in the Grid
            'intSubTotalIndex++;
            grdViewProducts.Controls(0).Controls.AddAt(e.Row.RowIndex, GrandTotalRow)
        End If
    End Sub

    ''' <summary>
    ''' Event fires when data binds to each row
    ''' Used for calculating Group Total 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Protected Sub grdViewProducts_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdViewProducts.RowDataBound
        ' This is for calculation of column (Total = Direct + Referral)
        If e.Row.RowType = DataControlRowType.DataRow Then

            strPreviousRowID = DataBinder.Eval(e.Row.DataItem, "Year").ToString()

            Dim dblDirectRevenue As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "DirectRevenue").ToString())
            Dim dblReferralRevenue As Double = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "ReferralRevenue").ToString())

            Dim lblTotalRevenue As Label = DirectCast(e.Row.FindControl("lblTotalRevenue"), Label)
            lblTotalRevenue.Text = String.Format("{0:0.00}", (dblDirectRevenue + dblReferralRevenue))

            dblSubTotalDirectRevenue += dblDirectRevenue
            dblSubTotalReferralRevenue += dblReferralRevenue
            dblSubTotalTotalRevenue += (dblDirectRevenue + dblReferralRevenue)

            dblGrandTotalDirectRevenue += dblDirectRevenue
            dblGrandTotalReferralRevenue += dblReferralRevenue
            dblGrandTotalTotalRevenue += (dblDirectRevenue + dblReferralRevenue)
        End If
    End Sub
End Class
Style sheet
.SubTotalRowStyle{
    border:solid 1px White;
    background-color:#81BEF7;
    font-weight:bold;
}
.GrandTotalRowStyle{
    border:solid 1px White;
    background-color:Gray;
    font-weight:bold;
}
Below is the output of Grand Total example

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

Implementation on Second Method (using Class for each variable)
Defining an entity class for each column which required Total

Download the working example of the second method in C# here and in VB here

[Update Jan 21 2012]
The example code and the post has been reviewed for better understanding.



21 Responses to “Group Total, Grand Total in Grid View”

  • Anonymous says:
    27 May 2011 19:06

    Thank.

  • gopal says:
    16 June 2011 11:21

    Thank You for providing this code.......

  • Anonymous says:
    4 April 2012 13:11

    links down?

  • Thiru says:
    4 April 2012 15:06

    Hi. All links are fine. For downloading the source code, you need to login to the box.com. So just sign up box.com once and login to that.

    Or send me a mail with source links you need, I will reply with the source code.

  • lilimissdeveloper says:
    14 September 2012 14:02

    This is just awesome! I wish it was in VB.net though :) Thank you!!!

  • xrum says:
    29 January 2013 22:03

    my totals are coming up on top of the groupped data. what did i miss?

    basically looks like:

    total for something: 8
    something 4
    something4

    total for that: 9
    that 5
    that 4

  • Thirumalai M says:
    30 January 2013 10:52

    Hi xrum,
    I verified the code, it works fine in IE, Firefox. I feel you might be missing some variable declaration or using it in code for Ex: intSubTotalIndex in the code.
    Pls run the code I given in the downloadable link. if that work fine, then could be issue with your code.

  • Anonymous says:
    16 February 2013 20:08

    Hi!
    Nice code, but when I click File- view in browser in visual express studio for Web I get the following error:

    What is wrong?

    System.FormatException: Indatasträngen hade ett felaktigt format.

    Källfel:

    Ett undantag som inte hanteras genererades vid körningen av den aktuella webbegäran. Information om undantagets ursprung och plats kan identifieras med undantagsstackspårningen nedan.

    Stackspårning:


    [FormatException: Indatasträngen hade ett felaktigt format.]
    System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt) +10614203
    System.Convert.ToDouble(String value) +48
    GridViewTotal_VB.SubTotalGrid.grdViewProducts_RowDataBound(Object sender, GridViewRowEventArgs e) in D:\Blog\Previous\GridViewTotal1\GridViewTotal2_VB\GridViewTotal_VB\SubTotalGrid.aspx.vb:96
    System.Web.UI.WebControls.GridView.OnRowDataBound(GridViewRowEventArgs e) +111
    System.Web.UI.WebControls.GridView.CreateRow(Int32 rowIndex, Int32 dataSourceIndex, DataControlRowType rowType, DataControlRowState rowState, Boolean dataBind, Object dataItem, DataControlField[] fields, TableRowCollection rows, PagedDataSource pagedDataSource) +181
    System.Web.UI.WebControls.GridView.CreateChildControls(IEnumerable dataSource, Boolean dataBinding) +3724
    System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data) +67
    System.Web.UI.WebControls.GridView.PerformDataBinding(IEnumerable data) +14
    System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +123
    System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +33
    System.Web.UI.WebControls.DataBoundControl.PerformSelect() +138
    System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +30
    System.Web.UI.WebControls.GridView.DataBind() +4
    System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +105
    System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +75
    System.Web.UI.Control.EnsureChildControls() +83
    System.Web.UI.Control.PreRenderRecursiveInternal() +42
    System.Web.UI.Control.PreRenderRecursiveInternal() +168
    System.Web.UI.Control.PreRenderRecursiveInternal() +168
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +974



    --------------------------------------------------------------------------------
    Versionsinformation: Microsoft .NET Framework-version:4.0.30319; ASP.NET-version:4.0.30319.17929

  • Thirumalai M says:
    17 February 2013 16:57

    Hi, The code which I provided is in VS 2010 Ultimate. If you are using any other version, better to recompile the code and run it. Because this is not programming issue, I am unable to verify that.

  • Anonymous says:
    11 March 2013 20:16

    I used this code with RowCommand and the event doesn't fire in all rows :(

  • Thirumalai M says:
    12 March 2013 10:38

    Hi. RowCommand doesn't fire in all rows means, is it firing one row? As the control is from ASP.NET, it works fine. There must some other issue, pls try to run the example code given.

    BTW, we don't use RowCommand in this example. Are you trying some different way? Try to verify once. Hope you did not add the event in the GridView script -
    OnRowCommand="grdViewProducts_RowCommand"

  • Anonymous says:
    12 March 2013 19:44

    Hello

    Sorry for my bad english

    My gridview has 10 lines, with the subtotals has 13 lines. The RowCommand works from the 1º line to the 10º, but don't fire in the 11º, 12º and 13º line.
    When I click in the last line, they disappears and don't fire the rowcommand.
    I'm using the example in a little different way, but found no help on another pages

  • Thirumalai M says:
    12 March 2013 23:10

    If possible you pls send me a sample code to me, so I can verify the issue and let you know. You can try the example source I given in this page, and change the code as per this.

  • Anonymous says:
    13 March 2013 19:35

    The events

    protected void gvMaterialEntrada_RowCommand(object sender, GridViewCommandEventArgs e)
    {

    switch (e.CommandName)
    {
    case "Visualizar":

    string[] args = e.CommandArgument.ToString().Split('|');

    int _cdEmpresa = Convert.ToInt32(args[1]);
    int _quantidadeentrada = Convert.ToInt32(args[2]);
    int _cdMaterial = Convert.ToInt32(args[0]);
    int _almox = Convert.ToInt32(ddlAlmoxarifado.SelectedValue);


    EntradaMaterialBLL bll = new EntradaMaterialBLL();
    MaterialEntrada material = new MaterialEntrada();

    gvMaterialEntrada.DataBind();
    VisualizaMaterialInternaUC1.Carregar(_cdMaterial, _almox, _cdEmpresa, _quantidadeentrada, FW.Operacao.Update);

    break;
    }

    }

    protected void RowCreated(object sender, GridViewRowEventArgs e)
    { // Cria linhas de subtotal em tempo de execução
    bool AdicionarTotal = false;


    if ((IDColunaAnterior != "") && (DataBinder.Eval(e.Row.DataItem, "numeroMaterial") != null))
    if (IDColunaAnterior != DataBinder.Eval(e.Row.DataItem, "numeroMaterial").ToString())
    AdicionarTotal = true;

    if ((IDColunaAnterior != "") && (DataBinder.Eval(e.Row.DataItem, "numeroMaterial") == null))
    {
    AdicionarTotal = true;
    TotalIndex = 1;
    }

    //Inicia adição do subtotal
    if (AdicionarTotal && Total != 0)
    {
    GridView gvMaterialEntrada = (GridView)sender;
    GridViewRow SubTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
    TableCell HeaderCell = new TableCell();

    HeaderCell.Text = "Total: ";
    HeaderCell.HorizontalAlign = HorizontalAlign.Left;
    HeaderCell.ColumnSpan = 7;
    HeaderCell.Font.Bold = true;
    SubTotalRow.Cells.Add(HeaderCell);

    HeaderCell = new TableCell();
    HeaderCell.Text = Total.ToString();
    HeaderCell.Font.Bold = true;
    HeaderCell.HorizontalAlign = HorizontalAlign.Center;
    SubTotalRow.Cells.Add(HeaderCell);

    if (e.Row.RowType != DataControlRowType.Footer)
    {
    gvMaterialEntrada.Controls[0].Controls.AddAt(e.Row.RowIndex + TotalIndex, SubTotalRow);
    }
    else
    {
    gvMaterialEntrada.Controls[0].Controls.AddAt(e.Row.RowIndex, SubTotalRow);
    }
    TotalIndex++;
    Total = 0;
    }
    }


    protected void gvMaterialEntrada_RowDataBound(object sender, GridViewRowEventArgs e)
    {
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
    IDColunaAnterior = DataBinder.Eval(e.Row.DataItem, "numeroMaterial").ToString();
    double ContaTotal = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "quantidadeEntrada").ToString());
    Label lblTotalMaterial = ((Label)e.Row.FindControl("lblTotalMaterial"));
    lblTotalMaterial.Text = ContaTotal.ToString();
    Total += ContaTotal;
    }

    }




  • Anonymous says:
    13 March 2013 19:36

    the gridview














    <%--Permite Editar o campo --%>





































  • Anonymous says:
    15 March 2013 00:43

    I found another solution.
    Grouped subtotals via procedure and formatted the gridview rows where was the subtotal.

    the code:
    protected void RowCreated(object sender, GridViewRowEventArgs e)
    {
    if ((e.Row.RowType == DataControlRowType.DataRow) && (Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Codigo")) == 0))
    {
    e.Row.Cells[0].ColumnSpan = 7;
    for (int i = 1; i < 7; i++)
    {
    e.Row.Cells.RemoveAt(1);
    }

    }
    }



    Thanks for the help and attention :D

  • Anonymous says:
    30 July 2013 11:13

    Hi,
    i use your code and modified it to suit my data. unfortunately the subtotal and grandtotal displays double line as the figure also wrongly calculated to double. I debug and found that the rowdatabound and rowcreated been run double for each row. please advise.

  • Ommuruganandam says:
    11 September 2013 21:38

    Hello, Thanks for the wonderful article and all other parts of the articles are great.

    I incorporated your concept and tried to create a sub total row in my grid. When i debug, things are fine, but in browser, i see only blank cells in that row.

    Also when i tried to view the source, all the cells of other rows are having the text but for the subtotal rows, i can see only blank rows.

    Can you please let me know if you have any idea ?

  • Thirumalai M says:
    12 September 2013 02:07

    Please verify the example code provided in the download link. IF that works, you might be doing something different in your code where you implemented. Pls verify below

    1. Verify the CSS styles, is there any display:none, visible : hidden etc.,
    2. In the grdViewProducts_RowCreated, please verify the variable values assigning to the cell has some values. like below -
    HeaderCell.Text = string.Format("{0:0.00}", dblSubTotalDirectRevenue); // does dblSubTotalDirectRevenue has any values?
    3. Verify whether the subtotal shows in any other row, like previous row of subtotal or next row of subtotal etc., if yes, verify the intSubTotalIndex.
    4. If nothing works, You can take only two rows, and check how the values are calculated and verify if any issue. You can send me the code if your business allows by removing all security data.

  • Ommuruganandam says:
    23 September 2013 13:06

    Please pass your mail id to me @ oms024@gmail.com. I couldn't able to paste the code here.

    Thanks.

  • lingmaaki says:
    11 March 2014 10:56

    A good gridview tutorial

    http://asp.net-informations.com/gridview/asp-gridview.htm

    ling

Post a Comment