| Author |
Message |
DannyMack
Newbie
Joined: 08 Jul 2013
Online Status: Offline
Posts: 2
|
  
Topic: Total group formula
Posted: 08 Jul 2013 at 9:32am |
|
In my report I have 5 group levels. The detail consists of line items from invoices. Group 5 includes auto sums of sales, cost and margin from the detail by invoice. Group 5 also contains a formula to calculate commission. The other 4 groups also auto sum sales, cost and margin. I need these other groups to also sum the formula total from group 5 but I have not been able to figure out how to do this. (The commission has to be calculated at the invoice total (Group 5 level) and not at the line item detail level).
Edited by DannyMack - 08 Jul 2013 at 9:36am
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 09 Jul 2013 at 4:39am |
|
You could only calculate the group 5 values in the other group levels if they are at the last record for the group...or the values to be used in the calculation are in a field and always available...let me explain.
if you want the total invoice amount, and it is a simple sum of all the invoice lines, then you can get the value at any time and can use it at any level. If on the other hand your total for invoice involves some logic on the invoice lines, like don't add this one, and multiply this one by some value but don't multiply if other conditions are true, then you can't use the built in aggregates (sum/count/average etc) and the group 5 formula will only work in group 5 and on the last record for group 5 in all other levels
HTH
|
| IP Logged |
|
DannyMack
Newbie
Joined: 08 Jul 2013
Online Status: Offline
Posts: 2
|
Posted: 09 Jul 2013 at 6:09am |
|
I understand your explantion. I thought the built in aggregates would not work but I wanted confirmation to make sure I was not missing something. Since they will not work are there any other options to work around this limitation?
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 09 Jul 2013 at 8:59am |
|
The aggregates will work, as long as your report can use all of the data in a field. Then it is as simple as calling the aggregate with the correct grouping: SUM({table.field}, {group5}).
If you need to apply logic to modify the values by some other calculation, or to include/exclude values then the aggregates won't work, and you will need something like a running total or shared variables.
Does this help?
|
| IP Logged |
|
|
|
