Living a SharePoint life

Thursday, August 29, 2013

Create a project management milestone view with the Content Query Web Part

In my last blog post I showed how you can create a nice looking calendar icon for a Content Query Web Part linked to an events list. Now I want to use the same icon to show information from a task list. You can use this for instance as a view for milestones in a project management site. If you haven’t read that article, I suggest you do that first. I will rely in this blog post heavily on information you can read in that article.




To add a few more gadgets to our icon I want the icon to show me overdue milestones in red. Milestones that are 7 days prior to the due date shall be shown in yellow and if the due date is missing, I want to be informed as well.

For the non German readers: 'Nicht begonnen' means 'Not started' and 'In Bearbeitung' stands for 'In progress'.

Extending the CSS class


To show an overdue milestone we need to modify our CSS file we added to the master page. I have a blog post on how this is done. Open the CSS file and add the following snippet to the bottom were you posted the .calendarIcon class information:
.calendarIcon em.yellow {
 background:#726900;
 background:-webkit-gradient(linear, left top, left bottom, from(#726900), to(#baab00)); 
 background:-moz-linear-gradient(top,  #726900,  #baab00); 
}
.calendarIcon em.red {
 background:#7a1e00;
 background:-webkit-gradient(linear, left top, left bottom, from(#7a1e00), to(#c83200)); 
 background:-moz-linear-gradient(top,  #7a1e00,  #c83200); 
}
These two classes will support a new style we’re going to create.

Adding style to our page


Open the ItemStyle.xsl in your Editor and add the following template to it:
<xsl:template name="TasksCalendar" match="Row[@Style='TasksCalendar']" mode="itemstyle">
 <xsl:variable name="dateTime" select="ddwrt:FormatDate(string(@DueDate), 1033, 3)" />
 <xsl:variable name="dateTimeCondensed" select="ddwrt:FormatDate(string(@DueDate), 1033, 2)" />
 <xsl:variable name="date"  select="substring-before(substring-after($dateTime, ', '), ', ')" />
 <xsl:variable name="month" select="substring-before($date, ' ')" />
 <xsl:variable name="shortMonth" select="substring($month, 1, 3)" />
 <xsl:variable name="day"   select="substring-after($date, ' ')" />
 <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="@Title"/>
   <xsl:with-param name="UrlColumnName" select="'LinkUrl'"/>
  </xsl:call-template>
 </xsl:variable>
 <div class="item link-item">
  <div class="description calendar">
   <div class="calendarIcon">
    <xsl:choose>
     <xsl:when test="$day = ''">
      X
     </xsl:when>
     <xsl:otherwise>
      <xsl:value-of select="$day" />
     </xsl:otherwise>
    </xsl:choose>
    <em>
     <xsl:choose>
      <xsl:when test="ddwrt:DateTimeTick(ddwrt:Today()) >= ddwrt:DateTimeTick(@DueDate)">
       <xsl:attribute name="class">red</xsl:attribute>
      </xsl:when>
      <xsl:when test="ddwrt:DateTimeTick(ddwrt:Today()) > (ddwrt:DateTimeTick(@DueDate) - 6048000000000)">
       <xsl:attribute name="class">yellow</xsl:attribute>
      </xsl:when>
     </xsl:choose>
     <xsl:value-of select="$shortMonth" />
    </em>
   </div>
   <div class="Information">
    <xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate"/>
    <div class="title">
     <a href="{$SafeLinkUrl}" title="{@LinkToolTip}">
      <xsl:if test="$ItemsHaveStreams = 'True'">
       <xsl:attribute name="onclick">
        <xsl:value-of select="@OnClickForWebRendering"/>
       </xsl:attribute>
      </xsl:if>
      <xsl:if test="$ItemsHaveStreams != 'True' and @OpenInNewWindow = 'True'">
       <xsl:attribute name="onclick">
        <xsl:value-of disable-output-escaping="yes" select="$OnClickTargetAttribute"/>
       </xsl:attribute>
      </xsl:if>
      <xsl:value-of select="$DisplayTitle"/>
     </a>
    </div>
    <div class="assignedTo">
     <xsl:value-of select="@AssignedTo" />
    </div>
    <div class="status">
     <xsl:value-of select="@Status" />
    </div>
    <xsl:if test="$day = ''">
     <div><strong>
      No Date 
     </strong></div>
    </xsl:if>
   </div>
  </div>
 </div>
</xsl:template>
Save the ItemStyle.xsl back to Sharepoint and your ready to go. Make sure you have added the proper columns to the CQWP, otherwise you won't be able to pick up the date from your list.

“Houston we have a problem”


Of course we could tell the CQWP to filter our selection that only tasks with a due date should appear in our view. However, I find it more convenient, when I’m informed that I goofed on configuring the milestones. For this reason I added these lines that will show me an X on the icon instead of the date. Furthermore a text will be added as well.
<xsl:choose>
 <xsl:when test="$day = ''">
  X
 </xsl:when>
 <xsl:otherwise>
  <xsl:value-of select="$day" />
 </xsl:otherwise>
</xsl:choose>
<xsl:if test="$day = ''">
 <div><strong>
  No Date 
 </strong></div>
</xsl:if>

Adding some colors


You should have added the CSS part to your CSS file by now. In the style definition the following section will take care that the calendar changes it colors.
<xsl:choose>
 <xsl:when test="ddwrt:DateTimeTick(ddwrt:Today()) >= ddwrt:DateTimeTick(@DueDate)">
  <xsl:attribute name="class">red</xsl:attribute>
 </xsl:when>
 <xsl:when test="ddwrt:DateTimeTick(ddwrt:Today()) > (ddwrt:DateTimeTick(@DueDate) - 6048000000000)">
  <xsl:attribute name="class">yellow</xsl:attribute>
 </xsl:when>
</xsl:choose>
<xsl:value-of select="$shortMonth" />
Because the xsl:choose / xsl:when statement stops after the first match, the coloring for the exceeded tasks goes first. Especial the lines 32 & 35 are of interest, because here the testing is done. In line 32 the DueDate field is compared to the actual date. If it is equal or greater, a class property is added to the <em> tag. In line 35 we subtract 6048000000000 from the DueDate first.

I haven’t found much information on the ddwrt:DateTimeTick function. However there is one very good posting in the Microsoft Technet Forum that explains how the Ticks are calculated:

SharePoint Designer 2010 - Determine if today's date is within x days of a start date column using conditional formatting.

[...]

I was trying to do something similar. The problem with using the ddwrt:FormatDateTime approach is adding or subtracting days over the beginning or end of a month. 20110512 minus 20 days is not the date 20110492. I needed to pull a report of items that were older than today, but not older than two weeks ago. I used some of your code, Bpollard3 to make it work:

[(number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@End_x0020_Date)))) <=
number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))) and
(number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@End_x0020_Date)))) >=
number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))-12096000000000)]

That number, 12096000000000, is the number of ticks in 14 days. Here's why it wasn't working for you - Ticks are different in SPD than they are in the browser. In SPD, as in Excel, a Tick is the number of days since Jan 1, 1900. In the browser, a single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond. Here's how I calculated 14 days:

TimeTicks
millisecond10000
second10000000
minute600000000
hour36000000000
day864000000000
14 days12096000000000
Posted by Dave Carlile on Thursday, May 12, 2011 11:44 PM

Now that we know that one day are 864000000000 ticks we multiple this with 7 and get 6048000000000 ticks. Just what we do in line 35. This way we can color the calendar yellow 7 days prior the due date.

That’s it


The rest is simply configuring the CQWP and selecting the proper task list. You create filters that only items are selected that are not finished. You should also consider limiting the view to 2-3 items and sorting the list to show the most reason item first.

I hope you like these posts so far and would like to hear from you what other ideas you got, how to use the calendar icon in SharePoint.
This post is

Featured Post

How are Microsoft Search quota consumed?

With Office 365 Search, Microsoft has created a central entry point for the modern workplace. In one convenient spot, users can access all ...