title: SSRS Hierarchy Recursive parent - child with multiple parent
body: <p>Hi I need help with setup in SSRS to properly display children in multiple parents in Hierarchy report.</p>
  
  <p>This is what I have done. </p>
  
  <pre><code> select * from PCA
  </code></pre>
  
  <p>This is PCA table</p>
  
  <pre><code>Parent      Child
  ASSY1       CHILD1    
  ASSY1       CHILD2    
  CHILD1      ACHILD1   
  CHILD1      ACHILLD2  
  ACHILD1     BCHILD1   
  ACHILD1     BCHILD2   
  </code></pre>
  
  <p>This is the CTE in report dataset</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>This is the setup in SSRS rdl file. And the report when I run it. Group by Child, and I set the <strong>recursive parent</strong> as parent. I Also set the Group visibility so i get the plus sign to do drill down and also padding. all looks good.</p>
  
  <p><a href="https://i.stack.imgur.com/GInHa.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GInHa.jpg" alt="enter image description here"></a></p>
  
  <p>Padding left is set like so:</p>
  
  <pre><code>=20 * Level() &amp; "pt"
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/9qddu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9qddu.png" alt="enter image description here"></a></p>
  
  <p>UNTIL: I add one more row to the table.</p>
  
  <pre><code>Parent  Child
  ASSY1       CHILD1    
  ASSY1       CHILD2    
  CHILD1      ACHILD1   
  CHILD1      ACHILLD2  
  ACHILD1     BCHILD1   
  ACHILD1     BCHILD2   
  **ACHILLD2      BCHILD2**   
  </code></pre>
  
  <p>I expect something below ACHILLD2 
  But no: this is what I get</p>
  
  <p><a href="https://i.stack.imgur.com/kiZgS.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kiZgS.jpg" alt="enter image description here"></a></p>
  
  <p>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, </p>
  
  <p><a href="https://i.stack.imgur.com/fMK3c.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fMK3c.jpg" alt="enter image description here"></a></p>
  
  <p>But Instead I get this:
  <a href="https://i.stack.imgur.com/sdK34.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sdK34.png" alt="enter image description here"></a></p>