Tuesday 22 January 2013

Group Total and Grand Total in GridView – Part 9

Thirumalai M - 00:24
Labels:
We had seen many ways to show Group Total and Grand Total in GridView. In those posts, we had considered the Group Total will show at the end of the group and the Grand Total will show at the last row. But in many other requirements, we may need to show the Group Total when the group is starting (something like what it shown in Excel Pivot Table). So in this post, I am planning to post how to show the Group Total while starting the Group.

I have given two types of examples for providing more clarity on this implementation. For more understanding, here is the use case of the two examples –

First Example (One level Grouping)
  1. The records defined in the XML should bound to the Grid View in a normal way.
  2. The records should be grouped by Year and the Group Total should be shown at the beginning of each group.
  3. The Group Total must be displayed with different background color to differentiate the groups.
Second Example (Three level Grouping)

This example shows profit and loss sheet of market scripts. The data has taken from a public site.
  1. The records defined in the XML should bound to the Grid View in a normal way.
  2. The records should be grouped by Sector in first level, Name of the Company in second level and the Income/Expense details in third level. The Group Total should be shown at the beginning of each group.
  3. The Sector, Company Name, Income/Expense groups must be displayed with different background color to differentiate the groups.
This requirement talks about having three different groups, Sector is the first group and Company Name is the second group and Income/Expense is the third group. So the grid will have one or more Sector and each sector will have one or more Company Name. Each company name will have one or more Income/Export group.

Before going for actual implementation, please note the following points -
  1. To implement these examples, all the records must show in a single page of the Grid View (So, no pagination). Because for calculating the Group Total, the code required all the records must be in loop.
  2. The records must be sorted on the group wise. So all the records related to a particular group will show one after another. It will be useful for calculating cumulative values together. Keeping records in different group will be considered as separate group and cumulative values will be calculated as another separate group. As we have three groups in this example, we must sort by Sector at first and then Company Name and then Income/Export.
The First Example implementation goes as below –
The XML source which bond to the GridView
<?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 ASPX script
<asp:GridView ID="grdViewProducts" runat="server" AutoGenerateColumns="False" TabIndex="1"
    Width="100%" DataSourceID="XmlDataSource1" CssClass="grdViewOrders"
    CellPadding="4" ForeColor="Black" GridLines="Vertical"
    OnRowDataBound="grdViewProducts_RowDataBound"
    onrowcreated="grdViewProducts_RowCreated" 
    OnDataBound="grdViewProducts_DataBound"
    BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" >
    <Columns>
        <asp:BoundField DataField="" HeaderText="Year">
            <ItemStyle HorizontalAlign="Left" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="Period" HeaderText="Period">
            <ItemStyle HorizontalAlign="Left" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="AuditedBy" HeaderText="Audited By">
            <ItemStyle HorizontalAlign="Left" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="DirectRevenue" HeaderText="Direct">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="ReferralRevenue" HeaderText="Referral">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:TemplateField HeaderText="Total">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell" />
            <HeaderStyle CssClass="DataCell" />
            <ItemTemplate>
                <asp:Label runat="server" ID="lblTotalRevenue" Text="0" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <RowStyle BackColor="#F7F7DE" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <FooterStyle BackColor="#CCCC99" />
    <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
    <SelectedRowStyle BackColor="#CE5D5A" ForeColor="White" Font-Bold="True" />
    <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <AlternatingRowStyle BackColor="White" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <SortedAscendingCellStyle BackColor="#FBFBF2" />
    <SortedAscendingHeaderStyle BackColor="#848384" />
    <SortedDescendingCellStyle BackColor="#EAEAD3" />
    <SortedDescendingHeaderStyle BackColor="#575357" />
</asp:GridView>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" DataFile="Data/RevenueReport.xml"></asp:XmlDataSource>
The C# Code behind
// To keep track of the previous row Group Identifier
string strPreviousRowID = string.Empty;
int intGroupStartRowIndex = 0;

// 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;

IList<Total> TotalList;

protected void Page_Load(object sender, EventArgs e)
{
    TotalList = new List<Total>();
}

/// <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;

    if ((strPreviousRowID == string.Empty) && (e.Row.RowType == DataControlRowType.DataRow))
    {
        IsSubTotalRowNeedToAdd = true;
        intSubTotalIndex = 1;
    }

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

    if (e.Row.RowType == DataControlRowType.Footer)
        IsSubTotalRowNeedToAdd = false;

    // To add the runing total into List
    if ((e.Row.RowType == DataControlRowType.Footer) ||
        ((e.Row.RowType == DataControlRowType.DataRow) && (IsSubTotalRowNeedToAdd == true) && (strPreviousRowID != string.Empty))
        )
    {
        Total total = new Total();
        total.RowIndex = intGroupStartRowIndex;
        total.DirectRevenue = dblSubTotalDirectRevenue;
        total.ReferralRevenue = dblSubTotalReferralRevenue;
        total.TotalRevenue = dblSubTotalTotalRevenue;
        TotalList.Add(total);
    }

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

        GridViewRow SubTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

        TableCell cell = new TableCell();

        cell.Text = "Sub Total";
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.ColumnSpan = 3;
        cell.CssClass = "SubTotalRowStyle";
        cell.Text = DataBinder.Eval(e.Row.DataItem, "Year").ToString();
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblSubTotalDirectRevenue);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SubTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblSubTotalReferralRevenue);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SubTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", dblSubTotalTotalRevenue);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SubTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        //Adding the Row at the RowIndex position in the Grid
        grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, SubTotalRow);
        intGroupStartRowIndex = e.Row.RowIndex + intSubTotalIndex;
        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)
    {
        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);
    }
}

protected void grdViewProducts_DataBound(object sender, EventArgs e)
{
    foreach (Total total in TotalList)
    {
        GridViewRow row = (GridViewRow)grdViewProducts.Controls[0].Controls[total.RowIndex];
        row.Cells[1].Text = string.Format("{0:0.00}", total.DirectRevenue);
        row.Cells[2].Text = string.Format("{0:0.00}", total.ReferralRevenue);
        row.Cells[3].Text = string.Format("{0:0.00}", total.TotalRevenue);
    }
}
The Style Sheet
.SubTotalRowStyle{
    border:solid 1px Black;
    background-color:#81BEF7;
    font-weight:bold;
}
.GrandTotalRowStyle{
    border:solid 1px Black;  
    background-color:Gray;
    font-weight:bold;
}   
.DataCell
{
    border:solid 1px Black;
}
Here is the output of this example




The Second Example implementation goes as below –
The XML source which bond to the GridView
<?xml version="1.0" encoding="utf-8" ?>
<StockFinancials>
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Income" Sep12="13061.75" Sep11="10644.86" Sep10="8104.31" Sep09="6164.06" Sep08="5731.63" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Income" Sep12="1211.07" Sep11="928.32" Sep10="641.57" Sep09="484.49" Sep08="670.82" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Income" Sep12="11850.68" Sep11="9716.54" Sep10="7462.74" Sep09="5679.57" Sep08="5060.81" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Income" Sep12="32.01" Sep11="16.33" Sep10="20.59" Sep09="11.29" Sep08="-2.22" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Income" Sep12="37.33" Sep11="331.74" Sep10="158.36" Sep09="-214.24" Sep08="89.23" />

  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Expenditure" Sep12="8590.59" Sep11="7615.2" Sep10="5315.14" Sep09="3613.2" Sep08="3645.42" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Expenditure" Sep12="618.51" Sep11="436.91" Sep10="405.08" Sep09="285.54" Sep08="294.88" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Expenditure" Sep12="513.69" Sep11="446.75" Sep10="378.17" Sep09="316.82" Sep08="275.71" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Expenditure" Sep12="50.47" Sep11="133.66" Sep10="110.92" Sep09="76.17" Sep08="87.87" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Expenditure" Sep12="0" Sep11="578.18" Sep10="535.89" Sep09="446.66" Sep08="387.64" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Expenditure" Sep12="853.75" Sep11="23.84" Sep10="39.63" Sep09="21.51" Sep08="20.44" />
  <Financial Sector="Tyres" Name="MRF" BSECode="500290" NSECode="MRF" ISIN="INE883A01011" AccountSection="Expenditure" Sep12="0" Sep11="0" Sep10="0" Sep09="0" Sep08="0" />

  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Income" Sep12="6148.59" Sep11="5247.57" Sep10="3956.29" Sep09="5490.32" Sep08="3195.71" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Income" Sep12="502.51" Sep11="449.39" Sep10="279.16" Sep09="556.21" Sep08="400.64" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Income" Sep12="5646.08" Sep11="4798.18" Sep10="3677.13" Sep09="4934.11" Sep08="2795.07" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Income" Sep12="4.94" Sep11="24.05" Sep10="18.62" Sep09="22.79" Sep08="12.16" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Income" Sep12="-87.32" Sep11="178.66" Sep10="-74.17" Sep09="-73.23" Sep08="127.51" />
  
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Expenditure" Sep12="4212.64" Sep11="3739.46" Sep10="2330.59" Sep09="3476.04" Sep08="2013.1" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Expenditure" Sep12="224.32" Sep11="184.18" Sep10="165.36" Sep09="246.53" Sep08="139.79" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Expenditure" Sep12="294.8" Sep11="271.8" Sep10="253.98" Sep09="294.99" Sep08="176.72" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Expenditure" Sep12="59.53" Sep11="86.33" Sep10="60.35" Sep09="84.7" Sep08="68.65" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Expenditure" Sep12="432.17" Sep11="360.76" Sep10="310.15" Sep09="376.93" Sep08="219.47" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Expenditure" Sep12="55.6" Sep11="0.08" Sep10="0.09" Sep09="0.12" Sep08="45.39" />
  <Financial Sector="Tyres" Name="JK Tyre and Industries" BSECode="530007" NSECode="JKTYRE" ISIN="INE573A01034" AccountSection="Expenditure" Sep12="0" Sep11="0" Sep10="0" Sep09="0" Sep08="0" />

  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Income" Sep12="33542.65" Sep11="25974.05" Sep10="25706.93" Sep09="31092.55" Sep08="30788.34" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Income" Sep12="7908.1" Sep11="7108.91" Sep10="7292.43" Sep09="8117.76" Sep08="8878.85" />

  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Expenditure" Sep12="22808.5" Sep11="16957.15" Sep10="17592.57" Sep09="22725.93" Sep08="23484.24" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Expenditure" Sep12="3515.28" Sep11="2816.93" Sep10="1925.79" Sep09="1971.7" Sep08="2078.9" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Expenditure" Sep12="2888.22" Sep11="3785.13" Sep10="6056.48" Sep09="5977.72" Sep08="5834.95" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Expenditure" Sep12="524.53" Sep11="562.44" Sep10="619.5" Sep09="678.6" Sep08="578.35" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Expenditure" Sep12="5248.97" Sep11="3809.93" Sep10="2780.03" Sep09="4098.22" Sep08="3533.03" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Expenditure" Sep12="0" Sep11="0" Sep10="0" Sep09="0" Sep08="0" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Expenditure" Sep12="8843.63" Sep11="8594.16" Sep10="10221.99" Sep09="10795.14" Sep08="10855.18" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="ICICI Banks" BSECode="532174" NSECode="ICICIBANK " ISIN="INE090A01013 " AccountSection="Expenditure" Sep12="3333.37" Sep11="2380.27" Sep10="1159.81" Sep09="1931.1" Sep08="1170.05" />

  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Income" Sep12="27286.35" Sep11="19928.21" Sep10="16172.9" Sep09="16332.26" Sep08="10115" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Income" Sep12="5333.41" Sep11="4433.51" Sep10="3810.62" Sep09="3470.63" Sep08="2205.38" />

  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Expenditure" Sep12="14989.58" Sep11="9385.08" Sep10="7786.3" Sep09="8911.1" Sep08="4887.12" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Expenditure" Sep12="3399.91" Sep11="2836.04" Sep10="2289.18" Sep09="2238.2" Sep08="1301.35" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Expenditure" Sep12="2647.25" Sep11="2510.82" Sep10="3395.83" Sep09="2851.26" Sep08="974.79" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Expenditure" Sep12="542.52" Sep11="497.41" Sep10="394.39" Sep09="359.91" Sep08="271.72" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Expenditure" Sep12="5873.42" Sep11="5205.97" Sep10="3169.12" Sep09="3197.49" Sep08="3295.22" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Expenditure" Sep12="0" Sep11="0" Sep10="0" Sep09="0" Sep08="0" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Expenditure" Sep12="9241.64" Sep11="8045.36" Sep10="7703.41" Sep09="7290.66" Sep08="3935.28" />
  <Financial Sector="BANKS - PRIVATE SECTOR" Name="HDFC Bank" BSECode="500180" NSECode="HDFCBANK " ISIN="INE040A01026" AccountSection="Expenditure" Sep12="3221.46" Sep11="3004.88" Sep10="1545.11" Sep09="1356.2" Sep08="1907.8" />

</StockFinancials>
The ASPX script
<asp:GridView ID="grdViewProducts" runat="server" AutoGenerateColumns="False" TabIndex="1"
    Width="100%" DataSourceID="XmlDataSource1" CssClass="grdViewOrders"
    CellPadding="4" ForeColor="Black" GridLines="Vertical"
    OnRowDataBound="grdViewProducts_RowDataBound"
    onrowcreated="grdViewProducts_RowCreated" 
    OnDataBound="grdViewProducts_DataBound"
    BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" >
    <Columns>
        <asp:BoundField DataField="" HeaderText="Sector">
            <ItemStyle HorizontalAlign="Left" CssClass="DataCell" BorderStyle="Solid"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="" HeaderText="Name">
            <ItemStyle HorizontalAlign="Left" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="" HeaderText="Income / Expense">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="Sep12" HeaderText="Sep '12" DataFormatString="{0:N}">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="Sep11" HeaderText="Sep '11" DataFormatString="{0:N}">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="Sep10" HeaderText="Sep '10" DataFormatString="{0:N}">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="Sep09" HeaderText="Sep '09" DataFormatString="{0:N}">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
        <asp:BoundField DataField="Sep08" HeaderText="Sep '08" DataFormatString="{0:N}">
            <ItemStyle HorizontalAlign="Right" CssClass="DataCell"></ItemStyle>
            <HeaderStyle CssClass="DataCell" />
        </asp:BoundField>
    </Columns>
             
    <RowStyle BackColor="#F7F7DE" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <FooterStyle BackColor="#CCCC99" />
    <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
    <SelectedRowStyle BackColor="#CE5D5A" ForeColor="White" Font-Bold="True" />
    <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <AlternatingRowStyle BackColor="White" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />
    <SortedAscendingCellStyle BackColor="#FBFBF2" />
    <SortedAscendingHeaderStyle BackColor="#848384" />
    <SortedDescendingCellStyle BackColor="#EAEAD3" />
    <SortedDescendingHeaderStyle BackColor="#575357" />
</asp:GridView>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" DataFile="Data/StockFinancial.xml"></asp:XmlDataSource>
The C# Code behind
// To keep track of the previous row Group Identifier
string strPreviousSectorRowID = string.Empty;
string strPreviousNameRowID = string.Empty;
string strPreviousAccountSectionRowID = string.Empty;

int intSectorGroupStartRowIndex = 0;
int intNameGroupStartRowIndex = 0;
int intAccountSectionGroupStartRowIndex = 0;

// To keep track the Index of Group Total
int intSubTotalIndex = 1;

// To temporarily store Sub Total
ProfitLossTotal pfSectorGroupTotal;
ProfitLossTotal pfNameGroupTotal;
ProfitLossTotal pfAccountSectionGroupTotal;

IList<ProfitLossTotal> TotalList;

protected void Page_Load(object sender, EventArgs e)
{
    TotalList = new List<ProfitLossTotal>();
    pfSectorGroupTotal = new ProfitLossTotal();
    pfNameGroupTotal = new ProfitLossTotal();
    pfAccountSectionGroupTotal = new ProfitLossTotal();
}

/// <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 IsSectorSubTotalRowNeedToAdd = false;
    bool IsNameSubTotalRowNeedToAdd = false;
    bool IsAccountSectionSubTotalRowNeedToAdd = false;

    // This is the first row
    if ((strPreviousSectorRowID == string.Empty) && (e.Row.RowType == DataControlRowType.DataRow))
    {
        IsSectorSubTotalRowNeedToAdd = true;
        IsNameSubTotalRowNeedToAdd = true;
        IsAccountSectionSubTotalRowNeedToAdd = true;
        intSubTotalIndex = 1;
    }

    // When a group completed fully, next group started
    if ((strPreviousSectorRowID != string.Empty) &&
        (e.Row.RowType == DataControlRowType.DataRow) &&
        (strPreviousSectorRowID != DataBinder.Eval(e.Row.DataItem, "Sector").ToString())
        )
    {
        IsSectorSubTotalRowNeedToAdd = true;
        IsNameSubTotalRowNeedToAdd = true;
        IsAccountSectionSubTotalRowNeedToAdd = true;
    }

    if ((strPreviousNameRowID != string.Empty) &&
        (e.Row.RowType == DataControlRowType.DataRow) &&
        (strPreviousNameRowID != DataBinder.Eval(e.Row.DataItem, "Name").ToString())
        )
        IsNameSubTotalRowNeedToAdd = true;

    if ((strPreviousAccountSectionRowID != string.Empty) &&
        (e.Row.RowType == DataControlRowType.DataRow) &&
        (strPreviousAccountSectionRowID != DataBinder.Eval(e.Row.DataItem, "AccountSection").ToString())
        )
        IsAccountSectionSubTotalRowNeedToAdd = true;

    if (e.Row.RowType == DataControlRowType.Footer)
    {
        IsSectorSubTotalRowNeedToAdd = false;
        IsNameSubTotalRowNeedToAdd = false;
        IsAccountSectionSubTotalRowNeedToAdd = false;
    }

    // To add the runing total into List
    if ((e.Row.RowType == DataControlRowType.Footer) ||
        ((e.Row.RowType == DataControlRowType.DataRow) && (IsSectorSubTotalRowNeedToAdd == true) && (strPreviousSectorRowID != string.Empty)
        )
        )
    {
        pfSectorGroupTotal.RowIndex = intSectorGroupStartRowIndex;
        TotalList.Add(pfSectorGroupTotal);
    }

    if ((e.Row.RowType == DataControlRowType.Footer) ||
        ((e.Row.RowType == DataControlRowType.DataRow) && (IsNameSubTotalRowNeedToAdd == true) && (strPreviousNameRowID != string.Empty)
        )
        )
    {
        pfNameGroupTotal.RowIndex = intNameGroupStartRowIndex;
        TotalList.Add(pfNameGroupTotal);
    }
    if ((e.Row.RowType == DataControlRowType.Footer) ||
        ((e.Row.RowType == DataControlRowType.DataRow) && (IsAccountSectionSubTotalRowNeedToAdd == true) && (strPreviousAccountSectionRowID != string.Empty)
        )
        )
    {
        pfAccountSectionGroupTotal.RowIndex = intAccountSectionGroupStartRowIndex;
        TotalList.Add(pfAccountSectionGroupTotal);
    }


    if (IsSectorSubTotalRowNeedToAdd)
    {
        #region Sector Sub Total
        GridView grdViewProducts = (GridView)sender;

        GridViewRow SubTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

        TableCell cell = new TableCell();
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.ColumnSpan = 3;
        cell.CssClass = "SectionTotalRowStyle";
        cell.Text = DataBinder.Eval(e.Row.DataItem, "Sector").ToString();
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfSectorGroupTotal.Sep12);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfSectorGroupTotal.Sep11);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfSectorGroupTotal.Sep10);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfSectorGroupTotal.Sep09);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfSectorGroupTotal.Sep08);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "SectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

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

        pfSectorGroupTotal = new ProfitLossTotal();
        pfNameGroupTotal = new ProfitLossTotal();
        pfAccountSectionGroupTotal = new ProfitLossTotal();
        #endregion
    }

    if (IsNameSubTotalRowNeedToAdd)
    {
        #region Name Sub Total
        GridView grdViewProducts = (GridView)sender;

        GridViewRow SubTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

        TableCell cell = new TableCell();

        cell = new TableCell();
        cell.Text = string.Empty;
        cell.CssClass = "DataCell";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.ColumnSpan = 2;
        cell.CssClass = "NameTotalRowStyle";
        //cell.Text = DataBinder.Eval(e.Row.DataItem, "Name").ToString() + " (BSE : " + DataBinder.Eval(e.Row.DataItem, "BSECode").ToString() + ", NSE : " + DataBinder.Eval(e.Row.DataItem, "NSECode").ToString() + ")";
        cell.Text = DataBinder.Eval(e.Row.DataItem, "Name").ToString();
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfNameGroupTotal.Sep12);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "NameTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfNameGroupTotal.Sep11);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "NameTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfNameGroupTotal.Sep10);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "NameTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfNameGroupTotal.Sep09);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "NameTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfNameGroupTotal.Sep08);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "NameTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

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

        pfNameGroupTotal = new ProfitLossTotal();
        pfAccountSectionGroupTotal = new ProfitLossTotal();
        #endregion
    }

    if (IsAccountSectionSubTotalRowNeedToAdd)
    {
        #region Account Section Sub Total
        GridView grdViewProducts = (GridView)sender;

        GridViewRow SubTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);

        TableCell cell = new TableCell();

        cell = new TableCell();
        cell.Text = string.Empty;
        cell.CssClass = "DataCell";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Empty;
        cell.CssClass = "DataCell";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.HorizontalAlign = HorizontalAlign.Left;
        cell.CssClass = "AccountSectionTotalRowStyle";
        cell.Text = DataBinder.Eval(e.Row.DataItem, "AccountSection").ToString();
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfAccountSectionGroupTotal.Sep12);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "AccountSectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfAccountSectionGroupTotal.Sep11);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "AccountSectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfAccountSectionGroupTotal.Sep10);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "AccountSectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfAccountSectionGroupTotal.Sep09);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "AccountSectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

        cell = new TableCell();
        cell.Text = string.Format("{0:0.00}", pfAccountSectionGroupTotal.Sep08);
        cell.HorizontalAlign = HorizontalAlign.Right;
        cell.CssClass = "AccountSectionTotalRowStyle";
        SubTotalRow.Cells.Add(cell);

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

        pfAccountSectionGroupTotal = new ProfitLossTotal();
        #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 calculation of column (Total = Direct + Referral)
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        strPreviousSectorRowID = DataBinder.Eval(e.Row.DataItem, "Sector").ToString();
        strPreviousNameRowID = DataBinder.Eval(e.Row.DataItem, "Name").ToString();
        strPreviousAccountSectionRowID = DataBinder.Eval(e.Row.DataItem, "AccountSection").ToString();

        pfSectorGroupTotal.Sep12 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep12").ToString());
        pfSectorGroupTotal.Sep11 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep11").ToString());
        pfSectorGroupTotal.Sep10 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep10").ToString());
        pfSectorGroupTotal.Sep09 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep09").ToString());
        pfSectorGroupTotal.Sep08 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep08").ToString());

        pfNameGroupTotal.Sep12 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep12").ToString());
        pfNameGroupTotal.Sep11 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep11").ToString());
        pfNameGroupTotal.Sep10 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep10").ToString());
        pfNameGroupTotal.Sep09 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep09").ToString());
        pfNameGroupTotal.Sep08 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep08").ToString());

        pfAccountSectionGroupTotal.Sep12 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep12").ToString());
        pfAccountSectionGroupTotal.Sep11 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep11").ToString());
        pfAccountSectionGroupTotal.Sep10 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep10").ToString());
        pfAccountSectionGroupTotal.Sep09 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep09").ToString());
        pfAccountSectionGroupTotal.Sep08 += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Sep08").ToString());

    }
}

protected void grdViewProducts_DataBound(object sender, EventArgs e)
{
    foreach (ProfitLossTotal total in TotalList)
    {
        GridViewRow row = (GridViewRow)grdViewProducts.Controls[0].Controls[total.RowIndex];

        row.Cells[row.Cells.Count - 5].Text = string.Format("{0:0.00}", total.Sep12);
        row.Cells[row.Cells.Count - 4].Text = string.Format("{0:0.00}", total.Sep11);
        row.Cells[row.Cells.Count - 3].Text = string.Format("{0:0.00}", total.Sep10);
        row.Cells[row.Cells.Count - 2].Text = string.Format("{0:0.00}", total.Sep09);
        row.Cells[row.Cells.Count - 1].Text = string.Format("{0:0.00}", total.Sep08);
    }
}
The Style Sheet
.AccountSectionTotalRowStyle{
    border:solid 1px Black;
    background-color:#a8249d;
    font-weight:bold;
}
.NameTotalRowStyle {
    border:solid 1px Black;
    background-color:#e46144;
    font-weight:bold;
}
.SectionTotalRowStyle {
    border:solid 1px Black;
    background-color:#1c7647;
    font-weight:bold;
}
.GrandTotalRowStyle{
    border:solid 1px White;
    background-color:Gray;
    font-weight:bold;
}
.DataCell
{
    border:solid 1px Black;
}
Here is the output of this example

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


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

Post a Comment

Subscribe to: Post Comments (Atom)