Wednesday, December 10, 2014

Grouping by Dates (months) in month order, not alphabetical, internal and external lists (business lists).

The Problem

Being able to "group" related data using the group by function in SharePoint is a useful way of displaying and organizing data for the end user.  Grouping by project, category, or other metadatavalues allows data organization that greatly enhances the end users ability to rapidly find data.

So, it stands to reason that we may wish to be able to group by dates, where you first group by year, and then by month to organize the data, as follows:

However, within SharePoint, if you generate your month field as follows (calculated field against a list):

Month: =TEXT(Modified,"mmmm")

Then SharePoints group by function will sort by alphabetical order rather than month order.  If you are grouping by month, April will always appear first in the list (or last, if you have reverse sort order).  This is usually not what the end user wants.

Internal List Solution

A solution for internal lists already exists, and is outlined nicely in this blog post:

External List Solution

I needed to expand this by applying it to an external list so that I could get the group by sort order indicated in the screen shot above.

The key to the solution is the inclusion of white space.  SharePoint sorts by whitespace, making elements with less white space appear after those with more.  Even better (in this case), it strips white space from the final page meaning the whitespace fix is invisible to the end user.

So, I needed to insert whitespace into my month field in order to have the group by function correctly sort by month.

As I am using an external content type, I am using sql to populate the data values.  I first created the month field in sql as follows:

LEFT(DATENAME(MONTH, Pay_Purchase.Date),3) as groupByMonth

This value is returned as an external content type field in my external content type, and can be used as a group by field.

As is, the sql above will result in months sorted alphabetically by SharePoint, we need to insert whitespace to get the correct sort order.  This results in the following sql:

SPACE(MONTH(Pay_Purchase.Date)) + LEFT(DATENAME(MONTH, Pay_Purchase.Date),3) as goupByMonth

Now, when I group by the month field it is in month order. 

I can modify the sql as follows to get reverse order (this is also possible within SharePoint Designer 'Sort and Group' options within the business list webpart):

SPACE(12 - MONTH(Pay_Purchase.Date)) + LEFT(DATENAME(MONTH, Pay_Purchase.Date),3) as goupByMonth

Whether you are attempting to group by a date field on an internal or external list, the whitespace fix works the same.  You can now group by month and have the correct sequence of values.

No comments:

Post a Comment