Solved

Sum a formula field containing running total field

Posted on 2014-07-24
31
1,579 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 10
  • 2
  • +1
31 Comments
 
LVL 22

Expert Comment

by:Ido Millet
ID: 40217391
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
ID: 40217741
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
ID: 40217745
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:IO_Dork
ID: 40219831
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
ID: 40219844
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
ID: 40219855
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
ID: 40219858
thats it, the missing semi-colon on line three. thanks.  I'll see if this all works.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40219867
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
ID: 40219868
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
ID: 40219886
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
ID: 40220040
SInce the formula has a summary in it it can't be summarized

mlmcc
0
 
LVL 35

Expert Comment

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

 James
0
 

Author Comment

by:IO_Dork
ID: 40220098
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
ID: 40220138
Can you upload the report?

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 40220292
I'll see what I can do, just concerned about posting sensitive company data.
0
 
LVL 100

Expert Comment

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

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 40220385
Okay, will do. Traveling home and in traffic, will post later this evening as I have a long ways to go.
0
 
LVL 35

Expert Comment

by:James0628
ID: 40220409
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
ID: 40230008
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
ID: 40230173
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
ID: 40230194
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
ID: 40230213
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
ID: 40230230
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
ID: 40230362
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
ID: 40230370
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
ID: 40230479
Ok, on the road now so will check later tonight.
0
 

Author Comment

by:IO_Dork
ID: 40232096
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
ID: 40232248
Remove the formula from the group header.

mlmcc
0
 

Author Comment

by:IO_Dork
ID: 40232275
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
ID: 40232321
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
ID: 40232352
Excellent Help with this!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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