Default Order of Case Statement, for use in SSRS

Question

I'm building a report in SSRS. I have values from a DB column populating the row headings in a matrix in the report. These are display fine. The problem is that they display alphabetically and i don't know how to have them display in an order i have defined.

The SQL query looks like:

 CASE   
   WHEN Age >= 0 AND Age <3 THEN 'Newborn 0-3 Months'
   WHEN Age > 3 AND Age <= 12 THEN 'Infant 3-12 Months'
   When Age > 12 THEN 'Child'
   ELSE 'Invalid Age'
 END AS AgeGroup

Which works. But in my report it displays the columns so 'Child' displays before 'Infant' and so on.

Is there a way i can define the order? Thanks in advance

You need an order by clause in your query, can you post the full query syntax please — ChrisProsser

Yuck Yuck · Accepted Answer · 2013-06-26T11:38:24

You have to add a second case statement to use in the ordering:

CASE   
   WHEN Age >= 0 AND Age <3 THEN 10
   WHEN Age > 3 AND Age <= 12 THEN 20
   When Age > 12 THEN 30
   ELSE 40 -- or 0, perhaps
END AS AgeGroupOrder

Default Order of Case Statement, for use in SSRS

1 Answers