SSRS: Recursive Parent Child

Question

I want to create hierarchical report in SSRS using this simple table? Could you please help me? I follow the tutorial here: https://www.mssqltips.com/sqlservertip/1939/working-with-a-parent-child-hierarchy-in-sql-server-reporting-services-ssrs/

but I can't figure out how the EmployeeKey and ParentEmployeeKey apply to my table. As far As my table concern Col2 Is the Employee and Col1 is the parent. But in SSRS when I group by Col2 and Recursive Parent is COL1 I don't get the desired result.

Here is my table:

╔══════════════╦══════════════╗
║     COL1     ║     COL2     ║
╠══════════════╬══════════════╣
║ TEST01       ║ TEST02       ║
║ TEST01       ║ TEST03       ║
║ TEST01       ║ TEST04       ║
║ TEST02       ║ LAB          ║
║ TEST02       ║ STL40        ║
║ TEST03       ║ LABSTL       ║
║ TEST03       ║ STLSCH40     ║
║ TEST04       ║ LABSTL       ║
║ TEST04       ║ FLG41        ║
║ TEST04       ║ STLSCH40     ║
╚══════════════╩══════════════╝

This is the outcome that I want to get using SSRS. like indent style of the picture ..

╔═══════════════╦══╗
║     COL1      ║  ║
╠═══════════════╬══╣
║ TEST01        ║  ║
║ -TEST02       ║  ║
║ ----LAB       ║  ║
║ ----STL40     ║  ║
║ -TEST03       ║  ║
║ ----LABSTL    ║  ║
║ ----STLSCH40  ║  ║
║ -TEST04       ║  ║
║ ----LABSTL    ║  ║
║ ----FLG41     ║  ║
║ ----STLSCH40  ║  ║
╚═══════════════╩══╝

I don't know to get the indent style result above. Do I need to use HierarchyID and IsDescendantOf or Recursive CTE. This is the Recursive CTE that I did and the result under it.

Declare @Col1 varchar(30)='TEST01';
Declare @BomLevel Integer=0;
WITH tBOM
AS
(
select a.Col1 , a.Col2, @BomLevel "BOMLevel" from Component A
WHERE Col1= @Col1 
UNION ALL
Select c.Col1, c.Col2, BomLevel+1 From Component C
INNER JOIN tBOM on tBOM.Col2=c.Col1 
)
select Col1,Col2 ,BOMLevel from tbom 


Col1                             Col2                           BOMLevel
TEST01                      TEST02                          0
TEST01                      TEST03                          0
TEST01                      TEST04                          0
TEST02                      STL40                           1
TEST02                      LAB                             1
TEST03                      STLSCH40                        1
TEST03                      LABSTL                          1
TEST04                      STLSCH40                        1
TEST04                      FLG41                           1
TEST04                      LABSTL                          1

score 2 · Accepted Answer · answered Jan 20 '16 at 04:10

This should work for any depth (assuming you don't pass VARCHAR(50) for SORT_PATH). The 'Trick' while you are going down the hierarchy you build a string that you can sort by (SORT_PATH). At the end, we can use REPLICATE() with our BOMLevel to indent with something (in this case a hyphen).

SET NOCOUNT ON;

DECLARE @Component AS TABLE (
         COL1 VARCHAR(50) ,
         COL2 VARCHAR(50)
        );

INSERT  INTO @Component
        ( COL1, COL2 )
VALUES  ( NULL,     'TEST01' ),  -- ADDED
        ( 'TEST01', 'TEST02' ),
        ( 'TEST01', 'TEST03' ),
        ( 'TEST01', 'TEST04' ),
        ( 'TEST02', 'LAB'    ),
        ( 'TEST02', 'STL40'  ),
        ( 'TEST03', 'LABSTL' ),
        ( 'TEST03', 'STLSCH40' ),
        ( 'TEST04', 'LABSTL' ),
        ( 'TEST04', 'FLG41'  ),
        ( 'TEST04', 'STLSCH40' )

;
WITH    tBOM
          AS ( SELECT   A.COL1 , -- PARENT
                        A.COL2 , -- CURRENT
                        0 AS "BOMLevel",
                        CAST(A.COL2 AS VARCHAR(50)) AS SORT_PATH
               FROM     @Component A
               WHERE    A.COL1 IS NULL
               UNION ALL
               SELECT   C.COL1 ,
                        C.COL2 ,
                        BOMLevel + 1,
                        CAST(SORT_PATH + '.' + C.COL2 AS VARCHAR(50)) AS SORT_PATH
               FROM     @Component C
                        INNER JOIN tBOM ON tBOM.COL2 = C.COL1
             )
     SELECT COL1 ,
            COL2 ,
            BOMLevel,
            SORT_PATH,
            REPLICATE('-', tBOM.BOMLevel) + COL2 AS DISPLAY_PATH
     FROM   tBOM
     ORDER BY SORT_PATH

Hm. This solution is a lot simpler. It didn't occur to me how to sort like this. Only thing I'd add (if it even matters to the question) is `REPLICATE('-', SQUARE(BOMLevel))` would produce the full desired output. — ZLK, Jan 20 '16 at 04:31
I forgot to say that I want to achieve this using SSRS. But I will try this. thank you — BobNoobGuy, Jan 20 '16 at 17:24
Yes! I figure this out.. Correct the key is to have that SORT_PATH / key type to identify which row belongs to what parent! — BobNoobGuy, Jan 20 '16 at 17:42
Spoke to soon. as soon as I have more than 2 level deep the query does not work. — BobNoobGuy, Jan 20 '16 at 19:27
If I add this `INSERT INTO @Component ( COL1, COL2 ) VALUES ( 'STLSCH40', 'HELLOTHERE' )` to create an additional level `(TEST01>TEST04>STLSCH40>HELLOTHERE)`, it works how I would expect it to with a display_path of `---HELLOTHERE`. — Robert Paulsen, Jan 21 '16 at 02:52

score 0 · Answer 2 · edited May 23 '17 at 12:07

This seems like kind of a funky way to do it but I can't really think of a good way to output the results in the format you're expecting:

CREATE TABLE #table (Col1 NVARCHAR(100), Col2 NVARCHAR(100))
INSERT #table VALUES ('TEST01', 'TEST02')
, ('TEST01', 'TEST03')
, ('TEST01', 'TEST04')
, ('TEST02', 'LAB')
, ('TEST02', 'STL40')
, ('TEST03', 'LABSTL')
, ('TEST03', 'STLSCH40')
, ('TEST04', 'LABSTL')
, ('TEST04', 'FLG41')
, ('TEST04', 'STLSCH40');

WITH CTE1 AS (
    SELECT C1.Col1 C1
        , C2.Col2 C2
        , C3.Col2 C3
        , ROW_NUMBER() OVER (ORDER BY C1.Col1, C2.Col2, C3.Col2) RN
    FROM (SELECT Col1 FROM #table WHERE Col1 NOT IN (SELECT Col2 FROM #table)) C1
    LEFT JOIN #table C2 ON C2.Col1 = C1.Col1
    LEFT JOIN #table C3 ON C3.Col1 = C2.Col2
    GROUP BY C1.Col1, C2.Col2, C3.Col2)
, CTE2 AS (
    SELECT CASE WHEN RN = 1 THEN C1 ELSE NULL END C1
    , CASE WHEN RN = 1 THEN CAST('-' + C2 AS NVARCHAR(100))
        WHEN (SELECT C2 FROM CTE1 WHERE RN = C.RN-1) = C2 THEN NULL
        ELSE CAST('-' + C2 AS NVARCHAR(100))  END C2
    , CASE WHEN RN = 1 THEN CAST('----' + C3 AS NVARCHAR(100))
        WHEN (SELECT C3 FROM CTE1 WHERE RN = C.RN-1) = C3 THEN NULL
        ELSE CAST('----' + C3 AS NVARCHAR(100)) END C3
    FROM CTE1 C)
SELECT Col1
FROM CTE2
UNPIVOT (Col1 FOR Col IN (C1, C2, C3)) a

DROP TABLE #table

This outputs:

+--------------+
|     Col1     |
+--------------+
| TEST01       |
| -TEST02      |
| ----LAB      |
| ----STL40    |
| -TEST03      |
| ----LABSTL   |
| ----STLSCH40 |
| -TEST04      |
| ----FLG41    |
| ----LABSTL   |
| ----STLSCH40 |
+--------------+

... But it makes the assumption that the maximum depth of any parent is 2 (as per the example). If you go deeper than that and you're not sure how deep it goes, then you'd need to use dynamic SQL to produce a similar result. Of note, I've answered a similar question to this in the following link, if you want to see how to produce a HTML list as output from a parent/child table: https://stackoverflow.com/a/34445701/5552667

SSRS: Recursive Parent Child

2 Answers2

Linked