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.
Thank you!!! This worked perfectly!
ReplyDeleteThank you!!! Worked first time!
ReplyDeletemerci beaucoup
ReplyDeleteI've spent the better part of the day trying to figure this out - this is the only thing that worked after researching all the forums. Thank you very much!
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThank you very much...
ReplyDeleteTHANK YOU!!!!
ReplyDeleteThank you so much sir....
ReplyDeleteworked perfectly, many thanks!
ReplyDeleteHelped me out as well, many thanks!
ReplyDeleteThank you....
ReplyDelete