Change Lexical Order to Numeric order 1

[Jdbenike]

Let's say I have the code below in Reporting Services as a seperate Dataset

select distinct homelaborlevelname2
from vp_person
where homelaborlevelname2  >= 'D'
and homelaborlevelname2 <= 'E'
order by homelaborlevelname2

This would bring back
'D1'
'D10'
'D11'
'D12'
'D2'
'D3'
'D4'
'D5'
'D6'
'D7'
'D8'
'D9'
with what we have in our database now.
When I do a order by HomeLaborLevelName2 I need it to come back as

'D1'
'D2'
'D3'
'D4'
'D5'
'D6'
'D7'
'D8'
'D9'
'D10'
'D11'
'D12'
So the results would be in numeric order and not lexical order
Any Suggestions?

 

[SgtJarrow]

Assuming the "D" character is not static (could be other characters), the best way to accomplish this is to split the alpha character out from the numeral and then sort on the two fields independently. You could do the split either in the SQL or in the report...my preference would be the SQL.

Leave your HomeLaborLevelName2 field alone and include two more fields in your SQL....using SUBSTRING to get the two parts:

SUBSTRING(HomeLaborLevelName2, 1, 1) will give you the alpha character
SUBSTRING(HomeLaborLevelName2, 2, LEN(HomeLaborLevelName2) - 1) will give you the numeric portion

Then use these two new fields returned by the SQL as your sort columns in the report but do not display them...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[Jdbenike]

http://i43.tinypic.com/35a3pza.jpg

When I group by it still groups in the same number order.
Do we hafta do a 3rd substring?

Maybe I didn't understand you correctly.
I attached a picture of what I ran and the results.

 

[SgtJarrow]

The picture is not working for me (may be my firewall) but it sounds like the SSRS report is interpreting the numeric from the last substring as a string and not a integer (as I would expect).

Try changing the last substring from:

SUBSTRING(HomeLaborLevelName2, 2, LEN(HomeLaborLevelName2) - 1)

to:

CAST(SUBSTRING(HomeLaborLevelName2, 2, LEN(HomeLaborLevelName2) - 1) AS INT)

That should make SSRS see this as an integer and sort it properly.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[Jdbenike]

Beautiful.
Thank you.