locked
SSRS 2008 How to rollup values using a group and recursive parent RRS feed

  • Question

  • User1955997337 posted

    Hi,

    Im having an issue where I can't get my row values to total upwards when using a tablix and the 'recursive parent' option.  The following should help explain what im trying to achieve:

    My Dataset:

    ;with cols as
    (
     SELECT 1 colID, 'C1' col
     UNION SELECT 2, 'C2' 
    )
    , rows as
    (
     SELECT 1 RowID, 'R1' row, null ParentID
     UNION SELECT 2, 'R2', 1
     UNION SELECT 3, 'R3', 2 
     UNION SELECT 4, 'R4', 2 
     UNION SELECT 5, 'R5', 1 
     UNION SELECT 6, 'R6', 1 
    )
    ,data
    AS
    (
     SELECT 3 RowID, 1 as Amount
     UNION SELECT 4 RowID, 2 as Amount
    )
    SELECT r.RowID, r.row, c.colID, c.col, d.Amount, r.ParentID
    FROM rows r 
    CROSS JOIN cols c
    LEFT JOIN data d on d.RowID = r.RowID

    In my 'RowGroup' properties I go to 'advanced' and then set 'Recursive parent' to be 'ParentID'  and I've also set the padding left to be '=level()*20 & "pt"' to show the indent of the parent - child relationship

    My matrix configuration and output looks like the following.  Notice how R1 and R2 have no values.  I want them to show '3' as this is the rolled up total of the children rows.

    Thanks for all your help

    Thursday, October 20, 2016 3:17 PM

All replies

  • User-2057865890 posted

    Hi schott19,

    You could try setting something like this.

    Sum(Fields!XXXXX.Value,"Group1",Recursive)

    reference: http://blogs.microsoft.co.il/barbaro/2008/12/01/creating-sum-for-a-group-with-recursion-in-ssrs/ 

    Best Regards,

    Chris

    Friday, October 21, 2016 11:57 AM
  • User1955997337 posted

    Hi Chris,

    Thanks for you reply.  You are correct - this works when there is one column but when I have a dynamic number of columns it sums all of the columns and applied that value to the roll up.

    Sorry - I should of said in my initial post that I have multiple columns and not just one.  I don't believe it is possible as you need to consider the scope of the row and column.  SUM only allows one scope hence summing across all columns :(

    Thanks

    Dave

    Friday, October 21, 2016 12:41 PM