Print Page | Close Window

How do I create a Sum total for multiple fields?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12527
Printed Date: 29 Mar 2024 at 3:16am


Topic: How do I create a Sum total for multiple fields?
Posted By: Repent34
Subject: How do I create a Sum total for multiple fields?
Date Posted: 07 Mar 2011 at 10:42am
How do I create a Sum total for multiple fields?

I have a DB with the following tables/fields:

Table=GLAccount
Field=GLAccountNumber

In the field value, there are 7 different account numbers that I want to sum up (there are many more, but only want to sum seven of them). The account numbers represent different product lines.
They are:

410000.00
412500.00
415000.00
417500.00
420000.00
421000.00
431000.00

Each of these account numbers has a value associated with it that represents a sales dollar figure.

I need a formula that will add up the values represented by the account numbers and place that value in the report footer next to a field I've labeled as "Balance per GL".

Can someone point me in the right direction?

thank you all;
chris
http://www.access-programmers.co.uk/forums/editpost.php?do=editpost&p=1048613">Edit/Delete%20Message



Replies:
Posted By: DBlank
Date Posted: 07 Mar 2011 at 10:57am
use a running total with an evaluate formula of
GLAccount.GLAccountNumber in (410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00)


Posted By: Repent34
Date Posted: 07 Mar 2011 at 11:25am
Originally posted by DBlank

use a running total with an evaluate formula of
GLAccount.GLAccountNumber in (410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00)


I found where to create a new running total but not sure what to put where?  When I selected GLAccount.GLAccountNumber as the "Field to Summerize" and selected "Use a formula" under "Evaluate" and plugged in the text you suggested I only get errors about the formula.  Can you help me further?


Posted By: DBlank
Date Posted: 07 Mar 2011 at 11:29am

sorry, original "in" formula had parenths () but should be brackets[]

create a RT
name = whatever you want
field to summarize = GLAccount.GLAccountNumber
type of sumamry = SUM
evaluate = use a formula
click on the formula box and add your condition formula here
{GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
reset = never
place in your report footer
 


Posted By: Repent34
Date Posted: 07 Mar 2011 at 12:00pm
Originally posted by DBlank

sorry, original "in" formula had parenths () but should be brackets[]

create a RT
name = whatever you want
field to summarize = GLAccount.GLAccountNumber
type of sumamry = SUM
evaluate = use a formula
click on the formula box and add your condition formula here
{GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
reset = never
place in your report footer
 


Dan;

I realized I left part of the puzzle out when I got an answer of $779 BILLION dollars in sales!!

Further explanation:
My (Sales) report reflects sales for different products. The products are represented by various "account numbers" There are maybe 100 account numbers. Some of the account numbers, those I listed, need to have the dollar values they represent summed up so that I can plug that answer into a box on the form (Balance Per GL).

Also, this form does not currently show the account numbers nor does it need to, however, the account numbers I mentioned all relate to a field called "MonthBeginBal". If I drop the 'GLAccount.GLAccountNumber" field and the 'GLAccount.MonthBeginBalance" onto the report, I can look down through the account numbers and in the second field I dropped onto the form('GLAccount.MonthBeginBalance"), I can see the dollar amount involved.

It is this dollar amount of the seven account numbers that I want to sum. There are several instances of duplication of the account numbers and the same dollar value because in the DB there could be several sales of that product. I only need it to sum once, see below:

410000.00 $500

410000.00 $500

410000.00 $500

412500.00 $800

412500.00 $800

415000.00 $200

415000.00 $200

The formula should only sum each account number dollar value once, so the answer to the above would be $1500.00

Thank you for looking at this Dan.




Posted By: DBlank
Date Posted: 07 Mar 2011 at 12:03pm
try:
{GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
and
next({GLAccount.GLAccountNumber})<>{GLAccount.GLAccountNumber}


Posted By: DBlank
Date Posted: 07 Mar 2011 at 12:08pm
hopefully you also figured out the field to SUM is your $500,$800,$200 field
if the previous() function does not work, make a formula field
if {GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
 then {GLAccount.amount_field} else 0
then  in your RT change it as
name = whatever you want
field to summarize = new Formula field
type of summary = SUM
evaluate = on change of field (GLAccountNumber)
reset = never
place in your report footer
 


Posted By: Repent34
Date Posted: 07 Mar 2011 at 12:43pm
Originally posted by DBlank

hopefully you also figured out the field to SUM is your $500,$800,$200 field
if the previous() function does not work, make a formula field
if {GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
 then {GLAccount.amount_field} else 0
then  in your RT change it as
name = whatever you want
field to summarize = new Formula field
type of summary = SUM
evaluate = on change of field (GLAccountNumber)
reset = never
place in your report footer
 


Sorry Dan, I'm new to alot of this.  The formula field mentioned above didn't work.  Where do I create this "formula field" and is it replacing the Running Total field created at the beginning of this?




Posted By: DBlank
Date Posted: 08 Mar 2011 at 4:24am
The formuila field will be used with the Running Total.
You create the formula field in the field explorer under formula fields
right click and select new
name it whatever you want, i will call it 'Amount_W_0s'
add in the formula you want it to do
if {GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
 then {GLAccount.amount_field} else 0
replace the blue portion with the actual field from your db
save it.
drag and drop it onto your report canvas next to your other value so it should look something like:
Acct#                    AMount         'Amount_W_0s'
410000.00           $500                $500
410000.00           $500                $500
410000.00           $500                $500
412500.00           $800                 $800
412500.00           $800                 $800
412325.00           $900                 $0
412325.00           $900                 $0
412325.00           $900                 $0
415000.00           $200                 $200

Now update your RT as

name = whatever
field to sumamrize = @Amount_W_0s (your new formula field)
type of summary= SUM
evaluate = on change of record, select the GLAccountNumber field
reset = never
 
you can place it on the detail section to see how it calcualtes row by row


Posted By: Repent34
Date Posted: 08 Mar 2011 at 8:44am
Originally posted by DBlank

The formuila field will be used with the Running Total.
You create the formula field in the field explorer under formula fields
right click and select new
name it whatever you want, i will call it 'Amount_W_0s'
add in the formula you want it to do
if {GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
 then {GLAccount.amount_field} else 0
replace the blue portion with the actual field from your db
save it.
drag and drop it onto your report canvas next to your other value so it should look something like:
Acct#                    AMount         'Amount_W_0s'
410000.00           $500                $500
410000.00           $500                $500
410000.00           $500                $500
412500.00           $800                 $800
412500.00           $800                 $800
412325.00           $900                 $0
412325.00           $900                 $0
412325.00           $900                 $0
415000.00           $200                 $200

Now update your RT as

name = whatever
field to sumamrize = @Amount_W_0s (your new formula field)
type of summary= SUM
evaluate = on change of record, select the GLAccountNumber field
reset = never
 
you can place it on the detail section to see how it calcualtes row by row


Dan;

We are soooo close....I plugged in everything as described.  I'm still getting a HUGE total amount but only because the report is summing up ALL instances of these account numbers, even though the amount is still the same within each account number.  Is there a way to have the report/formula only count one instance of the account numbers in the formula?  I think that would be the finishing touch.

Before I think I mentioned that each account number represents a different product when apparently each account number represents a different "line" of products, thusly in the report, there can be dozens of the same account number, because the account numbers represent a line of products.

How do I get the formula to stop counting/summing up each account number after it finds the first one?

chris




Posted By: DBlank
Date Posted: 08 Mar 2011 at 8:48am

are the "account number" fields in order across the entire report so that you never jump back to one

like
410000.00 
410000.00  
410000.00  
412500.00     
412500.00   
412325.00
 
and NOT like
 
410000.00 
412500.00   
410000.00  
410000.00  
412500.00     
412500.00   
412325.00


Posted By: Repent34
Date Posted: 08 Mar 2011 at 8:53am
Originally posted by DBlank

are the "account number" fields in order across the entire report so that you never jump back to one

like
410000.00 
410000.00  
410000.00  
412500.00     
412500.00   
412325.00
 
and NOT like
 
410000.00 
412500.00   
410000.00  
410000.00  
412500.00     
412500.00   
412325.00


Yes, currently there is no grouping applied to the records.  There currently is record sorting by GLAccount number. so I see the accounts listed in numerical order as you have in the first example.

In it's finished form, the report and records would be sorted by CustomerCatNo(Sales Region) and then by Invoice number.

chris



Posted By: DBlank
Date Posted: 08 Mar 2011 at 8:58am
IN your RT change is the evaluate section set to
'On change of field' and the GLAccount number field selected?


Posted By: Repent34
Date Posted: 08 Mar 2011 at 9:19am
Originally posted by DBlank

IN your RT change is the evaluate section set to
'On change of field' and the GLAccount number field selected?


Yes here is a screen clip:

http://i686.photobucket.com/albums/vv225/Repent34/RTclipforDan.jpg




Posted By: DBlank
Date Posted: 08 Mar 2011 at 9:25am

can you post your final @GLAcctBalRunningTotalFormula



Posted By: Repent34
Date Posted: 08 Mar 2011 at 9:29am
Originally posted by DBlank

can you post your final @GLAcctBalRunningTotalFormula



You bet

http://i686.photobucket.com/albums/vv225/Repent34/RTclipforDan-1.jpg



Posted By: DBlank
Date Posted: 08 Mar 2011 at 9:35am
Everything seems in order. To trouble shoot, on the detail section set the GLacct# field, next to the MOnthBeginValue next to the @GLAcctBalRunningTotalFormula next to the Running Total
it should look like this
Acct#                    AMount         'Amount_W_0s'    RT
410000.00           $500                $500                  500
410000.00           $500                $500                  500
410000.00           $500                $500                  500
412500.00           $800                 $800                 1300
412500.00           $800                 $800                 1300
412325.00           $900                 $0                     1300
412325.00           $900                 $0                     1300
412325.00           $900                 $0                     1300
415000.00           $200                 $200                 1500
 
What does yours look like?


Posted By: Repent34
Date Posted: 08 Mar 2011 at 11:24am
Dan;

I had a slight mistake on my end that I fixed.

Here is what it looks like now.  Notice the:

Report Total Sales is $801,761,156.70  should be $1,556,817.78
Report total Adjustments is $30,284,266.00 should be $58,804.40
Report Net Sales $ 771,476,890.70  Should be $1,498,013.38
Balance per GL is $-1,498,013,38 so this is correct
Difference is $0.00  and is correct

The report is also 3332 pages long instead of the usual 9 or so pages.

Here are some screen shots showing the progression of some of the account numbers so you can get the idea.  I also included the last page of the report.

http://s686.photobucket.com/albums/vv225/Repent34/




Posted By: DBlank
Date Posted: 08 Mar 2011 at 11:43am
just guessing here...
You likely need to make similar running totals to get your other values.
YOu may have a cartesian join (I think that is what it is called) going on which is duplicating your rows and making the report length huge as well as your other values huge
 
 
 


Posted By: DBlank
Date Posted: 08 Mar 2011 at 11:49am
In File > Report Options there is a check box for 'Select Distinct Records'. it does not work with all data types but you might try that too.


Posted By: Repent34
Date Posted: 08 Mar 2011 at 12:18pm
I do notice that when I go to the DB Expert and the links tab to look at my joins, then go to close the DB expert, it warns me that "Your current link configuration contains multiple starting points.  Please be advised that this is generally not supported."  Could this be the issue?


Posted By: Repent34
Date Posted: 08 Mar 2011 at 12:22pm
I tried the distinct records option to no effect.




Posted By: Repent34
Date Posted: 08 Mar 2011 at 12:53pm
Dan, do you know how to stop the report from refreshing itself every minute or so?  To do anything (Dev) I have to wait for it to finish scanning/reloading all 3332 pages.  This is when I'm looking at the "Preview" tab.


Posted By: Keikoku
Date Posted: 09 Mar 2011 at 2:42am
Do you have "save data with report" option checked? If not, it will do another query whenever you run preview. I don't know why it would refresh itself "every minute or so" though.

And for development purposes, you should filter your query so that it only retrieves a dozen or so records. Or anything you need to verify your report is correct.

Chances are if it works for 100 records then it also works for 10000 records.


Posted By: DBlank
Date Posted: 09 Mar 2011 at 3:43am
when i work on a report with a lot of data I work in the Design mode to avoid the wait tiome for adding new data to the field and only move to the preview mode when I need to validate or check how things look.
 
as fo the link issue how are your tables linked now?


Posted By: Repent34
Date Posted: 09 Mar 2011 at 8:08am
Originally posted by Repent34

Dan, do you know how to stop the report from refreshing itself every minute or so?  To do anything (Dev) I have to wait for it to finish scanning/reloading all 3332 pages.  This is when I'm looking at the "Preview" tab.


I figured out my problem here so I thought I'd post it for those doing a Google search of this issue.

Ok I figured out the problem.  You may want to file this away in your memory banks…..


 I noted that the refresh of the records in CR was happening all on its own, whether or not I was working in CR or just letting it sit.  My laptop is a Macbook Pro running Parallels 6 with a Win7 Ultimate virtual machine.  I do all my CR Dev in Parallels/Win7.  What was happening was that, in the options for Parallels, there is an option to keep the clock for the virtual machine sync’d with the Mac.  I turned that option off and the problem went away.  Turned it back on, and the problem was back.  Turned it off again, problem gone. 


 Since the clocks were updating with each other every minute to stay sync’d, this sync event was noticed by the Win7 operating system, thus causing CR to refresh its open report every minute.


 I could see this also happening if using a third party system clock sync program, especially if it’s set to a short update time value.


I only wish I was as good at Crystal Reports Development as I am troubleshooting this kind of stuff!!


Blessings;


Chris





Print Page | Close Window