DVWP and Displaying Records Within a Specific Time Frame

By: colorado dreamer on 4:35 PM

Today I worked on a page which has a high bounce rate, frankly because it is not useful. It displayed more than 1000 records by paging through 25 records at a time.

The request I had was to display only 10 months worth of records - which would display a much more useful set of 47 records.

The page had a DVWP whose source was a SQL table. My adventure included tweaking the SQL select statement and initially adding an ORDER BY clause, but eventually opting not to use it and allow the DVWP to be sortable by the columns.

The biggest challenge I encountered was getting the syntax correct in the query while using the built-in filtering wizard of the dataview webpart.

I frequently ran into a roadblock with the generic SPD error message, "the server returned a non-specific error ..." whenever I tried to filter or sort. After much gnashing of teeth and clicking the undo button, I stopped using the wizard and went straight to the select statement.
.
Don't ask me why, but typing the ORDER BY clause into the statement works.

To get to the select statement, open your file in SharePoint Designer (SPD) and find the <Datasources> tag in the XSL code.

This is an edited version of the original SQL statement displaying all records - but it has all relevant parts.
<DataSources>
<SharePointWebControls:SPSqlDataSource runat="server" ... SelectCommand="SELECT TableName . OrderNumber , TableName . PublicationDate , TableName . ProjectNumber , TableName . ReportTitle , ... FROM TableName INNER JOIN TableName2 ON TableName . ProjectNumber = TableName2 . ProjectNumber " ID="Custom_x0020_Query2"></SharePointWebControls:SPSqlDataSource>
</DataSources>

To begin, I clicked the chevron to open the filter wizard.


The Filter Wizard is not going to give me the ability to say "display the records where the publication date is equal or less than 10 months from today".

But I picked a date from the Wizard so that I could look at the sql statement to get a clue about the syntax. That results in the following WHERE clause "WHERE [PublicationDate] &gt;= '1/23/2011' " and reminds me to escape the greater than symbol for CAML.

That gets me my 47 records - but the date is hard-coded. Not what I want.

After some failed attempts to get the SQL syntax right, I reached for an expert at Experts Exchange.

That quickly yielded the correct solution: WHERE [PublicationDate] &gt; DATEADD(MONTH,-10,getdate())" Which prompted me to read more about those SQL functions here:
http://www.electrictoolbox.com/date-add-sql-server-intervals-dates/

Now all that's left to do is see if the bounce rate improves on this page.