Sharepoint XSLT Data View: Get all items that were created in the last 30 days

Question

I know you can create a basic view to do this, but what I really need is a query in the XSLT Data View that will show all the records where @Status = 'Open' and @Created < 30 days ago.

I intend on displaying a chart that shows the count of how long tasks have been sitting in the pipe without being worked on. So, what I did was created an XSLT Data View that filters the data source to only pull out items that are still Open. Then in the xsl, I just want to do something like:

<xsl:variable name="THIRTYdaysCount" select="count(/dsQueryResponse/Rows/Row[normalize-space(@Created) &lt; $THIRTYdays])" />

I don't think that will work because the data needs to be formatted and I cant get $THIRTYdays to work either. Anyone able to show me an example on how I should be doing this?

Answer 1

Is THIRTYdays a calculated column or something like that? Xsl is not intended for date calculations.

Have you tried using the DataFormWebPart? It still renders it's output using XSL, but allows the data to be filtered before being retrieved using CAML and filters can be bound to querystring variables, Control values, CAMl values etc., etc..

Googling for DataFormWebPart will result in tons of explanations / tutorials.

Answer 2

Use a caml query and the content editor web part

    <Query>
    <ViewFields><FieldRef name='Title'/></ViewFields>
    <Where>
       <Gt>
           <FieldRef Name='Modified' />
           <Value Type='DateTime'>2009-10-05<</Value>
       </Gt>
    </Where>
...

Answer 3

Maybe I am missing the point of what you are trying to do but this fragment a CAML query that pulls out anything with a create date in the last 30 days?

<WHERE>
<GE>
<FieldRef Name="Created"/>
<Value Type="DateTime"><Today OffsetDays="-30" /></Value>
</GE>
</WHERE>

You will have to add Status = Open to this.

See this article on creating CAML queries - and U2U's CAML builder tool is very good too.

Answer 4

I went with a really ugly date to string and compare method:

    <xsl:variable name="THIRTYdaysDate">
  <xsl:call-template name="SubMonth">
      <xsl:with-param name="StartDate" select="ddwrt:TodayIso()" />
      <xsl:with-param name="MonthsToAdd" select="1" />
  </xsl:call-template> 
</xsl:variable>
<xsl:variable name="SIXTYdaysDate">
  <xsl:call-template name="SubMonth">
      <xsl:with-param name="StartDate" select="ddwrt:TodayIso()" />
      <xsl:with-param name="MonthsToAdd" select="2" />
  </xsl:call-template> 
</xsl:variable>
<xsl:variable name="NINETYdaysDate">
  <xsl:call-template name="SubMonth">
      <xsl:with-param name="StartDate" select="ddwrt:TodayIso()" />
      <xsl:with-param name="MonthsToAdd" select="3" />
  </xsl:call-template> 
</xsl:variable>

<xsl:variable name="THIRTYdays" select="count(/dsQueryResponse/Rows/Row[number(concat(substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),0,5), 
    substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),5,2),
    substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),7,2))) &gt;= $THIRTYdaysDate])" />
<xsl:variable name="SIXTYdays" select="count(/dsQueryResponse/Rows/Row[number(concat(substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),0,5), 
    substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),5,2),
    substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),7,2))) &gt;= $SIXTYdaysDate])" />
<xsl:variable name="NINETYdays" select="count(/dsQueryResponse/Rows/Row[number(concat(substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),0,5), 
    substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),5,2),
    substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),7,2))) &gt;= $NINETYdaysDate])" />
<xsl:variable name="GREATERdays" select="count(/dsQueryResponse/Rows/Row[number(concat(substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),0,5), 
    substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),5,2),
    substring(ddwrt:FormatDateTime(string(@Created), 1033, 'yyyyMMdd'),7,2))) &lt; $NINETYdaysDate])" />
<xsl:variable name="AllTasks" select="count(/dsQueryResponse/Rows/Row)" />