Months represented in wrong order in SSRS Report

0
votes

I'm running a SSRS report at present and one of the column headers is called [MonthName] - the report is currently producing the report with the months in alphabetical order so April, August, December etc

How I can get the report to be in order of January, February, March etc

I've tried ORDER BY MonthName but that doesn't seem to give me any joy, could someone give me a steer in the right direction please

Many Thanks Dan

4
visual-studioreporting-services
can you post your query or sample data. – Chanom First
Here is the Query: SELECT dt, WeekCommencing, Week, WeekofMonth, DayofWeek, MonthName, year, Site, Team, SubTeam, Client, Scheme,Calls_Offered, Calls_Answered FROM [LB ODS].WorkCalendarV2Emails WHERE (Site IN (@Site)) AND (Data_Label IN (@DataLabel)) and (dt >= @startdate) AND (dt <= @enddate) – Dan Tracey
I Suggest you to ORDER BY in query then delete sort on SSRS I dont know you data but I Read from your column name and I think you should ORDER BY DATEPART(MM,dt) - Assume dt is DateTime Value – Chanom First

4 Answers

0
votes

Use this expression on the sorting property. I'm assuming that your data is in Full monthname.

=SWITCH(Fields!MonthName.Value="January",1,
Fields!MonthName.Value="February",2,
Fields!MonthName.Value="March",3,
Fields!MonthName.Value="April",4,
Fields!MonthName.Value="May",5,
Fields!MonthName.Value="June",6,
Fields!MonthName.Value="July",7,
Fields!MonthName.Value="August",8,
Fields!MonthName.Value="September",9,
Fields!MonthName.Value="October",10,
Fields!MonthName.Value="November",11,
Fields!MonthName.Value="December",12)

Edit: Added another solution.

Another way is to add a Monthnumber field to your dataset. Use case statement in SQL or in Dataset properties > Field > Add > put the expression above. Name this field as Monthnumber then sort your Tablix by Monthnumber.

0
votes

Add a numerical representation of the month to the dataset either in the underlying query, or as a calculated column. This avoids lengthy case/switch statements which can make your code hard to read.

SQL

select datepart(month, (@month_name + ' 01 1900')) 'month_number'

Calculated Column Expression

=Month(CDate(Fields!month_name.Value.ToString + " 01 1900"))
0
votes

If the [dt] field is a DATETIME or DATE field, just ORDER BY that:

SELECT dt
    , WeekCommencing
    , [Week]
    , WeekofMonth
    , [DayofWeek]
    , [MonthName]
    , [year]
    , [Site]
    , Team
    , SubTeam
    , Client
    , Scheme
    , Calls_Offered
    , Calls_Answered 
FROM [LB ODS].WorkCalendarV2Emails 
WHERE (Site IN (@Site)) 
    AND (Data_Label IN (@DataLabel)) 
    AND (dt >= @startdate) 
    AND (dt <= @enddate)
ORDER BY dt
0
votes

@Dan-Tracey

For Chronological month order add this clause to your select statement: Month(SaleDate) as MonthSort

For financial year order add this clause to your select statement: CASE WHEN Month(SaleDate)<=3 THEN MONTH(SaleDate)+9 ELSE MONTH(SaleDate)-3 END AS MonthSort

Then in SSRS Go to the column group at the bottom, select the drop down, and in 'sort by' select the MonthSort column from your query

sort by month name