Order By Months Name in SQL and SSRS Report

Order By Months Name in SQL and SSRS Report

I was working on Report where i have to display all month Names in order by of Months if  User selectes more than 12 months then it will display like screen below.

Next january month it is showing after december which means Next year.

So, I Handled this in my sql only in my query i ordered by month and year.

below is the demonstration.

--First we will create a table

Create TABLE [dbo].[EmployeeMonth]

(

[EmpID] [nvarchar](10) NULL,

[Name] [nvarchar](10) NULL,

[MonthName] [nvarchar](10) NULL,

[Year] nvarchar(10)

) 

GO

INSERT INTO [dbo].[EmployeeMonth]VALUES(1,'A','March','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(2,'B','May','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(3,'C','December','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(4,'D','November','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(5,'E','January','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(6,'F','June','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(7,'G','February','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(8,'H','April','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(9,'I','October','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(10,'J','August','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(11,'K','July','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(12,'L','September','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(5,'E','January','2014')

INSERT INTO [dbo].[EmployeeMonth]VALUES(7,'G','February','2014')

GO

select * from [EmployeeMonth]

Output

EmpID Name MonthName Year

1 A March        2013

2 B May         2013

3 C December 2013

4 D November 2013

5 E January        2013

6 F June       2013

7 G February 2013

8 H April         2013

9 I October       2013

10 J August        2013

11 K July       2013

12 L September 2013

5 E January          2014

7 G February 2014

Now in order to get order by based on month and year to display in a report 

below is the query

select * from [EmployeeMonth]

order by YEAR,DATEPART(mm,CAST([MONTHName]+ ' 1900' AS DATETIME)) asc

Output

EmpID Name MonthName Year

5 E January         2013

7 G February 2013

1 A March         2013

8 H April         2013

2 B May         2013

6 F June         2013

11 K July         2013

10 J August         2013

12 L September 2013

9 I October         2013

4 D November 2013

3 C December 2013

5 E January         2014

7 G February 2014

Write the query in you report and you will achieve the Output as shown in the above screenshot.