Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Any situation worth understanding is confusing
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

22
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:

  1. In SQL Server Reporting services, add the Tablix/Matrix or List object to the canvas

  2. Add the required fields to the object

  3. Right click on the object

  4. Select the Properties option

  5. In the Propoerties dialog box, select Sorting - Add

  6. Press the [fx] button to the right of the Sort By text box

  7. Enter the expresion for the field to be sorted on:

    =VAL(Fields!Fieldname.Value)

  8. Press the OK button

  9. Press the Add button

  10. In the Then By text box, select the same field again, from the drop down

    [FieldName]

  11. Press the OK button

  12. Run the report



If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us
1

Joolz

25
May
Thanks for the tip!

MAKE A COMMENT

Name *

Email Address *

Comment *

To prove you are human what is:

76 + 92 =