sorting string numeric values in SSRS 2008

Question

I have a varchar field (i am grouping on) in a dataset that returns a list of values : 20, 25, 40, 100, 110, 'N/A'..

I want the "numeric" values sorted from low to high : i.e : 20, 25...110, 'N/A'

the problem is that the A>Z sorting in the grouping gives out the following output :

100, 110, 25, ..., N/A

I cannot convert to numeric datatype since there are string values..

Does anyone have a solution please ?

Thank you in advance Jam

Answer 1

There are several solutions you can implement here. I'll discuss the two I consider to be the easiest. If these don't work for you, let me know because there are a multitude of options. To make it easy, I've named the field you're referring to in your question as *num_text*.

Fix in SSRS: Go to the Tablix Properties for the tablix displaying the data in question. Go to Sorting tab. Click Add. In the "Sort by" field, click the expression button and type the following:

=CInt(IIF(Fields!num_text.value = "N/A",9999999,Fields!num_text.value))

Note, you need to convert any possible text values to a number greater than any possible integer/decimal. In this case I chose 9999999 based on the examples in your question. If you have multiple text values that are not converted to number, Report Builder/BIDS will allow you to save the report, but when you render it, it will show #Error, signifying that the CInt (or any other conversion formula you choose) failed on a non-numeric value.

Fix in SQL: Add a new field (like field_sort) with datatype numeric and use your case statement generating the current field in question saying:

, Case
    When --Criteria leading to "N/A"
        Then 9999999
        Else num_text
    End as field_sort

--Rest of SQL Script here

Order by field_sort

Then just display your num_text field in SSRS, and the values will be sorted properly. If you have many possible string values, then you might find it easier to fix in SQL (rather than specifying numerous IIF statements in SSRS.