SharePoint List Filtering by Date Spans: a Stepwise Tutorial

Update: we introduced a much simplified setup approach to value-range filters on 26^th Feb 2010. We recommend you try that first, before falling back if necessary to the steps described in this article.

FilterZen power user and SharePoint consultant Harry Parsonage (of , California) wrote this step-by-step walkthrough on how to set up date-range filters for work with SharePoint Lists, and invited us to share it with the public. This tutorial will be a real time-saver for newcomers to FilterZen (especially until we finalize a much simplified Date Range Filter Wizard user interface for this often-needed use case, which is on the medium-term roadmap). As so often, once you know the basic steps, you can click your filters together in under 5 minutes; however, figuring those out for the first time takes some experimentation and can quickly take much longer than anticipated. Harry, thanks a lot for this invaluable contribution to your fellow SharePoint power filterers!

---

I have a simple list of Tenants who have occupied in the past or are currently occupying units in a commercial property. The page below shows one page in several that have been prepared for a Property Site, viewed by the owner(s) of the commercial property. Tenant information is retrieved from a SQL 2005 Data View, using a Business Data Connector Web Part. I want to provide the viewer of this page the option to filter by a date range only those owners who have paid their rent within in a 15 day period, since rent is due on 1st of the month.

To filter the list, I will use the FilterZen Web Part from ROXORITY. Deploying the web part to the server farm is simple and straightforward. Once it is activated on the Site Collections, then it is available to use with almost any Web Part that can accept connections from another Web Part. (ie. FilterZen Web Part feeding filter range values into an All Items List View. )

Here are the steps used to apply the Web Part to this simple List. Since I want to provide the users with a date range, I need to have two filter attributes on the same List Column. I will use the Column named LastPmtDate, which has an internal name of bdlcLastPmtDate.

Step 1: Edit the page that contains the SharePoint List that you want to provide the date range filter on.

Step 2: Add the FilterZen Web Part onto the zone where the List Web Part is being displayed.

Step 3: Click on the Edit link of the FilterZen Web Part and connect it to the SharePoint List. In this example the list is named EST1 - Tenants so connect via Web Part Edit menu > Connections > Send Values as Filters To > EST1 - Tenants.

Step 4: Once the FilterZen Web Part is connected to the List (ie. built-in List Web Part), then in the Page Edit mode you should see something like this:

Now you are ready to start creating the two filters for the connected List. The filters are (1) beginning date range and (2) ending date range values, for example: >= 7/01/2009 AND <= 7/15/2009. We are going to create two Date Filters as part of this date filter range configuration. The information provided by ROXORITY calls these two separate filters on the same Column, so I prefer to call them attributes of the same filter. Now open the Edit menu of the FilterZen Web Part and choose Modify Shared Web Part from the menu. You will see a FilterZen Filter Web Part configuration tool pane like this:

Step 5: Add the first Filter by dropping down the top combo box called (Add new Filter) and you will see displayed a list of the columns in the list you are connected to. In our example the list is as follows:

In this walk-through I want to use the Column LastPmtDate for my date-range filter. So I choose from the Date Filter section the LastPmtDate Column as shown above. Also notice the internal column name is shown in parentheses (bdlcLastPmtDate) in our example. Once you select the Column name from the drop-down list, you'll need to click the small plus [+] icon that is now visible next to it.

Step 6: Once you add the first filter, you will see the following user interface, with the Date Filter Properties section already expanded.

If you are going to allow users to enter the values for the date range, then you do not need to enter a default value; however, you may enter one, say based on today's date, less 30 days, so it reflects the same period last month. In our example we will leave these 3 fields empty, so when the SharePoint List is first displayed it will show all the records that were retrieved from the SQL database view.

Scroll down the Date Filter Properties section and you will come to the bottom section. By default the optional label is filled in for you, using the Column display name. I will replace this with a more descriptive label of Begin Filter Date. Other than that, by default the option Allow users to enter a filter value is already checked, which we'll want to use in this tutorial.

Now you are ready to expand the Advanced Filter Properties section shown above to finish your configuration.

Step 7: Now click the Apply button and you will return to the opening FilterZen settings tool pane. Next add a second filter to the Web Part, using the same (Add new filter) drop-down list, but this time around, you may not see the same column name again, since now it is already being used as the first filter name. Since the second filter attribute is also going to be a date value, you can select another column which is also a date type, if there is one. I will use LeaseEnd Column for now:

Then, as before, click the [+] icon to add this second filter to the Web Part. When the second filter editor GUI opens, you will need to replace its current filter name with the same name as the first filter (ie. bdlcLastPmtDate). The filter name must match the internal Column name in order for the date values that we want applied to the List to be applied properly.

Next scroll down the Date Filter Properties section and replace the default Filter Label with one that indicates this user-entered date will be for the end of the date range. Remember, these two filters will result in the display of two date pickers, for entry of the beginning and end of the date filter range.

Click Apply and now you will again see the FilterZen opening settings tool pane. Now we need to again edit the second filter, because it still has the original internal Column name that we originally selected from the drop-down list. Click on the Edit icon located next to the drop-down list, once you have the second filter highlighted in the filters list box.

Edit the filter name, to again reflect the same internal Column name as the first filter uses, ie. bdlcLastPmtDate in our example. Then click Apply and you will return to the screen above, but with both filters having same name.

Step 8: Now that both filters have the same name, that of the internal Column name, you are ready to scroll all the way down to the Advanced Filtering Settings section:

Check the Use CAML Filtering mode option as shown on the right-hand screen-shot above.

Scroll down a bit more and enter your filter name as shown below:

Step 9: Next click Apply and then the Web Part will refresh the screen, and now you will see the two date pickers:

The two date pickers are for the two filters on the same Column in the list. However, you need to change the default operator from just equals into the two we want to use. The logic we need the Web Part is akin to:

>= Begin Filter Date AND <= End Filter Date.

To change the default operators, you'll need to go back and edit each Filter. Highlight the first filter and then click the Edit icon. This will open the first filter editor. Scroll down until you see the Advanced Filter Properties hyperlink, and click it to open this bottom section. Then scroll down until you see this field:

Select the is greater than or equals to from the drop-down list, then click Apply and you will return to the FilterZen settings tool pane. Now select the 2nd filter and once again click the Edit icon.

In the 2nd filter editor, again scroll down into the Advanced Filter Properties section and select the proper operator for the upper end of your date range filter:

Finally, close the FilterZen Web Part settings tool pane via Apply or OK and exit the Page Edit mode. Then you will see the SharePoint List, with the two date pickers located above it. Without any default start value or end value pre-configured, the whole list of items is displayed:

Now If I enter a date range, say 7/01/2009 and  7/15/2009, and click the Filter button, now I will now only display these 2 records versus 6 records shown without any filtering.

Clearing the filter values, results in the redisplay of all values.

Comments

[Tybo]

Hello,
I've found this webpart tutorial, and i ve a little prbl on the step 9.
The droplist to select operators stay grey and i cant choose any operator on it.
Any idea to help me plz ?

(im on moss2007, ie8, french'version)

[ROXORITY]

Hi Tybo,

have you enabled the "CAML Direct filtering mode" in the Web Part's "Filtering Mode" section? It should then be possible to select the operators just fine.