TOPN BASED ON PARENT CUSTOMER

[rossallen]

I have a report that lists 20 customers which I want to modify to show total sales based on the parent customer (though not all customers have parents). In the SQL database it would appear as follows:
RM_CUST RM_PARENT VALUE
Cust1 100.00
Cust2 20.00
Cust3 Cust2 10.00
Cust4 Cust2 30.00

Report should show
RM_CUST VALUE
Cust1 100.00
Cust2 60.00

The report in Crystal Version 9.2 is grouped by RM_CUST.
Do I have to have 2 subreports for customers that have parents and those that don't or could I use a formula at the group footer level to calculate and show the sales correctly?

 

[lbass]

Try inserting a group on this formula:

if not isnull({table.RM_PARENT}) then
{table.RM_PARENT} else
{table.RM_CUST}

Then right click on {table.value} in the detail section and insert a summary (sum). You can then go to report->topN and choose "sum of {table.value}" for the group sort.

-LB