BusinessObjects Board

How to find sum of individual groups in parent child relationship in crystal report

building reports crystal reports
#1

Hi Experts,
I have a table with columns like this

ID	       Description	               P_ID
201          P      
202          B                          201
203          B                          201
204          B		                    201
205          P		
206          B		                    205
207          B                          205

It is the P_ID which determines child relationship with the parent. So 202,203,204 are children of 201 hence count 3. Similarly 206 and 207 are children of 205 hence count is 2

 ID          Description          Parent Qty               Child Qty
201             P                     1                          3
205             P                     1                          2

I tried to create two groups i) P_ID ii) ID and calculated the sum but I am getting ‘5’ instead of individual for each group (3 and 2). Please advise how can I achieve this. Any help will be greatly appreciated.
Thanks!!

#2

If the P_ID field is in your data, I would do it like this, assuming that you’re just linking tables together:

  1. Add a second copy of the table to your report. When you do this Crystal will show a warning message that asks whether you want to “alias” the table and it will then add it to the report with “_1” on the end of the table name.

  2. Link from the ID of the original table to the P_ID in the aliased table.

  3. In the Select Expert, add this to the select formula:

IsNull({original_table.P_ID})

  1. Group by {original_table.P_ID}

  2. Suppress the group header and details section.

  3. Put the data in the group footer section, using the summary count of {table_1.ID} to get the Child Qty value.

-Dell