Useless calculated column today trick – XSL today() to the rescue.

______________________________________________________________________________
This blog has been discontinued. Please visit my new site based on a completely different concept:
Sharepoint Resource Center

SharepointResourceCenterFrontPAge
…Your gateway to all things Sharepoint…

______________________________________________________________________________

So after a long break I’ve decided to get back to blogging :)

Yesterday a new requirement was made that forced me to look at XSL to achieve my goal. I’d looked at some seemingly cryptic XSL code snippets before and found it to be very unappealing (to put it mildly). Turns out it can actually be fun and is definitely something I’ll delve into a bit more.

Scenario

We have a holiday booking system in place. Currently when requesting a holiday a user will be able to see his holiday allotment for the current year, the number of holiday days taken (or approved) and the number of holiday days remaining

HolRequest1

Requirement

When booking a holiday users should also see the number of holiday days accrued to date.

Now, the holiday days accrued figure itself is easy to calculate – simply by dividing yearly holiday allotment by 12 we get a monthly accrual. Multiply it by the number of months up to today and you get the holiday accrued to date figure.

Calculated column and [Today]

The problem is in order to calculate the holiday accrued to date figure I need to use today’s date to parse the number of months passed from beginning of the year up to now.

This would be really easy to achieve using a calculated column. Unfortunately, Sharepoint doesn’t offer a Today() function that would consistently return today’s date. Sure you can use the [Today] variable in calculated columns – it will correctly return today’s date. However, visit the list tomorrow and you’ll find the variable is still stuck at it’s original value – it won’t automatically update to show today’s correct date. You’d need to update the list item (edit it) to force [Today] to update. This problem has been discussed many times – in fact there’re plenty of people out there who still think the work-around suggested by many people (creating a dummy [today] column, creating your formula and deleting the [Today] column) works. It doesn’t – Sharepoint will simply not update the value of [Today] dynamically (see The truth about using [Today] in calculated columns and Calculated columns – the useless [Today] trick for more details).

So how do I get today’s date I need to compute the holiday days accrued to date figure? I can’t store the value in my list itself – however, with a bit of XSL I can take the underlying list data, parse the dynamic Today value, perform my calculations and overlay the result in a ListViewWebPart.

Below are the steps I followed:

1) Create a new .aspx page in SPD
2) Add a new web part zone
3) Add a ListViewWebPart that displayed the Holiday Register list to the new web part zone (the Holiday Register list is used to Track each employees holiday allotment, holiday days taken or approved and holiday days remaining).
4) Convert the webpart to XSLT (“Covert to XSLT Data View” command.)

image

(Note: Real Employee names have been replaced by “Employee Name”

5) Add a new column to the far right, call it “Holiday Days Accrued to date”
Note: Since I can’t store the value of Today()  or Days Accrued to date in the list itself I will instead take the data available in the list and use XSL to dynamically generate and display the figures I need.

6) Now the fun begins. How do I display today’s value? I’ve looked through a few articles and found the ddwrt:(Today) function (for a good post about it see Displaying today’s date using XSL and SharePoint . )

I highlighted the Holiday Days Accrued to Date column, switched to code and replaced the auto-generated line of code:

<xsl:text xmlns:ddwrt=”http://schemas.microsoft.com/WebParts/v2/DataView/runtime” ddwrt:nbsp-preserve=”yes” disable-output-escaping=”yes”>&amp;nbsp;</xsl:text>

with

<xsl:value-of select=”ddwrt:Today()”/>

Cool, I now have today’s date displayed.

DaysAccrued2

7) What I need here though is just the number corresponding to the current month (e.g. November – 11). Turns out there’s another function I can use

FormatDateTime(string szDate, long lcid, string szFormat);

Where:
string szDate - either an existing date column or a function (e.g. ddwrt:(Today())
long lcid - a number corresponding to the locale id (e.g. British English is 1033)
string szFormat - e.g. ‘yyyyMMdd’, ‘dd/MM/yyyy’ etc.

Combining the two functions together and using MM as my string szFormat I get the current month’s number

<xsl:value-of select=”ddwrt:FormatDateTime(string(ddwrt:Today()),1033,’MM’)”/>

DaysAccrued3

8) So far so good, what I need now is to calculate the average monthly  holiday accrual. The monthly accrual would be my yearly holiday allotment divided by 12

I create a new monthly holiday accrual column and put in the following XSL string there:

<xsl:value-of select=”format-number(@Holiday_x0020_allotment, ‘#,##0.00;-#,##0.00′) div 12″ />

The string above takes the value of my Holiday Allotment column and divides it by 12. I’m not sure why but I had to use the format-number() function to format the number (I’m sure there are better ways to do that) – even though the holiday allotment is a number type column.

DaysAccrued4

I then use the Round() function to round up (or down) the monthly holiday accrual figure:

<xsl:value-of select=”round(format-number(@Holiday_x0020_allotment, ‘#,##0.00;-#,##0.00′) div 12)” /></td><td class=”ms-vb”><xsl:value-of select=”ddwrt:FormatDateTime(string(ddwrt:Today()),1033,’MM’)” />

DaysAccrued5

9) Almost there, all the remains now is to multiply the figures

Accrual days to date = monthly holiday accrual * (current month –1)

<xsl:value-of select=”round((ddwrt:FormatDateTime(string(ddwrt:Today()),1033,’MM’)-1)*(format-number(@Holiday_x0020_allotment, ‘#,##0.00;-#,##0.00′) div 12))” />

DaysAccrued6

Note: XSL is quite particular when it comes to spaces and certain characters – I found it easier to use the built in XPath Expression Builder – you can invoke it by starting to type your xsl statement – e.g. <xsl:value-of select=  – the dialog box will appear allowing you to craft your code using an editor rather than typing stuff by hand.

HolRequest3

10) We now need to filter the list so that each employee only sees his/her own holiday allotment.

Chose Common Data View Tasks > Filter and specified the following filter

Employee equals [Current User]

DaysAccrued7

11) Now it’s just a matter of pasting the code behind the ListViewWebPart into my customized NewForm.aspx

HolRequest2

Done. The above approach may not be ideal as the holiday accrual data in not tracked in the list itself. However, till the next release of Sharepoint (when the [Today] “issue” will hopefully be fixed) using XSL offers a workable solution.

About these ads

2 Responses

  1. This is great, if I wanted to use this approach to caluclate how many days old my record was, what would you recommend?

  2. Hey, Will you be willing to share this custom page with me?

    thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: