Group Total, Grand Total in Grid View

Saturday 12 February 2011

Group Total, Grand Total in Grid View

Thirumalai M - 00:00
Labels: 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 –

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.
Declare a variable to keep track the Index of Sub Total. This will be used to place the Sub Total row in correct place.
Declare variables which are required to temporarily store the Group Total.
RowDataBound event is used to calculate the Group Total
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:

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).
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.

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

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

Reply

Anonymous says:

27 May 2011 at 19:06

Thank.
Reply

gopal says:

16 June 2011 at 11:21

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

Anonymous says:

4 April 2012 at 13:11

links down?
Reply

Thirumalai M says:

4 April 2012 at 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.
Reply

lilimissdeveloper says:

14 September 2012 at 14:02

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

xrum says:

29 January 2013 at 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
Reply

Thirumalai M says:

30 January 2013 at 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.
Reply

Anonymous says:

16 February 2013 at 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
Reply

Thirumalai M says:

17 February 2013 at 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.
Reply

Anonymous says:

11 March 2013 at 20:16

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

Thirumalai M says:

12 March 2013 at 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"
Reply

Anonymous says:

12 March 2013 at 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
Reply

Anonymous says:

13 March 2013 at 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;
}

}
Reply

Anonymous says:

13 March 2013 at 19:36

the gridview

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

Anonymous says:

15 March 2013 at 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
Reply

Anonymous says:

30 July 2013 at 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.
Reply

Thirumalai M says:

12 September 2013 at 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.
Reply

lingmaaki says:

11 March 2014 at 10:56

A good gridview tutorial

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

ling
Reply

Doug Dover says:

14 June 2014 at 03:03

I had a similar issue to one of the anonymous posters. My grid had a RadioButtonList on each row with the SelectedIndexChanged event specified. The event would not fire for the last several rows. I modified the code to only insert a "header" row for each group I needed, and I inserted no subtotal rows. It seems like for each group header row I added, one row at the bottom of the grid would not trigger the events. Any ideas??
Reply

Anonymous says:

13 March 2015 at 04:03

I used your code to create sub totals all comes fine, but wen I click the first row after first sub total the selected index is out and for the very last data row when I click subtotals disappear.

Need your expertise
Thanks
Reply

Anonymous says:

12 February 2016 at 17:48

thanks!
Reply

Anonymous says:

21 March 2017 at 04:11

Thank you for sharing this code. I have an issue where the subtotal row displays twice for each grouping... I have looked over the code all day and I do not see a difference in the download and my code. Any feedback would be helful. thanks!
Michael
Reply

Anonymous says:

22 November 2017 at 15:08

Thanks a lot.It helped me.

Thiru Technical blog

Saturday 12 February 2011

Group Total, Grand Total in Grid View

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

Post a Comment

Follow the Blog

Translate

Labels

Followers