Column sorting in SSRS report

Question

I want to sort my column numbers at report level in SSRS instead of my query as I have some other columns sorted at query level and doing both sorts at query level don't work well. At the moment I get Columns order like 1, 10,2,3,4,5,6 instead of 1,2,3,4,5,6...10. Once sorting is done I also want to add 'sales_' to the column name so I see sales_1, sales_2, sales_3 and so on. I understand this could be pretty straightforward but I'm new to SSRS. Thanks in advance.

Answer 1

The sort you are describing (1, 10,2,3,4) is a string sort, you need to convert the data type on that column to a numeric (in this case integer) type so that the sort is correct.

There are two solutions to this, you can add a calculated field to the record set, this can be helpful if you need to use this column multiple times, or you can simply use an expression for the sort order.

Right click on your dataset and select Add Calculated field, a dialog will open with all the column definitions for the query, here you can add your own custom fields. For a custom field you will need an expression, which was the other options originally, so lets have a look at that component.

Edit the sort definition for the Tablix or group and select the field you want to sort on, to sort the way you would like the value needs to be numeric, we can use the CInt function to convert a value to an integer

NOTE: when using expressions to convert data types, if your data might not be convertible you may need to validate the value before conversion otherwise your report execution will fail. The error messages are pretty helpful though and the expression dialog shows available functions an examples of usage for each of them.

For your second issue, use an expression in the field where you are displaying the data to concatenate the values, you can use the String.Format function or you can use simple string addition:

Expression Examples:

="sales_" + Fields!ItemValue.Value

="sales_" & Fields!ItemValue.Value

=String.Format("sales_{0}", Fields!ItemValue.Value)

I hope this helps you on your way, welcome to SSRS!