Let’s say you want to order your data in your tablix by last name, but you want certain names to be sorted first.
1) Double click the first grouping under Row Groups.
2) Select ‘Sorting’.
3) Click the ‘Add’.
4) Click the Expression button.
5) Add the following expression.
=switch( Fields!last_name.Value = "SMITH", 1, Fields!last_name.Value = "JONES", 2, Fields!last_name.Value = "WILLIAMS", 3)
Now, all persons will be sorted by SMITH, then JONES, then WILLIAMS.
But, what about the rest of the data. How can you get the rest of last name to be sorted ascending like such:
You can use a CASE statement in the SQL instead like this:
SELECT * FROM NAME_TABLE ORDER BY (CASE WHEN NAME_LAST= 'SMITH' THEN 1 WHEN NAME_LAST = 'JONES' THEN 2 WHEN NAME_LAST = 'WILLIAMS' THEN 3 ELSE 4 END), NAME_LAST