SharePoint - advanced view and filter techniques

Is this your first time here? Get regular updates

Advanced SharePoint View and Filter techniques

Date:October 26th, 2008 Author:Ryan Tags: filters, Formula, SharePoint, View
Category: Filter, SharePoint Ideas Comments:107 ;

Introduction

We discussed in a previous article how to customise SharePoint’s built in Task List and briefly looked at the power of Views with Filters. In this article we will build upon that by demonstrating some advanced use of Filters.

This article assumes that you are already familiar with Views and Filters – if not please review the previous article.

Overdue Tasks

To show a task lists Overdue tasks use the following Filter :-

Status – is not equal to – Completed

And

Due Date – is less than – [Today]

Note – [Today] is a special marker that SharePoint understands as the current date.

My Overdue Tasks

Add the following to the Overdue Tasks filter

Assigned To – is equal to – [Me]

Note – [Me] is a special marker that SharePoint understands as the current user.

Items added in Last 7 Days

It would be nice to be able to enter something like

Created – is less than – [Today] – 7

in the filter, but alas its not quite that simple!

[EDIT - This work around was written in early 2006 and WSS 2 / SPS 2003 wouldn't accept this - in WSS 3 / MOSS 2007 you can now use a FILTER formula like [Today]-7 as long as you keep the spaces out!]

The workaround is to create a Calculated column and then filter on that column.

Select Modify Settings and Columns, then Add a new columnGive the column name something like “Filter 7 Days” and select a Calculated column

Name the view "Filter 7 Days"

Enter a suitable DescriptionIn the Formula box enter “=Created+7″Choose Date and Time format and Date OnlyClear Add to default view as we don’t want this field to appear on any of the views

Create a new viewSetup a filter to show only :-

Filter 7 Days – is greater than or equal to – [Today]

Items Due in the next 7 Days

[EDIT - This work around was written in early 2006 and WSS 2 / SPS 2003 wouldn't accept this - in WSS 3 / MOSS 2007 you can now use a FILTER formula like [Due Date]-7 as long as you keep the spaces out!]

Select Modify Settings and Columns, then Add a new columnGive the column name something like “Filter Coming Due” and select a Calculated column

Set the new columns name and choose "calculated column"

Enter a suitable DescriptionIn the Formula box enter “=Due Date-7″Choose Date and Time format and Date OnlyClear Add to default view as we don’t want this field to appear on any of the views

Create a new viewSetup a filter to show only :-

Status – is not equal to – Completed

And

Filter Coming Due – is less than or equal to – [Today]

And

Due Date – is greater than or equal to – [Today]

This will ensure that the view only displays Uncompleted items that are due in the next 7 Days but are not Overdue.

Items due in the current Calendar Month

If you need to filter for a Calendar Week/Month (rather than a rolling 7/30 day period) then this post details how

How To Use Filters in SharePoint to show items in the current Calendar Month

Free SharePoint Calculated Column Cheat Sheet

a handy 3 page reference for calculated column functions and formulas.

Tags: filters, Formula, SharePoint, View

Did you enjoy this post? Get the next one by RSS or by email

107 Responses to “Advanced SharePoint View and Filter techniques”

Oscar says:

December 9, 2011 at 3:14 pm

Hello, I need to create an indicator (KPI) that shows the percentage of documents in a library in SharePoint 2007 with a date of revision (custom column) has expired (less than the current date). The problem is that the possibilities to filter the indicator does not allow using [Today]. How can I fix it? Thank you very much for the help. regards
Ryan says:

December 9, 2011 at 5:53 pm

@Oscar – Looks like its not possible
http://social.msdn.microsoft.com/Forums/en-US/sharepointbi/thread/1e25d81d-5e2a-47d9-8b2f-90807f4cb935/
Oscar says:

December 12, 2011 at 11:52 am

Thank you very much for your reply Ryan. I had already read the post that you recommend. However, I do not need to filter a KPI list, but to create an indicator based on all elements of a document library. I need to show the percentage of items in that library that have a date type property, less than the current day ([Today]). But the opportunities available to calculate the indicator does not include the use of the [Today].
The following example attempts to show what I need:

Library A
Name Review date
—- ———–
doc1 01/12/2011
doc2 01/03/2013
doc3 01/04/2013
doc4 01/05/2013

KPI List
Indicator Value
————– —–
Pending Review 25%
Conax says:

May 24, 2012 at 11:11 pm

I don’t want [Today]. Every example I found is using [Today]. I want the format for a time! Please.
Ryan says:

May 25, 2012 at 8:25 am

@Conax – I am afraid that SharePoint doesn’t have any Now() type function so you’re out of luck!
Conax says:

May 25, 2012 at 11:35 am

Thanks for the reply, Ryan.
I am not looking for the Now() type function.
What I have been longing to know is where you have placed a [Today], I actually want to specify a date with time in that box. I tried things like 25/05/2012 8:40:00, and 25/05/2012T8:40:00Z…etc but SharePoint always rejects it saying the format is invalid…
Is it actually possible to specify a date with time in the box? If I specify just a date then it accepts it. But I would like to be able to find items created after a certain time on certain date…

Much appreciated if you know how to do this.
Ryan says:

May 25, 2012 at 11:58 am

@Conax – gotcha. Its not easy as you have to break out SharePoint Designer to hack the view and this will be undone if anyone edits it after but this may help – http://nickgrattan.wordpress.com/2008/04/24/filtering-views-by-time-and-date/