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[current()/@Title=@Title]/@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[current()/@Title=@Title]/@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[not(@Title = preceding-sibling::*/@Title)]">
   <xsl:sort select="sum($Rows[current()/@Title=@Title]/@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[current()/@Title=@Title]/@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.

Technorati Tags: SharePoint,DVWP,Data View Web Part,Data form Web Part,ddwrt: namespace,GetFileExtension,MapToIcon

SharePoint Data View Web Part Extension Functions in the ddwrt Namespace

Serge van den Oever wrote this MSDN article about the functions that are available in the ddwrt namespace.  He wrote it for SharePoint 2003 and posted it to MSDN in October 2005, but I’ve never found a newer or better reference for MOSS.  I’ve used Serge’s postings many times to help solve challenges, and I recommend him if you ever run across them.

I’ve found a few additions to Serge’s list below, but I haven’t been able to find documentation for most of them:

Function (ddwrt:)	Called with (example)	Result
amkeyfield	ddwrt:amkeyfield=”ID”	???
amkeyvalue	ddwrt:amkeyvalue=”ddwrt:EscapeDelims(string(@ID))”	???
ammode	ddwrt:ammode=”view”	???
cf_ignore	ddwrt:cf_ignore=”1″	???
EscapeDelims EcmaScriptEncode	ddwrt:EscapeDelims(string(@ID)) ddwrt:EcmaScriptEncode(string(@ID))	??? Encodes a string into the ECMAScript format.

Here are a few excerpts from Serge’s article (any references to FrontPage are still valid for SharePoint Designer):

Introduction

Microsoft Windows SharePoint Services provides the powerful Data View Web Part that can perform an Extensible Stylesheet Language Transformation (XSLT) on XML data retrieved from a data source. When you are working with a Web site based on Windows SharePoint Services from within Microsoft Office FrontPage 2003, you can use the Data View Web Part to do the following:

• Define a query and data source from which to retrieve the XML data. Data sources can be SharePoint lists or external databases such as Microsoft SQL Server.
• Define the XSLT transformation that converts XML retrieved from the data source into HTML. FrontPage offers a WYSIWYG experience for editing these XSLT views, including live data preview.

During the XSLT transformation process, the Data View Web Part uses an XSLT extension object that provides several functions in the ddwrt namespace. These functions perform tasks such as accessing properties of a SharePoint list or firing events to connected Web Parts. This article describes the functions implemented by the extension object.

…

Following is information about the set of functions implemented in the ddwrt namespace. These functions are prefixed with ddwrt in the XSLT code that is generated by FrontPage.

AutoHyperLink
AutoNewLine
ConnEncode
Counter
FieldFilterImageUrl
FieldFilterOptions
FieldPrefix
FieldSortImageUrl
FieldSortParameters
FilterLink
FormatDate
FormatDateTime
GenDisplayName
GenFireConnection
GenFireServerEvent
GetFileExtension
GetStringBeforeSeparator
GetVar
IfNew
IsPrivilegedUser
Limit
ListProperty
MapToAll
MapToControl
MapToIcon
NameChanged
PresenceEnabled
SelectOptions
SetVar
ThreadStamp
Today
TodayIso
UrlBaseName
UrlDirName
UrlEncode
URLLookup
UserLookup

Technorati tags: SharePoint, XSL, ddwrt, Data View Web Parts, DVWP

Comparing Dates in SharePoint Using XSL

Especially when you are using Data View Web Parts (DVWPs), but anytime you are using a Web Part that allows custom XLS to control display, you may want to do a test on dates.  Doing this is relatively easy if you know a few of the ddwrt namespace date functions.

In this example, let’s say that you’d like to display calendar events that occur today or later.  (Old calendar events aren’t of much use to display.)

The first step is to get today’s date.  You can do this with the following XSL:

<xsl:value-of select=”ddwrt:Today()”>
(‘02/19/2008′ if your language is set to 1033)

You can also get the full ISO date and time with:

<xsl:value-of select=”ddwrt:TodayISO()”>
(‘2008-02-19T11:15:02Z’)

For comparison purposes, though, you’d like to have the date in YYYMMDD format.  You can do this conversion with the FormatDateTime ddwrt function:

<xsl:value-of select=”ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, ‘yyyyMMdd’)”>
(‘20080219′)

So, to compare your event date to today’s date, it’s a quick test:

<xsl:if test=”ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, ‘yyyyMMdd’) &lt;= ddwrt:FormatDateTime(string(@EventDate), 1033, ‘yyyyMMdd’)”>

Technorati tags: SharePoint, DVWP, XSL, compare dates

Eliminating Duplicates in Data View Web Parts

A Data View Web Part (DVWP) that displays a dropdown can be a useful tool to drive navigation.  See my previous post about showing content archives (Displaying an Archive for a SharePoint List) as an example.

If you have a decent number of items in the list you are using, you will likely end up with duplicates in your dropdown, which isn’t really what you want.  Here’s the trick to remove the duplicates.

When you add your DVWP, add your column for the dropdown, and set your DVWP layout to a dropdown view type, you will get a chunk of code like the following:

<xsl:template name="dvt_1.rowview">
  <option>
    <xsl:attribute name="value">
      <xsl:value-of select="@Your_x0020_Column" />
    </xsl:attribute>
    <xsl:value-of select="@Your_x0020_Column" />
  </option>
</xsl:template>

Find this code, and change it as follows:

<xsl:template name="dvt_1.rowview">
  <xsl:variable name="NewGroup" select="ddwrt:NameChanged(string(@Your_x0020_Column), 0)" />
  <xsl:if test="string-length($NewGroup)">
    <option>
      <xsl:attribute name="value">
        <xsl:value-of select="@Your_x0020_Column" />
      </xsl:attribute>
      <xsl:value-of select="@Your_x0020_Column" />
    </option>
  </xsl:if>
</xsl:template>

The ddwrt:NameChanged function will return a value only when the value of the column has changed since the last new value.

Next up: How to make something happen when the user selects a value from the dropdown.

Technorati tags: SharePoint, SharePoint Designer, Data View Web Parts