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()