Modify the lengths of List Form Fields

By Trần Mạnh Hùng

Hi again John here with a post that I hope will help out a lot of people.

The scenario is simple: You have just used SharePoint Designer to insert a Data View of some list. You have formatted the fields to be SharePoint:FormField controls and now you want to change the size of those controls.

Why is this so hard? Well the controls are rendered from the server (as are all asp.net controls) and so the markup (or HTML) used to make them display in the browser cannot be directly manipulated on the SPD design surface. What we need to do is determine the class SharePoint uses to render the size of the controls and then override it via CSS.

For this demo we ll use the default Title field from the default Announcements list. These steps also assume that you know how to create new pages and insert views on them.

  1. Make sure you have at least one announcement in your Announcements list
  2. Open your site in SharePoint Designer
  3. Create a new page based on the Master page
  4. Click on Task Panes > Data Source Library
  5. Click on the Announcements list and click Show Data
  6. Select the Title data value
  7. Click Insert Selected Fields as > Single Item Form
  8. Save the page and press F12 to Preview in Browser
  9. Right click the page in the browser and click View Source
  10. Select All > Copy
  11. Create another new page in SPD and Paste all of the content into this new page s Code View
  12. Click back into Design View
  13. Select the Textbox control for the Title field

At this point you can see that the class on this field is called ms-long. This is the class we need to override. So for other controls we just want to follow the same steps above. By getting the server generated HTML from the browser in step 10  we are able to use all of SPD s tools to understand the HTML. I do this a lot when dealing with the customization of server controls.

Now we just need to add a <style> block inside our first page that says something like below then save the page.:

<style>
.ms-long{width:100px;}
</style>

This will make the textboxes on this page 100 pixels wide.

If you want to know more about CSS in general check out the information on the Microsoft SPD Site. If you want to know more about Core.CSS in SharePoint check out this Style Guide.

Good luck!
-John Jansen
Test Lead SharePoint Designer

More...

Filter data in a Data View

By Trần Mạnh Hùng

With Microsoft Office SharePoint Designer 2007 you can easily filter data in a Data View.

When you create a Data View you can apply a filter so that the view includes only data that satisfies the criteria that you specify. For example you may have a data source that includes hundreds or thousands of records. However you want to view only a subset of those records. You can create a filter so that only that subset of records is included in your Data View.

When you use Office SharePoint Designer 2007 to filter data in a Data View you limit the records that are actually available in the Data View. You can also add a toolbar to the Data View so that visitors can filter data through the browser. However the toolbar affects only the presentation of data; the toolbar does not limit the amount of data that is actually available in the Data View. Find a link to more information about how to add such a toolbar in the See Also section.

To create the sample data and Data View that are used in the following examples or to learn more about Data Views see Create a Data View.

What do you want to do?


Add a filter

Northwind Traders a gourmet food distribution company keeps their product list in XML format. This product list also includes products that have been discontinued. Your team wants to create a Data View that displays only current products so that the Data View on your team site is not cluttered with products that Northwind Traders no longer carries. To do this you add a filter.

When you create a filter you build one or more expressions that are applied to the data. Data that satisfies these criteria is included in the Data View. There are three basic parts to an expression: the field name (the name of the field on which you want to filter) the operator (the rule that you want to apply to the value such as equal to greater than or less than) and the value (the data that you are looking for).

There are three steps to creating an expression. The first step is to identify the field that determines whether a record appears in the filtered results. In this example the Discontinued field is the basis for your filter because the data in this field must be checked for the records to be included in or excluded from the results. The second step is to specify the operator that you want to apply. You want only those records where Discontinued equals 0 so you specify Equals as the operator. The data in the field that you are looking for is 0 (Northwind Traders uses 0 to indicate false) so the value is 0.

  1. Open the page that contains the Data View that you want to filter.
  2. Right-click the Data View and then click Show Common Control Tasks on the shortcut menu.

     Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  3. In the Common Data View Tasks list click Filter.
  4. In the Filter Criteria dialog box click Click here to add a new clause.
  5. Click the Field Name box and then click the field that contains the values that you want. To follow the Northwind Traders example click Discontinued (Number).
  6. Click the Comparison box and then click the operator that you want. To follow the Northwind Traders example click Equals.
  7. Click the Value box and then type or select the criteria that you want. Because Northwind Traders uses 1 to indicate discontinued products and 0 to indicate products that have not been discontinued type 0.

    The sample expression in the Filter Criteria dialog box

    The sample expression in the Filter Criteria dialog box

  8. If you want to view the expression and edit it by using XPath click Advanced.

     Note   All filter criteria that you create in the Filter Criteria dialog box are actually XPath expressions. For more information on advanced filters see the later section Create an advanced filter.

  9. Click OK.

You now have a Data View that shows only current products and excludes discontinued products.

With Discontinued Equals 0 expression  four of five records appear

Top of Page Top of Page

Add a clause to an existing filter

You can use multiple expressions in a single filter. You do this by adding new clauses that specify additional criteria that the data must satisfy.

For example in the previous section you filtered the Data View so that it includes only current Northwind Traders products. Now suppose that you want your Data View to include only current products where the units on order equals zero so that you can decide whether to order additional units.

  1. Open the page that contains the Data View that you want to modify.
  2. Right-click the Data View and then click Show Common Control Tasks on the shortcut menu.

     Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  3. In the Common Data View Tasks list click Filter.

    In the Filter Criteria dialog box you can see any filter criteria that are currently applied to the Data View.

  4. At the end of an existing clause click the And/Or box and then do one of the following:
    • To create a filter where the data must match the criteria in both clauses click And.
    • To create a filter where the data must match the criteria in only one clause click Or.
    To follow the Northwind Traders example click And because you want your Data View to include only products that are not discontinued and where units on order equals zero.
  5. On the next row click Click here to add a new clause.
  6. Click the Field Name box and then click the field that contains the values that you want. To follow the Northwind Traders example click UnitsOnOrder (Number).
  7. Click the Comparison box and then click the operator that you want. To follow the Northwind Traders example click Equals.
  8. Click the Value box and then type or select the criteria that you want. Because you want to see only products that have no units on order type 0.

    A filter composed of two expressions as it appears in the Filter Criteria box

  9. Click OK.

You now have a Data View that shows only current products that have no additional units on order.

Of five sample data records  two now appear

Top of Page Top of Page

Group the clauses in a filter

When your filter contains multiple clauses you may want to group two or more clauses so that they are applied before another clause. By determining the order in which the clauses are applied you have precise control over what data appears in your Data View.

For example in the previous sections you created a filter with two clauses to see current products that have no additional units on order. This Data View helps you to decide whether to order additional units. To make this Data View even more useful you want to see current products where either units on order equals zero or units in stock is less than 20. To do this you add a third clause and group it with the second clause.

  1. Open the page that contains the Data View that you want to filter.
  2. Right-click the Data View and then click Show Common Control Tasks on the shortcut menu.

     Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  3. In the Common Data View Tasks list click Filter.

    In the Filter Criteria dialog box you can see any filter criteria that are currently applied to the Data View.

  4. At the end of the second clause click the And/Or box and then click Or.
  5. In the Filter Criteria dialog box click Click here to add a new clause.
  6. Click the Field Name box and then click the field that contains the values that you want. To follow the Northwind Traders example and add a third clause to your filter click UnitsInStock (Number).
  7. Click the Comparison box and then click the operator that you want. To follow the Northwind Traders example click Less Than.
  8. Click the Value box and then type or select the criteria that you want. To see when inventory is running low you want to see products where units in stock is less than 20 so type 20 in the box.
  9. Hold down SHIFT while you click the arrows in the leftmost column of the clauses that you want to group and then click Group. To remove grouping click Ungroup.

    To follow the Northwind Traders example hold down SHIFT while you click the arrows in the leftmost column of the second and third clauses.

    The sample expression in the Filter Criteria dialog box  including a bracket indicating the grouped clauses

    The bracket connecting the clauses indicates that those clauses are grouped.

  10. Click OK.

To help you track the products that need to be ordered you now have a Data View that shows only current products where either units on order equals zero or units in stock is less than 20. This Data View contains four records. Note that if you had not grouped the second and third clauses the Data View would contain two records because the clauses would have been evaluated consecutively. By grouping clauses you determine the order in which they are evaluated.

Of five sample data records  only four appear

Top of Page Top of Page

Modify a clause

You can view and modify any filter that was applied to a Data View by opening the Filter Criteria dialog box and making changes.

  1. Open the page that contains the Data View that you want to filter.
  2. Right-click the Data View and then click Show Common Control Tasks on the shortcut menu.

     Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  3. In the Common Data View Tasks list click Filter.

    In the Filter Criteria dialog box you can see any filter criteria that are currently applied to the Data View.

  4. In the Filter Criteria dialog box click an existing clause and then do any of the following:
    • Click the Field Name box and then click the field that contains the values that you want.
    • Click the Comparison box and then click the operator that you want.
    • Click the Value box and then type or select the criteria that you want.

Top of Page Top of Page

Delete a clause

  1. Open the page that contains the Data View with the filter that you want to modify.
  2. Right-click the Data View and then click Show Common Control Tasks on the shortcut menu.

     Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  3. In the Common Data View Tasks list click Filter.
  4. Click the the arrow in the leftmost column of the clause that you want to delete and then press DELETE.

Top of Page Top of Page

Delete a filter

  1. Open the page that contains the Data View with the filter that you want to delete.
  2. Right-click the Data View and then click Show Common Control Tasks on the shortcut menu.

     Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  3. In the Common Data View Tasks list click Filter.
  4. Hold down SHIFT while you click the the arrow in the leftmost column of each clause and then press DELETE.

     Note   If you are using an advanced filter the text Using advanced expression appears in the Filter Criteria dialog box. To delete an advanced filter click Advanced and then delete all text in the text box.

Top of Page Top of Page

Create an advanced filter

There may be times when you need to create a filter that uses more complex criteria than you can specify in the Filter Criteria dialog box. For example suppose that Northwind Traders wants to filter a Data View so that it displays only the products for which the value of the stock in inventory exceeds $1 000.00. In such a case you can create an XPath expression by using the options in the Advanced Condition dialog box.

In Office SharePoint Designer 2007 you can use XPath to create advanced filters. XPath is a language for locating and processing information in an XML document. The XPath Expression Builder provides IntelliSense for XPath making it possible for both novice and advanced users to create XPath expressions that perform complex filters on data.

 Note   XPath filtering is performed on the XML that is the basis for the Data View. If you perform an advanced XPath query on an SQL data source such as a Microsoft SharePoint list or library or an SQL database the data is first rendered as XML before the XPath filter is applied so the performance of the filter may be slower than expected.

  1. Open the page that contains the Data View that you want to filter.
  2. Right-click the Data View and then click Show Common Control Tasks on the shortcut menu.

     Tip   You can also click the Data View and then click the arrow Button image in the upper-right corner to show the Common Data View Tasks list. Click the arrow again to hide the list.

  3. In the Common Data View Tasks list click Filter.
  4. In the Filter Criteria box click Advanced.
  5. In the Advanced Condition dialog box under Select a field to insert double-click the field that you want to insert in the expression. To insert the full path of the field hold down CTRL while you double-click the field.

    In the example to create the Data View that displays only products with an inventory value greater than $1 000 double-click UnitsInStock. UnitsInStock appears in the Edit the XPath expression box.

  6. In the Edit the XPath expression box position the insertion point immediately after the field name and then press SPACEBAR.

    The IntelliSense list of XPath operators appears. IntelliSense for XPath helps you by providing a list of available fields or functions that are valid in the context of the expression.

  7. In the IntelliSense list of operators double-click the operator that you want.

    In the example double-click the asterisk (*). Now an IntelliSense list of available fields appears.

  8. In the list of available fields double-click the field that you want.

    To create the advanced filter in the Northwind Traders example double-click UnitsInStock and then press SPACEBAR.

  9. In the IntelliSense list of operators double-click the operator that you want.

    In the example double-click > (greater than) and then type 1000.

    The final expression looks like the following.

    the XPath expression to display products where the inventory value is greater than $1000

    At the bottom of the XPath Expression Builder you can see a preview of the results of the filter as you build the expression in the Edit the XPath expression box.

More...

Filtering and Formatting with Date Values

By Trần Mạnh Hùng

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.

More...

Database error

ERROR From DB mySQL

DB Error: Database query failed!
» Error No: 1194
» Error detail: Table 'bd_estore_online_users' is marked as crashed and should be repaired
» Query: SELECT * FROM bd_estore_online_users WHERE store_id = '21' AND uid='0' AND usertype='0' AND ip='54.80.102.170' Database error

ERROR From DB mySQL

DB Error: Database query failed!
» Error No: 1062
» Error detail: Duplicate entry '11700399' for key 'PRIMARY'
» Query: INSERT INTO bd_estore_online_users (id,store_id,sid,uid,username,usertype,ip,last_updated,last_page) VALUES (NULL,'21','kepe7ev00ttlo8tla5rrbbekp2','0','Guest','0','54.80.102.170','2018-08-18 20:46:23','/ac12645/sharepoint-services-3.html')