Creating a list with dynamically filtered date range

Okay, so some context. What I am trying to do is use two web parts, with the first providing a date that can be used to dynamically filter the rows shown below:

In my case, a report date is used to filter what projects are listed. The rules are:

(1) Start Date of project must be after the Report Date
(2) [End Date of project + 3 months] must be after the Report Date

Sounds easy right? Well, this is SharePoint, so no.

The first thing that needs to be done is setting up calculated columns that convert the source and filtering dates to serial form. After that, we need to customize the XSL view in SharePoint Designer to add a test that filters out rows that don't meet our criteria.

(1) First, make sure that the fields you want to compare against are listed in the Field List for the web part with Explicit set to TRUE. This hides columns from display but still allows them to be used for calculations:

<ViewFields>
  <FieldRef Name="x_Start_Date_S" Explicit="TRUE"/>
  <FieldRef Name="x_End_Reporting_Date_S" Explicit="TRUE"/>
    ...
</ViewFields>
<RowLimit Paged="FALSE">9999</RowLimit>

(NB: I've set RowLimit to 9999 and Paging off because this trick will hide rows without SharePoint knowing they are hidden. Therefore, if you had paging set to 5 items but 2 items were hidden, only 3 items would display on that page. This is not a trick to use when returning large lists!)

(2) Using the Web Part preview in Designer, highlight any whole
detail row. The easiest way to do this normally is to use the Skewer Click function under the Home tab and then click somewhere in the right vicinity.

(3) Click Design tab -> Customize XSLT -> Customize Item. This will expose the XSLT code for a detail row.

(4) Just above the
code, look for a tag that will be something like:

<xsl:template mode="Item" match="Row" ddwrt:ghost="show">

Change ddwrt:ghost="show" to ddwrt:ghost="" before doing anything else! When ddwrt:ghost="show" is set, this is SharePoint's way of saying "you can see the code but I'm not going to remember anything inside here when you close the file".

(5) Place an tag around the row to hide it when the date range tests aren't met:

  <xsl:if test="(@x_Start_Date_S &lt;= $ReportDate) and (@x_End_Reporting_Date_S &gt;= $ReportDate)">
    <tr>
      ...
    </tr>
  </xsl:if

(6) Save, close and view your handiwork!



Update: It appears that the DataViewWebPart isn't quite as finicky about accepting filter values. I've been able to connect a XsltListViewWebPart to a DataViewWebPart using built-in controls and the filter criteria listed above.