Filtering SQL Data View Web Parts in SharePoint

SharePoint's XSLT Data View Web Parts do support incoming Web Part Connections, but they are a fragile bunch. Connecting a Filter provider Web Part to List View Web Parts is straightforward. Connecting to a List-bound XSLT Data View can be a tad more tricky depending on circumstances, but still fairly easy. Connecting to an SQL-bound Data View requires a few Serious Setup Steps. This walkthrough helps you through it.

Prepare the SQL Server Database

This tutorial assumes that you'll use SharePoint Designer to initially create and connect your Data View Web Part. In your project, you'll need to connect to real, existing business data down the line, but for the first experimental steps, let's connect to a new, temporary dummy database. The one we have just created is really as simple as it gets:

Prepare the Data View Web Part

Connect your new Data View Web Part to your SQL Server data source in the usual way, then right-click the Web Part and select Web Part Properties. Make sure you set the following properties as shown below:

Create a "Data View Parameter"

You'll need to add a so-called Parameter to your Data View Web Part. Click its chevron to show the Common Data View Tasks pane and click the Parameters... link.

This will bring up the Data View Parameters dialog box where you can manage parameters. To filter the our SQL Name Column, we added a parameter that we called SqlName — but any name should do (but try to keep spaces, dashes and other odd characters out of your parameter name), for example MyNameParam.

Keep Parameter Source set to None in order to later receive the parameter value via Web Part connections. The Default Value can be left blank in order to show no results unless and until a filter is applied. To show all results (unless and until a filter is applied), set it to % (as you know your SQL, you sure know the wild-card meaning of this).

Add Data View Filter Criteria (builds your WHERE clause)

A Data View Parameter by itself doesn't do much unless you use it in some fashion. To use it for filtering, you can customize your SQL SELECT statements easily using the built-in Filter Criteria dialog box:

Set up your Filter Web Part

The remainder of this walkthrough is better performed inside your web browser rather than in SharePoint Designer, which unfortunately has a tendency to mess up existing Web Part Connections in between edit-and-save cycles. In Page Edit mode, add a filter provider Web Part of your choice — naturally, we'll use FilterZen ourselves here. Set up a filter with the name of your Data View Parameter; in this example, that's SqlName:

Connect the Filter Web Part

Connect the filter provider Web Part to your XSL Data View Web Part via the Web Part Connections menu, as usual:

When prompted about the connection type to be used by the consumer (ie. your Data View!), be sure to pick the Get Parameters from connection:

Testing your Filtering

That should be it! As already mentioned, XSL Data View Web Parts are a bit fragile when your changes conflict with previous state information carried over through your post-backs, so completely re-load the page (no F5 post-backs, but a complete reload by changing the URL after the ? query string separator or re-navigating to it) and then go about testing your filtering:

As usual: if you run into road-blocks, let us know in the forums, we're happy to help and we're pretty fast at it.