Hi, I am using a hierarchical tablix (defining the parent-child ID's) within a Tablix (SSRS 2008). I have a basically 3 columns being returned, NAME, MONTHINDEX, AMOUNT where the rows and the NAME and the columns are the MONTHINDEX and the values are the AMOUNT. The column therefore gets a default scope of MONTHINDEX which I assume is then applied to the scope for all columns and in which case when I do a recursive SUM such as SUM(Fields!Amount.Value, "MonthIndex", Recursive) All of my columns end up with the same value. What I need is the value to be recursive only in that column. Is this a limitation with SSRS, or is there a workaround? Thanks.

I haven't seen problems with the recursive behavior of the Tablix so I assume the issue has something to do with your data or the Tablix properties. What columnsare used to define the parent and child keys? Your detailsshould be grouped on the primary key (child key) and the parent group expression (on the Advanced page of the detail group dialog) should be set to the parent key. If these are set correctly, the aggregates should be rolling up within the recursive groups. Are records being arranged according to the recursive hierarchy?Paul Turley [Hitachi Consulting] SQLServerBIBlog.com

Yes, my parent-child relationships are set correctly and the data is viewing as a hierarchy fine. I have crreated a data set using a Table and one using the matrix in order to replicate each other adn the table does show correctly. Below is a copy of my RDL (the two datasets run against the master database - dont actually access any data though) if you are keen to see what I am referring to. The matrix is on the left and the table on the right. <?xml version="1.0" encoding="utf-8"?> <Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"> <DataSources> <DataSource Name="DataSource1"> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>Data Source=localhost\sqlexpress;Initial Catalog=master</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> <rd:DataSourceID>b02d3d7e-d6d1-438f-8479-27b1db42a6a4</rd:DataSourceID> <rd:SecurityType>Windows</rd:SecurityType> </DataSource> </DataSources> <DataSets> <DataSet Name="DataSet2"> <Fields> <Field Name="ID"> <DataField>ID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="NAME"> <DataField>NAME</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="PARENTID"> <DataField>PARENTID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="MONTHINDEX"> <DataField>MONTHINDEX</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="AMOUNT"> <DataField>AMOUNT</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>DataSource1</DataSourceName> <CommandText>SELECT 1 AS ID, 'Parent 1' AS NAME, NULL AS PARENTID, 0 AS MONTHINDEX, 0 AS AMOUNT UNION SELECT 4 AS ID, 'Parent 2' AS NAME, NULL AS PARENTID, 0 AS MONTHINDEX, 0 AS AMOUNT UNION SELECT 2 AS ID, 'Child 1' AS NAME, 1 AS PARENTID, 2 AS MONTHINDEX, 100 AS AMOUNT UNION SELECT 2 AS ID, 'Child 1' AS NAME, 1 AS PARENTID, 3 AS MONTHINDEX, 300 AS AMOUNT UNION SELECT 3 AS ID, 'Child 2' AS NAME, 2 AS PARENTID, 3 AS MONTHINDEX, 200 AS AMOUNT UNION SELECT 3 AS ID, 'Child 2' AS NAME, 2 AS PARENTID, 2 AS MONTHINDEX, 200 AS AMOUNT</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> <DataSet Name="DataSet1"> <Fields> <Field Name="ID"> <DataField>ID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="NAME"> <DataField>NAME</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="PARENTID"> <DataField>PARENTID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="COL0"> <DataField>COL0</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="COL1"> <DataField>COL1</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="COL2"> <DataField>COL2</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> </Fields> <Query> <DataSourceName>DataSource1</DataSourceName> <CommandText>SELECT 1 AS ID, 'Parent 1' AS NAME, NULL AS PARENTID, 0 AS COL0, 0 AS COL1, 0 AS COL2 UNION SELECT 2 AS ID, 'Child 1' AS NAME, 1 AS PARENTID, 0 AS COL0, 100 AS COL1, 200 AS COL2 UNION SELECT 3 AS ID, 'Child 2' AS NAME, 2 AS PARENTID, 0 AS COL0, 100 AS COL1, 200 AS COL2 UNION SELECT 4 AS ID, 'Parent 2' AS NAME, NULL AS PARENTID, 0 AS COL0, 0 AS COL1, 0 AS COL2</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> </DataSet> </DataSets> <Body> <ReportItems> <Tablix Name="Tablix4"> <TablixCorner> <TablixCornerRows> <TablixCornerRow> <TablixCornerCell> <CellContents> <Textbox Name="Textbox13"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>NAME</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox13</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCornerCell> </TablixCornerRow> </TablixCornerRows> </TablixCorner> <TablixBody> <TablixColumns> <TablixColumn> <Width>1.51042in</Width> </TablixColumn> </TablixColumns> <TablixRows> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="AMOUNT"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Sum(Fields!AMOUNT.Value, "MONTHINDEX", Recursive)</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>AMOUNT</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> <rd:Selected>true</rd:Selected> </CellContents> </TablixCell> </TablixCells> </TablixRow> </TablixRows> </TablixBody> <TablixColumnHierarchy> <TablixMembers> <TablixMember> <Group Name="MONTHINDEX"> <GroupExpressions> <GroupExpression>=Fields!MONTHINDEX.Value</GroupExpression> </GroupExpressions> </Group> <SortExpressions> <SortExpression> <Value>=Fields!MONTHINDEX.Value</Value> </SortExpression> </SortExpressions> <TablixHeader> <Size>0.25in</Size> <CellContents> <Textbox Name="MONTHINDEX"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!MONTHINDEX.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>MONTHINDEX</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> </TablixMembers> </TablixColumnHierarchy> <TablixRowHierarchy> <TablixMembers> <TablixMember> <Group Name="NAME"> <GroupExpressions> <GroupExpression>=Fields!ID.Value</GroupExpression> </GroupExpressions> <Parent>=Fields!PARENTID.Value</Parent> </Group> <SortExpressions> <SortExpression> <Value>=Fields!NAME.Value</Value> </SortExpression> </SortExpressions> <TablixHeader> <Size>1.59375in</Size> <CellContents> <Textbox Name="NAME"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!NAME.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>NAME</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>=Level() * 15 &amp; "pt"</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> </TablixMembers> </TablixRowHierarchy> <DataSetName>DataSet2</DataSetName> <Top>0.18417in</Top> <Left>0.10083in</Left> <Height>0.5in</Height> <Width>3.10417in</Width> <Style> <Border> <Style>None</Style> </Border> </Style> </Tablix> <Tablix Name="Tablix5"> <TablixBody> <TablixColumns> <TablixColumn> <Width>1in</Width> </TablixColumn> <TablixColumn> <Width>1in</Width> </TablixColumn> <TablixColumn> <Width>1in</Width> </TablixColumn> <TablixColumn> <Width>1in</Width> </TablixColumn> </TablixColumns> <TablixRows> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox14"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>NAME</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox14</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox16"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>COL0</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox16</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox18"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>COL1</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox18</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox20"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>COL2</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox20</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="NAME1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!NAME.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>NAME1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>=Level() * 15 &amp; "pt"</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="COL0"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!COL0.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>COL0</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="COL1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=SUM(Fields!COL1.Value, "Details", Recursive)</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>COL1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="COL2"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=SUM(Fields!COL2.Value, "Details", Recursive)</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>COL2</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> </TablixRows> </TablixBody> <TablixColumnHierarchy> <TablixMembers> <TablixMember /> <TablixMember /> <TablixMember /> <TablixMember /> </TablixMembers> </TablixColumnHierarchy> <TablixRowHierarchy> <TablixMembers> <TablixMember> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <Group Name="Details"> <GroupExpressions> <GroupExpression>=Fields!ID.Value</GroupExpression> </GroupExpressions> <Parent>=Fields!PARENTID.Value</Parent> </Group> </TablixMember> </TablixMembers> </TablixRowHierarchy> <DataSetName>DataSet1</DataSetName> <Top>0.18417in</Top> <Left>3.35417in</Left> <Height>0.5in</Height> <Width>4in</Width> <ZIndex>1</ZIndex> <Style> <Border> <Style>None</Style> </Border> </Style> </Tablix> </ReportItems> <Height>26.49084mm</Height> <Style> <Border> <Style>None</Style> </Border> </Style> </Body> <Code>Function GetSomething() return Report End Function</Code> <Width>198.17292mm</Width> <Page> <PageFooter> <Height>11.43mm</Height> <PrintOnFirstPage>true</PrintOnFirstPage> <PrintOnLastPage>true</PrintOnLastPage> <ReportItems> <Textbox Name="ExecutionTime"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Globals!ExecutionTime</Value> <Style /> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>ExecutionTime</rd:DefaultName> <Top>5.08mm</Top> <Left>101.6mm</Left> <Height>6.35mm</Height> <Width>50.8mm</Width> <Style> <Border> <Style>None</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </ReportItems> <Style> <Border> <Style>None</Style> </Border> </Style> </PageFooter> <LeftMargin>1in</LeftMargin> <RightMargin>1in</RightMargin> <TopMargin>1in</TopMargin> <BottomMargin>1in</BottomMargin> <Style /> </Page> <rd:ReportID>37455ff8-327f-4b71-bad2-c730cf95ef97</rd:ReportID> <rd:ReportUnitType>Inch</rd:ReportUnitType> </Report>

Hi, Unfortunately, the scenario you described is not supported so far (both 2008 and 2005), because it cannot do Recursive sum in two scopes in a matrix. If you replace the expression with SUM(Fields!Amount.Value, "NAME", Recursive), you will get the recursive total, but the tota willl ignore the monthindex group. Actually, you want to recursive sum for both groups NAME and MonthIndex, however you cannot write expression like this: SUM(Fields!Amount.Value, "NAME" and MonthIndex, Recursive). Right? So generally speaking, in a matrix, when you write the expression like this: SUM(Fields!Amount.Value) It will sum in the scope of two groupsNAME and MonthIndex, and if you want to specify the scope, you have to specify only one scope. And unfortunately, the recursive sum needs to specify the scope and scenario you described needs to specify two scopes. The workaround is returning the total in total area not the detail area. See: Name 0 2 3 total Parent1 800 Child1 100 300 800 Child2 200 200 400 Parent2 0 Total 0 300 500 800 I know this is not what you want, but as I mentioned above, you can only run the recursive sum in the group subtotal. I am sorry to say that. For more information, see: http://blogs.microsoft.co.il/blogs/barbaro/archive/2009/04/22/recursive-sum-for-a-group-in-a-matrix-in-ssrs.aspx Hope this helps. Raymond

I know this thread is a few years old, but I only just stumbled across it today. I just want to add in another suggestion for a workaround, which might be useful in some cases: Rather than having SSRS computing the recursive sum, you can let the SQL-server itself do so. You will have to write som nasty T-SQL, though :) Here's the how-to: Recursive Queries Using Common Table Expressions Cheers, Jørn Schou-Rode http://malamute.dk