Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Change Lexical Order to Numeric order 1

Status
Not open for further replies.

Jdbenike

MIS
Sep 11, 2008
74
US
Let's say I have the code below in Reporting Services as a seperate Dataset
Code:
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?
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top