Sorting by year and monthname

Question

I am building a report using Report Builder 3.0. I am grouping by Month, but the Month field in my cube is in the format "2013 November". It looks good, but it sorts alphabetically instead of in calendar order.

During my searching I found this page http://timyocum.blogspot.co.nz/2012/02/sorting-by-date-not-alphabetically-by.html

It has the right idea, but it doesn't work for the format of my data.

Can anyone help me figure this out?

Thanks

David

Answer 1

Sort by an expression:

=CDate(Fields!Month.Value)

This works OK for data in your format, even if it's a string. The expression converts the string to a date which can be sorted correctly,

I have some sample string-based data:

select Month = '2013 October'
union all select Month = '2013 November'
union all select Month = '2013 December'

A simple table based on this:

Sort by [Month]:

Sort by =CDate(Fields!Month.Value):

As per the blog post you include, you could also easily move the date conversion to a calculated field and sort by that, which could be useful if you're using it in multiple places.