1,804 articles and 14,579 comments as of Wednesday, January 12th, 2011

EndUserSharePoint has combined resources with NothingButSharePoint.com. You can now find End User (Mark Miller), Developer (Jeremy Thake) and IT Pro SharePoint (Joel Oleson) content all in one place!

This site is a historical archive and is no longer being updated. Please update your favorites, bookmarks and RSS feeds.

NothingButSharePoint.com
Monday, November 2, 2009

Comparing Dates in a Data View Web Part to Build a WSS KPI in SharePoint

Comparing DatesThis is yet another blog post on comparing dates in XSL in a data view web part.

This is my scenario:

 

 

  • I have a custom list.
  • The business purpose of the list is to support the idea of a new employee and his/her tasks to be completed within 7 days of starting the job.
  • They are in a “warning zone” after 4 days if they have not completed these tasks.
  • I want to create a simple dashboard that shows green when they have either completed the task or if they have more than 4 days to complete it.
  • I want the dashboard to show yellow if they are in the warning zone.
  • I want to to show red if they have not completed the task after the due date.

I borrowed from this article at Marc Anderson’s blog to get the comparison logic and this article at www.endusersharepoint.com for the basic ideas behind the dashboard written by Toni Frankola.

You should read the supporting blogs, but the implementation goes like this:

  1. Create a content type (columns + CT)
  2. Create a custom list and associate it with the content type.
  3. Create a web part page.
  4. Add the custom list to the web part page.
  5. Open up the page in SPD.
  6. Convert the list to a DVWP.
  7. Modify the XSL to generate the dashboard bits.

I have two dates: a warning date and a due date.  To compare the dates, my xsl does the following:

           <xsl:choose>

        <!-- When both handbook and policy are signed, we are green regardless of dates. -->
         <xsl:when test="@Employee_x0020_Handbook_x003F_ = 1 and @Security_x0020_Policies = 1">
         <img src="/_layouts/images/KPIDefault-0.GIF" alt="No problems"/>
         <a hrefDashboard.aspx">="/HumanResources/Lists/New Employee Checklist/EditForm.aspx?ID={@ID}&amp;Source=/HumanResources/Shared Documents/New Hire  [Edit]</a>
         </xsl:when>

         <!-- Show amber indicator if we're past the warning date. -->
         <xsl:when test="ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, 'yyyyMMdd') >= ddwrt:FormatDateTime(string(@TaskDueDate), 1033, 'yyyyMMdd')">
         <img src="/_layouts/images/KPIDefault-2.GIF" alt="Overdue"/>
         <a style="border: 0px" hrefDashboard.aspx">="/HumanResources/Lists/New Employee Checklist/EditForm.aspx?ID={@ID}&amp;Source=/HumanResources/Shared Documents/New Hire  [Edit]</a>
         </xsl:when>

         <!-- Show red indicator if we're passed the due date. -->
         <xsl:when test="ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, 'yyyyMMdd') >= ddwrt:FormatDateTime(string(@Warning_x0020_Date), 1033, 'yyyyMMdd')">
         <img src="/_layouts/images/KPIDefault-1.GIF" alt="Warning"/>
        <a hrefDashboard.aspx">="/HumanResources/Lists/New Employee Checklist/EditForm.aspx?ID={@ID}&amp;Source=/HumanResources/Shared Documents/New Hire  [Edit]</a>
         </xsl:when>

         <!-- If we get here, we're earlier than the warning date, so we're green. -->
         <xsl:otherwise>
         <img src="/_layouts/images/KPIDefault-0.GIF" alt="No problems"/>
         <a hrefDashboard.aspx">="/HumanResources/Lists/New Employee Checklist/EditForm.aspx?ID={@ID}&amp;Source=/HumanResources/Shared Documents/New Hire  [Edit]</a>
         </xsl:otherwise>

        </xsl:choose>

A few key points from above:

  • I tried to compare dates without using the ddwrt functionality and got nowhere.  I still don’t understand that.  They dates displayed correctly, but both “>” and “<” comparisons always failed.  In the end, ddwrt came to my rescue (thanks, again, Marc).
  • I’m also displaying an [Edit] link to the item.  I mainly did this so that I could easily test this out.  The link itself may be useful to someone trying to figure it out.
  • This is implemented in SharePoint online and works nicely. http://paulgalvin.spaces.live.com/mmm2009-05-12_15.10/
  • The images I reference in the _layouts directory (/_layouts/images/KPI…) are available out of the box in my environment so they are probably available for you as well.

Here’s a screen shot of what it looks like for me:

Comparing Dates

Paul Galvin, MVPPaul Galvin, Microsoft MVP – SharePoint
Web site: Paul Galvin’s SharePoint Space

Paul is a Solutions Architect currently working most closely with Microsoft Office SharePoint Server 2007. He was recently awarded Microsoft MVP – SharePoint status for his work with the SharePoint community.

 

Please Join the Discussion

5 Responses to “Comparing Dates in a Data View Web Part to Build a WSS KPI in SharePoint”
  1. Nice, Paul, and thanks for the thanks. You’re welcome! ddwrt, even though it is not documented at all (except by Serge’s article), is powerful stuff.

    M.

  2. Oscar says:

    Nice one Paul! And you are reusing the SP images :)

    I wrote a post a while back that calculated dates use XSL, but in addition it used linked data sources to retrieve list items for displaying on the dashboard.

    http://blogs.sharepointace.com/post/Building-Dashboards-Using-SharePoint-Designer-2007-ndash3b-Techniques-using-Linked-Data-Sources.aspx

  3. kukdai says:

    hi paul i cant see the xsl codes there the image is not displaying
    is it removed or some problem with it i really need this can you help me please

Trackbacks

Check out what others are saying about this post...
  1. Comparing Dates in a Data View Web Part to Build a WSS KPI…

    Note: this was first posted at http://www.endusersharepoint.com here: http://www.endusersharepoint.com/2009…




Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!