Friday, March 7, 2008

Filtering on multiple values with SPGridView

If you want to show list data in a Web Part, one of the options to consider is using the SPGridView control. There is a nice article on Bob's SharePoint Bonanza on how to set this up. This really is a great article, but it won't allow you to filter on multiple columns. My customer wanted to be able to do this though and he also wanted the Web Part to remember the filtering and sorting options when leaving the page.

I was able to make this work using custom Web Part properties. Here's how to do it:

- First, set up your SPGridvVew and it's datasource as described in Bob's article. When you're done, also add a private string to your Web Part. This string will remember the previous filter.

private string prevFilter = "";

Next, find the following line in the SetupObjectDataSource method:

dataSource.FilterExpression = (string)ViewState["FilterExpression"];

Below this line, add the following line:

prevFilter = dataSource.FilterExpression;

- Your next step is to create two custom properties: one for the filter settings and one for sorting.

private string userSettingsFilter;
private string userSettingsSort;

///


///Hidden property to remember filtering options set by the user
///

[Browsable(false), Category("Advanced"),
DefaultValue(""),
Personalizable(PersonalizationScope.User),
FriendlyName("User Settings Filter"), Description("Hidden user settings for filtering.")]
public string UserSettingsFilter
{
get
{
return userSettingsFilter;
}
set
{
userSettingsFilter = value;
}
}

///
///Hidden property to remember sorting options set by the user
///

[Browsable(false), Category("Advanced"),
DefaultValue(""),
Personalizable(PersonalizationScope.User),
FriendlyName("User Settings Sort"), Description("Hidden user settings for sorting.")]
public string UserSettingsSort
{
get
{
return userSettingsSort;
}
set
{
userSettingsSort = value;
}
}


- In the OnPreRender method, you first check if there is a sort expression set. If this is the case, then save it. Next, check the filter that has been saved to the ViewState. Depending on the column title (is there already a filter on that column or not), save the filter setting to the custom filter property by appending it with either " and " or " or ".

protected override void OnPreRender(EventArgs e)
{
//Check if there is a sort expression set. If this is the case: save it.
if (gridView.SortExpression != "")
{
userSettingsSort = gridView.SortExpression + "&" + gridView.SortDirection;
}

//Get the filter expression into the viewstate. If a filter is set, add it to the saved filter expression.
ViewState["FilterExpression"] = dataSource.FilterExpression;
if (prevFilter != string.Empty && ViewState["FilterExpression"].ToString() == string.Empty)
{
userSettingsFilter = "";
}
if (ViewState["FilterExpression"].ToString() != "")
{
if (userSettingsFilter != null)
{
if (userSettingsFilter != "")
{
//If there already is a filter on a certain column and the user adds another value of this column,
//add it with an "or" expression
if (userSettingsFilter.Contains(ViewState["FilterExpression"].ToString().Substring(0, ViewState["FilterExpression"].ToString().IndexOf('=') + 1)))
{
int tempIndex = userSettingsFilter.IndexOf(ViewState["FilterExpression"].ToString().Substring(0, ViewState["FilterExpression"].ToString().IndexOf('=') + 1));
userSettingsFilter = userSettingsFilter.Insert(tempIndex, "(");
string tempSettings = userSettingsFilter.Substring(tempIndex);
if (tempSettings.Contains(" and "))
{
userSettingsFilter = userSettingsFilter.Insert(tempIndex + tempSettings.IndexOf(" and "), " or " + ViewState["FilterExpression"] + ") ");
}
else
{
userSettingsFilter = userSettingsFilter.Insert(tempIndex + tempSettings.Length, " or " + ViewState["FilterExpression"] + ") ");
}
}
else
{
userSettingsFilter = userSettingsFilter + " and " + ViewState["FilterExpression"].ToString();
}
}
else
{
userSettingsFilter = ViewState["FilterExpression"].ToString();
}
}
else
{
userSettingsFilter = ViewState["FilterExpression"].ToString();
}
}

//Save the changed properties
this.SetPersonalizationDirty();
base.OnPreRender(e);
}


- Next, in the Render method, use the saved filter settings on the datasource's FilterExpression. If a sort setting has been saved, sort the gridview either ascending or descending (depending on the text in the saved setting) on the saved column.

protected override void Render(HtmlTextWriter writer)
{
dataSource.FilterExpression = userSettingsFilter;

if (userSettingsSort != null)
{
if (userSettingsSort != "")
{
if (userSettingsSort.ToLower().Contains("ascending"))
{
gridView.Sort(userSettingsSort.Substring(0, userSettingsSort.IndexOf('&')), SortDirection.Ascending);
}
else
{
gridView.Sort(userSettingsSort.Substring(0, userSettingsSort.IndexOf('&')), SortDirection.Descending);
}
}
}

gridView.DataBind();
base.Render(writer);
}


There are a few disadvantages when using this method though. Your list won't show icons showing which columns are being filtered on and you will only get the option to remove the filter you've last set. Due to this, removing that last filter value, will automatically remove all filters. Also, if you go away from the page and then return, you won't have the option to undo the filter and you will have no indication that there is a filter enabled (except for the fact that not all fields are shown). To remove this filter, you need to set an extra filter and then remove it again, so all filter options are cleared. Another disadvantage is when you set your filter options in a certain way that no data is shown, your heading will disappear and you won't be able to clear the filter anymore, so you'll be stuck with an empty SPGridView forever. To solve this, you can either make the custom filter property visible, so you can edit your Web Part and manually remove the filter, or you can add a button to the Web Part that clears the filter for you.

A better way of implementing this is rendering the view as html (see one of my previous posts). This way, the list will have the same look and feel as a default SharePoint list. I think that this last solution is better in almost every way, but for some situations you might need to use the SPGridView, for example when you need to use a certain datasource (which was the situation I was in, it was impossible for me to display the same data that I got from the datasource) and with this implementation, you're also able to filter on multiple values of one column, which is not possible with the default SharePoint list.

If you have any comments, remarks, possible improvements or questions, just shoot!

6 comments:

Pablo del Cerro said...

hey, could you find any better solution to this?

I nead to create a list on sharepoint exactly as the default ones from sharepoint, but with a WebService as DataSource.
I`m using this SPGridView, but i need to make the Filtering work the same as the one from the default sharepoint lists.

Is there any other better solution?

thanks Tom!!

Tom said...

Hi Pablo,

I'm still having the same problem too, so I'm afraid I can't help you any further... It would be great if the SPGridView would work the same way as the default ones, I still don't get why they have made it so that it looks like the default one, but doesn't have the same functionality...

If there's any specific feature you're looking for, feel free to ask, I might have implemented it already...

Anonymous said...

Hi Tom,
Thanks for posting a great blog. Were you able to find any solution better than this for filter. Thanks in advance.

Tom said...

You're welcome, always glad to help!

I'm afraid I haven't found any better solution yet... The customer used that one and later on decided to rebuild it into something completely different, so I didn't look into it any further...

mark said...

FWIW, i have spent a lot of time extending this to make multiple columns filter. To some extent i have succeeded in that I can apply multiple filters and get the filter icon. However, the SPGridView only "remembers" the most recent filter and so when I clear the most recent filter, the SPGridView thinks all filters are cleared. This leaves us with the need to have a "clear filters" button like you suggested.

I have found another post about the DataFormWebPart that *may* offer better sorting and filtering. I am going to look at that next.

Mark

Anonymous said...

Can anyone recommend the best MSP tool for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: N-able N-central remote support manager
? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!