We help IT Professionals succeed at work.

SSRS Specifc Sort Order

normajm400
normajm400 asked
on
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.
Comment
Watch Question

Commented:
You actually used constant 'Month Name'  it doesn't change so order by is useless therefore you get the error. You probably want to use field name here.

Regards
Emil

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.