question

David 2 1 avatar image
David 2 1 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
ssrssortingexpressionswitch
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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
0 Likes 0 ·
@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.
0 Likes 0 ·
@Oleg, many thanks. Unfortunately SSRS does not recognise the code. It appears that I can only sort a column, like DynamicValue, alphabetically or include the expression. Not both. :/
0 Likes 0 ·
@David 2 1 This is strange because there is only one expression, that is all. Does it complain about the line reading ***True, "5"&Fields!DynamicValue.Value***?
0 Likes 0 ·
@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.
0 Likes 0 ·
Show more comments
Oleg avatar image
Oleg answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 2 1 avatar image
David 2 1 answered
@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

expression-sort.jpg (125.2 KiB)
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
0 Likes 0 ·
@Oleg, may apologies, it's been a long week. You are indeed correct, as always. :) The Switch works perfectly now. Thank you so much.
0 Likes 0 ·
@Oleg, can you paste your comment with your answer into an answer box so that I can award it appropriately?
0 Likes 0 ·
@David 2 1 This is done. Thank you.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.