SharePoint xslt compare date and display kpi in dvwp

I already had a post about kpi’s in listview but the post is in german and it displays kpi icons based on a status using calculated columns and javascript. In this post i want to show you how to deal with the calculation between today and due date. Based on this returned value, i will display a green, yellow or red icon as kpi indicator.

Well, i first thought i can use the functions format date and transform them to a number to make my calculation. But i was wrong.



ddwrt:FormatDate(string(@DueDate),1033,1)

number(translate(substring(@DueDate,1,10),'-',''))


I thought those lines above would work for me, but they didn’t. It returned no values in both approaches. I got this from these posts (Article 1, Article 2) which hints me to my solution.

The idea

First i had to format them both in the same way: yyyymmdd

If both dates are in this format i can calculate the difference and based on the difference i can display a green, yellow or red icon – like a kpi indicator. So first what i did was to create three variables. One for the current date, the due date and the diff. I made them as output to compare whether the variables are really have the same output format. I realized that my currentdate comes out with 10/08/2013 09:47:33

I used substring cause the dateformat function didn’t work. With concat function i was able to put a string together which makes the format like yyyy.mm.dd – After that i can use translate and replace the “.” with an empty string.

The same i checked with due date. It output was 10.08.2013 – i only need the change the positions by using concat and substring functions. In the datediff variable i calculate the difference between duedate and current date.

Now i was able to display images based on the result of duedate. Well what i did was

if datediff > 2 display green icon

else if datediff <= 2 and datediff >=0 display yellow icon

else display red icon.



<xsl:variable name="currentDate" select="concat(substring(ddwrt:Today(),7,4),'.',substring(ddwrt:Today(),0,3),'.',substring(ddwrt:Today(),4,2))"></xsl:variable>
<xsl:variable name="DueDate" select="concat(substring(@DueDate,6,12),substring(@DueDate,3,4),substring(@DueDate,0,3))"></xsl:variable>
<xsl:variable name="DateDiff" select="number(translate(substring($DueDate,1,11),'.','')) - number(translate(substring($currentDate,1,11),'.',''))"></xsl:variable>
<!--<xsl:value-of select="$currentDate" /><xsl:text> | </xsl:text>
<xsl:value -of select="$DueDate"></xsl:value><xsl:text> | </xsl:text>
<xsl:value -of select="$DateDiff"></xsl:value><xsl:text> | </xsl:text>-->
<xsl:choose>
<xsl:when test="$DateDiff &amp;amp;amp;gt; 2">
<img src="_layouts/images/kpinormallarge-0.gif" alt="" style="width:16px;height:16px;border:0px;" />
</xsl:when>
<xsl:otherwise>
<xsl:choose>
<xsl:when test="$DateDiff &amp;amp;amp;lt;= 2 and $DateDiff &amp;amp;amp;gt;= 0">
<img src="_layouts/images/kpinormallarge-1.gif" alt="" style="width:16px;height:16px;border:0px;" />
</xsl:when>
<xsl:otherwise>
<img src="_layouts/images/kpinormallarge-2.gif" alt="" style="width:16px;height:16px;border:0px;" />
</xsl:otherwise>
</xsl:choose>
</xsl:otherwise>
</xsl:choose>


Really important notice: Check what values are on your browser – in my SharePoint Designer the returned values where different. It display always green cause he interprets the date different to my browser. Maybe an issue with german and english SharePoint Designer installation.

My result looks now like this:

kpitoday

Please notice that today is 08.10.2013 when i did the screenshot.

..:: I LIKE SHAREPOINT ::..

The article or information provided here represents completely my own personal view & thought. It is recommended to test the content or scripts of the site in the lab, before making use in the production environment & use it completely at your own risk. The articles, scripts, suggestions or tricks published on the site are provided AS-IS with no warranties or guarantees and confers no rights.

Karsten Pohnke About Karsten Pohnke
He is Consultant for SharePoint Solutions for collaboration, communication and business processes. He provides his customers applications based on standard features as well as development or combining the power of several microsoft tools like Dynamics CRM. In his free time he tries to collect tipps and worthy experience in this blog.

2 comments on “SharePoint xslt compare date and display kpi in dvwp

  1. HI, I am new to sharepoint. this is exactly describe what I want to do. But it is not clear how to run this code and whether I need to create column in my list. Please provide more details. Thanks

  2. hi, can you post a complete steps… I have a list view where I need to do the same. I have a column date called Next Assessment Date…I need to create a new column that show the KPI icons based on the date diff between today’s date and Next Assessment Date. How to implement this.

Submit comment

Allowed HTML tags: <a href="http://google.com">google</a> <strong>bold</strong> <em>emphasized</em> <code>code</code> <blockquote>
quote
</blockquote>

Please fill in the captcha: * Time limit is exhausted. Please reload CAPTCHA.