asked
Custom SSRS Alphabetical Sort Expression
Hi there, I have a SSRS report expression that uses =Switch to perform a sort of specific values however I'd like to expand this expression to Sort by alphabetically if none of these values are recognised. My =Switch expression is: =Switch ( Fields!DynamicValue.Value = "Risk", 1, Fields!DynamicValue.Value = "Poor", 2, Fields!DynamicValue.Value = "Okay", 3, Fields!DynamicValue.Value = "New", 4, True, 5) The data is very dynamic and can go beyond these 4 values. So if these 4 values are not returned how can I sort the data alphabetically? TIA
@David 2 1 Is it possible to sort by more than one expression? For example, suppose I have the table with one column and 10 rows, values are A, B, C, D, E, F, Risk, Poor, Okay, New. If I need to sort this data so that appears in the order as per your question, I can do something like order by that custom sort and then by the column. For example: declare @t table (DynamicValue varchar(10)); insert into @t values ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('Risk'), ('Poor'), ('Okay'), ('New'); select * from @t -- this is an equivalent of that custom sort order by case DynamicValue when 'Risk' then 1 when 'Poor' then 2 when 'Okay' then 3 when 'New' then 4 else 5 end, DynamicValue /* once custom sorted, sort leftovers */; The above produces this: DynamicValue ------------ Risk Poor Okay New A B C D E F
@David 2 1 With this idea in mind, in case if there has to be only one expression used to sort the data, you can bake-in the actual column values to your switch so that they participate in the fake sort by multiple conditions even though only one expression is used. Something like this: =Switch ( Fields!DynamicValue.Value = "Risk", "1", Fields!DynamicValue.Value = "Poor", "2", Fields!DynamicValue.Value = "Okay", "3", Fields!DynamicValue.Value = "New", "4", True, "5"&Fields!DynamicValue.Value ) I am not sure if SSRS allows such expressions, I know that Power BI does. Please let me know if this works for you.
@Oleg, no it successfully sorts Risk > Poor > Okay > New as ascending. However I need to amend the expression so that for values returned that are not any of Risk, Poor, Okay, New, they are sorted alphabetically. Right now it there is no sorting if the values are not one of the those mentioned. It just looks random.
This answer comes from the comments under original question. Using the idea from the first comment under question, in case if there has to be only one expression used to sort the data, it is possible to bake-in the actual column values to the switch so that they participate in the fake sort by multiple conditions even though only one expression is used. Something like this: =Switch ( Fields!DynamicValue.Value = "Risk", "1", Fields!DynamicValue.Value = "Poor", "2", Fields!DynamicValue.Value = "Okay", "3", Fields!DynamicValue.Value = "New", "4", True, "5"&Fields!DynamicValue.Value ) Using the small number as prefix (letters could be used as well), the source expression is directed to evaluate **Risk** as **1Risk**, **Poor** as **2Poor**, **A** as **5A**, **B** as **5B**, **C** as **5C**, etc. If dynamic values list consisted of these 5 items they would sort in the following order: **Risk**, **Poor**, **A**, **B**, **C**. Hope this helps. Oleg
@Oleg, thanks. Have I understood you incorrectly? I posted your code into the expression area for sorting the group. The issue I have is that besides the 4 known values that I need sorted in a particular way, the other values can be numerous, hence the DynamicValue. ![alt text][1] [1]: /storage/temp/4418-expression-sort.jpg
@David 2 1 It looks like you have misunderstood me. Under the first comment there is another comment which includes the **switch** statement. The statement should do the trick. The SQL script in the first comment is just a sample explaining the idea, it cannot be used in the expression. The switch from the second comment should just work and take care of any dynamic values.
19 People are following this question.
