Content Query Web Part - Common Enhancements

The content query web part affectionately known as CQWP is one of the go to oob SharePoint components. It's really great at rollups on content types within a site collection. The CQWP is built with xslt so this post will focus on modifications to "ItemStyle.xsl" or your own custom item style file. Be careful of white spaces and line returns when formatting the XSLT. A couple of tools that will help a great deal with the CAML and fields are U2U CAML query Builder, SharePoint Manager and SharePoint designer.

Use additional fields

First, export the webpart and open it in notepad. Edit the CommonViewFields property. The fields are formatted like this "internal name","type"; After adding the fields, save the file and import it back into the site. Test and make sure the web part still works. If not check names, types, white space and new lines.

   <property name="CommonViewFields" type="string">Sorted1,Text;Status,Text;CDate,Date;
   </property>

Formatting Dates

Add the highlighted line to the xsl:stylesheet tag.

<xsl:stylesheet
  version="1.0"
  exclude-result-prefixes="x d xsl msxsl cmswrt"
  xmlns:x="http://www.w3.org/2001/XMLSchema"
  xmlns:d="http://schemas.microsoft.com/sharepoint/dsp"
  xmlns:cmswrt="http://schemas.microsoft.com/WebParts/v3/Publishing/runtime"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime">

You can then format the date like this.

<xsl:value-of select="ddwrt:FormatDate(string(@CDate) ,1033 ,1)"/>

Headers and Footers

There are a number of posts out there on this subject but this is what has worked for me and has been the least painful to develop. First create variables for the header and footer and then use them at the top and bottom of the template.

   <xsl:variable name="HEADER">
     <xsl:if test="count(preceding-sibling::*)=0">
          <![CDATA[<table border="0" cellspacing="0" width="100%">
                <tr>
                <th>Header1</th>
                <th>Header2</th>
                <th>Header3</th>                </tr>]]>
      </xsl:if>
   </xsl:variable>

   <xsl:variable name="FOOTER">
     <xsl:if test="count(following-sibling::*)=0">
         <![CDATA[ </table> ]]>
      </xsl:if>
   </xsl:variable>

       You can then use them like this.

<xsl:value-of select="$HEADER" disable-output-escaping="yes"/>

<xsl:value-of select="$FOOTER" disable-output-escaping="yes"/> 

Only Show the Most Recent Entry

This is common requirement when rolling up status reports. This technique requires that the list items be in the correct order. To ensure that the list items are in the correct order the web part itself needs to be modified. To do this export the webpart and open it in notepad. Edit the QueryOverride property with the correct CAML statements. Save the changes and import the web part. (Any valid CAML can be placed in the QueryOverride tag.)

   <property name="QueryOverride" type="string"><![CDATA[<OrderBy><FieldRef Name="Sorted1"
     Ascending="True"/><FieldRef Name="CDate" Ascending="False"/></OrderBy>]]></property>

Now that the data is in the correct order, only show the first record. First create a variable that contains the value of the unique field. Then use the variable to get a count of records before the current record that contains the unique value.

   <xsl:variable name="UniqueField" select="normalize-space(@Sorted1)" />
   <xsl:variable name="CountUniqueField"
        select="count(preceding-sibling::*[@*['Sorted1']=$UniqueField])" />     

Wrap your detail html with the following if statement.

<xsl:if test="$CountUniqueField &lt; 1">
     <!-- Your xslt & html here --->

</xsl:if>

Format as KPI/Stop Light/Green Yellow Red

Here the status field is compared to green/yellow/red and the src attribute of the <img> tag is set appropriately.

   <img>
      <xsl:attribute name="src">
         <xsl:if test="normalize-space(@status) = 'Green'">/images/green.gif</xsl:if>
         <xsl:if test="normalize-space(@status) = 'Yellow'">/images/yellow.gif</xsl:if>
         <xsl:if test="normalize-space(@status) = 'Red'">/images/red.gif</xsl:if>
      </xsl:attribute>
   </img>

It looks like this all put together

    <xsl:template name="CustomItemStyle" match="Row[@Style='CustomItemStyle']"
    mode="itemstyle">
       
        <xsl:variable name="SafeLinkUrl">
              <xsl:call-template name="OuterTemplate.GetSafeLink">
                   <xsl:with-param name="UrlColumnName" select="'LinkUrl'" />
              </xsl:call-template>
        </xsl:variable>
        <xsl:variable name="DisplayTitle">
              <xsl:call-template name="OuterTemplate.GetTitle">
                <xsl:with-param name="Title" select="normalize-space(@Title)" />
                <xsl:with-param name="UrlColumnName" select="'LinkUrl'" />
              </xsl:call-template>
        </xsl:variable>
        <xsl:variable name="LinkTarget">_blank</xsl:variable>
        <xsl:variable name="UniqueField" select="normalize-space(@Sorted1)" />
        <xsl:variable name="CountUniqueField"
               select="count(preceding-sibling::*[@*['Sorted1']=$UniqueField])" />
         
        <xsl:variable name="HEADER">
            <xsl:if test="count(preceding-sibling::*)=0">
                 <![CDATA[<table border="0" cellspacing="0" width="100%">
                     <tr>
                       <th>Link To Item</th>
                       <th>Status</th>
                       <th>Date</th>           
                    </tr>
                 ]]>
           </xsl:if>
       </xsl:variable>

       <xsl:variable name="FOOTER">
           <xsl:if test="count(following-sibling::*)=0">
                <![CDATA[ </table> ]]>
           </xsl:if>
       </xsl:variable>

       <xsl:value-of select="$HEADER" disable-output-escaping="yes"/>
  
       <xsl:if test="$CountUniqueField &lt; 1">
           <tr>
              <td class="ms-vb">
                <xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate"/>
                  <a href="{$SafeLinkUrl}" target="{$LinkTarget}" title="{@LinkToolTip}"
                   style="font-size:12px;font-weight:bold;">
                       <xsl:value-of select="$DisplayTitle" />
                  </a>
              </td>
                       
              <td class="ms-vb">
                 <img>
                     <xsl:attribute name="src">
                         <xsl:if test="normalize-space(@status) = 'Green'">
                            /images/green.gif</xsl:if>
                         <xsl:if test="normalize-space(@status) = 'Yellow'">
                            /images/yellow.gif</xsl:if>
                         <xsl:if test="normalize-space(@status) = 'Red'">
                            /images/red.gif</xsl:if>
                     </xsl:attribute>
                  </img>
              </td>

              <td class="ms-vb">
                 <xsl:value-of select="ddwrt:FormatDate(string(@CDate) ,1033 ,1)"/>
              </td>

           </tr>
  
       </xsl:if>

       <xsl:value-of select="$FOOTER" disable-output-escaping="yes"/>

    </xsl:template>