Posted on September 30, 2011|by: Ross Beurmann| 16 Comments

Applies to:  SharePoint 2007

Anyone who has ever worked with SharePoint knows that creating filters on date values is not as straight forward as one would expect.  This example explains how to compile employee birthdays in a custom list that automatically displays only the current month’s birthdays.  With a few calculated columns, a data view, and minor XSLT this is easy to accomplish.

Figure 1 displays a screenshot my configured Birthdays list which displays only current month birthday entries on the home page of my site.  To create your own list that filters by current month follow the below steps. 


Getting Started – List Configuration
(These steps are completed in the browser.)

1. Create a custom list and name it Birthdays.

2. Create the following columns:

Column 1)  Birthday (birthday)  /  Date and Time  /  Field is Optional

Column 2)  Birthday Month (birthdaymonth) / Calculated Column / Field is Hidden

=CHOOSE(MONTH(Birthday),”January”,”February”,”March”,”April”,”May”,”June”,”July”,”August”,”September”,
“October”,”November”,”December”)

Column 3)  Day (day) / Calculated Column / Field is Hidden

=””&DAY(Birthday)

Column 4)  Employee (Title) / Single line of text / Field is Optional

Column 5)  Month (month) / Calculated Column / Field is Hidden

=CHOOSE(MONTH(Birthday),”01″,”02″,”03″,”04″,”05″,”06″,”07″,”08″,”09″,”10″,”11″,”12″)

Your column settings should display as in Figure 2 so that users do not see the hidden fields when a new birthday item is created.

3.  Create a view in the Birthdays list and name it currentbirthdays.  Columns to display are:
1- Birthday Month, 2- Day, and 3- Employee   /    Sort by:  Day (ascending) and Month (ascending).


Birthday list content and configuration 

Before proceeding to the Final Stretch section ensure your list contains the following:


Final Stretch – XSLT filtering in SharePoint Designer 2007

An XSLT filter is used to display only current month birthdays.  The XSLT expression uses the hidden Month column to compare the Birthday Month column to [Today’s] month.

NOTE:  In SharePoint 2010 create the Birthdays list then, in Designer, add an empty data source to a page.  Connect to the Birthdays list and add the columns (Birthday Month, Day, and Employee).  Access the filter settings and follow the below instructions.

4.  Open SharePoint Designer.  Navigate to the Birthdays list and double-click the currentbirthdays.aspx page to open it.

5.  Right-click and convert the list to an XSLT Data View.

6.  Right-click the data view web part and choose Show Common Control Tasks.

7.  Choose Filter.

8.  Check the Add the XSLT Filtering option and choose Edit.  The Advanced Condition dialogue box opens.

9.  In the Edit the XPath Expression field input the following XSLT and then click OK:
[@Month=number(ddwrt:FormatDateTime(string(ddwrt:Today()),1033,’MM’))]

10.  Select Ok to close the Advanced Condition dialogue box.

11.  Close Designer.  If prompted to Save or Check-in, do so.

NOTE:  After adding the list as a web part to a page or site, it may be necessary to repeat steps 4 – 11.

Filed in: Technology, Tips & Tricks

16 Comments

  1. Tiago
    March 27, 2012 at 1:16 pm

    Hey, can it be done in SP 2007?

    • June 25, 2013 at 8:11 am

      Hi Tiago, this article is specifically for 2007.
      Ross

  2. Troy
    September 12, 2012 at 3:54 am

    Hi,

    How wou;d you do this to display for current week?

    Regards

    Troy

    • June 25, 2013 at 8:31 am

      Troy,
      You would have to create a calculated column to figure out which week the birthdays were in and then apply the filtering based on the week rather than the month.
      Thanks
      Ross

  3. Raul
    February 26, 2013 at 5:30 am

    Hello,

    i have a little problem using this quide. After adding condition, the list made new tab part with pages 1-0. First page is empty, but when i’ll click “>” button then I see the right list. Does somebody know how to get rid from this first empty page?

    Raul

    • June 25, 2013 at 8:32 am

      Raul,
      Is it possible that you added the DVWP as a single Item view rather than a multiple item view?
      Thanks
      Ross

  4. keerthi
    June 24, 2013 at 7:05 am

    i need the same in sharepoint 2013..
    any help is appreciated..
    regards,
    Keerthi

    • June 25, 2013 at 8:34 am

      Keerthi,
      Have you tried adding the blank DVWP as Jenny mentioned for SP 2010. It should work for 2013 as well, let me know if you have tried that or not.
      Thanks
      Ross

  5. mark
    July 18, 2013 at 8:30 am

    hi,
    when i am adding this formula
    =CHOOSE(MONTH(Birthday),”January”,”February”,”March”,”April”,”May”,”June”,”July”,”August”,”September”,
    “October”,”November”,”December”)
    i got the error invalid syntax can u pls suggest

  6. July 18, 2013 at 10:36 am

    Mark,
    Try Brackets not parentheticals [Birthday] you can also simply type =CHOOSE(Month then select the Birthday column from the slection list to the right of the calculated value field, then finish the calculation.
    Hope this helps!
    Ross

  7. Deron
    February 6, 2014 at 5:01 pm

    Microsoft provides a ShPt widget for this (for ShPt2010, at least). See http://pinpoint.microsoft.com/en-nz/applications/birthday-reminder-add-on-for-sharepoint-12884911853

    • February 7, 2014 at 9:20 am

      Deron,
      Thanks for the link. The application you linked us to is another way to do the work, however the functionality is a little different. The email piece is nice but the downside is this is a web app level deployment. Microsoft recommends this, but it is not a Microsoft product. It orks in both MOSS and 2010 which is nice, but it’s two seperate installer files.
      Jenny’s approach is meant to stay away from the WSP method of doing things, you will see that all of her tips and tricks lean more towards utiliing list configurations and SPD to solve complex problems.
      Thanks again for the comment.
      Ross

  8. arik
    February 12, 2014 at 2:43 am

    hi,
    When I add the list as a web part to a page, I see the list and not the list view.

    why?

    • February 20, 2014 at 8:35 am

      Arik,
      Jenny stated that the filtering steps may need to be reapplied after the web part is put on the page. Make sure that your XSLT is filtering correctly and if you made a custom view in the list that that is the view displaying in the webpart.
      Thanks
      Ross

  9. Dinesh
    February 24, 2016 at 5:36 am

    hi Ross,
    Here I had created one Birthday Calendar for Employees.Now i’m trying to send birthday alert mail to all.could you please suggest me any way without using SharePoint designer.

  10. Dinesh
    February 24, 2016 at 5:37 am

    And also with out using any Webpart.

Your email is kept private. Required fields are marked *

*
*