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

Sunday, February 10, 2008

Implementation of Logging and Instrumentation Application Block in MOSS 2007

Continuing the series of articles on Enterprise Library, I have written the first article
on the implementation of Logging Application Block in MOSS 2007. Due to inclusion of formatted code and images, I have directly uploaded this on Codeproject.

The article can be viewed here

Sunday, February 03, 2008

Microsoft Enterprise Library - Using Design Patterns and Best Practices with Sharepoint 2007 development

Patterns and Practices Group within Microsoft has provided developers with useful code libraries in form of Enterprise Library which solve the common programming tasks and provide the developers with best practices.

The patterns & practices Enterprise Library is a library of application blocks designed to assist developers with common enterprise development challenges. Application blocks are a type of guidance, provided as source code that can be used "as is," extended, or modified by developers to use on enterprise development projects.

Note: The purpose of this article is introduction of Microsoft Enterprise library. I would cover each specific application block in subsequent blog articles and demonstrate how they can be effectively used in Sharepoint 2007 development environment.

The seven application blocks are:

  • Caching Application Block - allows developers to incorporate a local cache in their applications.
  • Configuration Application Block - allows developers to read and write configuration information.
  • Data Access Application Block - allows developers to incorporate standard database functionality in their applications
  • Cryptography Application Block - allows developers to include encryption and hashing functionality in their applications.
  • Exception Handling Application Block - allows developers and policy makers to create a consistent strategy for processing exceptions that occur throughout the architectural layers of enterprise applications.
  • Logging and Instrumentation Application Block - allows developers to incorporate standard logging and instrumentation functionality in their applications.
  • Security Application Block - allows developers to incorporate security functionality in their applications.
Each of the application blocks uses similar design patterns and share similar deployment and configuration requirements.

Getting Started with Enterprise Library

In order to get rolling with the Enterprise Library you'll first need to download the library from Microsoft's site. The download is a near 9 MB file, which includes the complete Enterprise Library application block source code, Quick Start examples, off-line documentation, and the GUI tool.

During this installation process you can specify what application blocks to install, if you only want to install a subset of the seven. Upon completion of the installation, the files should be in the \Program Files\Microsoft Enterprise Library folder.