Calculate Days between Two SharePoint List Dates in XSL Using ddwrt:DateTimeTick

There was an interesting question over in the MSDN Forums the other day that I struggled to answer. It was a headscratcher, so I had to figure it out. In the thread, emfuentes27 wanted to know why s/he saw different results in SharePoint Designer than in the browser when using this formula:

<xsl:value-of select="number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@Due_Date))))- number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))" />

Not only had I never seen the ddwrt:DateTimeTick function (no documentation on that is available anywhere at all that I can find), but the numbers just didn’t make sense.

[important]The ddwrt namespace functions are incredibly valuable, but they are simply not documented by Microsoft anywhere. There is a single article by Serge van den Oever (@svdoever) from the SharePoint 2003 days which explains it (very well), but that’s really it.[/important]

The ddwrt:DateTimeTick isn’t documented there and I’d never seen it in the wild. At least now I know about it.

To determine the days between two dates in the past, I’ve always used date arithmetic XSL templates, as I explain in my post Date Arithmetic in SharePoint DVWPs. The ddwrt:DateTimeTick function turns out to be a lot easier to use, but as I said, the numbers just didn’t make sense. They didn’t make sense, that is, until I went back to a little arithmetic.

It seems that the values in the browser were off from the ones in SPD (I tested this in WSS 3.0 because I had that VM open) by a factor of 864000000000. Trying to figure out the significance of that, I realized that it’s the number of seconds in a day times 1 million:

864000000000 = 60 * 60 * 24 * 1000000

60 * 60 * 24 = the number of seconds in a day.

Wolfram Alpha helps with this. Try going there and typing in 86400 seconds.

Who knows why this is the case, but this equation will give you the right answer in a browser (it will be wrong in SPD), which is what you are really after in the first place:

<xsl:value-of select="(number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@Lead_x0020_Date)))) - number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@Created))))) div 864000000000" />

The ddwrt:DateTimeTick function is a great tool for your XSL work in SharePoint. It returns the number of “ticks” since Dec 30, 1899 in SharePoint Designer. That’s an odd date to use as the base, but you can test it by adding this to your XSL:

1899-12-30::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-01')))" /><br/>
1899-12-31::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-02')))" /><br/>
1900-01-01::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-03')))" /><br/>

Even odder, the base date in the browser seems to be Jan 1, 0001. You don’t see that date bandied about too often. Go ahead; give it a go:

0001-01-01::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-01'))) div 864000000000" /><br/>
0001-01-02::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-02'))) div 864000000000" /><br/>
0001-01-03::<xsl:value-of select="ddwrt:DateTimeTick(ddwrt:GenDisplayName(string('0001-01-03'))) div 864000000000" /><br/>

This is yet another example where the rendering engine in SharePoint Designer doesn’t yield the same results as what the SharePoint server provides. Don’t expect something like this to be fixed any time soon, though, given Microsoft’s abandonment of the Design and Split View in SharePoint Designer 2013.

We don’t really care what the base date is for zero ticks, of course; we just want to be able to use the values to determine the difference in days between two “ticks”. As long as you use the million-day-seconds trick, all is well.

This post also appeared at NothingButSharePoint.com on 2013-01-09. Visit the post there to read additional comments.

The Content Query Web Part (CQWP) and the ddwrt Namespace

I’m positive I’ve run into this little issue before, but since I don’t seem to have done a post on it, I stymied myself again.

A client of mine spotted a nice post from Ben Tedder (@bentedder) showing how to Create a Classifieds Listing using SharePoint 2010. It uses a bunch of Content Query Web Part (CQWPs) to build up a sort of virtual bulletin board where people can post things they have for sale, help they need, upcoming community events, etc. Ben has been doing a lot of really cool stuff with SPServices, so I’m familiar with his stuff and know that the quality level is high.

Ben’s post gives a nice outline of how to create this type of solution, and we decided I’d implement it. Normally I’d jump straight to the Data View Web Part (DVWP) for something like this, but there’s some appeal to the CQWP in this case. It’s not unlikely that they will want to change both how the categories are laid out on the page and what categories are available, and there’s no reason to involve a developer at that point; the CQWP is pretty easily adjusted by a reasonably sophisticated user.

When I first dropped Ben’s template into the ItemStyles.xsl file, saved it, and refreshed my page, I got an error. No worries, I figured, I must have missed a closing tag or comma or something. About an hour and a half later, I’d tried every version of paring the XSL down that I could think of and had pretty much isolated the issue to where I was displaying the Created date. But I couldn’t for the life of me figure out why it wouldn’t work.

Time for a drive to get some lunch, and within five minutes the answer had occurred to me. (I’m a big proponent of separating myself from the physical location where something has me stumped – I find it lets my mind wander into different territory.)

The problem was that the ddwrt namespace wasn’t registered in ItemStyles.xsl. What that means is that where I was using the ddwrt:FormatDateTIme function, SharePoint had no idea what I was talking about, and it threw an error. When I displayed the Created date without using the ddwrt function, it worked just fine.

[important]If you’re not familiar with the ddwrt namespace, you should be if you write any XSL at all for SharePoint. The only article I’ve ever found which explains it is one on MSDN from Serge van den Oever
which he wrote way back in 2005 called SharePoint Data View Web Part Extension Functions in the ddwrt Namespace. It’s such an important namespace, it always amazes me that this is the only article which explains it on Microsoft’s site.[/important]

Once back in the saddle, I looked at one of my trusty DVWPs and figured out where the references need to be. Here’s the top of the ItemStyles.xsl file before I edited it:

SNAGHTML1de513ac

and after I added the reference to the ddwrt namespace.

SNAGHTML1de594eb

It’s a simple little change, but absolutely necessary if you want to use the ddwrt namespace functions in your CQWPs.

Now, with this post, I’m happy that I won’t paint myself into this corner again, and hopefully it’ll help a few of you to stay out of that corner as well.

Showing Subtotals in a DVWP, Sorted by the Subtotals – Part 1

I ran across a post over at the MSDN SharePoint – Design and Customization forum which intrigued me enough to want to figure it out.  The question was about showing subtotals for a given column’s values, with the results sorted by the subtotal, descending.

In other words, for a list with these items:

Title Amount
Bob 20
Chrissy 10
Bob 5
Mabel 50

Return this:

Title Amount
Mabel 50
Bob 25
Chrissy 10

Here’s the code to do it.  The tricky parts were to get the xsl:sort value right and then to display the items with duplicates removed using ddwrt:NameChanged.  I’m sure that there are more efficient ways to do this, but this one works!

<xsl:template name="dvt_1.body">
    <xsl:param name="Rows"/>
    <xsl:for-each select="$Rows">
        <xsl:sort select="sum($Rows&#91;current()/@Title=@Title&#93;/@Amount)" data-type="number" order="descending" />
        <xsl:call-template name="dvt_1.rowview">
            <xsl:with-param name="Rows" select="$Rows"/>
        </xsl:call-template>
    </xsl:for-each>
</xsl:template> 

<xsl:template name="dvt_1.rowview">
  <xsl:param name="Rows"/>
  <xsl:variable name="NewTitle" select="ddwrt:NameChanged(string(@Title), 0)"/>
  <xsl:if test="string-length($NewTitle) > 0">
<tr>
<td class="ms-vb">
       <xsl:value-of select="@Title"/>
    </td>
<td class="ms-vb">
       <xsl:value-of select="sum($Rows&#91;current()/@Title=@Title&#93;/@Amount)"/>
    </td>
   </tr>
  </xsl:if>
 </xsl:template>

UPDATE 2009-05-21: I just found another approach over at Christian’s Pampigt blog.  By changing the xsl:for-each to test for unique Titles, we don’t need to do the work in the dvt_1.rowview template below.  I don’t know that this would be any more efficient (I’m sure the XSL gurus out there would know), but it’s a nice alternate approach.

<xsl:template name="dvt_1.body">
  <xsl:param name="Rows"/>
  <xsl:for-each select="$Rows&#91;not(@Title = preceding-sibling::*/@Title)&#93;">
   <xsl:sort select="sum($Rows&#91;current()/@Title=@Title&#93;/@Amount)" data-type="number" order="descending" />
   <xsl:call-template name="dvt_1.rowview">
    <xsl:with-param name="Rows" select="$Rows"/>
   </xsl:call-template>
  </xsl:for-each>
 </xsl:template>
 
 <xsl:template name="dvt_1.rowview">
  <xsl:param name="Rows"/>
   
<tr>
    
<td class="ms-vb">
     <xsl:value-of select="@Title"/>
    </td>
    
<td class="ms-vb">
     <xsl:value-of select="sum($Rows&#91;current()/@Title=@Title&#93;/@Amount)"/>
    </td>
   </tr>
 </xsl:template>

Search Results XSL Question

I had a question from a colleague today about how to do an XSL transformation of some search results.  Here’s the query:

Basically, I want to do the following in XSL:

  1. Parse a managed property, urlEncoded, that has the format of http://[doc library]/[filename] to separate path and filename
  2. Take that prefix and concatenate it with ‘/Forms/DispForm.aspx?ID=’
  3. Take that and concatenate it with another managed property, owsidinteger

All this in one big ugly xsl statement.

This is actually pretty simple, if you know how to do it!  Let’s assume some data for urlEncoded like this:

  • http://servername.com/doclib/doc1.doc
  • http://subdomain.servername.com/doclib/doc1.doc
  • http://subdomain.servername.com/site1/site2/site3/doclib/doc1.doc

Those are the three big levels of complexity.  Visiting the excellent reference MDSN article about the ddwrt namespace by Serge van den Oever, we can see that the UrlDirName function may do the trick:

public string UrlDirName(string szUrl);

Returns the directory name of the file in the given URL szUrl. For example, if szUrl is "/a/b/basename.ext", the value "/a/b/" is returned.

With the data above, UrlDirName will return the following:

  • /doclib
  • /doclib
  • /site1/site2/site3/doclib

So the ‘big ugly XSL’ answer is:

<xsl:value-of select="concat(ddwrt:UrlDirName(string(urlEncoded)), '/Forms/DispForm.aspx?ID=', owsidinteger)"/>

The syntax for the hyperlink is something like:

<a href="{concat(ddwrt:UrlDirName(string(urlEncoded)), '/Forms/DispForm.aspx?ID=', owsidinteger)}">
<xsl:value-of select="@Title"/>
</a>

Note the squiggly brackets.  I think the most common ‘gotcha’ with using the ddwrt functions is forgetting to do the explicit string conversion.

Displaying the Document Type Icon in a DVWP in SharePoint

Oftentimes you’d like to mimic the out of the box display capabilities of SharePoint when you create a view using a DVWP (Data View Web Part or Data Form Web Part).  One of the things you might like to do is to show the document type icon for the documents in your view.  These are the little icons that you see in views where you display the Type column for a Document Library, but you might want to show these icons in other cases as well.

By default, these icons are stored in _layouts/images.  This maps to the C:/Program Files/Common Files/Microsoft Shared/web server extensions/12/TEMPLATE/IMAGES folder in the SharePoint hive.  If you look in that folder, you will see thousands of images, but most of the document type icons take the form IC[Document Type].GIF.  So the Microsoft Word icon is ICDOC.GIF.

The information about which icon is displayed for each document type in stored in the DOCICON.XML file which is stored in the hive at C:/Program Files/Common Files/Microsoft Shared/web server extensions/12/TEMPLATE/XML.

The DOCICON.XML file contains mappings for the document type values (the document extensions):

<Mapping Key="doc" Value="icdoc.gif" EditText="Microsoft Office Word" OpenControl="SharePoint.OpenDocuments" />

as well as for the program IDs:

<Mapping Key="Word.Document" Value="ichtmdoc.gif" EditText="Microsoft Office Word" OpenControl="SharePoint.OpenDocuments" />

Finally, the DOCICON.XML file contains a default icon to use in case there isn’t anything specific about the document type or program ID for the specific file.  By default, this is the icgen.gif icon — the generic icon.

This is all well and good, but how do you take advantage of this well thought out scheme?  Well, the trusty yet surprisingly undocumented ddwrt: namespace comes to the rescue once again.  (Thank goodness for this article from Serge van den Oever or all of this would be an unknown mystery.)

There are two ddwrt functions that you can take advantage of here: GetFileExtension and MapToIcon.  So, all of this complexity can come down to one simple line to display the document type icon:

<img alt="Type" src="/_layouts/images/{ddwrt:MapToIcon('', ddwrt:GetFileExtension(string(@FileLeafRef)))}"/>

In this example, I want to display the document type icon for a file stored in a Document Library, so I am using the @FileLeafRef column (friendly name: Name).  First I call GetFileExtension to pull out the file extension (e.g., doc).  Note the type conversion to string.  As I have mentioned in previous posts, many of the ddwrt: functions require this explicit conversion.  Next, I pass that file extension to the MapToIcon function.  The MapToIcon function takes two arguments: Program ID and File Extension.  Since I don’t know or care about the Program ID in this case, I leave it blank and just pass in the File Extension.  All set: the compound function passes me back the document type icon, easy as pie.