Issue with Subtotals 1

[MikeCitizens]

Is there a way to add Subtotal figures together?

Here is my issue

I have a report that sorts Customers under their sales rep. Each customer has contracts underneath them. Each Customer has a Contract limit. Each Contract has a Profit number.

For Risk management purposes we only show 15% of the Contract limit

To get that I use the formula {FXLimits.SpotLimit}*.15

For each contract the Limit is present for but really I only want it listed once for each customer. So if I take a straight up sum of the limits I have a number way out of line. If I take the average for each customer I recieve the number I'm looking for.

Now Is there a way to take that Average total for each of the customers under a sales rep to give me a grand total for each Sales Rep?

Example

Sales Rep has three customers
Each Customer has 3 contracts
Each of these customers has a contract limit of 20 million.
15% of 20 million is 3 million
under the Sales Rep I need it to show 9 million
Currently after trying to subtotal I get 27 Million

Please let me know if anyone has run into this before

Is there a way to total subtotals??

Mike @ citizens

 

[Ngolem]

do this calculation in the customer footer rather than the detail section. Then it will be executed only once/customer.

In the Sales Rep header initialize the sum

//@InitSubtotal (suppressed)
WhilePrintingRecords;
numberVar SubTotal := 0;

In the Customer footer calc and add them up

//@CalcSubtotal (suppressed)
WhilePrintingRecords;
numberVar SubTotal ;

//this will take the last value of the limit
//for each customer but if all limited are the same , no problem
Subtotal := Subtotal + {FXLimits.SpotLimit} *.15 ;

Then in Sales Rep footer display the result

//@DisplaySubtotal
WhilePrintingRecords;
numberVar SubTotal ;

SubTotal ;

That should do it


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[MikeCitizens]

OK I have done what you said. However when checking the formula Subtotal := Subtotal + {FXLimits.Spot Limit} *.15
The system tells me that a number is required before the second Subtotal in the formula.

Mike

 

[Ngolem]

either it doesn't like "Subtotal" as a variable name...change to "Stotal" in all formulas...

or

{FXLimits.Spot Limit} is not numeric

if that is the case wrap it in Tonumber()

ie.

sTotal := sTotal + (ToNumber({FXLimits.Spot Limit} ) * 0.15);

NOTE: Change sTotal everywhere

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[MikeCitizens]

Hey thanks for your help, it worked for me.