Summing Running Totals 1

[ninjak]

Hello,

Our database has transactions grouped into several types, let's just call them Group A, Group B, etc. I'm making a report where I list the sum of all transactions for all groups, ex:

Group A $500000 5%
Group B $300000 3%
etc...

Total $10000000 100%

Where the dollar amount is the sum of all transactions of that type and the % is the percentage that each group contributes to the total revenue. So I have a group in the report and I'm outputting the group data in the group footer and then the total data in the report footer.

Recently I've had to change the way I calculate revenue. Based on certain logic, I have to use one field in the database sometimes and other times use another field. To implement this I'm evaluating based on a formula in my running total fields, something like:

({Claim.type} = 1)

for one type and then

not({claim.type} = 1) for the other type.

Then I have a formula to calculate everything based on the two sums.

Now everything works fine EXCEPT for calculating the percentage of total revenue that each group represents. I can no longer do Sum{RunningTotalField} to get the total revenue from all transactions, so I don't know what to divide by to get my %age. Right now I have hard coded the divisor but obviously there should be a better way.

I hope I didn't confuse everyone too much. If you have any suggestions please let me know. Thanks a lot.

 

[lbass]

Instead of using running totals, why not use conditional formulas, as in:

if {table.claim} = 1 then {table.amt}

if {table.claim} <> 1 then {table.amt}

Then you could insert summaries on these, and use formulas wiht subtotals for your percentages instead of running totals.

-LB

 

[ninjak]

Thanks for the help, that worked perfectly. I didn't know you could Sum() formulas. What are the exact rules as to what you can Sum() and what you cannot?

 

[lbass]

Any formula where "insert summary" is enabled can be summarized. The only caution is that you should not use "count" on a conditional formula, as it will simply count all instances of the formula that are not null, and a distinctcount will count all distinct instances that meet your criteria and add a one for any that don't, so the result will be off by one, EXCEPT when all records meet your criteria. There is a solution to this, which is to create a null formula that then is referenced as the default value in the conditional formula that you are distinct counting.

-LB