June 18, 2015 at 7:58 pm
I have the following text field in SSRS report:
Version
2.0.0.0
1.0.0.0
1.2.0.0
2.1.8.8
2.2.32.7
1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly?
Once sorted ascending, report should show
Version
1.0.0.0
1.2.0.0
1.4.11.0
2.0.0.0
2.1.8.8
2.2.32.7
Thank you in advance
June 18, 2015 at 11:46 pm
rockstar283 (6/18/2015)
I have the following text field in SSRS report:Version
2.0.0.0
1.0.0.0
1.2.0.0
2.1.8.8
2.2.32.7
1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly?
Once sorted ascending, report should show
Version
1.0.0.0
1.2.0.0
1.4.11.0
2.0.0.0
2.1.8.8
2.2.32.7
Thank you in advance
First, see the first Blue link in my signature line below for how to post problems like this to get the best help.
The following will work provided that you always have 4 items separated by periods on each row. If that's not right, then post the readily consumable data like it says in the article. Thanks.
WITH
cteTestData (YourColumn) AS
(
SELECT '2.0.0.0' UNION ALL
SELECT '1.0.0.0' UNION ALL
SELECT '1.2.0.0' UNION ALL
SELECT '2.2.32.7' UNION ALL
SELECT '2.2.8.8' UNION ALL --Changed this to show it works
SELECT '1.4.11.0'
)
SELECT *
FROM cteTestData
ORDER BY PARSENAME(YourColumn,4)+0
,PARSENAME(YourColumn,3)+0
,PARSENAME(YourColumn,2)+0
,PARSENAME(YourColumn,1)+0
;
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2015 at 1:47 pm
Thank you for your input mate.
I have found the solution for this. I break down the strings and add trailing zeros and make their length is 8(in my case). e.g. '1.2.11.0' becomes '01021100' and so on..then convert this to Integer and then in the interactive sort, use this integer field to sort.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply