Link to home
Create AccountLog in
Avatar of normajm400
normajm400Flag for United States of America

asked on

SSRS Specifc Sort Order

Hello,

I'm new to working with SQL, SSRS and Report Builder - Access is my area of experience.  In my first Tablix I need to set up quarters and then months in a specific order - August through July.
I've got everything working except when Q2 is expanded and it doesn't show the months in the order needed

+Q2
November

-Q2
January|November|December

Quarter code:
  ,Case 
	When DatePart(month,[Posting Date]) in (8,9,10) then 'Q1'
	When DatePart(month,[Posting Date]) in (11,12,1) then 'Q2'
	When DatePart(month,[Posting Date]) in (2,3,4) then 'Q3'
	Else 'Q4'
End As 'Quarter'

Open in new window


Order by code:
ORDER BY (CASE WHEN 'MonthVal' = 2 THEN 0 
	When 'MonthVal' = 3 then 1
	When 'MonthVal' = 4 then 2
	When 'MonthVal' = 5 then 3
	When 'MonthVal' = 6 then 4
	When 'MonthVal' = 7 then 5
	When 'MonthVal' = 8 then 6
	When 'MonthVal' = 9 then 7
	When 'MonthVal' = 10 then 8
	When 'MonthVal' = 11 then 9
	When 'MonthVal' = 12 then 10
	When 'MonthVal' = 1 then 11 END)

Open in new window


When I try to add the following code to Order by
,(CASE WHEN 'Month Name' LIKE 'February' then 0
	When 'Month Name' LIKE 'March' then 1
	When 'Month Name' LIKE 'April' then 2
	When 'Month Name' LIKE 'May' then 3
	When 'Month Name' LIKE 'June' then 4
	When 'Month Name' LIKE 'July' then 5
	When 'Month Name' LIKE 'August' then 6
	When 'Month Name' LIKE 'September' then 7
	When 'Month Name' LIKE 'October' then 8
	When 'Month Name' LIKE 'November' then 9
	When 'Month Name' LIKE 'December' then 10
	When 'Month Name' LIKE 'January' then 11 END)

Open in new window


I get the following error: A constant expression was encountered in the ORDER BY list, position 2.

I know I should set this up as a function or something similar so I can reuse it, but I would like to get this working just for this first report.

Suggestions please...

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of itcouple
itcouple
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of normajm400

ASKER

Because of your helpful comment, I had to rethink this.  What I did was create a Select/Case statement named MonthSort that reassigned a number to the months in the order needed, then used that in the Sort by field in the column group properties.  Worked perfectly.