SSRS:递归父级子级

问题描述：

我想使用此简单表在SSRS中创建分层报告吗? 请你帮助我好吗?我在这里遵循该教程: https://www.mssqltips.com/sqlservertip/1939/working-with-a-parent-child-hierarchy-in-sql-server-reporting-services-ssrs/

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/

但是我不知道EmployeeKey和ParentEmployeeKey如何应用于我的表.就我的表而言，Col2是雇员，而Col1是父母.但是在SSRS中，当我按Col2分组并且递归父级为COL1时，我没有得到想要的结果.

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.

这是我的桌子:

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

这是我想使用SSRS获得的结果.像图片的缩进样式..

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  ║  ║
╚═══════════════╩══╝

我不知道上面的缩进样式结果.我是否需要使用HierarchyID和IsDescendantOf或递归CTE. 这是我所做的递归CTE及其结果.

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

答

这对任何深度都适用(假设您未为SORT_PATH传递VARCHAR(50)).向下浏览层次结构时，将构建一个字符串，可以按(SORT_PATH)对其进行排序.最后，我们可以将REPLICATE()与BOMLevel一起使用以缩进某些字符(在本例中为连字符).

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

SSRS:递归父级子级

相关推荐