SSRS 2008 month number not displayed in order

2
votes

I have a SSRS 2008 report that generated columns of the months along with other data based on year halves. I have the tablix column group and sort set for [Mon] and the first half of the year generated just fine but when I run the report for the second half it does not display in order :

MonthNumber 10       11        12        7     8       9
MonthName   October  Movember  December  July  August  September

The SQL code that is used generated the following rows which appear in order of month number. 

Mon
7
8
9
10
11
12
1
reporting-servicesssrs-2008
Looking at that, I wonder if Mon (which I presume is the month number) is coming through and/or being treated as text... You can see they are being sorted as strings, i.e. 10 will be before 7. Sorting by =CInt(Fields!Mon.Value), i.e. explicitly sorting as an integer, would likely solve the issue. Can you confirm the data type of Mon? – Ian Preston
Ian, your suggestion worked perfectly! – Michael Rowley
Thanks for the update - I figured that was 99% likely to be the issue, but difficult to confirm without replicating myself. Since it looks OK now, I'll add the suggestion as an answer! – Ian Preston

1 Answers

1
votes

I would say that Mon is being treated as a string value, for whatever reason, i.e. from the query or in the dataset definition, as you can see that in your example the columns are being sorted as strings, i.e. 10 will be before 7 when sorted as text and not numeric values.

You have two options:

First is to sort by an expression like: =CInt(Fields!Mon.Value), i.e. explicitly sorting as an integer, which solve the issue if Mon is being treated as text.

The other option is to make sure that Mon is being treated as an integer at the dataset level - either way should be fine.