Problem in getting Grand total from running total

[maulae]

Problem in Salesperson commission report
I am working on Salesperson commission report and because of some complications in individual transactions I am having difficulty in creating final amount of the salesperson commission.
The scenario is, each sales person has more than one customer and each customer has more than one invoices, each invoice contains just one $ amount of the transaction but contains more than one miscellaneous charges and tax amount. The percentage commission rate is different for each invoice that should be applied on invoice amount minus totals of all related miscellaneous charge and taxes of that invoice.

Report Requirement: Calculate commissionable amount and net commission of each invoice, Salesperson and Grand total of all sales person.

Logic used: I have groups on Salesperson, Invoice and Invoice amount, I calculated running totals of Miscellaneous charges and tax amount grouped by each invoice amount group and got net commission by multiplying related rate, its working fine.

Problem: I need to produce Totals on each Salesperson and all salespersons, when I tried to produce further running total of each invoice it does not allow me. And I can not produce required totals on individual transaction because of single invoice amount and multiple miscellaneous charges plus tax amount that gives me commissionable amount.

I will appreciate if somebody has idea how to get salesperson commission total and grand total ?

 

[dgillz]

How did you produce running totals? Did you use a formula with the variables or did you create a running total object?

Is there a reason to have a running total as opposed to a total?

Please post back with your crystal version, database and connectivity used, sample data, current formulas used, desired output and current output.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[maulae]

Thanks for your quick response, I created my initial running total through running total objects,
I have my lowest group on Invoice amount and I took running total for invoice Misc charges and with their related tax amount then I subtracted these two totals from invoice amount to get the salesperson net commissionable amount, then I multiplied the percentage rate and get net commission of that salesperson for that invoice, up to here is fine but I can not use that net commission to get total of all invoices of that salesperson, each invoice has different percentage rate for same salesperson that also restricts me to create logic.
The reason to oppose a running total is that I have single invoice amount and multiple misc charges and charges and tax amount, I don’t know to handle that.
I am using crystal 11 and working on Progress database, I have created report directly on the tables and using ODBC,
Here is my sample data,
Salesperson1
Inv. #: 10
Amount : 355
Miisc Charges1: 84, Misc. Charges2: 16
Tax amount1: 23, Tax Amount: 2.00
Salesperson commission rate: 5%

Inv. #: 20
Amount : 450
Miisc Charges1: 120, Misc. Charges2: 30
Tax amount1: 30, Tax Amount: 8.00
Salesperson commission rate: 4%

And similarly multiple invoices for salesperson 2 and other

Formula used
1. InvTaxAmt = Running total for TaxAmount field grouped by Invoice amount
2. MiscAmountRT = Running Total for MiscAmount field grouped by Invoice amount

Next formula:
Commissionableamount= {InvcHead.InvoiceAmt}- {#InvTaxAmt}-({#MiscAmountRT})

Next Formula:
NetCommissionAmout: (CommissionableAmount * CommssionRape) /100

Required output

SalesPerson: 1
Invoice# amount Misc. Charges Tax Amount Comm_Able amt Rate Net commission

10 355 110 25.00 220 5% 11.00
20 450 120 30.00 300 4% 12.00

Totals: 805 230 55 520 23.00

=====================================================

Similarly for
SalesPerson: 2
Invoice# amount Misc. Charges Tax Amount Comm_Able amt Rate Net commission

5 200 100 30 70 3% 2.1
15 300 120 20 160 5% 8.00
25 400 200 30 170 4% 6.8

Totals: 900 420 80 300 16.9

==================================================

Grand Totals for all salesperson;

Invoice# amount Misc. Charges Tax Amount Comm_Able amt Rate Net commission
1705 650 135 820 39.9


I appreciate your time.