Saturday, February 23, 2008

Passing multiple filter values to EWA pivot table filter cell

Filter webparts in WSS are a great way to provide filtering in many different webparts like List View, Business Data Catalog, Excel Web Access etc.

They are also great way to provide data view personalization when used with Excel services coupled with Analysis Services of SQL server.

This is described in detail on Excel Team blog here

In this post, we will see how to create a simple filter webart which provides values to pivot table filter cell.

I am mentioning pivot table filter cell exclusively because the filter webpart examples on MSDN has to be slightly modified to provide values
to filter cell.

Basically, we are building a filter provider which


  • Implements the ITransformableFilterValues interface.

  • The interface required few properties to be overridden, the most important of them is public virtual ReadOnlyCollection<string> ParameterValues.
    This parameter contains read only collection of strings which are passed to the consumer webpart.

  • The webpart returns the instance of itself through a public method SetConnectionInterface()

using System;
using System.Collections.Generic;
using System.Web.UI;
using System.Web.UI.WebControls;
using aspnetwebparts = System.Web.UI.WebControls.WebParts;
using wsswebparts = Microsoft.SharePoint.WebPartPages;
using System.Collections.ObjectModel;

namespace ExcelFilters
{
public class ExcelFilters : aspnetwebparts.WebPart, wsswebparts.ITransformableFilterValues
{
CheckBoxList cblRegionList;
ListItem cbitemRegion;
string[] countries = new string[]
{ "Canada", "Central America", "Mexico", "South America", "United States",
"Albania", "Andora", "Austria", "Aizerbejan", "Belarus", "belgium",
"Bosnia and Hersegovina", "Bulgaria" };

public virtual bool AllowMultipleValues
{
get
{
return false;
}
}
public virtual bool AllowAllValue
{
get
{
return true;
}
}

public virtual bool AllowEmptyValue
{
get
{
return false;
}
}
public virtual string ParameterName
{
get
{
return "Geography";
}
}

public virtual ReadOnlyCollection ParameterValues
{
get
{
string[] values = this.GetCurrentlySelectedGeographies();
List param=new List();

foreach (string str in values)
{
if(!string.IsNullOrEmpty(str))
param.Add(str);
}
return values == null ?null :new ReadOnlyCollection(param);
}
}

protected override void CreateChildControls()
{
cblRegionList = new CheckBoxList();
cblRegionList.AutoPostBack = true;
Controls.Add(cblRegionList);

foreach (string region in countries)
{
cbitemRegion = new ListItem();
cbitemRegion.Text = region;
cblRegionList.Items.Add(cbitemRegion);
cbitemRegion = null;
}

base.CreateChildControls();
}

[aspnetwebparts.ConnectionProvider("Region Filter", "ITransformableFilterValues", AllowsMultipleConnections = true)]
public wsswebparts.ITransformableFilterValues SetConnectionInterface()
{
return this;
}

public string[] GetCurrentlySelectedGeographies()
{
String[] choices = new String[countries.Length];
bool anythingSelected = false;

for (int i = 0; i < cblRegionList.Items.Count; i++)
{
if (cblRegionList.Items[i].Selected)
{
anythingSelected = true;
choices[i] = cblRegionList.Items[i].Text;
}

}
if (!anythingSelected)
choices = null;

return choices;
}

protected override void RenderContents(HtmlTextWriter output)
{
this.EnsureChildControls();
RenderChildren(output);

}
}
}

Now, rather than explaining the simple webpart, which is already explained by Microsoft, I will point out key points which differentiates it from the examples at many places.

If we notice GetCurrentlySelectedGeographies() method, it returns the string array of selected geographies. However, the length of this string array is constant and is equal to number of selectable items, which causes the other items in string array to become null.

In the MSDN example here the string array is directly passed by converting it to ReadOnlyCollection of strings. This will not work since it includes the null items
in the array as well.

string[] values = this.GetCurrentlySelectedGeographies();
return values == null ?null :new ReadOnlyCollection(values);

Pivot table Report filter cell, expects a Collection of values only which are selected. Thus we need to modify the code as follows

string[] values = this.GetCurrentlySelectedGeographies();      
List param=new List();
foreach (string str in values)
{
if(!string.IsNullOrEmpty(str))
param.Add(str);
}
return values == null ? null :new ReadOnlyCollection(param);

This code snippets, creates a new List which includes all the string objects except the null items from the original string array which is returned by GetCurrentlySelectedGeographies()

4 comments:

Kristin said...

When I try to implement this (WSS v3.0) web part it seems to only filter on the first item in the list that is checked. If I have multiple items checked it will only filter on the first one. Is there any way to filter on multiple? For Example if both Canada and Mexico are checked they should both show in my filtered list rather than just Canada (because it is first in the list). Any further information on how to accomplish this would be greatly appreciated. Thanks.

Madhur said...

Hi Kristin

Try debugging your webpart to see the values which are being passed on.
Make sure that you are maintaing the case compatibility.

Madhur

Kristin said...

In order to get this to work I had to write my own Consumer Web Part to handle multiple parameters. I was hoping I could use this and attach this filter web part directly to a standard out of the box Sharepoint list view web part and have it work. Do you know of any way to attach this filter web part directly to a Sharepoint List View Web part, or will this only work with a custom web part consumer?

Madhur said...

If the list view webpart can accept multiple values. . it should not have no problems .. You can just try it by manually providing the multiple filter values .. may be through choice filter