Passing multiple filter values to an EWA pivot table filter cell

• Download source code - 1.13 KB

Introduction

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

They are also a great way to provide data view personalization when used with Excel Services coupled with the Analysis Services of SQL Server. This is described in detail on the Excel Team blog here.

Building the Web Part

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

I am mentioning a pivot table filter cell exclusively because the filter Web Part examples on MSDN have to be slightly modified to provide values to a filter cell.

Basically, we are building a filter provider which:

• Implements the ITransformableFilterValues interface.
• The interface requires a few properties to be overridden, the most important of them is public virtual ReadOnlyCollection<string> ParameterValues. This parameter contains a read only collection of strings which are passed to the consumer Web Part.
• The Web Part returns an 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<string> ParameterValues
        {
            get
            {
                string[] values = this.GetCurrentlySelectedGeographies();               
                List<string> param=new List<string>();

                foreach (string str in values)
                {
                    if(!string.IsNullOrEmpty(str))
                        param.Add(str);
                }
                return values == null ?null :new ReadOnlyCollection<string>(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 this simple Web Part which is already explained at various places, I will point out the key points which differentiate it from the examples at other places.

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

In the MSDN example here, the string array is directly passed by converting it to a 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<string>(values);

The 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<string> param=new List<string>();
foreach (string str in values)
{    
    if(!string.IsNullOrEmpty(str))        
    param.Add(str);
}
return values == null ? null :new ReadOnlyCollection<string>(param);

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