Interactive sort on a text field having decimal numbers

Question

I have the following text field in SSRS report:

Version
2.0.0.0
1.0.0.0
1.2.0.0
2.1.8.8
2.2.32.7
1.4.11.0

I want to sort this field interactively. How do I do that?

Once sorted ascending, report should show

Version
1.0.0.0
1.2.0.0
1.4.11.0
2.0.0.0
2.1.8.8
2.2.32.7

Thank you in advance

Answer 1

To use Interactive Sorting, right-click on the column header and view Text Box Properties.

Click on the Interactive Sorting tab and check the Enable Interactive Sorting box.

Normally you would use Detail Row but you can also specify to use a Group instead.

Put the field or expression that you want to sort by in the Sort By expression.

You need to do the same for any additional columns you wish to sort.

As to your data, the field you have would be sorted as text and not a number. This works for the data that you have listed but may cause issues when you have versions in the double digits. A sorted list would order a list like:

1. 1.15
2. 10.5
3. 2.33
4. 3.2
5. 9.1

If this is a potential issue, you would need to figure out a way to deal with it. You could use an expression that adds zeroes to the first number to deal with this:

=RIGHT("000" & LEFT(FIELDS!Version.VALUE, INSTR(FIELDS!Version.VALUE) - 1) & MID(FIELDS!Version.VALUE, INSTR(FIELDS!Version.VALUE) + 1, LEN(FIELDS!Version.VALUE))

For more info: Interactive sorting In SSRS on Values - Matrix report

Answer 2

I have found the solution for this. I break down the strings and add trailing zeros and make their length is 8(in my case). e.g. '1.2.11.0' becomes '01021100' and so on..then convert this to Integer and then in the interactive sort, use this integer field to sort.