Search code examples
reporting-services

SSRS order number with (-)

In SSRS i am trying to sort a group of number as an example down. The numbers are like

3-102
3-202
10-102
10-502
12-802

When I just sorted without expression it pulls first number 10. Then I tried to used val(fields!apt.value) expression.

Then it sorted only first numbers 3, 10 ad 12. Second numbers wasn't in order

How to sort these numbers as I order at the top. Which function should I use

Solution

  • You'll need to add 2 sort expressions (or combine them into 1 but 2 is easier IMO)

    The first expression will be

    =VAL(SPLIT(Fields!apt.Value, "-")(0))

    and the second

    =VAL(SPLIT(Fields!apt.Value, "-")(1))

    All we are doing here is splitting the apt string using - as the delimiter, into an array, then taking the first element (0) for the first expression. The second expression is exactly the same but we take the second element (1).

    Then we use VAL() (as you tried)

    Below is the output. The left table is unsorted. The right table uses the sort expression above

    enter image description here

    Edit after OP update

    If you have instances where apt field does not contain a "-" then you will need to change the SECOND expression only

    =IIF(SPLIT(Fields!apt.Value, "-").Length > 1
, SPLIT(Fields!apt.Value & "-", "-")(1)
, "")

    This simply adds adds some text to fake the missing part so the SPILT() function always returns at least 2 elements.