This project is no longer supported

After some thought on this, I have decided to take a differant approach. Since the new approach is quite a bit differant, I elected to launch it as a new project available here http://hosteduserctrlfilter.codeplex.com. Enjoy!

Project Description

Once I started working with Sharepoint choice filters and dashboards, it became immediately apparent that the lack of dependant filters and filters that could also read directly from SQL was a problem. The solution ended up being quite simple. This is my attempt at it. Enjoy.



Details

There were two major goals I wanted to accomplish with this project; build a Filter web part that could pull its data from a SQL Data Source, and make the web part capable of being linked to other filters of the same variety for the purpose of performing cascade filtering. This ended up being ratherr challenging. There isn't a whole lot of documentation out there about this. I finally the following examples:

How To: Build a Custom Filter Provider Web Part - By Edhild
http://blogs.msdn.com/edhild/archive/2007/03/28/how-to-build-a-custom-filter-provider-web-part.aspx

MOSS 2007 Filter webparts - create your own provider and consumer - By Ton Stegeman
http://www.sharepointblogs.com/tonstegeman/archive/2007/09/21/moss-2007-filter-webparts-part-1-create-your-own-provider-and-consumer.aspx

Using their examples, I was able to put together the basic filter that reads from SQL and populates the list. From there, all I had to was wire up the standard connections for selected value and default value and add a custom connection to support the linked lists. I will try to flush this out more over time to better explain the code, but take a look for yourself and see how it all works. :-)

Setup

This project is functional as is. There are some recommended add ons in order ease the process.

Manage Application Configuration Feature
http://features.codeplex.com
This feature allows administrators to setup configuration values all the way down to the list level. I use this in the web part to store the connection string and list population sql. You don't have to have this, but if you exclude it, you'll have to figure out another way of populating the Web.Properties collection, which is where the data is actually stored.

WSP Builder Extensions
http://wspbuilder.codeplex.com
For me, this is the single most valuable tool in my SharePoint arsenal. Already it has saved me hours of money (work) while building MOSS features. Get it, use it, love it. This project is actually a WSPProject and follows the Web Part with Feature template.


InitialState.png

The page we are going to be putting this filter on is a standard dashboard page on MOSS 2007. I have built a small report that simply takes three parameters and prints them to the screen.



AddFilter.png
First we need to add the filter to the page by taking the page into edit mode and adding our Linkable SQL Drop Down Filter web part. Once it is added, it shows that the connection string is not set. We'll take a moment now and set this up.

Prerequisite: Before performing these steps, be sure you have installed the Manage Application Configuration Feature mentioned above and have activated it on the site level.
SiteSettings.png
Go to the Site Settngs for the current site and find the "Custom Site Config Settings" option.


Configsettings.png
Here you will add the necessary connection string and sql statements that you will use to populate your lists. As you can see, I have the connection string and the three sql statements already filled out. Notice how the keys are all lower case. This is done automatically when you enter in a new configuration entry. remember this when you are setting up your web part as these keys are case sensative.

Once you have the connection string and population sql in place, you are ready to configure your web part.


FilterSettings.png
The settings for this web part are all stored in a section titles "Filter Settings". Each label in filter settings has a tooltip with some instructions.
  1. Connection String Config Key - This is key from the settings that you just set up. If this is wrong or missing, the default message "Connection string not set" will be displayed.
  2. Store SQL in Config Entry - This indicates whether the SQL Statement used to populate the lists will be found in the Application Configurations or if it will be defined in the next setting
  3. SQL Query - Either the key for the sql query as described above or the actual query itself
  4. Label - The label to show next to the drop down liast AND on the connections popup.
  5. Include All - Indicates whether an "All" option will be displayed at the top of the list.
  6. Disable on Parent All - Indicates whether the drop down list will be disabled if the parent linked drop down is currently set to "All" (has no effect on the top level drop down or disconnected drop downs)

Configure your settings as you like and click OK. Repeat the process for the rest of the drop down filters you wish to use.

Connections.png
Once you have the filters all assembled, it's time to connect everything. There are three connections available on this web part:

#Selected Value (Outbound) - This is the value that is currently selected in the drop down list. This defaults to an empty string in the case of configuration errors. The destination control for this would be something like the SSRS Report viewer.
#Filter Value (Inbound/Outbound) - This is the value that is currently selected in the drop down list. This defaults to an empty string in the case of configuration errors. The destination control for this would be a child linkable drop down filter.
#Default Value (Inbound) - This is the default value to set the drop down list to. if the value suggested is not actually present in the drop down list, this value will be ignored.


FinalState.png
Once everything is connected, the filter, will cascade and populate the Report parameters as shown.


Last edited Jul 22, 2009 at 10:02 PM by Rogerstigers, version 11