Custom Filters in SPGridView

There is an excellent Blog about SPGridView and filters at http://www.sharepointblogs.com/bobsbonanza/archive/2007/07/02/filtering-with-spgridview.aspx, It pretty much covered what I was looking to accomplish in my Grid View control.

My Grid View was querying a large SQL table and displaying paged records using SPGridView and off course the Filters! The problem I had was as the Table was pretty large and we were doing paging in stored procedure and bringing back only 10 records per page. Now the issue is SPGridView will only show Filter values from the 10 records that came back from the stored proc.

So What if you want to show unique Filter values from all the rows from the DB? Luckily it’s quite simply. When header in SPGridView is clicked it makes a Callback to the page and if you look at the Request.Form collection it sends __CALLBACKPARAM with a value of _SPGridView_;ColumnName and using that information you can interpret in your code that the request is to show Filters and in my case I fired up another stored proc to get all the records so Filter dropdown can show all the unique values from the table.

Here is a helper method to figure out if the request is to get the Filter values:

private bool IsFilterOnlyRequest(){

//If it is a callback from Filter Menu on SPgrid than

//Extract the name of the col. the grid is being filtered on

if (Page.IsCallback && !string.IsNullOrEmpty(Request.Form[“__CALLBACKPARAM”]))
{

//Request.Form[“__CALLBACKPARAM”] will have a format of “_SPGridView_;Col Name”
string[] param = Request.Form[“__CALLBACKPARAM”].Split(“;”.ToCharArray());

if (param != null) {

return true;
}

}

return false;

}

About these ads

8 thoughts on “Custom Filters in SPGridView

  1. Excellent Post Thanks!
    But I have one question: how can I fill the filter dropdown with my data?
    I can identity the filter column with your function, I retrieve all data from my database, but now I need to fill the dropdown.
    Thanks in advance.

    • The filter Dropdown is build dynamically when user clicks on the filter, so in your code when you can do something like

      if(IsFilterOnlyRequest())
      {
      // SELECT [SelectedColName] FROM My TableName
      // return the Data of above query
      }

      that will give you the data to build the Filter Dropdown

  2. My problem is not use the IsFilterOnlyRequest function and load data from database.
    My problem is to bind the data that I retrieve from database, to the Filter Dropdown. How can I retrieve the filter dropdown?

    I have my datatable and I need to show the data in the filter dropdown.
    Thanks.

    • To get the data into the drop-down you re-bind the Grid to the data you got after IsFilterOnlyRequest() and the SPGridView works out to show the dropdown, remember that it will not refresh the data into your grid displayed on the page as IsFilterOnlyRequest() is an AJAX CALLBACK

  3. I’m bit confused even after reading the replies. How do i bind the data to filter dropdowns?
    I understand how to determine a filter callback and fetching the data from back-end but how do i populate the filter dropdown?
    If i re-bind the complete data to the grid on determining Page callback, this will defeat the purpose of retrieving a paged set of data from backend.
    Can you please share a sample code as well?

    • To Populate the Filter Dropdown you re-bind the grid with only filter data and SPGrid will work out that it only needs to show the Filter drop, see one of the replies above,

      You do not have to query for complete data, you can makequery to your backend system, to only return distinct records to show in FIlter,

      • Let’s say i’m binding a data List to the grid.
        So on callback, i should fetch the distinct values and convert into a List.
        Otherwise, i’ll always get an error

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s