Friday, January 25, 2013

Date Calculations in the Content Query Web Part

The content query web part (CQWP) can do a lot. It can do so much you might think it's Superman. I can't think of many weaknesses. I don't have any Kryptonite on hand to test for weakness, nor is General Zod in the proximity of my server, so let's just say the CQWP is SharePoint's Superman; mild mannered web part with a powerful data aggregation alter ego.

There's a lot of information out there about date calculations using dataview web parts, but I haven't found any about date calculations and the CQWP. What if you have a list - with an associated content type - somewhere on your site and you wish to display that data on your intranet's home page, all the while performing some calculations and displaying the output in an aesthetically pleasing manner? Why deal with the often klugdy look and feel of a dataview web part when you can augment the already powerful CQWP, style it easier, and make your SharePoint page look awesome?

I'm happy to report that the implementation of date calculations between data views and CQWP is about the same, just with a few different steps. In this post I'll walk you through how to incorporate date calculations into a CQWP with a custom stylesheet.

First let's firm up the requirements a little bit more. I have a list of accidents at my site collection root; I want to calculate the difference between the last accident and today, and display the difference on my home page. The last accident date is being captured in a date field called "Last Accident." Additionally, there is only to be one item in this list and it'll be updated as accidents occur. For everyone's sake, let's hope this list isn't updated too much.

  1. Create the accidents list and populate the Last Accident field
  2. On your home page, add a CQWP and build your query. Validate that the list item is displaying.
  3. Download this stylesheet from the SharePoint Designer blog and upload it to a folder in your Style Library. Make sure the stylesheet is published.
  4. Create a new custom item stylesheet. We need to add two declarations. First, let's make the dates pretty. We do that by adding this right before xsl:output:
  5. In the new stylesheet around line 8, we need to import the stylesheet added in step 3.
    <xsl:import href="../Style Library/XSL Style Sheets/CustomStyles/date_templates.xsl"/>
    The beginning of your stylesheet should look like this:
      exclude-result-prefixes="x d xsl msxsl cmswrt"
      xmlns:xsl="" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
      <xsl:import href="../Style Library/XSL Style Sheets/CustomStyles/date_templates.xsl"/>
      <xsl:output method="xml" indent="yes"/>
  6. Create a new custom style. First we need to set up our variables, which are the dates we're calculating the differences between. Be sure to use the column's internal name.
     <xsl:template name="AccidentClock" match="Row[@Style='AccidentClock']" mode="itemstyle">
    	    <xsl:variable name="accidentdate">
    	      <xsl:value-of select="ddwrt:FormatDate(string(@LastAccident), 1033,1 )"/>
    	    <xsl:variable name="today">
    	      <xsl:value-of select="ddwrt:FormatDate(string(ddwrt:Today()), 1033, 1)"/>
  7. The define the delta - the difference between the dates - as another variable
    <xsl:variable name="date_diff">
    	      <xsl:call-template name="getDayDelta">
    	        <xsl:with-param name="paramDateB" select="ddwrt:FormatDateTime(string(@LastAccident), 1033,'yyyy-MM-dd')"/>
    	        <xsl:with-param name="paramDateA" select="ddwrt:FormatDateTime(string(ddwrt:Today()), 1033,'yyyy-MM-dd')"/>
  8. The rest is just HTML styling. The main attraction here is making the delta to display nicely by using this XSL nugget: < xsl:value-of select="$date_diff"/>. The < xsl: if > at the end is to ensure that the web part's footer will only display if there is one or more items
     <div class="wp-image-wrapper">
    	      <img  src="/_Layout/images/Accident_Clock_Icon.png" class="accidentClockIcon"/>
    	    <ul class="accident-clock">
    	      <li class="accident-clock-days" style="list-style:none">
    	        <xsl:value-of select="$date_diff"/>
    	      <li class="accident-clock-days-since" style="list-style:none">
    	        days since
    	      <li class="accident-clock-thelast" style="list-style:none">
    	        the last accident
    	    <xsl:if test="count(following-sibling::*)=0">
    	      <div class="accident-footer">
    	          <a href="../hyperlink">More about safety <img src="/_Layouts/images/LinkArrow.png"/></a>
  9. Save the custom stylesheet and upload it to the Style Library.
  10. Now that we have a stylesheet, export the CQWP from the homepage and modify the property ItemXslLink.
     <property name="ItemXslLink" type="string">../Style Library/XSL Style Sheets/CustomStyleSheets/AccidentClock.xsl</property>
  11. Save the webpart and then import it to the homepage.

Here's what the final result looks like.

If a big NAN displays where the delta should be, that means the calculation didn't work. (NAN stands for Not a Number). Mainly this could be that the internal name used in the XSL is not correct. Double check that the name is correct and consistent in your custom stylesheets. Also validate that the stylesheets are published.

This is just the beginning of date calculations in the CQWP. In this example I only showed you how to calculate and display the delta for one item in one list, but the possibilities are only limited by your imagination. And it's not that hard to do. In the example I provided in this post, you don't have to fret about updating the CQWP's data mapping or common view fields properties. The only requirements are to upload the date template stylesheet to your site and reference it in your custom XSL stylesheet and then add the DDWRT declaration in your new stylesheet.

Additional Reading/Inspiration