Crystal Reports & SQL Reporting Services Training Consultants

Account Log in:

Username:

Password:

*Don't let weeds grow around your dreams*

Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers

Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog

May

How Do I Sort Alphanumerical Field Values in SQL Server Reporting Services 2019?

The client was creating a report in SQL Server Reporting Services 2019, and when the field was sorting the data was not sorting correctly.

When checking the data in the SQL server database, the data had been stored as a varchar (10) data type, allowing the end user to add alphanumerical data into the field, thus causing the sorting issues.

Solution:

There are a number of methods to resolve this issue, but I found this method quite quick and easy to apply.

By using the VAL() function, which allows one to extract the numerical part of a field.

Then one can sort on the field by:

In SQL Server Reporting services, add the Tablix/Matrix or List object to the canvas
Add the required fields to the object
Right click on the object
Select the Properties option
In the Properties dialog box, select Sorting - Add
Press the [fx] button to the right of the Sort By text box
Enter the expression for the field to be sorted on:

=VAL(Fields!Fieldname.Value)
Press the OK button
Press the Add button
In the Then By text box, select the same field again, from the drop down

[FieldName]
Press the OK button
Run the report