I'm having a real problem trying to filter a sharepoint list by date. I want to produce a list of months/years where for each entry there is a count from the list, where the counts include dates that are greater than or equal to the 15th of that month, or less than the 15th of the following month. I can do this fine for any month except for December, where I want the count to go from 15 Dec 2009 to 14 Jan 2010 (for example). I really cannot see why there should be any difference as I am using the same method. If anyone can see a mistake in my code, or a better way of doing it, I would be extremely grateful.
<xsl:template name="generateTable">
<xsl:param name="numMonths" />
<xsl:param name="Rows" />
<xsl:param name="dvt_Rows" />
<xsl:param name="tday" select="$startDay" />
<xsl:param name="tmonth" select="($startMonth + msxsl:node-set($numMonths)-1) mod 12 + 1" />
<xsl:param name="tyear" select="$startYear + floor(($numMonths+msxsl:node-set($startMonth)-1) div 12)" />
<xsl:variable name="date" select="concat($tday,'/',$tmonth,'/',$tyear)"/>
<tr>
<td>
<xsl:value-of select="ddwrt:FormatDateTime(string($date),3081,'MMMM yyyy')"/>
</td>
<td>
<xsl:choose>
<xsl:when test="$tmonth=12">
<xsl:value-of select="count(/dsQueryResponse/Rows[1]/Row[(
(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'M yyyy' ) )=(ddwrt:FormatDateTime ( string ( $date ) , 3081, 'M yyyy' ) )
and number(substring(ddwrt:FormatDateTime(string(@Date), 3081, 'dd'),1,2)) >=$startDay)])+
count(/dsQueryResponse/Rows[1]/Row[(
concat(string(number(substring(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'M yyyy' ),1,2))+11),' ',
string(number(substring(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'MM yyyy'),4))-1))=string(ddwrt:FormatDateTime ( string ( $date ) , 3081, 'M yyyy' ) )
and number(substring(ddwrt:FormatDateTime(string(@Date), 3081, 'dd'),1,2)) <$startDay)])"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="count(/dsQueryResponse/Rows[1]/Row[(
(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'M yyyy' ) )=(ddwrt:FormatDateTime ( string ( $date ) , 3081, 'M yyyy' ) )
and number(substring(ddwrt:FormatDateTime(string(@Date), 3081, 'dd'),1,2)) >=$startDay)])+
count(/dsQueryResponse/Rows[1]/Row[(
concat(string(number(substring(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'MM yyyy' ),1,2))-1),
substring(ddwrt:FormatDateTime ( string ( @Date ) , 3081, 'MM yyyy'),3,6))=(ddwrt:FormatDateTime ( string ( $date ) , 3081, 'M yyyy' ) )
and number(substring(ddwrt:FormatDateTime(string(@Date), 3081, 'dd'),1,2)) <$startDay)])"/>
</xsl:otherwise>
</xsl:choose>
</td>
</tr>
<xsl:if test="msxsl:node-set($numMonths) > 0">
<xsl:call-template name="generateTable">
<xsl:with-param name="numMonths" select="msxsl:node-set($numMonths)-1" />
<xsl:with-param name="dvt_Rows"/>
<xsl:with-param name="Rows"/>
</xsl:call-template>
</xsl:if>
</xsl:template>
In the code, tday, tmonth and tyear are todays date in d/m/y, numMonths is a variable to hold the number of months required in the table, and startDay is 15.
As far as I can see, the adding of 11 months to the Date fields works, but extracting the year, changing it to a number and subtracting 1, is where the problem seems to be. But I can't get it to work, so I have completely run out of simple ideas.
The XML document is the Sharepoint list and I don't know how to share it as pure XML. The only reference I make to the XML is /dsQueryResponse/Rows[1]/Row which selects rows in the Sharepoint list, and uses the Date field (@Date) to perform the filter. Does this help at all?
I'm using Sharepoint 2007, and I started by using the Sharepoint Designer to obtain the Sharepoint list by inserting a dataFormWebPart. It's then possible to access the Sharepoint list in the XSLT by using xpath, /dsQueryResponse/Rows/Row which references all rows in the list. (I have added [1] in my code here to reference a specific list as I have two list data sources).
I would prefer to filter the list in Sharepoint first, and considered that first, but maybe I am doing something wrong, as I can't get that to produce exactly what I want - which includes zeros when there are no entries for a specific month, as well as the >=15 of the month filter. My method almost works which is why it is so frustrating, but I would welcome any alternatives especially if they are simpler and/or quicker. Thanks