SQL Server Reporting Services (SSRS) – Order by different values from the same database table column

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:

SMITH
JONES
WILLIAMS
ANDERSON
DAVIS
EDWARDS
etc.

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
Please follow and like us: