Google-like Filtering of Hierarchical Grid

[Bob]

I have a Hierarchical RADGrid...

<telerik:RadGrid ID="RadGrid1" runat="server"
     GridLines="None"
     Width="900px"
     OnNeedDataSource="RadGrid1_NeedDataSource"
     oncolumncreated="RadGrid1_ColumnCreated"
     AutoGenerateHierarchy="true"
     AutoGenerateColumns="true" >
</telerik:RadGrid>

That I "adjust" using code-behind...

protected void RadGrid1_NeedDataSource(object source, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
    this.RadGrid1.DataSource = CreateDataSet();
}
private DataSet CreateDataSet()
{
    DataSet dataset = new DataSet();
    string previousFY = string.Empty;
    string currentFY = string.Empty;
    if (DateTime.Now.Month >= 7)
    {
        previousFY = DateTime.Now.Year.ToString();
        currentFY = (DateTime.Now.Year + 1).ToString();
    }
    else
    {
        previousFY = (DateTime.Now.Year - 1).ToString();
        currentFY = DateTime.Now.Year.ToString();
    }
    OpenDataDataContext db = new OpenDataDataContext();
    // FUND
    var prevFYFundTotals = from t in db.open_FY_Totals
                            join f in db.Funds on new { Fund = t.Fund } equals new { Fund = f.fieldcode }
                            where
                                t.FY == currentFY
                            group new { t, f } by new
                            {
                                t.FY,
                                Fund = t.Fund.Substring(0, 2)
                            } into g
                            orderby
                                g.Key.FY,
                                g.Key.Fund
                            select new
                            {
                                g.Key.FY,
                                g.Key.Fund,
                                Fund_Desc = g.Max(p => p.f.desc30),
                                Budget = String.Format("{0:$#,0.00}", (System.Decimal?)g.Sum(p => p.t.BudgetAmt)),
                                Actual = String.Format("{0:$#,0.00}", (System.Decimal?)g.Sum(p => p.t.ActualAmt)),
                                Encumbered = String.Format("{0:$#,0.00}", (System.Decimal?)g.Sum(p => p.t.EncAmt)),
                                Committed = String.Format("{0:$#,0.00}", (System.Decimal?)(g.Sum(p => p.t.ActualAmt) + g.Sum(p => p.t.EncAmt))),
                                PercentCommitted = String.Format("{0:0.00}%", (Decimal?)Convert.ToDecimal((g.Sum(p => p.t.ActualAmt) + g.Sum(p => p.t.EncAmt)) / g.Sum(p => p.t.BudgetAmt) * 100)),
                                Remaining = String.Format("{0:$#,0.00}", (System.Decimal?)(g.Sum(p => p.t.BudgetAmt) - (g.Sum(p => p.t.ActualAmt) + g.Sum(p => p.t.EncAmt))))
                            };
    DataTable dtPrevFYFundTotals = new DataTable();
    dtPrevFYFundTotals.TableName = "PrevFYFundTotals";
    dtPrevFYFundTotals.Columns.Add("Fund", typeof(string));
    dtPrevFYFundTotals.Columns.Add("Fund_Desc", typeof(string));
    dtPrevFYFundTotals.Columns.Add("Budget", typeof(string));
    dtPrevFYFundTotals.Columns.Add("Actual", typeof(string));
    dtPrevFYFundTotals.Columns.Add("Encumbered", typeof(string));
    dtPrevFYFundTotals.Columns.Add("Committed", typeof(string));
    dtPrevFYFundTotals.Columns.Add("PercentCommitted", typeof(string));
    dtPrevFYFundTotals.Columns.Add("Remaining", typeof(string));
    DataColumn[] keys = new DataColumn[1];
    keys[0] = dtPrevFYFundTotals.Columns["Fund"];
    dtPrevFYFundTotals.PrimaryKey = keys;
    dataset.Tables.Add(dtPrevFYFundTotals);
    foreach (var item in prevFYFundTotals)
    {
        dtPrevFYFundTotals.Rows.Add(new object[] { item.Fund, item.Fund_Desc, item.Budget, item.Actual, item.Encumbered, item.Committed, item.PercentCommitted, item.Remaining });
    }
    // RESP
    var prevFYRespTotals = from t in db.open_FY_Totals
                            join r in db.Resps on new { Resp = t.Resp } equals new { Resp = r.fieldcode }
                            where
                                t.FY == currentFY
                            group new { t, r } by new
                            {
                                t.FY,
                                Fund = t.Fund.Substring(0, 2),
                                t.Resp
                            } into g
                            orderby
                                g.Key.Fund,
                                g.Key.Resp
                            select new
                            {
                                Fund = g.Key.Fund.Substring(0, 2),
                                Resp = g.Key.Resp.Replace(".", ""),
                                Resp_Desc = g.Max(p => p.r.desc30),
                                Budget = String.Format("{0:$#,0.00}", (System.Decimal?)g.Sum(p => p.t.BudgetAmt)),
                                Actual = String.Format("{0:$#,0.00}", (System.Decimal?)g.Sum(p => p.t.ActualAmt)),
                                Encumbered = String.Format("{0:$#,0.00}", (System.Decimal?)g.Sum(p => p.t.EncAmt)),
                                Committed = String.Format("{0:$#,0.00}", (System.Decimal?)(g.Sum(p => p.t.ActualAmt) + g.Sum(p => p.t.EncAmt))),
                                PercentCommitted = String.Format("{0:0.00}%", g.Sum(p => p.t.BudgetAmt) > 0 ? ((g.Sum(p => p.t.ActualAmt) + g.Sum(p => p.t.EncAmt)) / g.Sum(p => p.t.BudgetAmt)) * 100 : 0),
                                Remaining = String.Format("{0:$#,0.00}", (System.Decimal?)(g.Sum(p => p.t.BudgetAmt) - (g.Sum(p => p.t.ActualAmt) + g.Sum(p => p.t.EncAmt))))
                            };
    DataTable dtPrevFYRespTotals = new DataTable();
    dtPrevFYRespTotals.TableName = "PrevFYRespTotals";
    dtPrevFYRespTotals.Columns.Add("Fund", typeof(string));
    dtPrevFYRespTotals.Columns.Add("Resp", typeof(string));
    dtPrevFYRespTotals.Columns.Add("Resp_Desc", typeof(string));
    dtPrevFYRespTotals.Columns.Add("Budget", typeof(string));
    dtPrevFYRespTotals.Columns.Add("Actual", typeof(string));
    dtPrevFYRespTotals.Columns.Add("Encumbered", typeof(string));
    dtPrevFYRespTotals.Columns.Add("Committed", typeof(string));
    dtPrevFYRespTotals.Columns.Add("PercentCommitted", typeof(string));
    dtPrevFYRespTotals.Columns.Add("Remaining", typeof(string));
    keys = new DataColumn[2];
    keys[0] = dtPrevFYRespTotals.Columns["Fund"];
    keys[1] = dtPrevFYRespTotals.Columns["Resp"];
    dtPrevFYRespTotals.PrimaryKey = keys;
    dataset.Tables.Add(dtPrevFYRespTotals);
    foreach (var item in prevFYRespTotals)
    {
        dtPrevFYRespTotals.Rows.Add(new object[] { item.Fund, item.Resp, item.Resp_Desc, item.Budget, item.Actual, item.Encumbered, item.Committed, item.PercentCommitted, item.Remaining });
    }
    //OBJECT
    var prevFYObjTotals = from t in db.open_FY_Totals
                            join o in db.Object_Mgts on new { Obj = t.Obj } equals new { Obj = o.fieldcode }
                            where
                            t.FY == currentFY
                            group new { t, o } by new
                            {
                                t.FY,
                                Fund = t.Fund.Substring(0, 2),
                                t.Resp,
                                t.Obj
                            } into g
                            orderby
                            g.Key.Fund,
                            g.Key.Resp,
                            g.Key.Obj
                            select new
                            {
                                Fund = g.Key.Fund.Substring(0, 2),
                                Resp = g.Key.Resp.Replace(".", ""),
                                Obj = g.Key.Obj.Replace(".", ""),
                                Obj_Desc = g.Max(p => p.o.desc30),
                                Budget = String.Format("{0:$#,0.00}", (System.Decimal?)g.Sum(p => p.t.BudgetAmt)),
                                Actual = String.Format("{0:$#,0.00}", (System.Decimal?)g.Sum(p => p.t.ActualAmt)),
                                Encumbered = String.Format("{0:$#,0.00}", (System.Decimal?)g.Sum(p => p.t.EncAmt)),
                                Committed = String.Format("{0:$#,0.00}", (System.Decimal?)(g.Sum(p => p.t.ActualAmt) + g.Sum(p => p.t.EncAmt))),
                                PercentCommitted = String.Format("{0:0.00}%", g.Sum(p => p.t.BudgetAmt) > 0 ? ((g.Sum(p => p.t.ActualAmt) + g.Sum(p => p.t.EncAmt)) / g.Sum(p => p.t.BudgetAmt)) * 100 : 0),
                                Remaining = String.Format("{0:$#,0.00}", (System.Decimal?)(g.Sum(p => p.t.BudgetAmt) - (g.Sum(p => p.t.ActualAmt) + g.Sum(p => p.t.EncAmt))))
                            };
    DataTable dtPrevFYObjTotals = new DataTable();
    dtPrevFYObjTotals.TableName = "PrevFYObjTotals";
    dtPrevFYObjTotals.Columns.Add("Fund", typeof(string));
    dtPrevFYObjTotals.Columns.Add("Resp", typeof(string));
    dtPrevFYObjTotals.Columns.Add("Obj", typeof(string));
    dtPrevFYObjTotals.Columns.Add("Obj_Desc", typeof(string));
    dtPrevFYObjTotals.Columns.Add("Budget", typeof(string));
    dtPrevFYObjTotals.Columns.Add("Actual", typeof(string));
    dtPrevFYObjTotals.Columns.Add("Encumbered", typeof(string));
    dtPrevFYObjTotals.Columns.Add("Committed", typeof(string));
    dtPrevFYObjTotals.Columns.Add("PercentCommitted", typeof(string));
    dtPrevFYObjTotals.Columns.Add("Remaining", typeof(string));
    keys = new DataColumn[3];
    keys[0] = dtPrevFYObjTotals.Columns["Fund"];
    keys[1] = dtPrevFYObjTotals.Columns["Resp"];
    keys[2] = dtPrevFYObjTotals.Columns["Obj"];
    dtPrevFYObjTotals.PrimaryKey = keys;
    dataset.Tables.Add(dtPrevFYObjTotals);
    foreach (var item in prevFYObjTotals)
    {
        dtPrevFYObjTotals.Rows.Add(new object[] { item.Fund, item.Resp, item.Obj, item.Obj_Desc, item.Budget, item.Actual, item.Encumbered, item.Committed, item.PercentCommitted, item.Remaining });
    }
    // Create Relationships
    // Fund DataTable to Resp DataTable relationship
    DataRelation FundRespRelation = new DataRelation("FundResp", dataset.Tables["PrevFYFundTotals"].Columns["Fund"],
                                                                    dataset.Tables["PrevFYRespTotals"].Columns["Fund"]);
    // Resp DataTable to Obj DataTable
    DataRelation RespObjRelation = new DataRelation("RespObj", dataset.Tables["PrevFYRespTotals"].Columns["Resp"],
                                                                    dataset.Tables["PrevFYObjTotals"].Columns["Resp"]);
    // there may not be records all the way down...
    dataset.EnforceConstraints = false;
    dataset.Relations.Add(FundRespRelation);
    dataset.Relations.Add(RespObjRelation);
    return dataset;
}
protected void RadGrid1_ColumnCreated(object sender, GridColumnCreatedEventArgs e)
{
    if (e.Column.UniqueName == "Fund")
        e.Column.Visible = false;
    else if (e.Column.UniqueName == "Resp")
        e.Column.Visible = false;
    else if (e.Column.UniqueName == "Obj")
        e.Column.Visible = false;
    else if (e.Column.HeaderText.Contains("Desc"))
    {
        e.Column.HeaderText = "Account Description";
        e.Column.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        e.Column.HeaderStyle.VerticalAlign = VerticalAlign.Bottom;
        if (e.OwnerTableView.Name == "")
            e.Column.HeaderStyle.Width = Unit.Pixel(350);
        else if (e.OwnerTableView.Name == "PrevFYRespTotals")
            e.Column.HeaderStyle.Width = Unit.Pixel(265);
        else
            e.Column.HeaderStyle.Width = Unit.Pixel(240);
    }
    else if (e.Column.HeaderText == "Budget")
    {
        e.Column.HeaderText = "Annual Budget";
        e.Column.HeaderStyle.HorizontalAlign = HorizontalAlign.Right;
        e.Column.HeaderStyle.VerticalAlign = VerticalAlign.Bottom;
        e.Column.HeaderStyle.Width = Unit.Pixel(100);
        e.Column.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
    }
    else if (e.Column.HeaderText == "Actual")
    {
        e.Column.HeaderText = "Actual Spent";
        e.Column.HeaderStyle.HorizontalAlign = HorizontalAlign.Right;
        e.Column.HeaderStyle.VerticalAlign = VerticalAlign.Bottom;
        e.Column.HeaderStyle.Width = Unit.Pixel(100);
        e.Column.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
    }
    else if (e.Column.HeaderText == "Encumbered")
    {
        e.Column.HeaderText = "Unpaid Purchase Orders";
        e.Column.HeaderStyle.HorizontalAlign = HorizontalAlign.Right;
        e.Column.HeaderStyle.VerticalAlign = VerticalAlign.Bottom;
        e.Column.HeaderStyle.Width = Unit.Pixel(100);
        e.Column.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
    }
    else if (e.Column.HeaderText == "Committed")
    {
        e.Column.HeaderText = "Total Committed";
        e.Column.HeaderStyle.HorizontalAlign = HorizontalAlign.Right;
        e.Column.HeaderStyle.VerticalAlign = VerticalAlign.Bottom;
        e.Column.HeaderStyle.Width = Unit.Pixel(100);
        e.Column.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
    }
    else if (e.Column.HeaderText == "Percent Committed")
    {
        e.Column.HeaderText = "Percent Committed";
        e.Column.HeaderStyle.HorizontalAlign = HorizontalAlign.Right;
        e.Column.HeaderStyle.VerticalAlign = VerticalAlign.Bottom;
        e.Column.HeaderStyle.Width = Unit.Pixel(50);
        e.Column.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
    }
    else if (e.Column.HeaderText == "Remaining")
    {
        e.Column.HeaderText = "Remaining Budget";
        e.Column.HeaderStyle.HorizontalAlign = HorizontalAlign.Right;
        e.Column.HeaderStyle.VerticalAlign = VerticalAlign.Bottom;
        e.Column.HeaderStyle.Width = Unit.Pixel(100);
        e.Column.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
    }
}

The result is attached...

I want to add 'Google-like filtering' but I only need to filter on the 'Account Description' column (at each level).

I looked at the Telerik.Web.Examples.Integration.GridAndCombo example and tried to use it as a jumping off point but since it isn't a hierarchical grid i started running into walls.

Can you please point me in the right direction?

[Bob]

Ok, so I figured out how to create a textbox external to RadGrid and then using .FilterExpression and .Rebind() I can get the required results (almost)

protected void RadButton1_Click(object sender, EventArgs e)
{

    string newFilter = "(it[\"Fund_Desc\"].ToString().ToLower().Contains(\"" + RadTextBox1.Text.ToLower() + "\"))";
    RadGrid1.MasterTableView.FilterExpression = newFilter;
    RadGrid1.MasterTableView.Rebind();
}
protected void RadButton3_Click(object sender, EventArgs e)
{
    string newFilter = "(it[\"Resp_Desc\"].ToString().ToLower().Contains(\"" + RadTextBox2.Text.ToLower() + "\"))";
    GridTableView nestedView = (GridTableView)RadGrid1.MasterTableView.Items[0].ChildItem.NestedTableViews[0];
    nestedView.FilterExpression = newFilter;
    nestedView.Rebind();
}

I want to be able to populate a combobox with only the values from the first column, but i want the values from all of the expanded(visible) nested views.
I then want to be able to filter all of the expanded(visible) nested views based on the combobox selection.

Example (if I have filtered to the master table to 'Fund_Desc' contains "water" which leaves two funds and I expand both funds and want to then filter both expanded fund by 'Resp_Desc' contains "admin".

I am in my 60 day trial period and need to prove this is going to work within that time or I will have to take another approach (different controls, or different 3rd party controls, or standard .NET controls)

[Tsvetina]

Hi Bob,

Thank you for contacting us.

Your requirement is possible to achieve but will require a lot of custom logic, since it interferes at a few points with the normal flow of the grid behavior.

Generally, what you can try to do is to wire the ItemCommand event of RadGrid and if e.CommandName is RadGrid.FilterCommandName to check if the column in question is being filtered and if so, to raise a flag which will indicate that the nested tables will also need to be filtered (this help article deals with accessing the filter function, filtered column, etc.).

After RadGrid filters its MasterTableView, it will rebind and the expanded state of the nested views will be lost. Therefore, you would need to implement logic that will retain the expanded state, similar to this Code Library. And at the point where you expand the collapsed nested views, if the filter flag is raised, fire a filter command for each expanded table.

Regards,
Tsvetina
the Telerik team