How to set automatic numbering for multilevel group in SSRS report?-CodePudding

I am designing a SSRS report, which needs to generate automatic numbering for each group. The data comes from SQL query result, which looks like this:

Category        SubCategory       ItemName
------------------------------------------
Parent groupA   Parent group A1   item1
Parent groupA   Parent group A1   item2
Parent groupA   Parent group A2   item3
Parent groupA   Parent group A2   item4
Parent groupB   Parent group B1   item5
Parent groupB   Parent group B1   item6
Parent groupB   Parent group B2   item7
Parent groupB   Parent group B2   item8

My report layout:

1.Parent group A
   1.1 Parent group A1 
      1.1.1 Item 1
      1.1.2 Item 2
   1.2 Parent group A2
      1.2.1 Item 3
      1.2.2 Item 4
2. Parent group B
   2.1 Parent group B1 
      2.1.1 Item 5
      2.1.2 Item 6
   2.2 Parent group B2
      2.2.1 Item 7
      2.2.2 Item 8

I can't generate this rule by using RowNumber and RunningValue function, or haven't found a way. Hope to hear your idea. Thank you!

CodePudding user response：

This is a little verbose but should work.

All I've done here is calculate the notation and appended the column values to the end. You can then use this directly in the report.

I've reproduced your data as a table variable for illustration

DECLARE @t TABLE (Category varchar(30), SubCategory varchar(30) , ItemName varchar(30))
INSERT INTO @t VALUES 
('Parent groupA', 'Parent group A1', 'item1'),
('Parent groupA', 'Parent group A1', 'item2'),
('Parent groupA', 'Parent group A2', 'item3'),
('Parent groupA', 'Parent group A2', 'item4'),
('Parent groupB', 'Parent group B1', 'item5'),
('Parent groupB', 'Parent group B1', 'item6'),
('Parent groupB', 'Parent group B2', 'item7'),
('Parent groupB', 'Parent group B2', 'item8')

SELECT 
    *
    , CONCAT(DENSE_RANK() OVER(ORDER BY Category), '. ', Category) as CategoryID
    , CONCAT(
              DENSE_RANK() OVER(ORDER BY Category) , '.'
            , DENSE_RANK() OVER(PARTITION BY Category ORDER BY SubCategory), ' '
            , SubCategory
            ) as SubCategoryID
    , CONCAT(
              DENSE_RANK() OVER(ORDER BY Category) , '.'
            , DENSE_RANK() OVER(PARTITION BY Category ORDER BY SubCategory) , '.'
            , DENSE_RANK() OVER(PARTITION BY Category, SubCategory ORDER BY ItemName), ' '
            , ItemName
            ) as ItemID
    FROM @t

This gives the following results