SharePoint Date Filter: Filtering a List by Greater Than or Equal to Date

So, I was doing some playing around with mashups in SharePoint.  I was seeing what I could do to create pages a little more useful than you get using vanilla SharePoint without having to crack open Visual Studio.  One of the things I wanted to do was display a SharePoint list on the screen and add a filter to the screen that would allow a user to filter the list with a date field value greater than or equal to whatever a user entered.  Sound’s simple right?  Should be easy? Well, I dropped my SharePoint list on a page, dropped a Date Filter on the page (this is an Enterprise feature by the way), connected the filter to the list and… I simply could not find a way to make the filter work as anything but a “Date equal to” filter.  That’s not really very useful for most real world applications right? 

Well, I reached out to the queen of out-of-the-box web parts Laura Rogers (@WonderLaura) to ask her how to do this, and she had not yet tried.  “Wow” I thought. “Here’s my chance to teach Laura something.”  She also indicated that this is a fairly common question. 

So, after playing around I was able to fairly easily come up with a functional solution using SharePoint Designer. Now, before you jump on my case about using SharePoint Designer even though I’ve clearly stated you should not use it on your production servers, let me point out that by following the steps below you can export the web part created in SharePoint Designer and drop that web part on another page.  Thus, allowing you to develop in SharePoint Designer without breaking any of the development standards I have previously put in place.   

Without further adieu, the steps we will take are as follows:

  1. Convert List View to XSLT Data View
  2. Create a Parameter for the Data View
  3. Filter the Data View Using the Parameter
  4. Drop Date Filter Web Part on Page
  5. Connect Parameter on Data View to Date Filter

The Real World Scenario

Okay, here’s the situation.  I have an Issues list.  This list has a “Due Date” field which indicates when the issues have to be resolved by.  I want to view all of the issues that are due on or after date ‘x’.  Make sense?  The quick and dirty non-user friendly approach would be to create a view of the list and hard code the date range in there.  That’s not really useful or powerful though is it?  I want to allow other users to be able to dynamically filter on the list and show those dates.   Yes, I could also sort by due date, but is that really feasible or user friendly when the list grows to have thousands of entries?

Here is the solution I came up with.

Convert List View to XSLT Data View

First thing we need to do is get our Issues list on a page and convert it to an XSLT Data View.  Doing this gives a lot of functionality we need in SharePoint including:

  • The ability to export/import the web part
  • The ability to manually modify the XSLT for the web part
  • The ability to create and modify parameters for the web part

So, let’s get started.  Create a blank Web Part Page in SharePoint and open up that page in SharePoint Designer.

image

Click on “Click to insert a Web Part” which will bring up the web part list in the right side panel if it is not already there.  Find your list in question (Issues List in my case) and drag and drop it into the page.

Now, right click on the List and select “Convert to XSLT Data View”

image

Visually you won’t see a huge change, but you now have access to the “Common Data View Tasks” menu which is what we need.

image

Create a Parameter for the Data View

Next we want to create a new Parameter for the Web Part.  This parameter will eventually hold the value of our Date that we will use to filter the list on.

From the “Common Data View Tasks” menu, click on “Parameters”.

image

Click on “New Parameter”.  Give it a name (“DueDate” in this example).  Specify the Parameter Source as “Form” (not sure how necessary this is, but it’s what I did when it worked).  ‘Form Field’ and ‘Default Value’ are not needed.  When you are done you should have a parameter that looks like the following:

image

Click “OK”.  You now have a “DueDate” Parameter created.

Filter the Data View Using the Parameter

Even though our “DueDate” Parameter does not have a value yet and is not connected to a field, we still want to set up our Web Part to be filtered on this Parameter.  The Filter is set up as follows:

From the “Common Data View Tasks” Menu select “Filter:”

image

Click on “Click here to add a new clause”.  In this new clause specify the Field Name as “Due Date”, the Comparison should be “Greater Than or Equal”, and the Value should be the parameter we just created “[DueDate]”.  When you are finished your filter will look something like:

image

Click on “OK” and then save your page.  We are done with SharePoint Designer.

Drop Date Filter Web Part on Page

It’s time to open up your page in SharePoint. When you do, you will notice you see nothing but an empty list.  That’s because our filter is being used with our parameter and it does not have a value for “DueDate” yet.  Don’t panic. 

Open up the page in Edit mode by clicking on “Site Actions->Edit Page”

image

in edit mode, click on “Add a Web Part”.  Select “Date Filter” from the Filters section and click on “Add”.  Again, this is an Enterprise only feature.

image

Your page now looks something like this:

image

Go ahead and click “Open the tool pane” or “edit->Modify Shared Web Part” for your Date Filter and give the filter a name of “Due Date Greater Than or Equal To:”

image

Now click on “Apply”. 

Connect Parameter on Data View to Date Filter

We are now ready to connect the Date Filter to the Issues Web Part so that everything works.

From the Issues Web Part click on “edit->Connections->Get Parameters From->Filter: Due Date Greater Than or Equal To:”

image

Select “DueDate” from the window that pops up and click on “Finish”

image

That’s it! Now click on “Exit Edit Mode” and let’s see if it works!

image

Testing it out

Enter a date in the Date Filter or click on the Calendar icon and select a date.  Your list should now appear showing all the entries with due dates greater than or equal to the value in the Date Filter

image

Conclusion

That’s all there is to it.  Not too hairy I hope?  You can play with this in SPD or play with the XSLT directly and customize this even more, but that’s for another blog post.

Hope you guys learned something! Thanks again for stopping by.

posted @ Wednesday, September 16, 2009 4:05 PM
Print

Comments on this entry:

# re: SharePoint Date Filter: Filtering a List by Greater Than or Equal to Date

Left by Phil Schumann at 9/16/2009 7:08 PM
Gravatar
Awesome tutorial! Shameless plug, though: our Filter Web Part linked above will let you do this to out-of-box list views, no SharePoint Designer, XSL or coding required... but if third-party tools are not an option, good to know there are other ways to do this (if you have MOSS Enterprise, that is...)

# re: SharePoint Date Filter: Filtering a List by Greater Than or Equal to Date

Left by Jaden at 7/2/2010 10:52 AM
Gravatar
As for me it is a very useful post.

# re: SharePoint Date Filter: Filtering a List by Greater Than or Equal to Date

Left by Hvandesa at 7/15/2010 5:41 PM
Gravatar
Thanks! The only thing I had to tweek was the Filter was trying to "helpfully" strip out the Time from my date and I was getting nothing. I found the line and removed the "substring-before($DueDate,'T')" from the compare Line: <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[number(translate(substring-before(@Modified,'T'),'-','')) &gt;= number(translate($DueDate,'-',''))]"/>

# re: SharePoint Date Filter: Filtering a List by Greater Than or Equal to Date

Left by Dan at 10/7/2010 5:33 PM
Gravatar
Great article! I have this working now thanks to your steps and screen shots. Thanks! --Dan

# re: SharePoint Date Filter: Filtering a List by Greater Than or Equal to Date

Left by JS at 10/8/2010 10:39 AM
Gravatar
This works perfectly except after making the connection between the data view and the date filter, paging no longer works for the data view. Clicking the link to advance the page just changes the text from 1-100, to 101-200, but the exact same data is displayed. Any ideas?

# re: SharePoint Date Filter: Filtering a List by Greater Than or Equal to Date

Left by Edmund at 10/29/2010 8:15 AM
Gravatar
Excellent walk-through. How would the steps change for creating a date filter for a rolling range. For example, manager X wants to see all the reports updated for the last 7 days when he/she selects the view. That 7-day window will continue to roll forward and manager X wants to select the view on any day and view the reports within that 7-day window with out entering any data.

Your comment:



(not displayed)

 
 
 
 
 

Live Comment Preview:

 
«November»
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456