A customer pinged me with an interesting request: given a SharePoint list called “Birthdays” that contains name, month, and day, how can I display a list of today’s birthdays? I could do this with my eyes closed if I were writing code, but I know that this customer is not interested in writing more custom code. In fact, he wants to learn about ways that you can make changes like this to SharePoint without requiring a developer.
After a bit of tinkering, here is what I came up with. Let me know if you come up with something different.
Using SharePoint Designer 2007
SharePoint Designer 2007 has a great capability to create what is known as a DataViewWebPart (DVWP for short). To insert it, just go to the Data View menu and choose “Insert Data View”.
SharePoint Designer 2007 will then ask you to choose a data source from the Data Source Library pane. Choose the list you want, and click “Show Data” to see the columns and the data in the columns.
Once you click the Show Data menu item, you will see the columns and data.
Select some of the columns, then drag them onto your page where you want the data to be displayed.
The next part is to apply filtering. See that little arrow pointing right at the top right corner of our newly added web part? Click that to bring up the options for the web part. Click the Filter option.
In the Filter Criteria box, select the “Add XSLT Filtering” checkbox, this will enable the Edit button. Click the edit button to edit the XSLT filter.
Once you click Edit, you have a screen that lets you define the XPath expression for the filter.
The value I entered for the XPath expression is:
[contains(string(ddwrt:FormatDate(string(ddwrt:Today()),1033,15)),@Month) and @Day=substring(ddwrt:TodayIso(),9,2)]
The next bit is a little more complicated… how in the world did we come up with this XPath expression?
Explaining the XPath Filter
The XPath filter expression is a little easier to read if you break it down into its parts. It is looking for rows that have the current month name (such as “October”) and the current day (such as “10”). To see this, let’s work inside-out and build this thing up.
ddwrt:Today() – today’s date
string(ddwrt:Today()) – today’s date is a datetime data type, we convert it to a string value
ddwrt:FormatDate(string(ddwrt:Today()),1033,15) – we use the function FormatDate, which requires 3 parameters (string, long, long). This is why we converted the value in the previous step to a string. The second parameter is the locale, 1033 being the English locale. The final parameter indicates the format to use. I had to search on this one, and used Paul’s post on ddwrt FormatDate and FormatDateTime to understand which value to put in here. I needed something that would cause the month name to show up, and format 15 fits the bill.
contains(string(ddwrt:FormatDate(string(ddwrt:Today()),1033,15)),@Month) – The contains function is used here to find a substring within a string. We have the full date from ddwrt:Today, and we represent it with a string like “Saturday, October 10, 2009” through the FormatDate function. We now only need to find if the string in the Month column is found in the string representing today’s date. As an example, today’s date is “Saturday, October 10, 2009”, so I match any row with the value “October” in the Month column. If the value in the Month column for the current row is “July”, then we would not match that row and it would not be included in the results.
ddwrt:TodayIso() – This handy little function returns dates in the format yyyy-mm-ddThh:mm:ssZ . We will use this to obtain the current day.
substring(ddwrt:TodayIso(),9,2) – Today’s date as returned from ddwrt:TodayIso is “2009-10-10”. So, I can use the substring starting at the 9th position and working for 2 characters to retrieve the date as a substring of today’s date.
@Day=substring(ddwrt:TodayIso(),9,2) – This is where we actually filter the values. We will only match rows that meet the condition above (obtaining the current month name) and the condition that the value in the Day column matches today’s day numeric value.
What’s cool is that in SharePoint Designer it will show you the matched rows as you manipulate the XPath expression. It’s also cool that you can provide these kinds of filters as an end user without requiring a team of developers to include this functionality in the next build… an end user can add this stuff rather quickly and meet their business needs, allowing the developers to focus on projects that add strategic business value to the company.