Interactive sort on a text field having decimal numbers

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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