No-code solution: Displaying the days since a SharePoint list item was last changed

9/20/2011 3:01 PM

by Justin Joyce, LANtek

Note:  This article is part of a collection of posts from four years of the Get the Point blog for SharePoint end-users.

Overview: Custom Aging Reports With No Code

One of the often requested functional pieces of a SharePoint site is an aging report for either tasks or list items. In other words, how many days/months has it been since this list item was last modified?

On the surface this seems to be a very simple request. After all, we have dates for items being created and modified, we have the ability to store custom dates when certain changes to items take place through event receivers. We have calculated columns where we can include Excel-like formulas to work with our information. This seems like a pretty straight forward proposition. We pick a date field, create a calculated column, and then do a formula something along the lines of [DateField] – [Today]. Ah, not so fast though! As anyone who has attempted this "simple" task knows, trying to use something like [Today] in a calculated column causes problems. Try to insert [Today] into your calculated column's formula box will give you an error message something like this:

Why is this? Well, it has to do with the way calculated columns are calculated.

Let's take a simple formula as an example:

= IF( [Column1]<=[Column2], "OK", "Not OK")

All this says is that if Column1 is less than or equal to Column2, then display OK, otherwise display Not OK. This is a fairly typical basic formula for a calculated column and it makes a basic assumption about the list item that contains these columns: The values for Column1 and Column2 will never be able to change without an Update event on the list item.

That's right, calculated columns will only recalculate when the list is updated (or created) since they assume the information you are calculating is contained in the item itself. This creates a problem when you're trying to use something that changes independent of the item's fields, such as today's date.

Now I wasn't in the meeting where they decided that this is the way that calculated columns would function, however, if I had to make an educated guess I would assume that they function this way for performance. Imagine if you had a list of several thousand items, each of which contained a calculated column that needed a "live" update. That would mean that some mechanism, perhaps a timer job, would have to iterate through each item that contained that calculated column every so often and update its value. This could be extremely taxing in terms of performance because with larger deployments this job might constantly be running and changing things. That's just my guess, but it makes quite a bit of sense if you think about it.

There are some suggestions for similar solutions floating around out there that involve tricking SharePoint in to accepting a Today value by first creating a column named Today, then adding it to your formula, then deleting it. These are all well and good, but remember what I said about when calculated columns are updated. This value will only change when the item is updated which means your values will soon be incorrect, especially in the case of a day calculation.

I've seen others using clever JavaScript to write the values to the page. This would work also, but I am pretty much categorically against client script when it can be avoided.

Implementation:

So what to do? Calculated columns are out of the question for so called "volatile" functions like Today. It is possible that we could develop some custom code to take care of this for us like a Computed Column, timer job, or scheduled process to come along and update every single item that needs this calculation made. That brings us back to the problem of performance I mentioned in the last paragraph though, and additionally it is a brittle solution that would be highly specific to the site/list/column in question. On top of those two concerns, you would also have to go find a nerdy guy, such as myself, that knows how to code and persuade him to develop this solution for you. But there is an easier way!

If you have rights to create fields and edit pages on your site, and have a bit of knowledge about XSLT and creating views, you can put together an XSL template that can be included in a list view and will faithfully calculate your value each time the page is requested. This scenario removes our concern over performance, and doesn't require custom code to be developed and deployed via a solution.

Perfect. So how do we do it?

1. Create or select the field that will act as our source. It must be a date type.

2. Create our field that will act as a placeholder for the value being calculated.

3. Add both of these fields to a content type and add that content type to a list.

4. Create a view of that list containing both the source and placeholder columns.

5. Upload the XSL template to the Styles Library.

6. Set the "XSL Link" property for the List View Web Part through the UI.

7. Success!

Let's explore an example use case and walk through the implementation. Our customer wanted a view of their main list that would tell them how long a particular list item had been sitting at its status. This list contained a custom site content type derived from the Item type and added to the list. There was already an event receiver in place that captures each time that status field on the list item was changed and saved that date to a column called "Date Status Changed". All this wiring is not required, and can be done with ANY date field (it just so happens this is our implementation but feel free to experiment). The bare minimum you will need is your source date field and placeholder field to hold your calculation (more on this in the next paragraph) added to your list, although I suggest you use site columns and site content types in case you wish to reuse this solution in other places on your site.

So we have our source date that we can use in our calculation against today's date. Now we can create a custom site column to use as a container for our calculated value. In this case I chose to use a calculated column since it will not be able to be changed on the new or edit item forms, but can be selected for display in the views since we don't want users entering arbitrary values in to this column. It could be confusing as to why it is not being displayed in the views, etc.

Now that we have our site column, we can add it to our content types that will be used in our list. Next, we need to create our view that will later be customized with our XSLT. Make sure you create a standard view that contains your source date column and your new calculated column that will act as a placeholder for the calculated value.

We now have everything in place that we will require to support our custom aging report. All that remains is creating our XSL template, uploading it to the site's Style Library, and linking it to our list view. The XSL template we will be using is going to contain some normal SharePoint-generated markup for generating the view as well as our own custom markup used to override certain parts of this and calculate our desired value for us.

Giving credit where credit is due, the XSL templates for doing the actual calculations I am using for this solution were graciously provided by "swirch" on the MSDN forums:
http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/aeda905b-9bc6-40c4-bd22-21306c5cb0d2/

Download the XSL stylesheet (aging.zip) I have put together located here:
https://OneDrive.live.com/?cid=c262e8e2d59a86d9&permissionsChanged=1&id=C262E8E2D59A86D9!104

Opening this up in your favorite text editor you will see plenty of normal SharePoint XSL markup for rendering the views, if you keep scrolling down to line 357 you will see the start of the custom templates that I added to the markup, the first one being the "DateDiff" template followed by "calculate-julian-day" and "FieldRef_printTableCell_EcbAllowed.Days_x0020_At_x0020_Status". These are our three templates that will make and display our calculations in our views. If you are going to be using different field names than were specified earlier in this article, you will need to go through these templates and replace any references to the other names. Remember, for this you will want to use the INTERNAL name of the field not the display name.

Once you are satisfied that the template is ready to go, navigate to your Style Library and upload it under the "XSL Style Sheets" folder then copy down the link to the file. This will allow us to easily make changes to it later, or add it to different parts of the site as we please.

Next, go to your list and select the view you created earlier in this article. From the "Site Actions" menu click on "Edit Page".

Find your List View Web Part on the page and open the Web Part menu by clicking on the small downward facing arrow in the upper right hand corner. From this menu select "Edit Web Part".

This will open up the Web Part's menu on the right hand side of your browser window.

Click on the + for the "Miscellaneous" section and locate the "XSL Link" property.

Paste in the link to your XSL file in your Styles Library that you copied down earlier (this can be a relative or absolute link).

Click "OK" to save your changes then click the "Stop Editing" button on the "Page" ribbon on the top of the page.

If everything was configured correctly you should now see numbers in your "Days At Status" column.

And finally, here is what it would look like with some test data of various dates:

Summary:

There it is: a nicely formatted, robust, and better performing way to create an aging report in SharePoint., complete with a simple no-code implementation. This has quite a few potential applications aside from the one use case we explored here. Another common scenario for this type of report is attaching it to a tasks list so that you can see how long it's been since a task was created at a glance.

Enjoy!

--Justin

Justin Joyce, LANtek

Comments

Steps missing
10/8/2012 3:51 AM
ok I followed the steps, but there must be something missing - how will the XSL know which date to use, or which field to add the days since into? hate it when steps are missed.

No-Code, agreed!
8/30/2012 12:12 PM
I agree - I don't think this really counts as "no code".
Interestingly, through some screwup of SharePoint, I have a working calculated column using Today... not sure how or why because I can't get it to do it again, but the one is still there and working.

Formula for "Days At Status" Calculated Column?
5/2/2012 7:39 AM
Justin - What is the formula you used for your "Days At Status" calculated site column (placeholder column)? Was it "=today"?

SharePoint 2007
12/2/2011 11:29 AM
Currently I have not attempted to apply this solution to SharePoint 2007, however I am looking in to it. Unfortunately there is no XslLink property surfaced on the web part through the UI.

Great Post
11/30/2011 9:53 AM
Hello,
Great Post.
I am using SharePoint 2007.
I don't have a Misc section as noted above.
Do you have steps for a SP2007 configuration?
Thank you.

Re: No-code solution: Displaying the days since a SharePoint list item was last changed
10/11/2011 8:24 AM
Hi Chris.
great find!
i'm going to take a look at what you posted hopefully later on today and see if i can make this solution a little more robust.
i'm glad you liked the post, and i'm very glad you were able to find a solution to the European date format. :)
-Justin

Solution for European Date Formats
10/11/2011 6:45 AM
Hi again Justin,
FYI, I found a solution for the issue I mentioned previously on this page;
https://sharepointbydummies.wordpress.com/2011/07/13/possible-work-around-to-date-format-issue-sharepoint-2010/

European Date Formats
10/7/2011 3:59 AM
Hi Justin,
This is a really good solution thanks, and just the sort of thing I've spent the last two days looking for! However I'm having a bit of a problem with it and I was hoping you could help me.
I've altered your code slightly to calcultate the number of days until something happens, rather than since, by switching the variables in the last line of the "DateDiff" function;

<xsl:value-of select="$JulianToday - $JulianStartDate"></xsl:value-of>

However I'm only able to get it to caclulate the difference correctly half of the time. So for instance with this date (format dd/MM/yyyy);

30/12/2011

It calculates correctly, but with this date (same format)

12/10/2011

It calculates as if if 10-Dec-2011 rather than 12-Oct-2011.
I tried simply switching the positions of the day and month values in the "JulianStartDate" variable, like this;

<xsl:with-param name="Month" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),7,2)"/>
<xsl:with-param name="Day" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),5,2)"/>

And this corrected the problem with the second date, however it was then incorrect for the first date!
I've also tried altering the FormatDateTime calls to use European LCIDs and various alterations to the last parameter of FormatDateTime (e.g. ddMMyyyy, MMddyyyy) with the appropriate adjustments to the substring positional parameters without success.
I'd greatly appreciate any advice you can offer.
Thanks,
Chris

No-Code
9/21/2011 4:27 AM
I don't think that XSL qualifies as a "no-code" solution, as understanding the XSL language is not for everyone - however it doesn't involve programming. Besides that: Nice solution, thank you!