Building A SPGridView Control – Part 4: Filtering Multiple Columns at Reverse Alchemy

Building A SPGridView Control – Part 4: Filtering Multiple Columns

33 Comments Published by Erik Burger February 1st, 2010 in Programming, SharePoint

In Part 2 of this series, we added filtering to our ASPGridView. Since then I’ve gotten a lot of questions about implementing multi-column filtering. Again the main reason seems to be “If SharePoint can do it, why can’t we?”. And that’s exactly the kind of question that gets me going I love a good challenge. And remember, we set out to build a control that matches the built-in functionality of SharePoint as closely as possible. So multi-column filtering should definitely be in the list of features.

Multi-column filtering comes with it’s own set of unique challenges. For one, we cannot use the same techniques we used in single-column filtering. Part of the problem is that a lot of the filtering action in the SPGridView is client side, which means we’ll need to use some JavaScript to get things working. We’ll also need to dive a lot deeper into the inner working of the SPGridView.

In order to do that and at the same time retain all the work we’ve done so far, I opted to use the Proxy Pattern. This means we create a proxy object that routes most of the functionality directly to the SPGridView object but in the case of filtering it performs some work of it’s own and then routes to the original object. It’s really very close to standard inheritance but we computer people like our patterns, don’t we?

The code for our proxy object (aptly named SPGridViewProxy) is as follows:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint.WebControls;
using Menu = Microsoft.SharePoint.WebControls.Menu;
using System.Web.UI;
using System.Reflection;
using System.Web.UI.WebControls;
 
namespace ReverseAlchemy.BuildingASPGridViewControl.Controls
{
  public class SPGridViewProxy : SPGridView, IPostBackEventHandler
  {
    protected override void OnLoad( EventArgs args )
    {
      base.OnLoad( args );
      ScriptLink.Register( this.Page, "aspgridview.js", false );
    }
 
    protected override void Render( System.Web.UI.HtmlTextWriter writer )
    {
      base.Attributes["callbackEventReference"] =
        base.Attributes["callbackEventReference"].Replace( "SPGridView_FilterCallbackHandler", 
                                                           "ASPGridView_FilterCallbackHandler" );
 
      MenuItemTemplate t = this.FindControl( "SPGridViewFilterMenuTemplate" ).Controls[3] as MenuItemTemplate;
      t.ClientOnClickScript = t.ClientOnClickScript.Replace( "__ClearFilter__", "__ClearFilter__;%FIELDNAME%" );
 
      if ( this.HeaderRow != null )
      {
        foreach ( TableCell cell in this.HeaderRow.Cells )
        {
          Menu menu = (cell.Controls[0] as Menu);
          if ( menu != null )
          {
            menu.ClientOnClickPreMenuOpen = 
              menu.ClientOnClickPreMenuOpen.Replace( "SPGridView_FilterPreMenuOpen", 
                                                     "ASPGridView_FilterPreMenuOpen" );
          }
        }
      }
 
      base.Render( writer );
    }
 
    protected new void DoFilterPostBackEventProcessing( string filterData )
    {
      string filterExpression = String.Empty;
      if ( filterData.StartsWith( "__ClearFilter__" ) )
      {
        string[] data = filterData.Split( ';' );
        string fieldName = data[1];
 
        string oldFilterFieldName = base.Attributes["filterFieldName"];
        List<string> fieldNames = new List<string>( oldFilterFieldName.Split( ';' ) );
        fieldNames.Remove( fieldName );
        base.Attributes["filterFieldName"] = String.Join( ";", fieldNames.ToArray() );
 
        string oldFilterExpression = Convert.ToString( ViewState["FilterExpression"] );
        string[] filters = oldFilterExpression.Split( new string[] { " AND " }, StringSplitOptions.RemoveEmptyEntries );
        List<string> newFilters = new List<string>();
        foreach ( string filter in filters )
        {
          if ( !filter.Contains( String.Format( "[{0}]", fieldName ) ) )
          {
            newFilters.Add( filter );
          }
        }
        filterExpression = String.Join( " AND ", newFilters.ToArray() );
      }
      else
      {
        filterExpression = ExtractFilterExpression( filterData );
      }
 
      ViewState["FilterExpression"] = filterExpression;
 
      if ( filterExpression == null ) { return; }
 
      DataSourceControl control = this.NamingContainer.FindControl( this.DataSourceID ) as DataSourceControl;
      PropertyInfo pi = control.GetType().GetProperty( "FilterExpression" );
      pi.SetValue( control, filterExpression, null );
 
      base.RequiresDataBinding = true;
    }
 
    private string ExtractFilterExpression( string filterData )
    {
      string[] data = filterData.Split( ';' );
      string fieldName = data[0];
      if ( String.IsNullOrEmpty( fieldName ) )
      {
        return null;
      }
 
      string oldFilterFieldName = base.Attributes["filterFieldName"];
      if ( oldFilterFieldName != null )
      {
        if ( !oldFilterFieldName.Contains( fieldName ) )
        {
          base.Attributes["filterFieldName"] += ";" + fieldName;
        }
      }
      else
      {
        base.Attributes.Add( "filterFieldName", fieldName );
      }
 
      string fieldValue = data[1].Replace( "%3b", ";" ).Replace( "%25", "%" );
 
      string oldFilterExpression = Convert.ToString( ViewState["FilterExpression"] );
      string newFilterExpression = String.Format( "[{0}] = '{1}'", fieldName, fieldValue );
 
      if ( !String.IsNullOrEmpty( oldFilterExpression ) )
      {
        if ( oldFilterExpression.Contains( newFilterExpression ) )
        {
          newFilterExpression = oldFilterExpression;
        }
        else
        {
          newFilterExpression = String.Join( " AND ",
                                        new string[] { oldFilterExpression, newFilterExpression } );
        }
      }
 
      return newFilterExpression;
    }
 
    private string GetValueAfterPrefix( string value, string prefix )
    {
      if ( String.IsNullOrEmpty( value ) ||
           String.IsNullOrEmpty( prefix ) ||
           !value.StartsWith( prefix ) )
      {
        return null;
      }
 
      if ( prefix.Length < value.Length )
      {
        return value.Substring( prefix.Length );
      }
 
      return String.Empty;
    }
 
    #region IPostBackEventHandler Members
 
    void IPostBackEventHandler.RaisePostBackEvent( string eventArgument )
    {
      string valueAfterPrefix = GetValueAfterPrefix( eventArgument, "__SPGridView__;" );
      if ( valueAfterPrefix != null )
      {
        string filterData = GetValueAfterPrefix( valueAfterPrefix, "__Sort__;" );
        if ( filterData == null )
        {
          filterData = GetValueAfterPrefix( valueAfterPrefix, "__Filter__;" );
          if ( filterData != null )
          {
            DoFilterPostBackEventProcessing( filterData );
          }
        }
        else
        {
          DoSortPostBackEventProcessing( filterData );
        }
      }
      else
      {
        base.RaisePostBackEvent( eventArgument );
      }
    }
 
    #endregion
  }
}

That’s quite a bit of code to wade through so I added some line numbers to make it a bit easier. I mentioned that we needed to do some JavaScript to make things work. In the OnLoad method (line 14) we register our JavaScript file. This is basically the original spgridview.js file with a few modifications.

In the Render method on line 20 we modify the callbackEventReference and the template for the filtering menu (SPGridViewFilterMenuTemplate) to reroute their events to our new JavaScript file. The menu template works exactly the same as we discussed in Part 3 of this series.

Note this line:

27	t.ClientOnClickScript = t.ClientOnClickScript.Replace( "__ClearFilter__", "__ClearFilter__;%FIELDNAME%" );

The original SPGridView doesn’t specify which filter to clear if we click the ‘Clear Filter’ option in the menu. That makes sense, because there’s always only one column being filtered. To enable multi-column filtering we need to know which filter to clear. Note that %FIELDNAME% is a Menu token (again, see Part 3 for more details).

Before we go into the other methods, let’s see what we’ve changed in the JavaScript file. Fortunately, not much. In order to render the correct menu. the client-side script needs to know whether filtering is turned on for a specified column. This is handled in the SPGridView_FilterPreMenuOpen and the SPGridView_FilterCallbackHandler methods. Below is the original code snippet inside those methods that takes care of this:

var filterCurrentlyOn = false;
if ((filterFieldName != null) &&
    (filterFieldName.length > 0) &&
    (filterFieldName == dataFieldName)) 
{
    filterCurrentlyOn = true;
}

In order to support multiple columns, we simply change the condition in line 4 to:

4	(filterFieldName.match(dataFieldName) != null)

Other than that we changed the name of the methods so they don’t conflict with the existing ones and changed some of the error messages to refer to the ASPGridView instead of the SPGridView. The modified JavaScript file lives in the LAYOUT directory of the 12 hive.

Back to our proxy object. Our goal is to reroute the filtering to our proxy and then let the actual SPGridView object take care of the rest. The IPostBackEventHandler.RaisePostBackEvent method on line 149 is a 1-on-1 copy of the original method. Its sole purpose is to make sure the DoFilterPostBackEventProcessing method of our proxy is called instead of the original.

The DoFilterPostBackEventProcessing method is where all the magic happens. In lines 48 to 76 we take the provided filter expression and build a new one. Note lines 54 and 57 where we set the filterFieldName attribute of the SPGridView control. This is used by our JavaScript to turn filtering on and off.

Lines 80 to 82 override the FilterExpression property of our DataSource control to accommodate multiple columns. Finally line 84 causes the SPGridView to re-bind its data.

And that is all there is to it. All that is left for us to do is replace all the references to the SPGridView object by references to the SPGridViewProxy object in our ASPGridView class.

You can find the source of this instalment here.

Actually, I am not completely satisfied just yet. As you’ll notice, the list of values you can filter on does not reflect the fact that you are filtering on more than one column. I’ll address that issue in a next post

I hope you’ve found this post interesting and helpful. I look forward to hearing from you.

Erik

If you liked this post, please click on one of the advertisements below. Thanks!

33 Responses to “Building A SPGridView Control – Part 4: Filtering Multiple Columns”

Feed for this Entry Trackback Address

1 Mark Parter Feb 9th, 2010 at 10:05 pm

Works a treat, thanks. Looking forward to the next installment which I presume is to fix the fact that the UI only presents to the user that one filter has been applied, when in reality it’s allowing multiple.
2 Erik Burger Feb 9th, 2010 at 10:52 pm

Hi Mark,

I am glad you liked it (and that it worked ). I am going to address a problem with the UI showing the wrong information, but I am wondering if we are talking about the same thing. Are you talking about the icon not appearing on the columns that are filtered or the filter menu not showing the correct data? If the first, that should actually work so we might have to look into that a little more. If the second, that’s the one I’ll be talking about. To illustrate, you can find a screenshot of the ASPGridView in action here.

Regards,

Erik
3 Mark Parter Feb 22nd, 2010 at 5:32 pm

Hi Erik,

I was refering to the fact that the icon (for me anyway), only shows for the last column that was filtered, although the filter is still being applied to both columns.

I had a look at your screenshot but vcan’t see what the issue is. Is it the fact that you’ve filtered and the item isn’t highlighted in the list of filter values?

This part works for me although again, it’s only being applied to the last filtered column. So the filter appears to be applied, it’s just the UI aspects which are awry.

Thanks,

Mark
4 Erik Burger Feb 22nd, 2010 at 10:25 pm

Hi Mark,

What I meant was that the possible filter values in the “Total Sales H” column are not filtered to represent the already active filters on the other two columns. What I would want to happen is that the possible filter values only contain those values that match both “J. Smith” and “Asia” (as in “SELECT FROM [Total Sales H] WHERE [NameH] = ‘J.Smith” AND [RegionH] = ‘Asia’).

As for the icons not showing correctly, I actually forgot to include the necessary changes to the grid_RowDataBound method of the ASPGridView class in my post. My apologies. I’ve updated the post.

Thanks for pointing this out.

Erik
5 Mark Parter Feb 23rd, 2010 at 12:12 am

Thanks for updating your post. At the risk of being cheecky, have you ever encountered this issue I’m experiencing?

http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment/thread/a1a64a0d-4f4e-4168-8b7e-19926cc2a056
6 Mark Parter Feb 23rd, 2010 at 12:46 am

Hi Erik,

Sorry to be a pest but I just can’t seem to get more than one filter icon showing at a time. I’ve recopied your proxy code above and checked the code in your grid_RowDataBound event of the ASPGridView class and it looks the same as what I’ve currently got.
7 Erik Burger Feb 28th, 2010 at 3:50 pm

Hi Mark,

No, I’m afraid your issue is completely new to me. The workaround provided seems way to hackish to me though so I wouldn’t bet my money on that being the solution. If you are using your own SPGridView control, maybe attaching the debugger might shed some light on the issue, or at least where it is occurring.
Also, you could try to add the user you are logging into SP with to the authorized users in your SQL database. If I had to hazard a guess, it somehow seems that the SP user’s credentials are used to try and log in to SQL, but only after the connection to SQL is refreshed. Adding the SP user’s credentials to your SQL database would then solve the issue. It’s a VERY wild guess and it still sounds hackish to me but it’s more manageable than the offered solution. I think.

Good luck,

Erik
8 Erik Burger Feb 28th, 2010 at 3:53 pm

Hi Mark,

No problem whatsoever. I provided the code so I’d like to see it work for you too Would you be able to send me your code so I can have a look at it? I am sure the problem is something simple (as these problems tend to be) but without the code I can’t think of anything to point you in the right direction. My e-mail address is eburger at reversealchemy dot nl.

If you can’t send me the code (for commercial reasons, for example) please let me know and I will try and flesh out exactly which parts of the code are involved in the setting of the icon. That might help. But having the code would be better

Erik
9 Rolf Schneider Mar 29th, 2010 at 2:28 pm

Small update:

filtering didn’t “survive” when using paging. You just have to move (line 80-82)

DataSourceControl control = this.NamingContainer.FindControl( this.DataSourceID ) as DataSourceControl;
PropertyInfo pi = control.GetType().GetProperty( “FilterExpression” );
pi.SetValue( control, filterExpression, null );
into the very beginning of the render-method. Enculapsed by a check of viewState(“FilterExpression”).

If (viewState(“FilterExpression”) != Null)
{
‘ the three lines above
}

Greets
Rolf
10 Erik Burger Apr 1st, 2010 at 3:05 pm

Hi Rolf,

Well spotted, you are correct. I remember a similar issue with sorting not “surviving” in part 2 of this series. The solution is similar.

Thanks,

Erik
11 Forrest Towne Apr 26th, 2010 at 6:36 pm

Erik,

In the Render method, where it iterates through the HeaderRow.Cells, the Cell.Controls(0) appears to be DataControlLinkButton rather than Menu. This appears to be the only control. Is this because I don’t enable filtering on all columns? So I added a Type check and only execute if the Type Is Menu. This appears to work.

Now my problem is the Filter DropDown doesn’t open. Any idea on what to check?
12 Forrest Towne Apr 26th, 2010 at 9:40 pm

Erik,

I located a couple of typos in the aspgridview.js, now the DropDownMenu opens and populates. The new problem occurs when I select a value. I get a PopUp Windows that states “The syntax of the callback value is not supported by SPGridView.” Any cluse as to where I should look?
13 Forrest Towne Apr 26th, 2010 at 10:09 pm

Erik,

I located the problem, it was due to the way I converted the C# to VB. So I no longer am getting any errors, however, sny postback appears to disable the “Clear Filter” option. Examples include paging, sorting, or selecting. It also appears that the multiple Filter option doesn’t work. I only get the last Filter applied.
14 Erik Burger Apr 27th, 2010 at 8:14 am

Hi,

When I get home tonight I will send you the full solution I created for this. I think in your changes before you broke something vital. Seeing the full code (which “works on my machine” ) might help you get your solution to work, as well.

Erik
15 Nikunj Bhanushali Apr 27th, 2010 at 1:10 pm

Hey,

Gr8 solution.. I have used your given implementation for customizing spGridview & it works execlent. I have created a Web part for it. But when I add more then one of this webparts on the page, functionality starts to break. Selecting a filter in one web part results in reseting the other one.

Havent been able to get a fix for the same..any ideas to share..?
16 Erik Burger Apr 27th, 2010 at 1:43 pm

Hi Nikunj,

I am glad to hear you liked my solution.

I recall a similar problem I encountered earlier in writing this series which was solved by using a NamingContainer.

In your case, though, things might be more complicated since JavaScript is involved. If I am right, SharePoint is basically treating both controls as one which is why the filter event is raised to both controls. What I would suggest is to attach a debugger to both your web parts and see what is being submitted back the the grids when you change/set a filter.

I am sorry for being so vague but I am currently at work and have no access to the actual code. When I get some time I will see if I can duplicate your issue and see whether I can come up with a better answer.

Best,

Erik
17 Forrest Towne Apr 27th, 2010 at 4:59 pm

Erik,

I will download your code and try it.

Where I am currently at is that the “filterFieldName” attribute is always returning null. I have step through it over and over. It looks like it is adding the new FieldName, but on the next cycle it returns null again.

Forrest
18 Forrest Towne Apr 29th, 2010 at 8:42 pm

Erik,

I added some logic to use SaveControlState/LoadControlState. I saved the FilterFieldNane, FilterFieldValue, and FilterExpression. By doing this I was able to make the multiple filter work and continue even if sorted or paged. I also got the Filter Icon I use to show up on the appropriate headers. I also have the “Clear Filter” staying enabled on all menus. What I was lacking was showing the FilterFieldValue that was currently in use. I thought it would be an easy change to the aspgridview.js.

I thought I could use the .match like you did for the FilterFieldName. Ever since I made the edit I get an error when I click on the menu.

The Error is a pop up and states that Object is expected! I tried changing the ScripLink.Register back to the original spgridview.js and I still get the same error. Any ideas on what might be happening?

Forrest
19 Forrest Towne Apr 30th, 2010 at 11:34 pm

Erik,

Thought I should update you on where I am know. I have the multiple filters now marking the proper value and keeping the “Clear Filter” enabled. I am able to clear one and have it properly disable the “Clear Filter” and unmark the value. My addition that shows the filter icon like it does in SharePoint also works. I had to do some Type checking as some columns may not have filtering enabled.

The only problem I still see, which I believe you mentioned earlier is that the dropdownlist shows all values instead of just the filtered values. Don’t know if I’ll find time to research that as they want me to get my project finished ASAP.

For the Clear Filter I find the index for the FieldName and remove the same from the FieldValues and FilterExpressions. Basically duplicating what you had already for the FieldNames.

Thanks for your help, it was extremely valuable.

Forrest
20 Erik Burger May 7th, 2010 at 8:58 am

Hi Forrest,

You are very welcome but you did most of the work yourself

Thanks for the update, I really appreciate it. I am glad you got it working. I started looking into the dropdownlist issue but didn’t get to a satisfactory (i.e. non hack) solution yet. If I do -which involves finding some time somewhere- I’ll be sure to let you know. If you figure something out in the meantime I’d love to hear it.

Erik
21 Nikunj Bhanushali May 10th, 2010 at 10:53 am

Hello Erik,

Thanks for the reply.

I tried to debug the issue & found that the Filter menu event was getting wired to the very first Grid view on the page. So in the Proxy I had to overwrite the OnClientClickScript of menu in Render method to set the Target Control ID to the current grid views ID.

Dont know what was causing such a behaviour but I managed to fix it that way.

Thanks Again

~Nikunj~
22 Erik Burger May 10th, 2010 at 4:35 pm

Hi Nikunj,

I hardly did anything but you are welcome anyway

Well done on fixing the issue. So instead of being tied to both GridViews as I suspected it was only being tied to the one. Makes sense in a weird kinda way

Thanks for posting your solution, I am sure other people will be helped by it as well.

Erik
23 Nowshad May 17th, 2010 at 7:08 am

Hi,

Your posts looks great and mu request is can i get an working example of this code?
24 Nowshad May 17th, 2010 at 11:43 am

Where to get the updated post which fixes the filter image for multiple columns and enable the clear links??????????/
25 Erik Burger May 18th, 2010 at 10:00 am

Hi,

Thanks for the compliment. Drop me a line at the address specified in the Contact menu and I’ll send you the source asap.

Erik
26 Erik Burger May 18th, 2010 at 10:03 am

Hi,

I am hoping that the source I’ll send you will fix that for you..I am having an issue with WordPress not wanting me to edit posts so I cannot fix the errors in there at the moment. The issues you describe aren’t happening in my code so I am hoping that will be the same for you. If not, we’ll fix it. Also, take a look at the other comments.

Erik
27 greg Jun 18th, 2010 at 5:41 am

This is a great post. I couldn’t find the source codes for part 4. could you please send me the codes? Thank you!

Greg
28 Erik Burger Jun 18th, 2010 at 3:25 pm

Hi Greg,

Thanks for the compliment I sent you an e-mail with the latest source.

Erik
29 tzvi Jun 21st, 2010 at 9:40 am

Thanks a million! Your code has been a big help!
I’d like to get your updated source codes for part 4.
As of the aspgridview.js file I can’t detect it on my computre, I’ll be greatfull to get a copy of it too.

Tzvi
30 Niels Jun 23rd, 2010 at 11:02 am

Hi Erik,

i love your series about the SPGridView. I’m currently using it for displaying data from an external SQL Server. After some modifications to “ExtractFilterExpression” the Filtering mechanism is working great. However as soon as i have filtered by more than one column, the “Clear Filter from XYZ” is disabled. In addition, the value by which the column is currently filtered isn’t lit up. Would you mind sending me your latest source so i can compare it to mine?

Niels
31 Erik Burger Jun 23rd, 2010 at 11:51 am

Hi Niels, tzvi,

I am glad you found my post useful. Since you are not the first (or hopefully the last) to ask about the source, I just edited the post to include a link to the latest source. Please let me know how you get on, I am always interested to hear it.

@tvzi: the aspgridview.js file is actually one of my own, so that’s why you couldn’t find it. It’s included in the download, of course

Cheers,

Erik
32 abc Jul 10th, 2010 at 5:22 am

Hi,

Could you share how editing is done in the filtered rows???
33 Erik Burger Jul 13th, 2010 at 8:25 am

Hi,

Editing in the filtered rows should be exactly the same as editing without filtering. If you can do the one, you can do the other. Is your question about how to enable row editing in the grid? If so, perhaps this post can point you in the right direction. If not, let me know and I might whip up a short post on how to enable editing in the ASPGridView.

Regards,

Erik

About

I am an independent .NET and SharePoint Developer living in The Netherlands. My main focus lies with C# and the .NET Framework.

Disclaimer

All opinions expressed in this blog are solely those of the author. You may use all the information provided here but please understand that it is provided "AS IS" and comes with no warranty of any kind.