Custom Sorting With An Expression in SSRS Reports

A. You want to stay away from hard coding values into expressions in SSRS, especially text values. If you find yourself in a situation where you absolutely need to declare a report variable and use the variable in the expression this way you only have to update one spot instead of 10. It also makes it easier to manage the reports over time.

B. Ideally you want to do your sorting in SQL, not SSRS as it takes more longer to sort than SQL

C. Sorting text and number has to be done in two separate columns (See sample below)

Here is an a very rough example of how you could manipulate T_Memb_Level on the SQL side and then join it to the query where the data is pulled for the constituents.

declare
@SortMethod        tinyint = 3
,@SortAsc        bit = 1

/*
1 - Max Amount
2 - Rank
3 - Alpha

*/
if @SortMethod = 1 Set @SortAsc = 0
Select * from
(Select   [rank], description, end_amt
,iif( Constituency in (29,20) , Description, format(Start_Amt, 'C2') + ' - ' + format(End_Amt, 'C2')) as Lbl
, ROW_NUMBER () over
(order by
Case @SortMethod
    When 2 then [Rank]
    When 3 then 0
    else End_Amt end , Description) as SeqNo   from T_MEMB_LEVEL ) as B
    order by iif(@SortAsc = 1, SeqNo , SeqNo * -1)

A. You want to stay away from hard coding values into expressions in SSRS, especially text values. If you find yourself in a situation where you absolutely need to declare a report variable and use the variable in the expression this way you only have to update one spot instead of 10. It also makes it easier to manage the reports over time.

B. Ideally you want to do your sorting in SQL, not SSRS as it takes more longer to sort than SQL

C. Sorting text and number has to be done in two separate columns (See sample below)

Here is an a very rough example of how you could manipulate T_Memb_Level on the SQL side and then join it to the query where the data is pulled for the constituents.

declare
@SortMethod        tinyint = 3
,@SortAsc        bit = 1

/*
1 - Max Amount
2 - Rank
3 - Alpha

*/
if @SortMethod = 1 Set @SortAsc = 0
Select * from
(Select   [rank], description, end_amt
,iif( Constituency in (29,20) , Description, format(Start_Amt, 'C2') + ' - ' + format(End_Amt, 'C2')) as Lbl
, ROW_NUMBER () over
(order by
Case @SortMethod
    When 2 then [Rank]
    When 3 then 0
    else End_Amt end , Description) as SeqNo   from T_MEMB_LEVEL ) as B
    order by iif(@SortAsc = 1, SeqNo , SeqNo * -1)

**SOLVED**

Thank you, Sir! As it turns out, my predecessor (who originally wrote the script) had a donor level column in the script, much like what you explain above. Once I sorted by donor level everything came together.