Start Free Trial

asked on

Sum a formula field containing running total field

ok, so I have a report that calculates the sales contribution per broker. Because of the way the database is configured and because a broker can represent one or both sides a sale, I am using a repeater table to be able to return a record back two times in the report in the event that said broker on both sides of the sale. This allows me to calculate their respective commission on both sides of the sale.

With that said, I am trying to sum the commissions for each broker in two different columns that represent each side of the sale - buy side and sell side. I was able to construct everything using groups and running totals, however I am unable to generate the Grand Totals from the running totals. Here are buyside examples of the fields and formulas I have set up:

Col 1 - Buy Side sales running total per broker (this ensures that only buyside sales are totaled).
{#buy_contr}

Col 2 - Commission Rate
{@rate_buyside} - calculates the commission rate on a sliding scale.

Col 3 - Commission
{@comm_buyside} - Formula: {#buy_contr}*({@rate_inv}/100)

The problem I am having here is that I want to sum Col 3 {@comm_buyside} in the report footer, but I get the error that you can't sum a running total field (even when its inside a formula field). Alternatively, I tried creating a running total field of Col 3 in the RF instead, but that did not work either - {@comm_buyside} is not an avail field to choose from in the "create running total" window...I assume b/c the formula itself is a running total.

I need to use this col 3 field b/c each broker's commission rate dependent on the volume they do so can be different than other brokers. So somehow I need to sum {@comm_buyside} in the RF.

Please let me know if I need to clarify anything.

total formulas and running totals can't be totaled in Crystal.

You can accumulate the desired values via a global variable.
Or perhaps switch from Running Totals to a simple SUM of a detail-level formula of:
IF Condition = True THEN <Value to Sum> ELSE 0
You can SUM that formula at any level.

ASKER

the problem is a sum of detail cant be done b/c the commission rate depends on the sum total for Group 1 (Broker). then I need to take all those sums and add them together.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

error adding the new col 3 formula you suggested.

got an error saying "the remaining text does not appear to be part of the formula" for the second part of the formula:

CurrentCommission := {#buy_contr}*({@rate_inv}/100);
TotalCommission := TotalCommission + CurrentCommission ;
CurrentCommission

I copied and pasted the formula you posted for the detail section...did I implement it wrong or is there something missing in the formula?

If that is the full formula you missed the first 3 lines that declare the variables

If you included the first 3 lines there is a ; missing at the end of the 3rd line
WhilePrintingRecords;
Global NumberVar TotalCommission;
Local NumberVar CurrentCommission;

mlmcc

ASKER

i was able to get rid of using running totals ({#buy_contr}) in group 1 and work with a regular summing functions. I just had to alter one of my detail formulas to only return sales data per record if certain conditions were meet. but this still leaves me with the issue of not being able to sum in the report footer a formula field (col 3) that contains a reference to a sum field/function.

ASKER

thats it, the missing semi-colon on line three. thanks. I'll see if this all works.

If you can get regular summary function to work you should be able to simply put them in the report footer.

mlmcc

ASKER

Does this strictly work for running totals or will it also work if I changed all the columns to use a summary function/field in place of the running total field? Because right now the totals in the group footer are correct but the total in the report footer is not even close to the right number...much higher than is should be.

ASKER

well, i still use col 3, but it uses sum formula instead of the running total.

Col 3 - Commission {@comm_buyside}

This:
Sum ({@Contribution_Inv}, {@Broker})*({@Rate_Inv}/100)
Instead of this:
{#buy_contr}*({@rate_inv}/100)

but the new formula of {@comm_buyside} still can't be summed in the report footer.

SInce the formula has a summary in it it can't be summarized

mlmcc

Have you tried mlmcc's formulas with the Sum function instead of the running total?

James

ASKER

James - I did, I tried but the report footer field he suggested returns a total that is way above what it should be.

Can you upload the report?

mlmcc

ASKER

I'll see what I can do, just concerned about posting sensitive company data.

No need to have data. I just want to see where the formulas are.

mlmcc

ASKER

Okay, will do. Traveling home and in traffic, will post later this evening as I have a long ways to go.

You probably have something like multiple records per broker and the formula that adds to the variable is using the total for the broker, but you put the formula in the detail section, so the total for each broker is added for each record.

If it's something like that, you'd want to put the formula that adds to the variable in a broker group header or footer, so that the figure for each broker is only added once.

But that's just a guess. It would probably help if we could see the report, as mlmcc suggested.

James

ASKER

Yes, James I do have multiple records per broker. I'll try you suggestion and then post by report if it does not work.

ASKER

how do I make sure there is no data in the report before I post it here? whats the best way to do that?

There is an option to SAVE DATA WITH REPORT
IN the full versions it is under the FILE menu
Uncheck it and save the report

If you can't find it add a filter that is always false then save the report

mlmcc

ASKER

ok, I also just ran the report on a day that has not data so it comes back blank...that should work too i think.

ASKER

I tried what James suggested, but it did not work, the report total was closer (lower total) but still way off.

Please note, that the names of the formulas and columns in the actual report are different from what I posted b/c I was trying to make it easier to relate as the real fields and formulas are not as straight forward.

{#buy_contr} = {@Contribution_Inv}
{@rate_buyside} = {@Rate_Inv}
{@comm_buyside} = {@Comm_Inv}
MLMCC's formulas - {@Formula - Header}, {@Formula - Detail}, {@Formula - Footer}

You'll see the columns I am referencing highlighted in Yellow in the report.
Sales-Commision-Report---EE.rpt

Try this version.

You had the detail formula in both the group header and footer. It should have been in only the footer

The one in the group header should have been the one you had in the page header

The formula in the page header should have been in the report header.
Sales-Commision-Report---EE-Rev1.rpt

There is currently a bug in the EE file download.
Right click the file
CLick SAVE TARGET SAS
Change the extension to RPT
CLick SAVE
WHen you get the popup that the file is saved - Click OPEN

mlmcc

ASKER

Ok, on the road now so will check later tonight.

ASKER

ok, so I give it a try and unfortunately the @formula - footer you created (when placed in the report footer) does not provide a sum for all the totals in each Group Footer 1. For example:

GF1 for Commission field ("MLMCC's Formula") would show:
10
20
5
5
15
MLMCC's Report Footer {@Formula - Footer} should sum these group totals and display 55, but instead its just returning the total in the last GF1...15.

Any ideas?

Remove the formula from the group header.

mlmcc

ASKER

mlmcc, that worked!! So do you have a link or something where I can read up on these kinds of global and local formulas so I can better understand them and how to construct and incorporate them?

ASKER

For my own records I am posting the final solution here:

Place Header Formula in the RH
WhilePrintingRecords;
Global NumberVar TotalCommission;
TotalCommission := 0;
''

Place GF1 (Detail Formula) in the PH & GF
WhilePrintingRecords;
Global NumberVar TotalCommission;
Local NumberVar CurrentCommission;

CurrentCommission := Sum ({@Contribution_Inv}, {@Broker})*({@rate_inv}/100);
TotalCommission := TotalCommission + CurrentCommission ;
CurrentCommission

Place Footer Formula in the RF
WhilePrintingRecords;
Global NumberVar TotalCommission;
TotalCommission

ASKER

Excellent Help with this!!