normajm400
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:
Order by code:
When I try to add the following code to Order by
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.
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'
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)
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)
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER