Reverse Alchemy

Building A SPGridView Control – Part 2: Filtering

46 Comments Published 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!

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

Feed for this Entry Trackback Address

1 Lukasz May 27th, 2009 at 17:06

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 18:35

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 01:14

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 07:23

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 18:00

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 18:31

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 21:43

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 18:39

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

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

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

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

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 14:11

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 18:35

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 08:58

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 16:33

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 16:24

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

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 18:42

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 19:22

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 23:30

Ok,

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

Thank you,

Ana
22 Silvia Jun 16th, 2010 at 13:32

Thanks for your Sharepoint SPGridView posts!!!!
23 Hang Nguyen Oct 5th, 2010 at 11:32

Thanks a lot Erik. Your post was great!!!
24 Mlsoun Feb 2nd, 2011 at 23:41

Hello mistr, precise topic, hmm

one question:
I have myWeb
on myWeb I have to many subWebs.
each subWeb have only 1 SPList with only 1 SPListItem

I would like to set all this items to DataTable and show them in your SPGridView, BUT
method SelectData() with set breakpoint is calling to many time – allways from grid.Columns.Add( column );

and this is to hi load for my server. Have you any idea?
25 Erik Burger Feb 9th, 2011 at 06:53

Hi Mlsoun,

I am not sure why SelectData would be called multiple times (that is, more than two), especially from that location in the code. I’d have to look into that further. I think the best approach to your problem, however, is to see if you cannot fix your architecture on a SharePoint list level (perhaps by making a top-level list which can be accessed by the webparts in your subwebs?). Having so many SPLists with only a single SPListItem seems to be very inefficient.

Erik
26 Sandeep Pote Feb 18th, 2011 at 12:27

Hi Erik,

This is very helpful post, but am facing some problems in clearing the filter if I have multiple grids in a single page.
If I have two grids on a page and clear the filter of second grid, filtering of first grid takes place, and second time the second grid filter happens, am not able to make why this is happening.

Tried to dig into the script of the menu controls of grid 2, where in I could find the ClientOnClickPreMenuOpen property of menu control sets the control id of grid 1. Following is the code written for the same on prerender event.

if (control is Microsoft.SharePoint.WebControls.Menu)
{
Microsoft.SharePoint.WebControls.Menu menuControl = control as Microsoft.SharePoint.WebControls.Menu;
jsFunctionCall = jsFunctionCall.Replace(“%TEMPLATECLIENTID%”, this.GridView.ClientID + “_SPGridViewFilterMenuTemplate”);
menuControl.ClientOnClickPreMenuOpen = jsFunctionCall.Replace(“%MENUCLIENTID%”, menuControl.ClientID);
}

Please see if you can help me out in this.

Thanks and Regards,
Sandeep
27 Jyothsna Apr 26th, 2011 at 13:29

Hi ERik,
I am not able to apply sorting and filtering in my aspx page.
I am not using webpart. I am using aspx page where i have my spgridview.
I want the sorting on click on column name.
I even want the dropdown beside the column which contains ascending,descending,clear filter and all others which comes for out of box listview webpart column.
It is throwing error when i click on ascending,descedning. and javascript error is occuring on click of the filter dropdown on column.
means no part of above code is working..
can you please help me what might be the mistake?
Please help me it is very urgent….

Thanks,
Jyothsna
28 Erik Burger Apr 26th, 2011 at 13:38

Hi Jyothsna,

It could be that because you are not using a web part that your page does not have access to the required JavaScript files. The SPGRidView is specific to SharePoint and had quite a lot of JavaScript dependencies. What kind of errors are you getting?

Regards,
Erik
29 Jyothsna Apr 27th, 2011 at 07:55

“Error: SPGridView_FilterCallbackerrorHandler() was called result=There was an error in the callback.”
This is the javascript error which is coming up and even on click of ascending or descending it goes to yellow page(eror).How can it work with out writing an event code in sorting?????
But in the above code no code is written in sorting event….

Thanks,
Jyothsna
30 Erik Burger Apr 28th, 2011 at 15:37

Hi Jyothsna,

Sorting is actually provided by the DataSource and all you have to tell the SPGridView is that sorting should be enabled. It’s a bit of magic.

It still seems to me that there may be a problem with access to the required JS files. I must admit I have never used the ASPGridView in an aspx page but I do know that if you use a web part a lot of references are wired up for you automatically.

I’m sorry I cannot help you further.

Erik
31 Elad Rosenberg Sep 6th, 2011 at 13:28

Hi,
Great article.
my filtering is also stuck with loading… and i dont have special chars…
i didnt understand Florent solution, i just copy & paste the code from artile.

thanks in advance
32 Elad Rosenberg Sep 7th, 2011 at 12:53

Hi,
great article but notice that if u combine this with AllowGrouping=true u will suffer from run time error when postback (sorting, paging).
To solve this just make the SPGrid enableViewState = false.
also see this :
http://mossindepth.blogspot.com/2009/05/spgridview-grouping-is-it-easy-to-apply.html
33 Erik Burger Sep 7th, 2011 at 12:59

Thanks for sharing that Elad, glad you figured it out .

Erik
34 akko Sep 16th, 2011 at 14:21

@Elad, How did you do it?
I sign the spgriview.groupfield = “title”; and
set the spgridview.enableViewstate = false.
but it doesn’t help. Can you explain more?

I read your attached link. but i doen’t solved problem, in this example.

btw, in this example, it set viewstate for sorting, filtering. so if you display the viewstate for grouping, do sorting and filtering works?
35 Althaf Dec 21st, 2011 at 16:40

Great article. I could make sort and filtering work absolutely fine but on integer (number field) columns, the filter dropdown does not show values in ascending order. Filter shows as 1,11, 100, 2, 4, 41 instead of showing 1,2,4,11,41,100. How do we make filter expression understand that this column is integer type?
36 Erik Burger Dec 22nd, 2011 at 10:26
Hi Althaf,

Thank you for your compliment. I am glad the code seems to be working well for you.

We set the filter expression as follows:
grid.FilteredDataSourcePropertyFormat = "{1} = '{0}'"
As you can see there we use single quotes around the filter value. This causes the value to be treated as a string. Removing the quotes should resolve the problem with integers. You will need to come up with a way to determine whether the value to be filtered is a string or integer.

I hope this helps,

Erik
37 Althaf Dec 23rd, 2011 at 00:32

As far as my understanding goes, filter expression is used to filter the data i.e. after choosing the value from the filter dropdown. I tried putting without quotes as well but did not help.

I am looking to get the filter dropdown values itself in asc/desc order based on their datasource column type. Is there any datasource/grid event to track the values assigned to filter dropdown that gets the distinct set of values from the entire recordset binded to the grid?
38 Erik Burger Dec 29th, 2011 at 13:05

Hi Althaf,

Ah I misunderstood your question then, my apologies. I am not sure about the answer but I would start by taking a look at the javascript attached to the SPGridView. The filter dropdown is built there. That may also provide a hint as to which method is called to return the recordset.

I hope this helps.

Erik
39 Denis Fedynich Jan 10th, 2012 at 17:07

Hello, Erik

Great article. Thanks a lot for its sharing.

I don’t know, how it can be possible, because as I see, all functions worked well for all previous users. But I have a problem with sorting. I have just made copy-paste of the source code and had the exception while trying to use sorting from the drop-down menu. The exception was like ‘System.IndexOutOfRangeException: Cannot find column Region. at System.Data.DataTable.ParseSortString(String sortString)…..’. I want to mention, that if I click on the header sorting goes well, but if I use Sorting from the drop down menu I got this error. It doesn’t matter what column I use. The exception occurs on every of them. Do you have any ideas about this situation?
40 Erik Burger Jan 11th, 2012 at 12:22

Hello Denis,

Thank you for the compliment. Could it be that you forgot to include the js file specific for the aspgridview? As far as I can remember (and unfortunately it’s been a while) the dropdown menu is managed by the javascript.

Kind regards,

Erik
41 Sathish May 18th, 2012 at 14:46

This is a very very good article except that I could not get paging to work when filtering is applied. If you apply a filter on a column and then click the next page, it gets all the data back. It’s like pager control is not getting the filtered data for it to get the correct count.
42 Sathish May 18th, 2012 at 16:16

For the pager to work when filtering, you need to do the following:

1. Add the below 2 lines under this line : pager.GridViewId = grid.ID;

pager.ClickNext += new EventHandler(pager_ClickNext);
pager.ClickPrevious += new EventHandler(pager_ClickPrevious);

2. Handle these 2 events as below. important thing to note here is to restore the filter when page is changing.

void pager_ClickPrevious(object sender, EventArgs e)
{
if (ViewState[“FilterExpression”] != null)
{
gridDS.FilterExpression = (string)ViewState[“FilterExpression”];
}
}

void pager_ClickNext(object sender, EventArgs e)
{
if (ViewState[“FilterExpression”] != null)
{
gridDS.FilterExpression = (string)ViewState[“FilterExpression”];
}
}
43 Erik Burger Jul 16th, 2012 at 08:58

Thanks Sathish!
44 Matt Jun 18th, 2012 at 19:45

Hi Erik,

Thanks so much for the awesome tutorial. I’m having an issue with the filtering piece. Instead of a control I’m implementing this directly on an application page. Everything works great execpt that the Filtering event is not firing on my datasource. The filter oddly enough is working, but b/c the event is not firing the filter is never getting stored into the ViewState. Any ideas?
45 Raj Jun 25th, 2012 at 11:10

Hi Erik,

Great Article. Thank you so much for sharing this. I have integrated the filtering and sorting code in my appliation and it works fine.

But there is one issue I am facing here. On one of the columns inside the SPGridView, I have both filtering and sorting enabled. Some of the rows in this column are hyperlinks. I have configured filtering as mentioned above.

Assigning the BoundField.HtmlEncode = false while creating the column, I am able to prevent hyperlink field values from being HTML-encoded inside the SPGridView. The issue I am facing is that on click of the Filter dropdown, the hyperlink field in the filter is showing the full HTML-encoded text in the dropdown for the filter. I have mentioned the same issue with screenshots in the below URL.

http://social.msdn.microsoft.com/Forums/en/sharepointdevelopment/thread/c4037fe0-f191-4ca5-adfa-9b89af1577d1

Could you please let me know what could be the issue here?

Regards,

Raj

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

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.

Building A SPGridView Control – Part 2: Filtering

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

Leave a Reply

About

Disclaimer

Categories

Recent Comments

Archives