Whilst working on a SSRS based report I had hit a sort order problem. Entries in a main report tablix needed to be sorted by their OrderNumber but this was in the form 1.2.3; so a neither a numeric or alpha sort gave the correct order i.e. a numeric sort fails as 1.2.3 is not a number and an alpha sort worked but give the incorrect order 1.3, 1.3.1, 1.3.10, 1.3.11, 1.3.12, 1.3.2.

When I checked the underlying SQL it turned out the OrderNumber was being generated, it was not a table column. The raw data was in a single table that contained all the leaf nodes in the hierarchy, the returned data was built by a SPROC using a recursive call.

The solution was to also calculate a SortOrder as well as the OrderNumber. I did this using a Power function on each block of the OrderNumber and added the results together. In the code shown below we can have any number of entries in the first block and up to 999 entries in the second or third block. You could have more by altering the Power function parameters

declare @EntryID as nvarchar(50) = ‘ABC1234';

WITH SummaryList AS
    (
        SELECT
        NI.ItemID,
        NI.CreationDate,
        ‘P’ as ParentOrChild,
        cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) as nvarchar(100)) as OrderNumber,
        cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) *  Power(10,6) as int) as SortOrder
        FROM dbo.NotebookItem AS NI
        WHERE NI.ParentID IS NULL AND NI.EntryID = @EntryID

        UNION ALL

        SELECT
        NI.ItemID,
        NI.CreationDate,
        ‘L’ as ParentOrChild,
        cast(SL.OrderNumber + ‘.’ + cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) as nvarchar(100)) as nvarchar(100)) as OrderNumber,
        SL.SortOrder + (cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) as int) * power(10 ,6 - (3* LEN(REPLACE(sl.OrderNumber, ‘.’, ‘’))))) as SortOrder
        FROM dbo.NotebookItem AS NI
        INNER JOIN SummaryList as SL
            ON NI.ParentID = SL.ItemID
    )

    SELECT
        SL.ItemID,
        SL.ParentOrChild,
        SL.CreationDate,
        SL.OrderNumber,
        SL.SortOrder
    FROM SummaryList AS SL
    ORDER BY SL.SortOrder


This query returns the following with all the data correctly ordered

ItemID

ParentOrChild

CreationDate

OrderNumber

SortOrder

22F72F9E-E34C-45AB-A4D9-C7D9B742CD2C

P

29 October 2014

1

1000000

E0B74D61-4B69-46B0-B0A9-F08BE2886675

L

29 October 2014

1.1

1001000

CB90233C-4940-4312-81D1-A26CB540DF2A

L

29 October 2014

1.2

1002000

35CCC2A1-E00F-43C6-9CB3-732342EE18DA

L

29 October 2014

1.3

1003000

7A920ABE-A2E2-4CF1-B36E-DE177A7B8681

L

29 October 2014

1.3.1

1003001

C5E863A1-5A92-4F64-81C6-6946146F2ABA

L

29 October 2014

1.3.2

1003002

23D89CFF-C9A3-405E-A7EE-7CAACCA58CC2

L

29 October 2014

1.3.3

1003003

CE4F9F6B-3A58-4F78-9C1F-4780883F6995

L

29 October 2014

1.3.4

1003004

8B2A137F-C311-419A-8812-76E87D8CFA40

L

29 October 2014

1.3.5

1003005

F8487463-302E-4225-8A06-7C8CDCC23B45

L

29 October 2014

1.3.6

1003006

D365A402-D3CC-4242-B1B9-356FB41AABC1

L

29 October 2014

1.3.7

1003007

DFD4D688-080C-4FF0-B1D0-EBE63B6F99FD

L

29 October 2014

1.3.8

1003008

272A46C6-E326-47E8-AEE4-952AF746A866

L

29 October 2014

1.3.9

1003009

F073AFFA-F9A1-46ED-AC4B-E92A7160EB21

L

29 October 2014

1.3.10

1003010

140744E7-4950-43F8-BA0C-0E541550F14B

L

29 October 2014

1.3.11

1003011

93AA3C05-E95A-4201-AE03-190DDBF31B47

L

29 October 2014

1.3.12

1003012

5CED791D-4695-440F-ABC4-9127F1EE2A55

L

29 October 2014

1.4

1004000

FBC38F00-E2E8-4724-A716-AE419907A681

L

29 October 2014

1.5

1005000

2862FED9-8916-4139-9577-C858F75C768A

P

29 October 2014

2

2000000

8265A2BE-D2DD-4825-AE0A-7930671D4641

P

29 October 2014

3

3000000