SSRS - Sort by number part of string

Question

I have an SSRS report that will be used in Dynamics 365 so I can't use SQL in the dataset to help here.

I have a product/version code column that is string mixing letters and numbers. For example:

FF8, FF9, FF10, FFA

These are going in to a column header and form a column group which is also sorted by the code. The standard alphabetical sorting is giving this order:

FF10 - FF8 - FF9 - FFA

I'm happy to use a substring in my sort expression to remove a preceding product code but I would like the numbers in ascending numerical format followed by text versions alphabetically:

FF8 - FF9 - FF10 - FFA

Answer 1

I would add a calculated column to your dataset that strips the non-numeric characters and converts to a number. This would make it easier to sort

A formula like this might help

=System.Text.RegularExpressions.Regex.Replace(Fields!productcode.Value, "[^0-9]", "")

The ^ symbol means "not" so this Regex expression will remove all characters that are not in the range of 0 to 9 (i.e. all non-numeric characters)

According to this, Regex.Replace should be supported in CRM's sandboxed reports

Answer 2

You also could use expression like below in Sort

=switch(Fields!name.Value="FF10",3,Fields!name.Value="FF9",2,Fields!name.Value="FF8",1,Fields!name.Value="FFA",4)

Zoe