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.

Read More

Improving the Hyperlink / Picture Column for Contributors

By: colorado dreamer on 5:50 PM

A recent article by Richard Harbridge, Powerful Columns You Probably Didn’t Know About, gave me an idea for improving my contributor's experience in linking to documents within lists in SharePoint MOSS 2007.

As it is right now, my contributor opens a new list item form, clicks attach and proceeds to fill out the form. When she gets gets to the form field, which is a "picture or hyperlink" column, it is a challenge to add the hyperlink to the attached document.
Since this is a new item, the path to the attachment is pointing to the local path of the document - so that won't work.  She could save the item, then edit the item at which point the path is correctly available by either clicking on the link to the attachment and pasting it from the browser address bar or right clicking and viewing the properties of the attachment.  None of that is very intuitive or seamless for the end user.

Here's the current columns in her list.  There are three columns that could have a need for the contributor to add a hyperlink to a document.

 

This is the "View Presentation" column 

  

Following Richard's advice, my solution was to create two new site columns.

The multiple lines of text columns will be replaced with a column type of "Full HTML Content with Formatting and Constraints for Publishing".  The Hyperlink or Picture column will be replaced with a column type of "Hyperlink with Formatting and Constraints for Publishing".

 

With these new site columns, when my contributor needs to type text and include a hyperlink, she can browse to the document.  

She has a much easier time adding a hyperlink to her document by browsing rather than trying to copy/paste the link pointing to a list attachment.

 

The browse dialog box allows the contributor to upload the document if it doesn't already exist.  It places the item here, /Presentations/Documents/Forms/AllItems.aspx, rather than in the list as an attachment.  In my environment, that is not a problem and will also make it easier to replace a document if that was needed.

I wish I had known about these publishing columns available to new site columns and used them rather than the standard list columns.  It is going to take me a while to retro-fit several of my lists with these site columns, but in the long run, it is an improvement for both my contributor and me.

Read More