CODEWITHSUNDEEP
sql-serversortingreportingservices-2005
MStodd
MStodd

Reputation: 4746

How do I sort a column in an SSRS report alphabetically AND numerically?

I know nothing about SSRS

I have an SSRS report with a column containing text and numbers. I want the column sorted with all the text first sorted alphabetically, then all the numbers sorted numerically.

The data as it stands is all just strings.

Is there a way to do this by simply writing a sort expression, or do I need to do the sort on the data ahead of time somehow and assign a sort order member?

example:

If I have the following:
"cat"
"bear"
"1"
"5.0"
"10"
"-4"
"dog"
"-6"

I would want:

"bear"
"cat"
"dog"
"-6"
"-4"
"1"
"5.0"
"10"

thanks, Mark Stoddard

Upvotes: 0

Views: 1440

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

Subject to revision based on the answers to my comment, but here is a start.

DECLARE @foo TABLE(bar VARCHAR(32));

INSERT @foo SELECT 'cat'
  UNION ALL SELECT 'bear'
  UNION ALL SELECT '1'
  UNION ALL SELECT '5'
  UNION ALL SELECT '10'
  UNION ALL SELECT '-4'
  UNION ALL SELECT 'dog'
  UNION ALL SELECT '-6';

SELECT bar FROM @foo
ORDER BY 
    CASE WHEN bar NOT LIKE '%[^0-9.-]%' THEN 2 ELSE 1 END,
    CASE WHEN bar NOT LIKE '%[^0-9.-]%' AND bar <> '.' THEN CONVERT(DECIMAL(12,3), bar) END,
    bar;

Result

bar
----------
bear
cat
dog
-6
-4
1
5
10

Upvotes: 1

Related Questions