Solved

Sum a formula field containing running total field

Posted on 2014-07-24
31
1,391 Views
Last Modified: 2014-07-31
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.
0
Comment
Question by:IO_Dork
  • 18
  • 10
  • 2
  • +1
31 Comments
 
LVL 22

Expert Comment

by:Ido Millet
Comment Utility
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

by:IO_Dork
Comment Utility
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

by:
mlmcc earned 500 total points
Comment Utility
Try changing the COL3 formula

In the report header add a formula
WhilePrintingRecords;
Global NumberVar TotalCommission;
TotalCommission := 0;
''

Open in new window



New Col3 formula

WhilePrintingRecords;
Global NumberVar TotalCommission;
Local NumberVar CurrentCommission

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

Open in new window


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

Open in new window


mlmcc
0
 

Author Comment

by:IO_Dork
Comment Utility
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?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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

by:IO_Dork
Comment Utility
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

by:IO_Dork
Comment Utility
thats it, the missing semi-colon on line three. thanks.  I'll see if this all works.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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

by:IO_Dork
Comment Utility
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

by:IO_Dork
Comment Utility
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.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
SInce the formula has a summary in it it can't be summarized

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Have you tried mlmcc's formulas with the Sum function instead of the running total?

 James
0
 

Author Comment

by:IO_Dork
Comment Utility
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

by:mlmcc
Comment Utility
Can you upload the report?

mlmcc
0
 

Author Comment

by:IO_Dork
Comment Utility
I'll see what I can do, just concerned about posting sensitive company data.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
No need to have data.  I just want to see where the formulas are.

mlmcc
0
 

Author Comment

by:IO_Dork
Comment Utility
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

by:James0628
Comment Utility
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

by:IO_Dork
Comment Utility
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

by:IO_Dork
Comment Utility
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

by:mlmcc
Comment Utility
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

by:IO_Dork
Comment Utility
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

by:IO_Dork
Comment Utility
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
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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

by:mlmcc
Comment Utility
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
0
 

Author Comment

by:IO_Dork
Comment Utility
Ok, on the road now so will check later tonight.
0
 

Author Comment

by:IO_Dork
Comment Utility
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

by:mlmcc
Comment Utility
Remove the formula from the group header.

mlmcc
0
 

Author Comment

by:IO_Dork
Comment Utility
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

by:IO_Dork
Comment Utility
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

by:IO_Dork
Comment Utility
Excellent Help with this!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now