Solved

# Sum a formula field containing running total field

Posted on 2014-07-24
1,391 Views
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).

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

Col 3 - Commission

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.
0
Question by:IO_Dork
• 18
• 10
• 2
• +1

LVL 22

Expert Comment

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.
0

Author Comment

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.
0

LVL 100

Accepted Solution

mlmcc earned 500 total points
Try changing the COL3 formula

``````WhilePrintingRecords;
Global NumberVar TotalCommission;
TotalCommission := 0;
''
``````

New Col3 formula

``````WhilePrintingRecords;
Global NumberVar TotalCommission;
Local NumberVar CurrentCommission

TotalCommission := TotalCommission + CurrentCommission ;
CurrentCommission

``````

In the report footer add a formula to display the total
``````WhilePrintingRecords;
Global NumberVar TotalCommission;
TotalCommission
``````

mlmcc
0

Author Comment

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:

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?
0

LVL 100

Expert Comment

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
0

Author Comment

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.
0

Author Comment

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

LVL 100

Expert Comment

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

mlmcc
0

Author Comment

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.
0

Author Comment

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

This:
Sum ({@Contribution_Inv}, {@Broker})*({@Rate_Inv}/100)

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

LVL 100

Expert Comment

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

mlmcc
0

LVL 34

Expert Comment

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

James
0

Author Comment

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

LVL 100

Expert Comment

mlmcc
0

Author Comment

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

LVL 100

Expert Comment

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

mlmcc
0

Author Comment

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

LVL 34

Expert Comment

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
0

Author Comment

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

Author Comment

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

LVL 100

Expert Comment

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
0

Author Comment

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.
0

Author Comment

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.

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
0

LVL 100

Expert Comment

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
0

LVL 100

Expert Comment

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
0

Author Comment

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

Author Comment

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?
0

LVL 100

Expert Comment

Remove the formula from the group header.

mlmcc
0

Author Comment

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?
0

Author Comment

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
0

Author Closing Comment

Excellent Help with this!!
0

## Featured Post

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirementsâ€¦
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater â€¦
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax â€” just include tâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦