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;
}
Ketul's Weblog 
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
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
Quiet simple!
Thanks a lot!
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