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 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 :(
None
0 Points
37 Posts
SSRS 2008 How to rollup values using a group and recursive parent
Oct 20, 2016 03:17 PM|schott19|LINK
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:
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
All-Star
17652 Points
3510 Posts
Re: SSRS 2008 How to rollup values using a group and recursive parent
Oct 21, 2016 11:57 AM|Chris Zhao|LINK
Hi schott19,
You could try setting something like this.
reference: http://blogs.microsoft.co.il/barbaro/2008/12/01/creating-sum-for-a-group-with-recursion-in-ssrs/
Best Regards,
Chris
None
0 Points
37 Posts
Re: SSRS 2008 How to rollup values using a group and recursive parent
Oct 21, 2016 12:41 PM|schott19|LINK
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