Friday, February 24, 2012

Sorting by date (not alphabetically by month name) in SSRS 2008 R2

We've all created reports in SSRS 2008 R2 that have date values in them.  And by default, the sorting is alphabetically by month name, like this:


This is obviously not what I wanted when I created my report to sort by date.  So, with a couple changes, and a new calculated field, we can fix this. 

Go into the Report Builder Design view.  Right click on your dataset name in the Report Data pane, and choose:  Add Calculated Field

In the Dataset Properties window, select the Fields section.  Then click the Add - Calculated Field button.  Type in a name for our date sort field.  I usually use:  MonthSort for the name.  If you are sorting on a different date part, then name the new field accordingly.

In the Expression field, type in the following:


This tells SSRS to format our Month field as:  yyyyMM.  So, April 15, 2012, would be formatted to:  201204.  So, now that we created the field, we need to have our report use it.  Click Ok to close the Expression window, and Dataset Properties window.

My particular date field happens to be a Row Group named:  Month.  So, I need to update the sort on this grouping to sort by date value, and not alphabetically on the month name.  So, I will right-click on the month grouping in the Row Groups:
Click on Group Properties.  Then click on the Sorting section, and change the Column to Sort by to our newly created MonthSort field.

Click Ok to close this window.  And run your report.  You will now see the month values sorted by month value, and not alphabetically.




11 comments: