Filtering and Formatting with Date Values

Filtering and Formatting with Date Values

Rate This
  • Comments 62

andy Hi, I'm Andy Lewis, and I'm delighted to be joining the fine company of the various folks who have already posted to the SharePoint Designer blog. My current role is to write user assistance content for SharePoint Designer. In past jobs, I've designed and coded web applications, including both server and client code. I tend to get most interested in the user experience part of a software application, but of course the nuts and bolts of creating that experience are what make working in software challenging (and thus fun!). I'm here today to share ideas and some code that can help you work with dates in a Microsoft Office SharePoint Designer application.

Whatever data source you use in your application (SharePoint list, SharePoint library, database connection, etc.), you almost certainly will need to work with date values. For example, a SharePoint library has built-in fields to track when an item was created and when it was modified. And you might create your own date field in a list or table to designate when an item is due to a stakeholder, or when you estimate it will be done, or when it was completed.

Displaying a date value in a Data View is a fairly simple matter; you add it as you would any other column. If SharePoint Designer detects that the column holds date values, it automatically adds extra formatting (which you can customize) to properly display the date, and optionally the time. But filtering or conditionally formatting based on date values is a more complex capability to build.

In this article I'll show you how to build rich date-value capabilities with minimal effort. This article will:

Here are some code format conventions you will see in this article:

<p>This is code you need to add within this code block.</p>

<p>This is code you need to delete within this code block.</p>

<p>The ellipses that follow indicate code that continues after this point
but is truncated ...

Note: Some of the material that explains how to perform the more basic tasks is hidden from view by default if you have JavaScript turned on. If you want to print this article with all the material included, click here: Show All.

Conditionally format items between today's date and a calculated date field value

Suppose you have a large document library and want emphasize those items that have been recently modified (within the past 15 days) so that it is easier for your customers to notice and then optionally look at whatever changes were made to those documents. You decide to use conditional formatting rather than a filter because you want to show the documents in the way your customers typically want to see them, with all the documents shown grouped by project.

The final result could look something the following image, in which documents modified in the past 15 days show the Modified date highlighted in green:

date_fun_calc_field_data_view      

A calculated field is one mechanism you can use to determine a date range that is defined on one end by a today's date and on the other end by a date that is n days before or after today's date.

Format of calculated date field is different than standard date field

There is one wrinkle to using a calculated field for this purpose. The actual format of the data returned by a calculated date field is MM/DD/YYYY. The conditional formatting feature of SharePoint Designer expects to get the data in ISO format (YYYY-MM-DDTHH:MM:SSZ). For, example if the date value is 11/30/2007, then the format of the data returned from a SharePoint list would be:

  • From a Date and Time field (Date Only) in the Pacific time zone: 2007-11-30T08:00:00Z
  • From a Calculated date field (Date Only): 11/30/2007.

To deal with this inconsistency, we will tweak the XSL code generated by the SharePoint Designer conditional formatting feature, and leverage one of the XSL templates in the date template library shown below.

Steps

  1. Create the Modified_Recently calculated field with a formula of [Modified] + 15. How?

  2. Create a list view. For example:

    date_fun_calc_field_list_view
  3. Convert the list view to a Data View. How?

  4. Apply conditional formatting that displays the value in the Modified column with a background color of green (#00FF00) when Modified_Recently Greater Than Or Equal [Current Date]. How?

  5. Make the convertCalcDateValue template available to the XSL stylesheet in your Data View. See Make the date templates available to your Data View for information on how to do this.

  6. Find the XSL code that displays the Modified field. One way to do this is to switch to Split view and select one of the values in the Modified column.
  7. Create a variable that reformats the Modified Recently value from MM/DD/YYYY format to ISO format (YYYY-MM-DDTHH:MM:SSZ).
    <TD Class="{$IDAAOPHB}">
    
       <xsl:variable name="Modified_Recently_ISO">
          <xsl:call-template name="convertCalcDateValue">
          <xsl:with-param name="paramDate" select="@Modified_Recently"/>
          </xsl:call-template>
       </xsl:variable>
    
       <NOBR><span>
    
       <xsl:attribute name="style">
          <xsl:if test="
    number(translate(substring-before(@Modified_Recently$Modified_Recently_ISO,'T'), '-','')) &gt;= number(translate(substring-before($Today,'T'),'-',''))"> background-color: #00FF00; </xsl:if> </xsl:attribute> <xsl:value-of select="ddwrt:FormatDate(string(@Modified) ,1033 ,1)" /> </span></NOBR> </TD>

The calculated field method has several limitations:

  • This approach works only if your data source is a SharePoint list or library. If your data source is database connection or the Business Data Catalog, then you need another solution (see the following sections).
  • You need a field for each date value that needs to be part of a date range. So for example, if you wanted to show different colors depending on how recently a document was modified, you would need a different calculated field for each date range.
  • You cannot specify date ranges such as "last quarter" or "next month".

Despite these limitations, the example above illustrates how the convertCalcDateValue template gives you a way to take the value from a calculated date field and work with it in a Data View to do conditional formatting. You can also use this template to do filtering or whatever else you need to do with value from a calculated date field.

Next, I'll describe how to do conditional formatting using a pure XSL solution with no calculated field needed.

Conditionally format items based on date range

Suppose you want to give your customers better visibility of documents that require attention because they are not yet complete and they are either due soon (for example, within 15 days) or overdue. You want to offer a Data View that looks something like this:

date_fun_date_due_cf_dv

Note: In the above example, assume that today's date is 1/3/2008.

Although our example for this section is a SharePoint document library, the techniques in this section will work for any kind of data source (database connection, Business Data Catalog, and so on). Here is how you build this kind of view:

  1. Create a list view with the fields and grouping as shown in the above screenshot.

  2. Convert the list view to a Data View. How?

  3. Make the date templates available to your Data View. For information on how to do this, see Make the date templates available to your Data View.

  4. Make sure the ParameterBinding named Today is included in the ParameterBindings tag of the Data View. How?

  5. Make sure the Parameter named Today is included in the XSL style sheet of the Data View. How?

  6. Find the XSL code that displays the Date_Due field. One way to do this is to switch to Split view and select one of the values in the Date_Due column.
  7. Use the dueDateDelta template to add conditional formatting to the the Date_Due field by adding the highlighted code:

    <!--Date_Due-->
    <TD Class="{$IDABW30D}">
    
    <!-- if this document is not complete -->
    <xsl:if test="@Status != 'Complete'">
       <!-- get the delta in days between the due date and today --> 
       <xsl:variable name="dueDateDelta">
          <xsl:call-template name="getDayDelta"> 
             <xsl:with-param name="paramDateA" select="@Date_Due"/>
             <xsl:with-param name="paramDateB" select="$Today"/>
          </xsl:call-template>
       </xsl:variable>
    
       <xsl:choose>
          <!-- if due date is before today, then document is
          overdue, so highlight in red -->
          <xsl:when test="$dueDateDelta &lt; 0">
             <xsl:attribute name="style">background-color:#FF0000;</xsl:attribute>
          </xsl:when>
          <!-- if due date within 15 days after today, then document is
          due soon, so highlight in yellow-->
          <xsl:when test="$dueDateDelta &lt; 15">
             <xsl:attribute name="style">background-color:#FFFF00;</xsl:attribute>
          </xsl:when>
       </xsl:choose>
    </xsl:if>
    
    <NOBR>
       <xsl:value-of disable-output-escaping="no" 
    select="ddwrt:FormatDate(string(@Date_Due),number($Language),1)" /> </NOBR> </TD>

Notice that we used a slightly different visual effect in this example than in the prior example.  We light up the entire table cell, which may be more noticeable than in the previous example, where we used a span to restrict the conditional formatting to highlight just the text inside the table cell. Suppose you wanted to make the effect even more noticeable by highlighting the entire row:

date_fun_date_due_cf_dv2

To do this, you simply need to move the xsl:if block just below the tr tag (How?):

 

After you create this Data View, there are a lot of ways you could further enhance it.  For example, for overdue items you could offer a link that sends mail to the owner of the document requesting more information.

Filter to show items within a date range

Now for our trickiest and most interesting scenario: filtering a Data View based on a date range.  Although you can directly filter a Data View in a number of ways, when it comes to filtering a Data View based on a date, the only option available out of the box is to filter items based on a date value relative to today.  So you can quickly create a Data View that answers questions like these:

  • Is date_due before today?
  • Is date_due equal to or after today?

But typically web applications need to answer date-oriented questions such as these:

  • What work is due in the next 60 days?
  • What work is due between date A and date B?
  • What work did we complete last month?
  • What work did we complete last quarter?

In order to answer these kinds of business questions, you need to do some extra work to filter your Data View.

Create a Data View that shows documents due in various date ranges

For this example, we will create a Data View that can show items that are due within a few different ranges:

date_fun_date_range

The following steps describe how to create this kind of Data View.

  1. Create a list view with the fields and grouping as shown in the above screenshot.

  2. Convert the list view to a Data View. How?

  3. Apply a filter to the Data View so that it shows only items in which Date_Due is greater than or equal to [start] and less than or equal to [end], where [start] and [end] are Query String parameters. How?

  4. Add some room above the Projects Data View by inserting a few paragraphs above it.
    <asp:Content ContentPlaceHolderId="PlaceHolderMain" runat="server">
    
    <p></p>
    <p></p>
    <p></p>
    
    <WebPartPages:DataFormWebPart ...
  5. Create a blank Data View by dragging it above the Projects Data View. For detailed steps, see Create a blank Data View.
  6. Make the supplemental date templates available to your blank Data View. For detailed steps, see Make the supplemental date templates available to your Data View.
  7. Replace the contents of the <xsl:template> tag inside the blank Data View with the following code.
    <xsl:template match="/">
       <p style="margin:.5em 0 .5em .5em">Choose a date range:</p>
    
       <ul style="margin:0 0 1em 3em">
          <li>
             <a>
                <xsl:attribute name="href">
                <xsl:text>Due_Dates.aspx?</xsl:text>
    
                <xsl:text>start=</xsl:text>
                <xsl:call-template name="getDateRange">
                   <xsl:with-param name="paramBaseDate" select="$Today"/>
                   <xsl:with-param name="paramRangePeriod">LAST</xsl:with-param>
                   <xsl:with-param name="paramRangeType">MONTH</xsl:with-param>
                   <xsl:with-param name="paramRangeBoundary">START</xsl:with-param>
                </xsl:call-template>
    
                <xsl:text>&amp;</xsl:text>
    
                <xsl:text>end=</xsl:text>
                <xsl:call-template name="getDateRange">
                   <xsl:with-param name="paramBaseDate" select="$Today"/>
                   <xsl:with-param name="paramRangePeriod">LAST</xsl:with-param>
                   <xsl:with-param name="paramRangeType">MONTH</xsl:with-param>
                   <xsl:with-param name="paramRangeBoundary">END</xsl:with-param>
                </xsl:call-template>
                </xsl:attribute>
    
                <xsl:text>Last month</xsl:text>
             </a> 
          </li>
    
          <li>
             <a>
                <xsl:attribute name="href">
                <xsl:text>Due_Dates.aspx?</xsl:text>
    
                <xsl:text>start=</xsl:text>
                <xsl:value-of select="substring-before($Today,'T')"/>
    
                <xsl:text>&amp;</xsl:text>
    
                <xsl:text>end=</xsl:text>
                <xsl:call-template name="getDateFromDays">
                   <xsl:with-param name="paramBaseDate" select="$Today "></xsl:with-param>
                   <xsl:with-param name="paramDelta">30</xsl:with-param>
                </xsl:call-template>
                </xsl:attribute>
    
                <xsl:text>Next 30 days</xsl:text>
             </a> 
          </li>
    
          <li>
             <a>
                <xsl:attribute name="href">
                <xsl:text>Due_Dates.aspx?</xsl:text>
    
                <xsl:text>start=</xsl:text>
                <xsl:value-of select="substring-before($Today,'T')"/>
    
                <xsl:text>&amp;</xsl:text>
    
                <xsl:text>end=</xsl:text>
                <xsl:call-template name="getDateFromDays">
                   <xsl:with-param name="paramBaseDate" select="$Today "></xsl:with-param>
                   <xsl:with-param name="paramDelta">60</xsl:with-param>
                </xsl:call-template>
                </xsl:attribute>
    
                <xsl:text>Next 60 days</xsl:text>
             </a> 
          </li>
       </ul>
    
    </xsl:template>

Move filter links to left nav area

It is not a great use of page real estate to have the links appear over the Data View.  A better UI design would be to have the links appear in the left navigation area, highlighted in red below:

date_fun_date_range_left_nav_links

The following steps describe how to put these links in the left navigation bar.

  1. Create a new master page by copying and modifying default.master.  Add a new content placeholder called PlaceHolderDataViewParameters.

    ...
    
    <td valign="top" width="100%">
       <asp:ContentPlaceHolder id="PlaceHolderLeftNavBarDataSource" runat="server" />
       <asp:ContentPlaceHolder id="PlaceHolderCalendarNavigator" runat="server" />
       <asp:ContentPlaceHolder id="PlaceHolderLeftNavBarTop" runat="server"/>
       <asp:ContentPlaceHolder id="PlaceHolderDataViewParameters" runat="server" />
       <asp:ContentPlaceHolder id="PlaceHolderLeftNavBar" runat="server">
    
    ...

    (For a detailed explanation of how to copy and modify the default master page, see Modify the default master page.)

  2. Edit the content page you created in the previous section in Code view and add the PlaceHolderDataViewParameters content placeholder to the content page.

    <asp:Content ContentPlaceHolderId="PlaceHolderDataViewParameters" runat="server">
    <div class="ms-quicklaunchouter">
    <div class="ms-quickLaunch" style="width:100%;">
    
    <WebPartPages:DataFormWebPart ... />
    
    ...
    
    </WebPartPages:DataFormWebPart>
    </div>
    </div>
    </asp:Content>
    

    Note that to make the presentation nicer and consistent with the default SharePoint site look and feel, we use a few nested div tags.

  3. Move the Data View you created into the new content placeholder.

Final comments on date range filtering

One advantage of the approach described above is that it filters the data at the database layer of the application.  You could use the Add XSLT Filtering checkbox in the Filter Criteria dialog box to accomplish a similar result, but you would have several potential issues if you use that model.  For large sets of data, performance would suffer and the load on the database server would be higher.  You would also need to create multiple pages, one for each filter you want to use, rather than being able to leverage a single page for all of the filters you need to offer your users.

Hack the CAML query to filter for items within a date range relative to today

I know this sounds violent, but I promise this procedure does not involve harming animals.  As discussed above, you can use the Filter Criteria dialog box to restrict a Data View to showing only list items with date values before, on, or after today. But it does not let you filter to show list items with a due date that is either due soon (say within 15 days from today) or overdue (in the past).  There is a fairly simple workaround for this limitation.

  1. Create a Data View and apply the following filter in the Field Criteria dialog box: Date_Due Less Than Or Equal [Current Date].

    date_fun_date_range_caml_tweak_filter

  2. In Code view, find the SharePoint:SPDataSource tag.  Within that tag, find the selectcommand attribute. It should look something like this:

    <SharePoint:SPDataSource
       ...
       selectcommand="&lt;View&gt;&lt;Query&gt;&lt;OrderBy&gt;&lt;FieldRef Name=&quot;Date_Due&quot;
       Ascending=&quot;TRUE&quot;/&gt;&lt;/OrderBy&gt;&lt;Where&gt;&lt;Leq&gt;&lt;FieldRef Name=&quot;
       Date_Due&quot;/&gt;&lt;Value Type=&quot;
       DateTime&quot;&gt;&lt;Today/&gt;&lt;/Value&gt;&lt;/Leq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;"
       ...
    >
  3. Let's make a few harmless text replacements to the selectcommand attribute to make it more readable:
    <SharePoint:SPDataSource
       ...
       selectcommand="<View><Query>
       <OrderBy><FieldRef Name='Date_Due' Ascending='TRUE'/></OrderBy>
       <Where><Leq><FieldRef Name='Date_Due'/><Value Type='DateTime'><Today/></Value>
       </Leq></Where></Query></View>" 
       ...
    >
  4. Add the OffsetDays attribute to the Today element and set its value to a negative integer to yield a date n days before today or a positive integer to yield a value n days after today.  For this example, we want to show items due before 15 days after today.
    <SharePoint:SPDataSource
       ...
       selectcommand="<View><Query>
       <OrderBy><FieldRef Name='Date_Due' Ascending='TRUE'/></OrderBy>
       <Where><Leq><FieldRef Name='Date_Due'/><Value Type='DateTime'><Today OffsetDays='15'/></Value>
       </Leq></Where></Query></View>" 
       ...
    >

For more information on CAML syntax, see Collaborative Application Markup Language (CAML).

Filter a SQL Server Database Connection relative to today

I recently created a Data View of a SQL Server database connection and tried to filter the records relative to today's date.  For example, to show all records where Date_Due is equal or greater than today's date. There is an issue with the Filter Criteria dialog box so that when you try to set this up, the Data View fails, showing this message in Design view: The server returned a non-specific error when trying to get data from the data source. Check the format and content of your query and try again. If the problem persists, contact the server administrator.

I found a workaround by looking up the literal that SQL Server uses to represent today's date. So after you create a Data View and apply the filter using the Filter Criteria dialog box, go into Code view and in the SelectCommand attribute, replace @Today with CURRENT_TIMESTAMP.  For example:

<asp:SqlDataSource
   ...
   SelectCommand="SELECT * FROM [vw_ProjectDocs] WHERE [Date_Due] &gt;= @Today CURRENT_TIMESTAMP"
>

Create a blank Data View

Sometimes it's handy to be able to dynamically render HTML at runtime.  For example, you might need to create a hyperlink that includes today's date as a query string argument.  You can use a Data View to obtain data such as today's date or arguments from the page's query string.  You can use its XSL parser to then render HTML using XSL's various capabilities (string manipulation, math functions, conditional logic, etc.)

Following are the steps to create a blank Data View.

  1. Copy the the following text into a blank text file and save it as blankdv.xml.
    <?xml version="1.0" encoding="utf-8" ?>
    <item></item>
  2. Edit the .aspx page where you need the dynamic HTML generation and switch to Design view.
  3. From the Data Source Library task pane, drag blankdv.xml onto the place in the page where you need the dynamically-rendered HTML.

Because we don't plan to show any data, we want to make the code of the Data View as small and simple as possible to make adding our custom XSL as simple as possible. One way to do this follows.

  1. Click the Data View, and then click the arrow ZA101003741033 in the upper-right corner to show the Common Data View Tasks list.
  2. On the Common Data View Tasks list, choose Change Layout. Then, in the Data View Properties dialog box, choose the plain list layout.

    date_fun_plain_layout_choice
  3. Click OK.
  4. In Code view, make the following modifications to the XSL block in the Data View.
    <XSL>
    <xsl:stylesheet version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" 
       xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" 
       xmlns:asp="http://schemas.microsoft.com/ASPNET/20" 
       xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" 
       xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
       xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
       xmlns:SharePoint="Microsoft.SharePoint.WebControls" 
       xmlns:ddwrt2="urn:frontpage:internal">
    <xsl:output method="html" indent="no"/>
    <xsl:decimal-format NaN=""/>
    <xsl:param name="FileName" />
    <xsl:param name="dvt_apos">'</xsl:param>
    <xsl:param name="Today"/>
    
    <xsl:variable name="dvt_1_automode">0</xsl:variable>
    <xsl:template match="/">
       <xsl:call-template name="dvt_1"/>
       Hello World!<br/> 
       Today is <xsl:value-of select="$Today"/>. <br/>
       Put your content here.
    
    </xsl:template>
    
    <xsl:template name="dvt_1">
    <xsl:variable name="dvt_StyleName">NumTitl</xsl:variable>
    <xsl:variable name="Rows" select="/item" />
    <ol>
    <xsl:call-template name="dvt_1.body">
    <xsl:with-param name="Rows" select="$Rows" />
    </xsl:call-template>
    </ol>
    </xsl:template>
    <xsl:template name="dvt_1.body">
    <xsl:param name="Rows" />
    <xsl:for-each select="$Rows">
    <xsl:call-template name="dvt_1.rowview" />
    </xsl:for-each>
    </xsl:template>
    <xsl:template name="dvt_1.rowview">
    <li class="ms-vb">
    <xsl:value-of select="text()" />
    <xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
    <br /><span ddwrt:amkeyfield="" ddwrt:amkeyvalue="string($XPath)" ddwrt:ammode="view" />
    </xsl:if>
    </li>
    </xsl:template>
    
    </xsl:stylesheet>
    </XSL>
    </WebPartPages:DataFormWebPart>
  5. Replace the content of the <xsl:template> tag with your content.

SharePoint Designer Supplemental Date Templates

This section provides the date templates you need to build date filtering and date-driven conditional formatting features into your Data Views. You might reasonably ask whether it makes sense to use XSL this way; why not just use ASP.NET code, where a rich API provides a wealth of calendar-oriented functionality.  And if you can put code on a server, then that option could make sense.

But for those of us who don't have our own server, or who don't have the permissions to put code on a server, then the date templates in this section provide a way for you to expand the kind of applications you can build with nothing more than the Full Control permission level in a SharePoint site plus SharePoint Designer.  Furthermore, it is generally expensive to create and maintain custom ASP.NET code, and there is additional security risk as well.  If you can stretch a Data View to do what you need, then you can save yourself a lot of trouble.

Capabilities provided by these templates

By copying these templates into your SharePoint site, you get the benefit of these capabilities:

  • Convert a calculated field value into ISO format (convertCalcDateValue)

  • Get number of days between two dates (getDayDelta)

  • Get start and end dates for various date ranges relative to today such as last month, next month (getDateRange)

  • Get date that is n days before or after a date (getDateFromDays)

  • Various utility templates

Note: getDateFromDays is not leap-year accurate.  So while I have not yet solved this problem, if you can think of a good solution, I encourage you to post it as a comment to this article.

Make the date templates available to your Data View

There are two ways to make the date templates available to your Data View:

  • Copy the templates into a separate .xsl template file and add an xsl:import tag to your Data View.
  • Copy the individual templates you need into the XSL block of the Data View.

Copy the date templates into the Data View

If you want to quickly and simply pull the templates into a Data View, perhaps to try them out, you can simply copy them into your Data View directly.

  1. Select the templates you want to use (including any templates that they call) in the Template Code section of this article and copy them to the clipboard.
  2. In SharePoint Designer, edit the page that contains your Data View in Code view, put the cursor before the first  xsl:template start-tag in the Data View, and then paste the templates into the Data View.

Create and use date templates by importing from a separate file

This method is efficient and has the advantage of letting you use the same set of templates from multiple Data Views in multiple pages in your site.  If you need to enhance or add to the templates for your own needs, having them in a single file means you have to modify them in only one place.

Important: There seems to be a problem with using this method if you created your Data View by converting a list view to the Data View.  The page renders OK in the web browser, but fails to render visually in Design or Split view in SharePoint Designer.  So one way to work around this issue is to copy the templates into your Data View until you are done designing it, and then as a last step, switch to using the import method described below.

First create the file:

  1. In SharePoint Designer, click File, and then click New.
  2. Click General, and then click Text File.
  3. Switch to this browser window, select the templates in the Template Code section of this article and copy them to the clipboard.
  4. Switch to SharePoint Designer and paste the templates into the new text file.
  5. Click File, and then click Save.
  6. Save the file to the directory where you want it to be stored, with the name you want to give it (for example http://mysite/shared_templates/date_templates.xsl.

Next import the date templates into your Data View so that you can use them in your XSL code:

  1. Edit the page that contains your Data View in Code view and find the xsl:stylesheet start-tag.
  2. Insert a new xsl:import tag immediately after the xsl:stylesheet start-tag. For example, if the page that contains your Data View is http://mysite/reports/due_dates.aspx, and the date templates are stored in http://mysite/shared_templates/date_templates.xsl, then you would modify your Data View as follows:
    <xsl:stylesheet ...
       <xsl:import href="../shared_templates/date_templates.xsl"/>
       <xsl:output method="html" indent="no"/>
       ...

Template Code

Attached to the bottom of this article is the XSL code for the date templates. I recommend you download it rather than view it in your web browser.  In Internet Explorer, you can download it by right-clicking the date_templates.xsl link and choosing Save Target As.

Conclusion

I hope you find the material in this article useful.  I want to give special thanks to John Jansen, Rob Howard, Eray Chou, and  Les Smith for their contributions, suggestions, and tips for this article.  I look forward to hearing your feedback and questions, and especially to seeing your solution to make the getDateFromDays  template leap-year accurate!

Attachment: date_templates.xsl
  • Hi Again,

    I managed to get the functionality to work in a standard DFWP.  However, once I apply the "EDIT" functionality it errors with the following message:

    Failed Setting processor stylesheet : 0x80004005 : Required attribute '{http://www.w3.org/1999/xsl/Transform}version' is missing.  Error occurred during compilation of included or imported stylesheet "../Shared Documents/VFlow/Date_templates.xsl'.

    Has anyone else come across this?  And, is there any solution?

    Thanks

  • Sorry for the delay in responding.  I will respond in the order the comments came in.

    Alex: I'm not sure if the bug you describe is present in WSS 3.  As I said earlier, my templates don't deal with hour values, so you will need to create your own templates to enable hour-granular filtering.  If you give it a try, please let us know how it goes.

    PatrickC: Hmmm, sounds like a good challenge.  Sorry I don't have time to think it through more, but if you create a template, please consider sharing it here.

    LISpeedyG: Is the DFWP you are working with a converted list view?  If so, then importing may not work at design time (but should work OK at runtime in the browser).  You might need to copy the templates directly into the DFWP in order to work on it using SPD.

    Andy Burns: So it sounds like you are saying it works OK on one PC and not on another?  I don't have any direct knowledge of such an issue.  Can you determine what might be different about the two PCs?  Are they both editing the exact same page?

    Tiziano: THANK YOU for finding that bug; nice catch! Wow, I really should have run more negative paramDelta value tests.  I've posted an update to date_templates.xsl (a one-line change) that, according to my tests, fixes the bug.  Please confirm if you think this version is good in your situation.

    Thanks all for your comments, questions, and debugging!

    Andy

  • Cool

    Thanks Andy, that fix it right away :)

    Cheers

  • Hello Andy,

    I am trying to do parameterezed filtering with the DFWP.  I have configured my parameter to get it's value from a text box control (originally I tried the Sharepoint Calendar Control).

    It doesn't work.  It seems sharepoint has a problem getting a date value from the text box.  Do you know if I am doing something wrong, or does this technique just not work for date values?

    Thanks alot

    Martin

    PS I have managed to get something working via connected webparts, I can only configure the connection completely via the browser (ie specify which parameter receives the filtered date value).  I can't specify which parameter receives the filtered date value in SPD.

  • Hi Martin,

    My experience to filtering a DFWP for date values is limited to using a query string parameter.  Perhaps you could use a GET form so that the argument is passed via the query string.

    Andy

  • I was trying your post, when I made the date templates availables to my Data View got the following error:

    "This web element doesn't have a valid stylesheet XSLT

     ERROR: XML declaration not valid"

    I'm working with MOSS 2007 in spanish

    Thanks a lot

  • Martin - After looking into your question about using a Calendar control for filtering, there seems to be two issues going on.

    1) As noted in the comments of http://blogs.msdn.com/sharepointdesigner/archive/2007/03/05/asp-net-controls-filter-the-data-view.aspx, there are situations where the Text property is not the one we want to get the value from. For the calendar control you need to make sure to use the SelectedDate property.

    <ParameterBinding Name="Param1" Location="Control(Calendar1,SelectedDate)" DefaultValue="4/10/2008 12:00 AM"/>

    2) The date that is output by the control does not match the format expected by the SharePoint List datasource. Because of that, source level filtering won't work. One way to work around this would be to create a custom control that wrapped the calendar control, but made sure to format the SelectedDate as UTC. The other option is to use XSLT filtering combined with the ddwrt:FormatDateTime runtime function:

    ddwrt:FormatDateTime(string(@Modified), 1033, 's') = ddwrt:FormatDateTime(string($Calendar), 1033, 's')

  • Mario: I suggest you try making the templates available inline rather than importing them.  Edit the page that contains your Data View in Code view, put the cursor before the first  xsl:template start-tag in the Data View, and then paste the templates into the Data View.

    Andy

  • Bravo!

    Thanks for helping us learn these cool tricks... perfect for MOSS users who don't have Enterprise, but need some KPI type stuff.

    Cheers,

    John

  • Hi I would like to implement this however i get an eror

    xsl:import may not be used here

    when i try and import your stylesheet as described

    Cheers

    craig

  • Hi, craig.  Please make sure that you inserted the xsl:import tag immediately after the xsl:stylesheet start-tag, as described above.

  • Hope this helps someone.....

    There's an easy way compare dates in MOSS:

    Create Calculated field based on Date as so:

    Name: Date Expires

    Formula (Add a year if not empty): =IF([Date Trained]="","",TEXT([Date Trained]+365,"yyyy-MM-ddTHH:mm:ssZ"))

    Set 'Data Type' to Date and Time.

    In SharePoint Designer use the normal inbuilt CF Builder, here's my code to display colour if training has not expired:

    <xsl:if test="number(translate(substring-before(@Date_x0020_Expires,'T'),'-','')) &lt; number(translate(substring-before($Today,'T'),'-',''))">color: #FFFFFF; background-color: #FF0000;</xsl:if>

    Display Column (in UK time)

    <xsl:value-of select="ddwrt:FormatDateTime(string(@Date_x0020_Expires), 2057,'dd/MM/yyyy')" />

    Cheers.

    Liam.

  • Hi All,

    We are now facing a problem with the calculated datetime field in WSS. The regional setup is GMT+8. The time displayed in the WSS list form webpart is correct. But when it comes to the Dataview webpart, the calculated date time is 8 hours faster than it should be.

    Pls kindly advise. Thanks.

    Ben

  • Hi Ben,

    Unfortunately, you have run into a known issue with regard to Calculated date fields. We have the issue logged in our database for consideration in the next Service Pack, and we are tracking it for the next version of Office as well.

    Andy

  • Hi,

    I'm trying to filter a DataView in SharePoint Designer by a date range. But I want the user can enter the dates (From  - To), and display the items that where Created after "From date" and before "To date".

    Is it possible? I'm new with SharePoint Designer and I don't know how to accomplish it.

    Thanks,

Page 2 of 5 (62 items) 12345
Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post