CODEWITHSUNDEEP
sortingreporting-servicesssrs-2012
Prince
Prince

Reputation: 43

Ordering an alphanumeric column in a matrix in SSRS

I have a column that l need to order in a Matrix but my problem is that the column has a mixture of numbers and characters as it's a range field and the range differs on each grouping parent. an example of the grouping looks like

Create Table #temp
(
range Varchar(30)
)
Insert into #temp (range)
select '[0-501]' Union      
select '[13001-17001]'   Union  
select  '[17001-999999]'   Union    
select '[8501-13001]'    Union      
select '[501-8501]' 

SELECT  * 
FROM  #temp order by range

drop table #temp


[17001-999999] 
[0-501]     
[13001-17001]       
[8501-13001]        
[501-8501]

Preferred result after ordering is as below

[0-501]  
[501-8501]  
[8501-13001]
[13001-17001]   
[17001-999999]

Upvotes: 0

Views: 41

Answers (2)

Soundappan A
Soundappan A

Reputation: 331

Try this below query it will give desired output

SELECT  * 
FROM  #temp 
order by cast(replace(replace(replace(range,'-',''),'[',''),']','') as bigint)

Upvotes: 1

Wouter
Wouter

Reputation: 2976

It would be better to design your table so that is has some kind of "Sort Order" column. But you could use this:

SELECT *,TRY_CAST(SUBSTRING(range,2,CHARINDEX('-',range)-2) AS INT) as SortOrder
FROM #temp

Then use this column to order your matrix.

Upvotes: 1

Related Questions