Custom Filters in SPGridView

May 30, 2008 at 10:59 pm 8 comments

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

Entry filed under: MOSS. Tags: .

MOSS SendEmailActivity and Correlation Token Multiple Event Driven Activities in a Single Workflow State

8 Comments Add your own

  • 1. Alessandro Sorcinelli  |  March 22, 2009 at 10:47 am

    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.

    Reply
    • 2. pateketu  |  March 22, 2009 at 1:09 pm

      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

      Reply
  • 3. Alessandro Sorcinelli  |  March 23, 2009 at 6:26 pm

    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.

    Reply
    • 4. pateketu  |  March 23, 2009 at 7:29 pm

      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

      Reply
  • 5. Alessandro Sorcinelli  |  March 23, 2009 at 9:43 pm

    Quiet simple!
    Thanks a lot!

    Reply
  • 6. Sunil Singhal  |  August 22, 2011 at 4:48 pm

    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?

    Reply
    • 7. pateketu  |  August 23, 2011 at 8:12 am

      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,

      Reply
      • 8. Sunil Singhal  |  August 25, 2011 at 6:55 am

        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

Trackback this post  |  Subscribe to the comments via RSS Feed


Recent Posts

KWitty

Feeds

Add to Technorati Favorites

del.icio.us

Categories


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: