SPGridView Deep Dive: Custom Filter Menu Values

13 September 2012

Over the last few days, I've learnt far more about SPGridView than I'd ever wanted to know, and here's a gnarly case I worked out and thought I'd share the knowledge!

As a base, I'm hoping you've set up a fully functioning SPGridView with custom sort and paging - that's covered via a combination of my previous blog posts on SPGridViews and maybe this guide to the ObjectDataSource itself. If you're accessing any substantial sort of dataset, you'll want to be using custom paging and custom sorting simply to avoid selecting massive datasets on every GridView operation. Lucky for us, SPGridView hooks into your ObjectDataSource much like a normal grid view so you shouldn't have too much trouble here.

What I was looking at doing, was seeing how far I could customize the Filter dropdown - specifically, there was a requirement in the specification I was building to for the dropdown menu to have categories, which the SPGridView would filter off.

So it kind of looks like below:

Custom Filter Menu

If you've got the normal automatic SPGridView filtering going, you'll know that it really just gives you a ginormous list of all the possible values for you to pick from, as your filtering options. That's not really ideal for a few reasons though, since if you have a large number of potential values, this operation can get really expensive (hence why we wanted to use categories).

So, the steps that I ended up doing to achieve filter by category:

  1. hook into the postback for when the FilterMenuPreOpen is called
  2. work with a hidden BoundField column
  3. ensure the sort condition is still valid
  4. javascript override for the SPGridView_FilterCallbackHandler

Hook Into The Postback For When The FilterMenuPreOpen is called

So you're probably wondering how SharePoint comes up with the filter values in the first place - it turns out that what it does behind the scenes when you open that dropdown menu is that it actually calls your ObjectDataSource's select method again (with a page count of 0) and (possibly also DataBinding to something at some stage, then) returns the correct filter values. We made our performance totally awesome by not having our select method on our ODS grab all items that ever existed, so what the filter dropdown is requesting is not ideal.

We need a way to determine within the select method, whether it's a normal select operation, or whether it's a populate call for the filter drop down menu. And luckily, someone somewhere made it possible for us to do so - we can check for the presence of the "CALLBACKPARAM" - if the filter dropdown has been called, Context.Request.Form["CALLBACKPARAM"] will contain SPGridView;FilterColumn, where FilterColumn is the internal name of the column from your DataTable.

So, at the top of your select method for your ODS, you can do something like:

if (!String.IsNullOrEmpty(Context.Request.Form["__CALLBACKPARAM"]))
        {
            string[] callbackParam = Context.Request.Form["__CALLBACKPARAM"].Split(';');
            if (callbackParam.Length == 2)
            {
                switch (callbackParam[1])
                {
                    case "MyFilterColumn":
                        dt.Rows.Add(0, ... ,callbackParam[1], ..., 0);
                        //add a row for every category you want to add to your filter menu if you are not doing a custom order
                        return dt;
                }                    
            }
        }

One thing to note is that we're still returning a DataTable, and you must make sure the columns match the DataTable your SPGridView normally binds to or SharePoint will complain and throw an error in Javascript when you try to open that dropdown. The only value you need to populate in your DataTable rows are the ones for the correct column being filtered on (which explains the 0 values in the sample above). At this stage, if you're not interested in controlling the order of the categories being rendered, you can just create a Row for each category you want and return your 'filter set' DataTable, but because I want to control the order I'm passing through the filter column name back. This will make more sense when we get to the Javascript override near the end.

Now that we've customized the contents of the Filter Menu, we need to make sure filtering still works: make sure your select method looks at the FilterExpression on your ObjectDataSource, and applies the correct "filter" to your SQL select statement, or other such data-grabbery.

However - there's some voodoo black magic which I couldn't work out where even though your ODS select method looks at the incoming filter expression, grabs data, and returns an appropriate DataTable, if the selected filter value doesn't exactly match the incoming data, the row gets discarded. It's easier to explain with an example; so say you change your Filter DataTable to return "0 - 10", "11 - 20" and "20 - 30" - you'll find that when you choose to filter on 11 - 20, the SPGridView will claim no rows were found (even though you stepped through your code and verified the DataTable being returned by the select statement has rows), because the filter column values were, say, 14, 12 and 19, but none were "11 - 20" exactly (if anyone knows why SharePoint does this, please let me know...)

So to get around this, we work with a hidden BoundField column (thanks to super colleague Alla for the suggestion)

Since we know the filter does some magic checks against the actual value of the column after our select statement, we're going to split our one column into two - one to control the Filtering, and one to control the actual data.

So take your SPGridView, and modify it:

column = new BoundField();
column.DataField = "MyFilterColumn";            
column.SortExpression = "MyFilterColumn";
column.HeaderText = "My Filter Column";            
appGridView.Columns.Add(column);

column = new BoundField();
column.DataField = "MyFilterColumnData";
column.SortExpression = "MyFilterColumnData";
column.HeaderText = "My Filter Column Data";    //won't actually be seen anyway
column.Visible = false;                         //hidden data column - swap out the values @ render time. 
appGridView.Columns.Add(column); 

And the basic idea is to fill the MyFilterColumn with the category (eg 1 - 10), and the MyFilterColumnData with the actual value (eg 5), but when the grid is being displayed, overwrite the category display for each DataRow with the actual column data (since displaying a whole bunch of 1 - 10 is not really that useful). To perform the data substitution, we can use the RowDataBound event on the SPGridView:

    private void appGridView_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        //do substitution
        if (sender != null && e.Row.RowType == DataControlRowType.DataRow)
        {
            object dataItem = e.Row.DataItem;
            e.Row.Cells[4].Text = (e.Row.DataItem as DataRowView)["MyFilterColumnData"].ToString();
        }
    }

In the example above, the index for the Cells array is the 0-based index for your SPGridView's Columns Collection - you'll want to be targeting the cell that normally displays the value of "MyFilterColumn".

After doing all this, our black magic filter-check is now happy (since the selected filter value technically matches in the background), but we see the correct results. Excellent!

At this stage, it's a good idea to make sure your Sort Condition is still valid - it should be fine since if you've set up the hidden bound columns the way I've set out above, the column name hasn't changed, but it's worth confirming sorting still works as you expect it to.

The final piece of the puzzle is the javascript override for the SPGridView_FilterCallbackHandler. As mentioned previously, you don't have to do this if you don't care about what order your filter categories are presented in - SharePoint gives it to you in alphabetical order by default as far as I can tell, which is probably fine for most cases. However, it's not okay when you've got a set of categories like:

  • 1 - 10
  • 20 - 50
  • 100 - 250

Since it goes by alphabetical order, when you open the filter dropdown, you'll actually get 1 - 10, followed by 100 - 250, then 20 - 50; not quite what we're after. We can fix this by understanding a little bit about the clientside stuff that goes on when you hit that Filter menu drop down, and a bit of reflector...

Looking in the CreateChildControls method for the SPGridView class:

[SharePointPermission(SecurityAction.Demand, ObjectModel=true)]
protected override int CreateChildControls(IEnumerable dataSource, bool dataBinding)
{
    ...

    if (this.AllowFiltering && !string.IsNullOrEmpty(this.FilterDataFields))
    {
        //some stuff

        for (int k = num1; k < Math.Min(this.Columns.Count, (int)strArrays.Length - num2); k++)
        {
            DataControlField dataControlField = this.Columns[k];
            string str = strArrays[k + num2];
            if (dataControlField != null && !string.IsNullOrEmpty(str))
            {
                //some stuff here

                if (this.HeaderRow != null)
                {
                    Menu menu = new Menu(dataControlField.HeaderText);

                    //stuff

                    string[] strArrays1 = new string[5];
                    strArrays1[0] = "SPGridView_FilterPreMenuOpen('";    // Look - a JS call
                    strArrays1[1] = SPHttpUtility.EcmaScriptStringLiteralEncode(this.ClientID);
                    strArrays1[2] = "', '%TEMPLATECLIENTID%', '%MENUCLIENTID%', '";
                    strArrays1[3] = SPHttpUtility.EcmaScriptStringLiteralEncode(str);
                    strArrays1[4] = "', event); ";
                    menu.ClientOnClickPreMenuOpen = string.Concat(strArrays1);

                    //other stuff                    

                }
            }
        }
    }
    ...
}

You can see above it's attaching a ClientOnClick event to the SPGridView that calls SPGridView_FilterPreMenuOpen; so the whole kind of cycle (in my mind) goes:

User clicks --> SPGridView_FilterPreMenuOpen (in JS) -> a javascript EvalCall --> internal callback that calls the ODS Select method --> SPGridView_FilterCallbackHandler (in JS)

So from JS it does a postback, grabs the values, and then the javascript callback handles some data related to the ODS select method call and creates all the dropdown menu items. You can see the full definitions of the SPGridViewFilterPreMenuOpen and SPGridViewFilterCallbackHandler methods in the 14 hive in the Layouts folder, inside the spgridview.debug.js file...but back to our problem.

When we generate our DataTable in the very first step (if the callbackargument was for the filter dropdown), SharePoint doesn't respect the order in which the rows are added to that DataTable. Somewhere along the line, it gets ordered alphabetically, and this is what gets thrown back as the eval result into the SPGridViewFilterCallbackHandler. So to get around it, we can extend SPGridViewFilerCallbackHandler to ignore the result of the eval call, and specify our categories at the Javascript level.

Here's an example of how to do so - I added this directly to my .ascx but you should probably have it in a separate .js file, or something:

<script type="text/javascript">
var SPGridViewFCHBase = SPGridView_FilterCallbackHandler;    //store the original definition
SPGridView_FilterCallbackHandler = function (result, context) {
    var filterList = result;
    if(filterList === "MyFilterColumn")
    {
        filterList = "1 - 10;20 - 50;100 - 250";
    }
    SPGridViewFCHBase(filterList, context);
}
</script>

So remember in the example above, where I just threw back the name of the filter column? Here we check if that's what's present, and if so, set the appropriate list of categories for the filter. It's just a semi-colon delimited list, so you can pretty much have as many categories as you like, in an order you control.

And there you have it! Performant category based filtering for your SPGridView. Hopefully the next person who has to do this will have less trouble than I did.

Interesting fact: if you look into spgridview.debug.js, you'll see that there's a blank method called SPGridView_FilterMenuSort that's never called - I suspect the SharePoint team at one stage were planning to allow you to hook into this to specify the order of your filter values, but this probably got canned somewhere along the line...

Tags: filters, ObjectDataSource, Javascript, SPGridView, SharePoint

Add a Comment

No Comments