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 PCAThis is PCA table
Parent Child
ASSY1 CHILD1
ASSY1 CHILD2
CHILD1 ACHILD1
CHILD1 ACHILLD2
ACHILD1 BCHILD1
ACHILD1 BCHILD2This 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 tBOMThis 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.
Padding left is set like so:
=20 * Level() & "pt"
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
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,
But Instead I get this:
2条答案
按热度按时间tzdcorbm1#
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
rbl8hiat2#
We faced the same issue in SSRS and we solve it with combined ids and parentIds (concat) in order to get unique keys for recursive chain. Taking your example:
| Id | Parent | Child | ParentId |
| ------------ | ------------ | ------------ | ------------ |
| a | ASSY1 | CHILD1 | null |
| a | ASSY1 | CHILD2 | null |
| b-c | CHILD1 | ACHILD1 | a |
| b-d | CHILD1 | ACHILLD2 | a |
| c-e | ACHILD1 | BCHILD1 | b-c |
| c-f | ACHILD1 | BCHILD2 | b-c |
| d-f | ACHILLD2 | BCHILD2 | b-d |