Sql-server – SSRS Hierarchy Recursive parent – child with multiple parent

common-table-expressionhierarchyreporting-servicessql-serverssrs-2008

Hi I need help with setup in SSRS to properly display children in multiple parents in Hierarchy report.

This is what I have done. 

 select * from PCA

This is PCA table

Parent      Child
ASSY1       CHILD1    
ASSY1       CHILD2    
CHILD1      ACHILD1   
CHILD1      ACHILLD2  
ACHILD1     BCHILD1   
ACHILD1     BCHILD2

This is the CTE in report dataset

 WITH tBOM AS(
 SELECT Parent,Child, 0 as BOMLevel from PCA A
 WHERE Parent='ASSY1'
 UNION ALL
 SELECT C.Parent, C.Child, BOMLevel+1 from PCA C
 INNER JOIN tBOM on tBOM.Child=C.Parent
 )
 SELECT row_number() over (Order by Parent), * FROM tBOM

This is the setup in SSRS rdl file. And the report when I run it. Group by Child, and I set the recursive parent as parent. I Also set the Group visibility so i get the plus sign to do drill down and also padding. all looks good.

enter image description here

Padding left is set like so:

=20 * Level() & "pt"

enter image description here

UNTIL: I add one more row to the table.

Parent  Child
ASSY1       CHILD1    
ASSY1       CHILD2    
CHILD1      ACHILD1   
CHILD1      ACHILLD2  
ACHILD1     BCHILD1   
ACHILD1     BCHILD2   
**ACHILLD2      BCHILD2**

I expect something below ACHILLD2
But no: this is what I get

enter image description here

MORE to this. if I add more rows to increase the depth , the result will be more incorrect. For example if I add a children to BCHILD2,

enter image description here

But Instead I get this:
enter image description here

Best Solution

After reading hours of article. I come to the conclusion that SSRS is not able to achive my end goal. sad I know. but it is what it is

https://connect.microsoft.com/SQLServer/feedback/details/724449/bug-found-when-using-the-ssrs-recursive-hierarchy-feature-with-multi-parent-relationships

Bug Found When using the SSRS Recursive Hierarchy Feature with Multi-Parent Relationships - by MichaelLee

Status : Closed as By Design By Design

The product team believes this item works according to its intended design.

A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Description

In a SSRS report, you can create a recursive hierarchy with drill down controls by following this article: http://msdn.microsoft.com/en-us/library/bb630438(v=SQL.100).aspx

However, it seems that this feature does not work correctly when a child has multiple parents. If a child has two parents, you would expect that a child is placed under each parent. Instead, the child is placed only under the parent that appears first in the SQL table. If you add an additional column to the tablix and set the expression to '=CountRows("RowGroupName",Recursive)', you'll notice that the total records for the child is 2. So for some reason both records are being placed below the first parent, even though one of the records has a different parentid.

Note that in my situation, the child and parent ids are of the type uniqueidentifier.

DETAILS Comments (2) | Workarounds (3) | Attachments (0) Sign in to post a comment. Posted by Ibrahim Achkar on 4/20/2012 at 6:10 AM Hello Michael, I also found this bug. Any update concerning a resolution from Microsoft. As for your workaround, do you suggest getting the data and setting it in a new temp table in a different (1-to-1 relationship) format. With the new table at hand, do we still need to use the parent grouping in SSRS or another method is required?

Your assistance is highly appreciated.

Thank you, Ibrahim Posted by Riccardo [MSFT] on 2/13/2012 at 5:34 PM Thanks for your feedback. We're resolving this bug as By Design because we associate each group with a single parent group. We don't associate a group with multiple parent groups (but feel free to create a Suggestion for this capability).

To further explain the expected behavior, we first group the data based on the GroupExpression, or the ChildID field in this case. Then we evaluate the Parent expression, in this case the ParentID field, for each group. Since we associate each group with a single parent group, we need a single value. When an expression that should return a single value refers to fields in a scope with multiple data rows - this case is just one example - the behavior is officially undefined, but in practice, it tends to behave like the First aggregate function, which takes the first data row in scope.

Riccardo Muti SQL Server Reporting Services

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a65bf4a4-e3b6-4c33-aa9e-6f7d7e4b7f5e/bill-of-materials-recursive-parent-report?forum=sqlreportingservices

Hi duanekae, Just to confirm, I never had an adequate solution using SSRS for this. I have ended up using a custom SQL function to do this. I would love the get it working in SSRS though as this would be a much easier solution. Wednesday, January 13, 2016 4:04 PM Quote Avatar of Alex Lush - Severn Unival Alex Lush - Severn Unival

Related Question