Slinger's Thoughts

January 23, 2009

Why are dates so hard to work with in SharePoint?

Filed under: SharePoint — slingeronline @ 2:46 pm

It seems that Microsoft may have missed one of the biggest issues that afflicts any content management system.  While they do automatically calculate the date that an item was created (or uploaded, as the case may be), and they also let you know when the last time the file was edited, and who touched it, there are some other calculations that they don’t handle well, if at all.  One of the culprits is “Today.”  While there are workarounds that will let you add the “Today” value to a calculated column, there really isn’t much use to it since it won’t calculate on the fly, but only when an item is “touched.”  So, that pretty much makes it nothing more than a fancy “Modified” column.  And that’s pretty much useless.  Well, we have a help desk site at our company and my manager wanted me to create a report for the number of service tickets that we close per month, and per quarter.  Here’s where the fun really begins.

In our service requests system, when a resolution type is selected, a workflow finishes up and changes the status to “Closed” and then sets the resolution date to the current date.  Pretty Nifty.  Now I could go and add a calculated column for month, that calculates off of the resolution date column. (=IF(ISNULL([ResolutionDate]),0,MONTH([ResolutionDate]))  And that would give me the number of the month that the ticket was closed, unless it wasn’t closed, then the Month would return “0.”  Problem is, that will give me every ticket closed in whatever month of all years combined.  Not quite what I was looking for.  I could probably add a year to that too, but I’m trying to have this not be a pain in the ass. I found this site, and thought my prayers were answered. Especially since they have a section dealing with values like “Last Month,” “Last Quarter,” and “Last Year,” which are exactly what I need.  So I follow all of his instructions under the section labeled “Filter to show items within a date range.” I don’t follow all of them, because I don’t care where the control is, and I’m not really wanting to edit my master page. Let’s stick to function; how it looks I can worry about later.  So I do everything except that, including bouncing around the article for other steps that were involved.  And I test it.  Nothing.  It seems that whatever I use or wherever I place my List View, be it a List View Web Part or a Data Form Web Part, doesn’t pick up the “Start” and “End” variables, and so it displays nothing.  When I check the URL, I can see the appropriate variables there,  behind the page name. 

http://mysharepointsite/pages/results.aspx?start=2008-12-01&end=2008-12-31 as an example.  So I know that the control works.  But why isn’t my list view picking it up also.  I ran out of places to ask for help, so I’m asking you, my faithful readers, to help me out here.

I have narrowed it down to the webpart that renders the list.  I have tried manually changing the values in the URL to see if it was a formatting issue.  I have tried manually editing the filter values in the web part to see if it was a syntax error, and nothing works.  It doesn’t give me an error message, it just doesn’t do anything.  Kind of makes the whole thing useless. 

Advertisements

3 Comments »

  1. SPQuery can be used to search items meet your criteria.
    SPView my = web.GetViewFromUrl(“your list view url”);
    SPQuery myq = new SPQuery(my);
    myq.Query = “your query here”;
    SPList.RenderAsHtml(myq);
    The above code will render the exact view style defined in “your list view url”;

    Comment by Zhang Mingquan — February 6, 2009 @ 3:15 am

  2. FYI: I tried the method on msdn blog, only finished the first 3 steps and it already worked.

    Comment by Zhang Mingquan — February 6, 2009 @ 3:18 am

  3. I figured it out. Something that was left out was that you have to set the parameter. When creating the new parameter, set it as a query string parameter, and you have to set it to the value it is expecting. I did that and it worked perfectly. (One little mis-step breaks the whole thing.)

    Comment by slinger — February 6, 2009 @ 7:17 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: