Building A SPGridView Control – Part 2: Filtering at Reverse Alchemy

Building A SPGridView Control – Part 2: Filtering

23 Comments Published by Erik Burger May 24th, 2009 in Programming, SharePoint

Welcome to the second installment of my series on the SPGridView.

In Part 1 I introduced the SPGridView, the ObjectDataSource and the SPGridViewPager. The result was a reusable component that supports sorted and paging. In this installment, we will extend the component to support filtering.

Filtering is one of the most sought-after features of the SPGridView. Whenever a customer asks to display data in a grid, filtering is the first requirement they want implemented (sorting and paging seem to be so common they aren’t even mentioned). After all, the standard list views in SharePoint support filtering as well, don’t they?

As with sorting and paging, we do not get filtering entirely for free. However, getting filtering to work isn’t that hard.

Let’s get started. First we need to tell our SPGridView that we want to allow filtering:

// Filtering
grid.AllowFiltering = true;
grid.FilterDataFields = ",Name,Region,Total Sales";
grid.FilteredDataSourcePropertyName = "FilterExpression";
grid.FilteredDataSourcePropertyFormat = "{1} = '{0}'";

The FilterDataFields property tells the SPGridView what columns we want to be able to filter on. We want to be able to filter on all columns except the ID column. Note that instead of specifying just the columns we want to enable filtering on we leave the data field empty for those columns we do not want to be able to filter on. This explains the lone comma at the beginning of the string.

The FilteredDataSourcePropertyFormat property provides the format for our filter expression in a SQL-like syntax. The resulting string is stored in the property of the ObjectDataSource specified by the FilteredDataSourcePropertyName property.

Technically speaking the above 4 lines are enough to enable filtering on the SPGridView. But there are a few glitches.

First, if you first filter on a column and then try to sort on an other (or the same) column, the filtering is cancelled. Obviously, this is not what we want. We’d like our ASPGridView to remember it’s filter settings. To achieve this, we need to handle both the Filtering event on the ObjectDataSource and the Sorting event on the SPGridView. Add the following lines just after the ones you just added.

gridDS.Filtering += new ObjectDataSourceFilteringEventHandler( gridDS_Filtering );
grid.Sorting += new GridViewSortEventHandler(grid_Sorting);

And the implementations of the event handlers:

private void gridDS_Filtering(object sender, ObjectDataSourceFilteringEventArgs e)
{
    ViewState["FilterExpression"] = ((ObjectDataSourceView)sender).FilterExpression;
}
 
private void grid_Sorting(object sender, GridViewSortEventArgs e)
{
    if (ViewState["FilterExpression"] != null)
    {
        gridDS.FilterExpression = (string)ViewState["FilterExpression"];
    }
}

Simply enough, we store the value of the FilterExpression property in ViewState when we start filtering, then retrieve that same value and assign it back to the property when we do sorting. This works, but introduces an other glitch; when we try to remove the filter by using the Clear Filter from … command our rows are still filtered. What we need to do is clear out the ViewState property when we remove the filter. In order to do this, we need to know when the user clicks the Clear Filter from … command. This requires a little more indepth knowledge on how menu items clicks are processed in SharePoint and ASP.NET.

When a user clicks a menu item, a Javascript postback is performed, providing the server with the parameters needed to perform the command using the __EVENTTARGET and __EVENTARGUMENT variables. For example, when I filter all rows that have Asia in the Region column, the __EVENTARGUMENT variable is set to __SPGridView__;__Filter__;Region;Asia. The __EVENTTARGET variable is set to the originating control.

When a user clicks the Clear Filter from … menu item, the __EVENTTARGET variable is set to __SPGridView__;__Filter__;__ClearFilter__. To clear the ViewState we override the LoadViewState method as follows:

protected sealed override void LoadViewState( object savedState )
{
  base.LoadViewState( savedState );
 
  if ( Context.Request.Form["__EVENTARGUMENT"] != null &&
       Context.Request.Form["__EVENTARGUMENT"].EndsWith( "__ClearFilter__" ) )
  {
    // Clear FilterExpression
    ViewState.Remove( "FilterExpression" );
  }
}

Now filtering works exactly as expected. But I did mention another glitch. When we use filtering in a standard SharePoint list a small icon is added to the header of the column. Let’s go ahead and add that, too.

We’ll be adding the icon in the RowDataBound event, so first add the event handler to the CreateChildControls method:

grid.RowDataBound += new GridViewRowEventHandler( grid_RowDataBound );

Then the implementation of the event handler:

private void grid_RowDataBound( object sender, GridViewRowEventArgs e )
{
  if ( sender == null || e.Row.RowType != DataControlRowType.Header )
  {
    return;
  }
 
  SPGridView grid = sender as SPGridView;
 
  if ( String.IsNullOrEmpty( grid.FilterFieldName ) )
  {
    return;
  }
 
  // Show icon on filtered column
  for ( int i = 0; i < grid.Columns.Count; i++ )
  {
    DataControlField field = grid.Columns[i];
 
    if ( field.SortExpression == grid.FilterFieldName )
    {
      Image filterIcon = new Image();
      filterIcon.ImageUrl = "/_layouts/images/filter.gif";
      filterIcon.Style[HtmlTextWriterStyle.MarginLeft] = "2px";
 
      // If we simply add the image to the header cell it will
      // be placed in front of the title, which is not how it
      // looks in standard SharePoint. We fix this by the code
      // below.
      Literal headerText = new Literal();
      headerText.Text = field.HeaderText;
 
      PlaceHolder panel = new PlaceHolder();
      panel.Controls.Add( headerText );
      panel.Controls.Add( filterIcon );
 
      e.Row.Cells[i].Controls[0].Controls.Add( panel );
 
      break;
    }
  }
}

You can download the solution I created for this part of the series here.

I hope this post was useful to you. If so, let me know. If you find that there are things missing, I’d love to hear from you, too.

Erik

References:

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

23 Responses to “Building A SPGridView Control – Part 2: Filtering”

Feed for this Entry Trackback Address

1 Lukasz May 27th, 2009 at 5:06 pm

Hi Erik,

Thanks for really useful article. Helped a lot. One thing so far:
In filter expression grid.FilteredDataSourcePropertyFormat = “{1} LIKE ‘{0}’”; I suggest using ‘=’ instead of ‘LIKE’ as filtering columns with number values (SPField of type Integer or Number) throws an exceptions. ‘=’ in SQL can be use for text as well as for number comparisons.

Additionally line ‘if ( field.HeaderText == grid.FilterFieldName )’ in grid_RowDataBound won’t work if you have different header text than underlaying data source column name. When you are defining SortExpression on BoundField you have to use DataField propery, not HeaderText one. In this case you should compare underlying data field name there.

Hope it will help with keeping this article on high level as there isn’t many on the internet talking about SPGridView with nice, real live examples.

Keep up the good work
Lukasz
2 Erik Burger May 27th, 2009 at 6:35 pm

Hi Lukasz,

Thanks for the positive feedback, it’s always great to hear one’s work is appreciated. If there is anything you’d like to see in future articles please let me know.

You are absolutely right on the HeaderText, I will update the code as soon as I am able. However, I am not 100% sure whether changing the FilteredDataSourcePropertyFormat will work in all situations. When comparing strings you will still have to use single quotes, which will break the comparison of number values. That said, using ‘=’ is probably still closer to the desired functionality. And I am sure we’ll be able to find a solution for the “quote thing”

Erik
3 Dan Jun 17th, 2009 at 1:14 am

Great article! This is exactly what I need.

However, I receive a compile error on this line saying that DataControlField does not contain a definition for DataField:

if ( field.DataField == grid.FilterFieldName )

Should this be casted, perhaps?

Thanks,
Dan
4 Erik Burger Jun 17th, 2009 at 7:23 am

Hi Dan,

You’re absolutely right, I wasn’t paying attention. Sorry for that.

If all your columns are BoundFields, you can cast the field to this and use the DataField property. However, you cannot use this property when using TemplateFields (or ButtonFields, and possibly other derivatives of DataControlField).

Using the SortExpression property seems to work just fine for all types.

Thanks for your feedback, I hope this helps.

Erik
5 Florent Jun 17th, 2009 at 6:00 pm

Your article is really great, it gives a lot of useful hints but I still can’t manage to get the SPGridView to work. The filter menu always gets stuck on the “Loading…” (on the bottom of the opened menu) and throws a JS alert windows displaying a NullReferenceException.

The error is here :
http://i.mivi.fr/a0bd84f3b7f879c8374940076820b16f1ed23429.png

I can’t find any solution. I even tried to debug the JS code but the problem remains on the server side.

sorting with the _gridView.Sorting event does work and displays the little arrow.

filtering with the _gvds.Filtering event is never triggered

I’m using .Net 2.0 on VS2008 and an updated version of Sharepoint MOSS 2007.

Do you have any idea on how to solve this ?
6 Erik Burger Jun 17th, 2009 at 6:31 pm

Bonjour Florent,

If I am correctly seeing it in the image you provided you have ‘special’ characters in your field values. No surprises there as French does contain a fair number of those. Unfortunately, there is an issue with these ‘special’ characters not working in my current filtering solution.

I will look into it further as soon as I have some more time. The problem has something to do with either a double HtmlEncode or none at all.

Erik
7 Florent Jun 18th, 2009 at 9:43 pm

Hello. I finally found the solution to my problem. It was because the DataSource was only set in the “OnPreRender” of the WebPart, so it didn’t work on the AJAX call made for the menu.

I fixed my problem by caching the ObjectDataSource.
8 Erik Burger Jun 23rd, 2009 at 6:39 pm

Hello Florent,

That is good news! I am glad you found the solution. And thanks for getting back to me about it

Erik
9 Raj Jul 14th, 2009 at 12:59 pm

Could you please help me on How to display SQL data in SPGridView in Sharepoint?
10 Erik Burger Jul 27th, 2009 at 9:23 am

Hi Raj,

Of course! As I am using a standard DataTable as the data source for the SPGridView this shouldn’t be too hard. You select the correct data from your SQL database in the SelectData method and attach the resulting DataTable to the one I am using. Then you also need to add the right columns to the SPGridView in the GenerateColumns method.

If you run into any particular problems feel free to send me an email at burger.erik at gmail dot com.

Good luck and best regards,

Erik
11 Ton Stegeman Oct 23rd, 2009 at 11:19 am

Hi!

Great work! I added this to the new release of the Content By Type webpart. Very nice new feature that was not too difficult to add thanks to your guidance.
http://eoffice.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=21389

Thanks you very much!
12 Erik Burger Oct 25th, 2009 at 11:25 am

Hi Ton,

You are very welcome, I am glad I could help!

Erik
P.S. Give my regards to Johan Eweg (Sales). He’ll know
13 Mark Parter Nov 25th, 2009 at 2:11 pm

Thanks for posting this Erik, works a treat. However, have you managed to get it to filter in the same way as SharePoint does when presenting standard SharePoint list data? By that I mean, the ability to apply more than one filter at a time?
14 Erik Burger Nov 25th, 2009 at 6:35 pm

Hi,

I’m glad you found it useful. I do have a few ideas on how to get multiple-column filtering to work but I haven’t looked into it yet. I think I’ll do just that somewhere this week and I am sure you’ll find a new post on the topic if I figure something out

Erik
15 Vishal Nov 27th, 2009 at 8:58 am

Hi Erik
Great post indeed. i’m trying to use this post information to filter columns in my webpart,that webpart uses Site url,and list name and comma separated columns as property,also i’m giving filter columns as property,and in return it shows the relevant information irrespective of the location of the list.But while doing this,i’m not able to filter certain values,i mean text values and people values,and date time values when time is selected.I think i’m missing some small hint or point there.Can you please guide me to get this thing working.it will be a great help.if u say i can send you my code file and error screenshot as well.
Thanks In Advance
Warm Regards
Vishal Srivastava
Senior Software Engineer
16 Erik Burger Nov 28th, 2009 at 4:33 pm

Hello Vishal,

The SPGridView is able to filter anything you can write the proper expression for (assigned to the FilteredDataSourcePropertyFormat property. However, this value is pretty much static so you cannot filter anything ELSE than what matches the expression without some work.

What you can do is create the FilterExpression at runtime through gridDS_Filtering method (in the example code). Based on the type of field that is filtered you can adjust the FilterExpression property.

Let me know if this helps you out. If not we can take another look at your exact problem.

Erik
17 Robin PS Dec 16th, 2009 at 4:24 pm

Hoi Erik,

Thanks a lot!! Great article, it helped me a lot!i Keep up the good work.

Best regards,
Robin
18 Erik Burger Dec 17th, 2009 at 12:30 pm

Hoi Robin,

Thanks for your kind words, I am glad it helped you out I should be revisiting Filtering in a few days since there’s one thing that’s been bugging me so keep tuned!

Kind regards,

Erik
19 Ana Soares Jan 18th, 2010 at 6:42 pm

Hi Erik,

I have been working in this matter of spgridview, and first I have to thank you for this article that help me a lot.

but now I have been going around to achive another goal, had multiple filter working.

Does anyone ever get it working? Can you help me.

Best regards,

Ana
20 Erik Burger Jan 18th, 2010 at 7:22 pm

Hi Ana,

I am glad you found my article helpful

As for your question about multiple filtering, yes, I did get it working. The process is rather involved and I haven’t been able to write down how I achieved it. I am not really sure when I will be able to do this as I am rather busy at the moment but I will try to make it as soon as possible.

Perhaps I can make a start in helping you by e-mail? If you write me at eburger at reversealchemy dot net I can see what I can do on a bit of a shorter notice.

Best regards,

Erik
21 Ana Soares Jan 18th, 2010 at 11:30 pm

Ok,

I will wait for you answer on my e-mail.

Thank you,

Ana
22 Silvia Jun 16th, 2010 at 1:32 pm

Thanks for your Sharepoint SPGridView posts!!!!

1 Kit Menke’s Blog » Blog Archive » SharePoint’s SPGridView, filtering, and apostrophes Pingback on Sep 5th, 2009 at 7:10 am

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.