CODEWITHSUNDEEP
sqlreporting-servicesssrs-2008reportbuilder
Bartosz
Bartosz

Reputation: 4602

Sort by month value in ssrs

I have a pretty awkward setup in SSRS with custom grouping options (i.e. by office, seller, buyer etc.). In example, user can group items by year and then by month. The outcome of the report is then:

Gr1. | Gr2.
2015 | April
2015 | August
2015 | February
2015 | January 
and so on...

So both columns are alphabetically ordered, which works excellent for all custom grouping options but months - which should have their own logic for sorting. How could I implement that?

Upvotes: 0

Views: 8306

Answers (1)

Jonnus
Jonnus

Reputation: 3038

You should try to return all information form the database in its native type. Instead of returning the month names as ‘January’, ‘February’ etc, it would be better to return 20150101, 20150201 for example (or whatever the default date format is for your environment)

You can then alter the format of the type in the report. For example, set up the cell to return 

=MonthName(Month(Fields!myDate.Value),False)

To return the name of the Month for any date. SSRS will then know when ordering how to put the dates in the correct order.

ALTERNATIVE

Assuming you do not want to edit the way the data is returned, you can use a Select statement in the code behind your report to manually provide an order for the months.

Supposing the Dataset

Month      Val
---------- ---
January     1
February    2
March       3

When sorted by Month returns the following

enter image description here

Instead, insert the following code to your report (right click the report area, select Report Properties, then Code)

public function MonthNumber(MonthName AS String) AS Integer
    Dim MonthNum AS Integer = 0

    Select Case MonthName    
        Case "January"
            MonthNum = 1
        Case "February"
            MonthNum = 2
        Case "March"
            MonthNum = 3
    End Select

    return MonthNum
end function

Then set the Group Properties to be Sorted on

=Code.MonthNumber(Fields!Month.Value)

Gives the following result

enter image description here

This is because when ordering the set, instead of just looking at the name of the report, it passes the name through the Code, which is effectively telling the report which number to assign to each month. It is then ordered on this value instead of the month's name.

Hopefully this is useful to you. If you have any further question please let me know.

Upvotes: 1

Related Questions