GridView: Grouping with Subtotals

The issue has come up a couple times on a site I work on that we would like to be able to have a GridView that provides subtotals based on grouping by a certain field.  So we wanted it to sort and group by one field -- Publisher, for example -- then for all publishers of the same value, provide a subtotal row under the last row for that publisher, then continue on with the next group of Publishers.  Basically, this is the same type of grouping that Microsoft Access provides in its reports automatically, but it is not provided as a standard feature of GridView.

So I began looking for a solution.  I found one here that was pretty close to what I wanted.  It shows how to add a subheading after each group, but doesn't do any totalling of numeric fields:
Including Subheadings in a Datagrid

So I modified that solution to also provide subtotals on the subheader line.  I use a hash table to store all the different totals.  (I have another post on creating a footer row with totals and this solution ties in pretty tightly with that.)  And the other modification I made is to make the grouping field dynamic so that it can easily be changed by sending in different parameters in the URL.  Here it is:


        protected Hashtable total;
        protected string m_groupFieldName = "";

        protected void Page_Load(object sender, EventArgs e)
{
            // get grouping type
            string groupType = Request.QueryString["group"];
            if (groupType == "A")
            {
                m_groupFieldName = "A";
                ReportTitle.InnerHtml = "Grouped by A";
            }
            else if (groupType == "B")
            {
                m_groupFieldName = "B";
                ReportTitle.InnerHtml = "Grouped by B";
            }

   string sql = "YOUR SQL HERE";
            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["EMTConnectionString"].ConnectionString);
            SqlDataAdapter myCommand = new SqlDataAdapter(sql, myConnection);
            DataSet ds = new DataSet();
            myCommand.Fill(ds);

            string curCat = "", prevCat = "";
            int i = 0;
            DataRow subheaderRow = null;

            // Loop through the rows of the data adding placeholder rows into dataset
            while (i <= ds.Tables[0].Rows.Count - 1)
            {
                curCat = ds.Tables[0].Rows[i][m_groupFieldName].ToString();
                if (i == 0)
                {
                    prevCat = curCat;
                }
                if (curCat != prevCat)
                {
                    // categories changed, so make new row
                    subheaderRow = ds.Tables[0].NewRow();

                    // for the field on which we're grouping, display the grouped value and " TOTAL"
                    subheaderRow[m_groupFieldName] = prevCat + " TOTAL";

                    // set the value of each numeric field to the running subtotal calculated below
                    // or to a calculation based on those subtotals
                    subheaderRow["NumericFieldA"] = GetTotal("NumericFieldASubTotal");
                    subheaderRow["NumericFieldB"] = GetTotal("NumericFieldBSubTotal");
                    subheaderRow["APctOfB"] = (GetTotal("NumericFieldASubTotal") / GetTotal("NumericFieldASubTotal")).ToString();

                    ds.Tables[0].Rows.InsertAt(subheaderRow, i);

                    prevCat = curCat;
                    i += 1;

                    total.Clear();
                }

                // keep running totals for numeric columns
                AddToTotal(ds.Tables[0].Rows[i]["NumericFieldA"].ToString(), "NumericFieldASubTotal");
                AddToTotal(ds.Tables[0].Rows[i]["NumericFieldB"].ToString(), "NumericFieldBSubTotal");

                i += 1;
            }

  // make the final subtotal row
            subheaderRow = ds.Tables[0].NewRow();
            subheaderRow[m_groupFieldName] = prevCat + " TOTAL";
            subheaderRow["NumericFieldA"] = GetTotal("NumericFieldASubTotal");
            subheaderRow["NumericFieldB"] = GetTotal("NumericFieldBSubTotal");
            subheaderRow["APctOfB"] = (GetTotal("NumericFieldASubTotal") / GetTotal("NumericFieldASubTotal")).ToString();
            ds.Tables[0].Rows.InsertAt(subheaderRow, i);

            reportGrid.DataSource = ds;
            reportGrid.DataBind();

}

        protected void ReportGrid_RowDataBound(Object sender, System.Web.UI.WebControls.GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                string subheadFlag = DataBinder.Eval(e.Row.DataItem, m_groupFieldName).ToString();

                // See if we have a Subheader
                if (subheadFlag.Contains(" TOTAL"))
                {
                    // Format the row
                    e.Row.Font.Bold = true;
                    e.Row.BackColor = Color.LightSalmon;
                }
            }
        }
        protected double GetTotal(string type)
        {
           try
            {
                 double result = double.Parse(total[type].ToString());
                 return result;
            }
            catch
            {
                return 0;
            }

         }
        protected double AddToTotal(string itemStr, string type)
        {
            double item;

            if (itemStr == "True")
            {
                item = 1;
            }
            else if (!double.TryParse(itemStr, out item))
            {
                item = 0;
            }

            if (total.ContainsKey(type))
            {
                total[type] = double.Parse(total[type].ToString()) + item;
            }
            else
            {
                total[type] = item;
            }

            return item;
        }